|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Thursday, October 01, 2009 11:57 AM
Points: 2,
Visits: 75
|
|
|
|
|
|
Hall of Fame
Group: General Forum Members
Last Login: Monday, November 02, 2009 9:26 AM
Points: 3,280,
Visits: 962
|
|
Useful one. Good for the new bies in SSIS. Also the links provided at the end are useful. :)
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Monday, April 28, 2008 10:06 AM
Points: 1,
Visits: 8
|
|
Hi,
This is an old problem with importing Excel, we just put in a dummy first row with data in the form rewuired for each column, e.g. TEXT for text, 1234 for number, etc. , then delete the first record imported. tends to solve the problem for us.
Pete Wiggins
|
|
|
|
|
Valued Member
Group: General Forum Members
Last Login: Yesterday @ 10:32 AM
Points: 66,
Visits: 267
|
|
One could just save the Excel as a CSV file, load the file via BULK INSERT or OPENROWSET and be done.
Why go through hoops on and develop SSIS package? The portability and predictability of SSIS isn't that great anyway.
my 2 cents...
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: Wednesday, November 04, 2009 8:33 AM
Points: 1,203,
Visits: 577
|
|
I've had a similar problem - ended up resolving the problem as follows:
1) save the data as csv
2) create the table with the correct datatypes before importing.
That worked for me, and was alot simpler; although I believe your solution does help demonstrate some of the capabilties of SSIS.
Mark
|
|
|
|
|
Grasshopper
Group: General Forum Members
Last Login: Friday, September 11, 2009 10:16 AM
Points: 10,
Visits: 32
|
|
Much easier way is:
Load the Excel file with First Row as Header off even it contains the header. This can ensure all the fields are read in text format. And then add a Conditional Split to skip the first row.
|
|
|
|
|
SSCertifiable
Group: General Forum Members
Last Login: Today @ 6:14 PM
Points: 7,314,
Visits: 6,859
|
|
|
|
|
|
SSC-Addicted
Group: General Forum Members
Last Login: 2 days ago @ 2:41 PM
Points: 426,
Visits: 222
|
|
| I may be missing something, but couldn't you just modify the select clause on the spreadsheet and manually cast to the correct datatype?
|
|
|
|
|
Grasshopper
Group: General Forum Members
Last Login: Thursday, September 24, 2009 4:17 PM
Points: 10,
Visits: 39
|
|
| Call me crazy, but I think I would do a global change on the column to eliminate the dash.
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Wednesday, October 21, 2009 8:11 PM
Points: 3,
Visits: 28
|
|
I found an easier way using steps in this MS KB article on the very same subject. I tried it and it works very well.
Solution : Set ConnectionString property of Excel source file as following
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=filepath\filename.xls;Extended Properties=”EXCEL 8.0;HDR=YES;IMEX=1“;
NOTE: Setting IMEX=1 tells the driver to use Import mode. In this state, the registry setting ImportMixedTypes=Text will be noticed. This forces mixed data to be converted to text. For this to work reliably, you may also have to modify the registry setting, TypeGuessRows=8. The ISAM driver by default looks at the first eight rows and from that sampling determines the datatype. If this eight row sampling is all numeric, then setting IMEX=1 will not convert the default datatype to Text; it will remain numeric.
You must be careful that IMEX=1 not be used indiscriminately. This is IMPORT mode, so the results may be unpredictable if you try to do appends or updates of data in this mode.
Reference : http://support.microsoft.com/kb/194124/en-us
|
|
|
|
