SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Strange issue while loading data from Excel using SSIS package


Strange issue while loading data from Excel using SSIS package

Author
Message
sumon.mukherjee
sumon.mukherjee
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 Visits: 57
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
Attachments
dataLoad.xls (76 views, 18.00 KB)
Shivanand Kamath
Shivanand Kamath
Old Hand
Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)

Group: General Forum Members
Points: 366 Visits: 80
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.
Attachments
loadeddata.JPG (83 views, 67.00 KB)
sumon.mukherjee
sumon.mukherjee
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 Visits: 57
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?
Attachments
currentplaylis1t.xls (71 views, 403.00 KB)
A. Harmon
A. Harmon
SSC-Enthusiastic
SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)

Group: General Forum Members
Points: 175 Visits: 219
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?
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59671 Visits: 13297
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/


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
A. Harmon
A. Harmon
SSC-Enthusiastic
SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)

Group: General Forum Members
Points: 175 Visits: 219
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.
sumon.mukherjee
sumon.mukherjee
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 Visits: 57
Yes it works!!! thanks a ton guys!!
swathy.reddy7
swathy.reddy7
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 87
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....
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59671 Visits: 13297
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?


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search