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.
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.
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.
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
