April 5, 2010 at 11:54 am
Hi All,
I am running a SSIS package in SSK5 which loads data in some database tables from an excel file placed in a shared location.
The excel has 8 columns each of which are mapped to an equivalent field in a table in the database. Needless to say the datatypes of the fields are also similar.
Some of the columns in the excel are of datatype datetime, decimal (18,10) and int. I have attached a sample excel for reference.
The excel always does not have values for every field except the File# field which is the primary key in the database.
When I load the data into the database by running the SSIS package, I observe a strange behavior -
1) Scenario 1 - When the 1st row of the excel does not have any value for the fields Contract Start Date,Contract End Date,Ads Contracted & Rate, none of the data for these columns for the rest of the rows of the excel are populated into the database.
2) Scenario 2 - When the 1st row of the excel has some data for the fields Contract Start Date,Contract End Date,Ads Contracted & Rate, all the data in the excel for all the rows are populated in the database.
The datatype for Contract Start Date & COntract End Date are Datetime, Ads Contracted is int and Rate is Decimal (18,10).
I have tried loading the data using -
A) A SQL task where I run a query
INSERT INTO [DBTable]
(FieldList)
SELECT * FROM OPENDATASOURCE ('Microsoft.Jet.OleDB.4.0',
'Data Source=E:\Test\dataLoad.xls; extended Properties=Excel 8.0') WHERE [FILE #] is not null
B) By using data flow task where I use an Excel SOurce and OLEDB Destination.
I am observing this strange behavior in both the occasions.
This issue has left me at wits end and I dont see any logic behind it.
ANY HELP IN THIS REGARD WILL BE HIGHLY APPRECIATED.
Cheers,
Sumon
April 5, 2010 at 12:16 pm
Sumon,
I build a SSIS package using the your attached spreadsheet and using Excel Source and Ole DB source. I was able to reload the data with success.
I am attaching the screenshot from the SQL query output of the loaded data.
April 5, 2010 at 12:38 pm
Ohh thats great!
I am uploading another file which has more data. Can you please try this one? Please let me know if this one works fine for you as well or not?
April 6, 2010 at 11:05 am
are you getting any errors or warnings when you execute the package? My initial guess is that SSIS is trying to guess the datatypes, and when it sees the first row is blank, it may choose the wrong type. Are you getting nulls in the final table?
April 7, 2010 at 12:04 am
aharmon-1091890 (4/6/2010)
are you getting any errors or warnings when you execute the package? My initial guess is that SSIS is trying to guess the datatypes, and when it sees the first row is blank, it may choose the wrong type. Are you getting nulls in the final table?
I have to go with Aharmon here. Try putting IMEX=1 in your connection string.
For more information, see this URL:
http://swap.wordpress.com/2007/09/18/solution-for-excel-data-uploading-problem-using-ssis/
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 7, 2010 at 5:16 am
Agreed. IMEX=1 solved a very similar problem I had. This only seems to be an issue with the excel data source. Give it a try and let us know.
April 8, 2010 at 11:44 pm
Yes it works!!! thanks a ton guys!!
March 29, 2014 at 4:34 am
hi,
anybody please help me how can I load the data from excel to DB with using of SSIS package please share with screen shots....
March 29, 2014 at 7:54 am
swathy.reddy7 (3/29/2014)
hi,anybody please help me how can I load the data from excel to DB with using of SSIS package please share with screen shots....
Did you try Google?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply