x

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
more ▼

asked Oct 02, 2011 at 05:18 AM in Default

jmb4 gravatar image

jmb4
41 11 12 13

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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.
more ▼

answered Oct 02, 2011 at 07:15 AM

Daniel Ross gravatar image

Daniel Ross
2.9k 11 13 14

(comments are locked)
10|1200 characters needed characters left
Have you tried looking at a data conversion task to change the data type that SSIS is assuming the column is?
more ▼

answered Oct 02, 2011 at 06:21 AM

Tim gravatar image

Tim
36.4k 39 41 139

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.
Oct 02, 2011 at 06:49 AM jmb4
Good to know. I haven't come across this issue before so I will make a note of this as a just in case.
Oct 02, 2011 at 08:35 AM Tim
(comments are locked)
10|1200 characters needed characters left

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...

more ▼

answered Oct 02, 2011 at 11:34 AM

jmb4 gravatar image

jmb4
41 11 12 13

(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Oct 02, 2011 at 02:11 PM

jmb4 gravatar image

jmb4
41 11 12 13

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x946

asked: Oct 02, 2011 at 05:18 AM

Seen: 639 times

Last Updated: Oct 02, 2011 at 05:18 AM