October 7, 2004 at 12:43 am
I've created a DTS package to perform an import of an Excel data range and the import is failing because one of the columns in the spreadsheet contains a mix of datatypes (specifically, integers and strings). All of the string data is coming is as NULL, making me suspect that DTS has cast the source field as an integer.
The destination field is a varchar - I want to import all of the mixed integer/string data and cast as string.
Any ideas what I need to do?
Thanks for any input
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 7, 2004 at 5:22 am
If dts is not playing the game properly the only thing you can really do, is to do the export through an activex task.
That way you can format the data from the source to match the destination type before it gets there and ensure the insert occurs. It does slow things down a little, but if you have no other choice I would go this way.
Also a good way of dealing with nulls as you can specify a replacement value for them.
October 7, 2004 at 6:19 am
I had a simmilar issue and used a sql task to solve the problem.
here's the syntax if you want to adapt it and try.
INSERT INTO Ref_Rebate_Index
SELECT F4 AS Cust
, F5 AS LVL
, CONVERT(decimal(4,2),F6) AS CoYS
FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0; HDR=NO; IMEX=1;Database=\\NTsvr12\MIS\Rebate\Rebate Index Current Month.xls'
, 'SELECT * FROM [Rebate Index$] where F4 is not null and F4 not like ''par%'''
)
October 8, 2004 at 1:12 am
When importing from excel, SQLServer checks a predetermined number of rows to determine the datatype. IIRC, the default is 8-10 rows. If your first 8-10 rows contain integers, it will set the datatype as an integer. One way to correct this is to edit the spreadsheet to make sure that the first two rows contain both datatypes. It should then set the datatype to varchar. Additionally, there is a registry edit that can change the lookahead value, but I can't recall the correct entry right now.
Hope this helps.
Gordon Pollokoff
"Wile E. is my reality, Bugs Bunny is my goal" - Chuck Jones
October 8, 2004 at 1:19 am
It does help - thanks.
I had to get the job done yesterday, so I ended up mapping all of the strings to integers to get the import to work, then writing an UPDATE query in SQL Server to set the imported integers back to the correct string values. One of those jobs that should have taken 10 minutes ...
Next time, I'll know what to do.
Enjoy your weekend
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 8, 2004 at 2:25 am
Another way to solve this is to set the Excel connection parameters before running the data pump. This can be done with an ActiveX script as follows (VBScript):
Function Main()
'Prepares Excel prior to uploading the information
dim pkg
dim conXL
dim prp
dim objFSO
set pkg = DTSGlobalVariables.Parent
set conXL = pkg.Connections("InputExcelFile")
conXL.ConnectionProperties("Extended Properties").Value = "Excel 8.0;HDR=YES;IMEX=1"
Main = DTSTaskExecResult_Success
End Function
However, there may still be problems; having done this with a spreadsheet from an accounting depratment we then found that numbers formatted with () to show negative amounts wouldn't import to numeric fields. We used an ActiveX transformation to detect and remedy this one.
October 10, 2004 at 5:34 pm
Thanks Bill. What an ace surname!
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 20, 2006 at 5:11 am
Thanks alot. It's great!
April 20, 2006 at 8:28 am
Two other ideas for future reference:
1) You can edit the transformation step to define the data types
2) Make the transformation step itself a VB script. Doing so, you can check values as they pass through.
(Sorry, I don't have SS2K EM available or I'd be more detailed.)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply