question

Naveen Kumar avatar image
Naveen Kumar asked

SSIS Excel dumping

Hi, I want to dump excel sheet in to my database in to a new table automatically from a folder by ssis (I am able to do this), but my problem is that excel sheet will be having different columns each time (number of columns are not specific, it may be 3 or 4 or 10 ), So My package should search in that folder if the excel sheet is available then it should dump into a new table. As the number of columns are varying how to handle this. Pls help me. Thanks in advance.
ssisexcelimport-data
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image
WilliamD answered
One possibility would be to use a Script task in SSIS. You can use this to read the column names from the excel file and generate the target table ready for inserting. However, if you are reading files into your system, they should have some sort of agreed standard format. It is possible to dynamically import these files, but the data would have no meaning without structure. I suggest you look at creating a standard data import template (or more than one if necessary). This then allows your clients to supply the data in such a way that your import process can import and process the files intelligently.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hi William, I tried with script task as you said but not able to complete the task, plese help me by suggesting clear steps how to finish this task. Thanks in advance.
0 Likes 0 ·
Pavel Pawlowski avatar image
Pavel Pawlowski answered
SSIS is not good for such situation. In SSIS you need to have a fixed source and destination columns. So when your columns are varying then you can use a Script Task as @WilliamD mentioned, but this will quite complex and not effective. This kind of import will be much easier using the OPENROWSET function. you can use the INSERT INTO table(..) fields FROM OPENROWSET(....) OR SELECT fields INTO newTableName FROM OPENROWSET(...). Details you can find in a KB Article: [How to import data from Excel to SQL Server][1] What you need to remember are the X64 problems with Excel. To read Exc files on X64 SQL Server you can use the [Microsoft Access Database Engine 2010 Redistributable][2]. [1]: http://support.microsoft.com/kb/321686 [2]: http://www.microsoft.com/downloads/en/details.aspx?familyid=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=en
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Many Thanks for the solution
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.