Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
csharp22
Starting Member
1 Post |
 Posted - 2009-01-23 : 13:38:14
|
| Hi in one of my SSIS package i am importing data from excel to flat text filemy problem is in one of the excel column i have numbers as well as text data.when i am importing data from excel to flat text files..i am only getting numbers ....the text data is missing ( i am getting a blank space)any suggestions |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2009-01-23 : 15:00:03
|
| It's assuming the column is numeric. The fix for this is easy. I'm assuming you're using an excel connection manager in your package. Click on the connection manager and get it's properties window open. In the connection string add "IMEX=1". To test this I created an excel sheet with 1 column with a value of xyz and a value of 123. When I previewed the data from within ssis it showed me null, 123. As soon as I updated the connection string and let the metadata of my excel task refresh the string value started showing up. IMEX=1 tells sql to treat everything as strings on import. You can convert back to numbers if you need to in a seperate task item before outputting to the flat file. Connection string example belowProvider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Projects\ExcelAlphaNumeric\Test1.xls;Extended Properties="EXCEL 8.0;HDR=YES;IMEX=1";Mike"oh, that monkey is going to pay" |
 |
|
|
|
|
|
import from excel to flat file