January 24, 2011 at 9:59 pm
I'm trying to create a simple package in SSIS 2008 that loops through a folder a Excel files and imports them into new database tables.
The excel files have a varying number of columns, and the columns have different names.
My packages has a ForEach Loop to loop through the Excel files in a folder;
Inside the loop, a SQL task to drop the destination table if it exists (the file layout may have changed) and a data flow task to import the excel file to the new table. But I encounter errors with the destination table. I'm trying to set the table variable in the OLE DB Destination rowset, but the table does not exist so it fails.
It appears that what I'm trying to do is not possible - can anyone confirm for me, or otherwise tell me how I need to do this.
January 25, 2011 at 2:43 am
Facing a the same issue almost .
BackGround:A job runs every month to load an excel file to SQL Server.The Excel file differs Month to Month and hence cannot be imported to a specific pre-existing table.
What I am Trying to Do:
1.Created a variable [User::TableName] which will hold the name of the new destination table. This table name will be decided by another application.
2.Created a Excel Data Source to import the excel data
3.Created a OLE DB Destination for the data.
Question:
1.How do i use this variable [User::TableName] to assign the name of
the new table that is going to be created?
2.How to configure the package so that it creates a new table everytime it runs using [User::TableName] as the table name?
Stuck with this for a long time .Please help!!!
January 25, 2011 at 8:47 am
SK,
I will tell u the solution that i have found for my issue , check if its useful for u .
i have configured the destination so that a new table is created everytime it runs.But the name of the table remains constant.
In other words, open the destination editor, select table or view fast load option and click on the button new.
The default name will be xxxx destination , change it to something relevant to u .
Define a execute sql task in the control flow to delete the table if exists and then connect to the data flow. What is achieved by this is :
For 1 excel file, 1 new table with a fixedname is created everytime the package is run,
I was also looking at sp_rename function to rename the default destination table to some other name so that the next time the package runs it creates a new table again , but that did not work.
January 26, 2011 at 5:35 pm
Thanks for the suggestion koustav, but I have tried to do that. However, each of the files I'm trying to import is different (i.e. has different number of columns with different column names), so when it gets to a file that doesn't match the stored definition of the Excel file I get the error "The external columns for component "<Excel Source>" are out of synchronization with the data source columns"
I'm going with the assumption that it's impossible to import multiple different Excel files.
January 27, 2011 at 3:09 am
"I'm going with the assumption that it's impossible to import multiple different Excel files":angry:
Well if you mean with SSIS you might be able to do it but I will not try to do it.
In this case I would go to VB.net MVS (Microsoft Visual Studio) and start
(in Excel to export a TAB separated flat text files) an use bulk insert to import
the data to the server. This might look as more "complicated" than SSIS but in
this case you have to do scriping in SSIS. I prefer VB.net (or C#.net).
Gosta M:-)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply