July 19, 2006 at 1:27 pm
I am trying to import an Excel File into SQL Server 2000 Database using SSIS. Also tried DTS, etc.
The issue is that in Excel, there is a column of data that needs to come over exactly the way it is shown in Excel:
examples:
0+00
27+36
0+8
If I use the SSIS's Data Conversion Data Flow task and chance the column to String, it brings in the data like:
0
2736
8
I tried formatting it in Xcel, but it changes it to the results above.
Any suggetions would be appreciated.
Thanks,
Daniel
July 20, 2006 at 9:32 am
You may want to try adding IMEX=1 to your connection string (just before the end-quotes). This will force all columns to be treated as strings, so you may end up with more work, but it may better preserve the values you're trying to keep. Just use a Data Conversion task to change types afterwards to whatever you need.
-Pete
July 20, 2006 at 11:31 am
Where\how do I add the 'IMEX=1' to the connection string in my SSIS package? Do I go to properties on the Excel Source?
Thanks for your help.
Daniel
July 20, 2006 at 12:54 pm
Yes, the properties window for your Excel Source. Look for the Connection String property and add IMEX=1 (with ; before or after as appropriate to separate the parameters). You may need to refresh the connection after doing that to reflect the new source datatypes.
-Pete
July 20, 2006 at 4:42 pm
Thanks for your help. But there is still an issue with it. The data from the Excel spreadsheet was imported in from a different data source, and I am still unable to bring the data in from that one column as the way it is stored.
I can't reproduce this issue when I create a new spreadsheet and create the same data manually.
Wierd.
Thanks,
Daniel
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply