Showing posts with label excel. Show all posts
Showing posts with label excel. Show all posts

Monday, March 26, 2012

IF construct in DTS

Hi All,

I am new to DTS. I have to pick data from a excel sheet and put it in SQL Server table. If the column value is N/A in the excel then I should load NULL in the target table. Else the value. This is being the case I wrote this construct and it seems to load only NULLs into the target and the values are not getting loaded.

If Trim(UCase(DTSSource("ABC transport"))) = "N/A" Then DTSDestination("ABC_transport") = NULL Else DTSDestination("ABC_transport") = DTSSource("ABC transport")
Main = DTSTransformStat_OK

Thanks in advance.

Regards,
Sathishtry the code below, use the line breaks I have...

If Trim(UCase(DTSSource("ABC transport"))) = "N/A" Then
DTSDestination("ABC_transport") = NULL
Else
DTSDestination("ABC_transport") = DTSSource("ABC transport")
End If
Main = DTSTransformStat_OK|||Hi,

Thanks for the reply.

I tried it, still it loads only the NULL and not the values.

Regards,
Sathish|||Well the syntax is correct.

I'd check the source data. Is excel displaying N/A when there is something else in the cell? I know it will sometimes show rounded figures when the cell hold the full data.

Can you post a sample of the excel spreadsheet?|||Originally posted by rokslide
Well the syntax is correct.

I'd check the source data. Is excel displaying N/A when there is something else in the cell? I know it will sometimes show rounded figures when the cell hold the full data.

Can you post a sample of the excel spreadsheet?|||Okie,... well there are 1000 non N/A records, 5159 N/A records and 1 null,... how many non N/A records are you getting loaded in your db table?? 6159 or 5159?|||Hi,

That was pretty fast. I am getting loaded with 6159 NULLs.

Regards,
Sathish|||using the dts wizard to import the spreadsheet to a temp ABC transport table and using the following code...

'************************************************* *********************
' Visual Basic Transformation Script
' Copy each source column to the
' destination column
'************************************************* ***********************

Function Main()
DTSDestination("Renewal Month") = DTSSource("Renewal Month")
If Trim(UCase(DTSSource("ABC transport"))) = "N/A" Then
DTSDestination("ABC transport") = NULL
Else
DTSDestination("ABC transport") = DTSSource("ABC transport")
End If
Main = DTSTransformStat_OK
End Function

I get it with values where they are meant to be...

Wanna try this as a test to try and locate your problem?|||Not much luck. Still its not working for me. Let me try for some time to see whether I can get it.

Thank you very much for the help.

Regards,
Sathish|||No worries. Let me know if you need more help, sorry I couldn't solve your problem.|||The problem is with the excel driver - the excel driver attempts to guess at the datatype based on the first X number of entries. Save the excel file as a csv file and do the same dts script using the csv file and it will work.|||By if he is using VBScript to do the transformation then it would be using it's typing (eg. everything is a string) to do the comparison...

Also, wouldn't that also mean that my test transformation wouldn't work where it did work?

Friday, March 23, 2012

IE 7.0.5335.5 Beta 2 and SSRS 2000

Hi,
I'm trying to track and issue with IE 7 and reporting services.
First off, the report export to Excel works fine using IE 6
In IE 7 when I initially generate the report from a link in our website, the
report comes up correctly, but when I attempt to export that report to xls,
PDF, TIFF, HTML, etc I get an error that says:
"Internet Explorer cannon download Format=EXCEL from 192.168.0.250.
Internet Explorer was unable to open this Internet Site. The requested site
is either unavailable or cannot be found. Please try again later."
The site and format that it is trying to download is correct, but the
Certificate on the site is not working right (Known issue on our end). I
have added this site to the "Trusted Sites" under Internet Explorer"
Security.
Does anyone have a clue where this could come from?
Thanks
TrentAre you using SSL?
I had the same problem and found this topic which fixed my problem :
http://groups.google.co.nz/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/739f695f20237190/199c56a3165f6735?q=https&rnum=2#199c56a3165f6735|||That fixed it! Thank you for your response.
FYI - Solution is to uncheck "Do not save encrypted pages to disk"
This may be a long term security issue, but that is for another discussion.
Thanks again,
TTU
"CPNZ" wrote:
> Are you using SSL?
> I had the same problem and found this topic which fixed my problem :
> http://groups.google.co.nz/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/739f695f20237190/199c56a3165f6735?q=https&rnum=2#199c56a3165f6735
>sql

Wednesday, March 7, 2012

Identity column question

Trying to create a custom dts package that imports data from excel spreadsheet to db. One of the issues is that the columns in the main entity tables are not identity columns. So how do you get the appropriate PK?

Well I thought the following would work but it doesn't. Any ideas?

Basically trying to use the variable as the starting point in the temp identity declaration. Is this a syntax error or a "no-no".

declare @.maxid int
select @.maxid = (select max(objectid)+1 from object)

create table #service (
ObjectId int identity (@.maxid,1),
ModifiedAt datetime,
ModifiedBy int,
ObjectTypeId int,
[Name] nvarchar(100),
ServiceType nvarchar(100),
CategoryId int,
IsVisible int)

insert into #service (ModifiedAt,ModifiedBy,ObjectTypeId,[Name],ServiceType,IsVisible)
select getdate(),1,1,service,ServiceType,1 from importcreate table #service (
ObjectId int identity (1,1),
ModifiedAt datetime,
ModifiedBy int,
ObjectTypeId int,
[Name] nvarchar(100),
ServiceType nvarchar(100),
CategoryId int,
IsVisible int)

DBCC CHECKIDENT (#service, RESEED, @.maxid)|||thank-you very much