Wednesday, March 21, 2012

Importing from Excel to Sql Server 2005

I have about 50 Excel files from which I have to import data with some transformations to Sql Server. My first approach was to use Excel Source component in a data flow to read the data . However, as it turned out column X in some files was being converted to a DT_NTEXT blob and in other files it was mapped to WSTR. The reason I guess is because column X contained string of varying sizes - some greater than 255 characters while others less than 255 ( max was 3000 ) . My package used a ForEach loop to iterate over all the Excel files in a directory and feed that to the data flow task. I played around with IMEX and TypeGuessRows setting but they didnt help me . In my second approach I used 2 Excel sources ; one set up for the blob type and the other for the string type . I joined them together using a precedence contraint. This worked but I then figured out that there were 2 other columns in my data that exhibited the same behavior. I couldnt continue with the mulitple Excel source approach cause I would then have 8 Excel source components. Finally, I played around with Execute SQL Task . I selected the columns X,Y and Z , initialized 3 variables of type Object , used a ForEach to enumerate over the dataset and feed that to a script component that converted the objects to Strings. This seems to work for all types of data in the mulitple columns.

My question - has anyone encountered such problem ? What was the solution ? Just thought I would share this with the rest of the community. I cant seem to recall what the exact error I was getting ..but it was something like "cant convert long data to string " or something . I also keep getting annoying error icons in my Excel Source components used in the foreach loop. Something to do with acquire connection failed even after I set DelayValidation to "true".

Thanks

Another approach would be to use a conditional split component to redirect the two sets of files to two different data conversion components. You can then use the conversion components to convert the column X in both sets of files to Strings.

Carla

|||

Hi:

You have hit the most troublesome issue with the Jet Excel engine. I have found no easy way around for this problem. I have worked around the issue by having a dummy row in the second row of all my files. This dummy row contains dummy text >255 chars for all columns that can have >255 chars. My typeguessRows registry key is set to 2 and my connection strings have IMEX=1. This forces all these fields to type DT_NText.

I tried casting datatype in my select query, but Jet does not seem to support it. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1758786&SiteID=1

Please let me know if you find a solution.

HTH

Kar

|||

The solution was to use the third option which is using Execute SQL Task and mapping the troublesome columns to variable of Object type . You would then use a script component to cast the columns to String type. That seems to work right now.

Let me know if you have any further questions.

No comments:

Post a Comment