Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Strange issue while loading data from Excel using SSIS package Expand / Collapse
Author
Message
Posted Monday, April 5, 2010 11:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 13, 2010 4:02 AM
Points: 7, 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


  Post Attachments 
dataLoad.xls (40 views, 18.50 KB)
Post #896921
Posted Monday, April 5, 2010 12:16 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 11:25 AM
Points: 356, Visits: 78
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.






  Post Attachments 
loadeddata.JPG (48 views, 67.91 KB)
Post #896937
Posted Monday, April 5, 2010 12:38 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 13, 2010 4:02 AM
Points: 7, 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?


  Post Attachments 
currentplaylis1t.xls (28 views, 403.50 KB)
Post #896956
Posted Tuesday, April 6, 2010 11:05 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 2, 2011 12:57 PM
Points: 27, Visits: 89
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?
Post #897732
Posted Wednesday, April 7, 2010 12:04 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:02 PM
Points: 13,334, Visits: 10,201
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #898181
Posted Wednesday, April 7, 2010 5:16 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 2, 2011 12:57 PM
Points: 27, Visits: 89
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.
Post #898361
Posted Thursday, April 8, 2010 11:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 13, 2010 4:02 AM
Points: 7, Visits: 57
Yes it works!!! thanks a ton guys!!
Post #900201
Posted Saturday, March 29, 2014 4:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 7:12 AM
Points: 1, Visits: 49
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....
Post #1556200
Posted Saturday, March 29, 2014 7:54 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:02 PM
Points: 13,334, Visits: 10,201
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1556214
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse