June 23, 2009 at 8:10 am
Hi,
i have a problem in loading the excel file into a SQL-Server table using SSIS 2005 package.
The problem is, number of columns in the excel file will not be constant, and the order of the columns are also not constant.
Every time when i run the package, i have to drop and create the sqlserver table according to the columns in the excel file during runtime.
Can any one please help me in doing this.....
June 23, 2009 at 11:38 am
Is the destination columns in the SQL table going to remain fixed/constant?
June 23, 2009 at 11:05 pm
The destination columns will not be constant, it should change according to the columns in the Excel sheet.
June 23, 2009 at 11:13 pm
That's not easy at all. Somehow you are going to have to determine the underlying datatype of all the random columns, plus max string length etc.
Excel itself tries to do something like that when it parses a CSV file and usually cocks it up. So you can imagine that it's not straightforward. Unless you know in advance what the datatypes are, or you can bring them all in as text, you are in for a rough time.
Can you explain the background of this requirement, just out of interest?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 24, 2009 at 12:38 am
I too don't know the background of this requirement, one of my colleague is facing this problem....
If the columns in the Sql server Destination table is constant, can we load the excel file.........
June 24, 2009 at 1:04 am
I am confident that I could do it all in a script task, yes.
But I do not have anything written to help you out ... maybe someone else has done something similar and can post specifics.
You will not, in my opinion, find it possible to do this using the standard data flow components - they are not flexible enough for your needs.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 24, 2009 at 9:08 am
I was going to suggest the same that Phil did. Although my suggestion would be to use a script component as a source instead of a script task. Both would work though. Biggest thing to know is that it looks like based on your requirements you're going to have to write some VB.net or C# code (C# in version 2008 only) to accomplish what you want to do.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply