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