question

jmb4 avatar image
jmb4 asked

Integration services 2005-Excel connection manager not reading....

character text when first 50 rows are numbers in a column, works for CSV's but I need this to work for Excel. Are there ways around this? Thanks J
ssis
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tim avatar image
Tim answered
Have you tried looking at a data conversion task to change the data type that SSIS is assuming the column is?
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Unfortunately, the data conversion only converts data once it has been loaded from the Excel file, but this is too late. The data conversion cannot take place at the same time as the data load.
0 Likes 0 ·
Good to know. I haven't come across this issue before so I will make a note of this as a just in case.
0 Likes 0 ·
Daniel Ross avatar image
Daniel Ross answered
Add IMEX=1; into the connection string, this tells excel to read everything as text no matter what. Then you can do your data conversions later in the package. Check out connectionstrings.com, a great developers resource.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

jmb4 avatar image
jmb4 answered
Thanks Daniel. Interestingly the IMEX property for the Excel connection is already= 1. However I googled IMEX=1 and found that I will need to change the registry settings for Excel.. Sledge hamer to crack a nut..This can cause issue going forward. Either way when I look at the connection previewer for Excel with IMEX at 1 in the conection string the columns with text data still show as null. Do'h...
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

jmb4 avatar image
jmb4 answered
Found a way to do this. Thanks to all for your help. Just modify the reg keys below: Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/TypeGuessRows (Change the value for TypeGuessRows to zero) Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/ImportMixedTypes (change this to Text but it should already be text) Also ensure IMEX=1 in the connection string for the Excel connection properties. I didn't need to change the data type afterwards, it retains the DTSTR (string type) and the previewer now has the correct values not just the nulls.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.