x

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

asked Jun 09, 2011 at 08:29 PM in Default

Naveen Kumar gravatar image

Naveen Kumar
194 13 17 21

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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

answered Jun 09, 2011 at 10:13 PM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

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.
Jun 10, 2011 at 03:43 AM Naveen Kumar
(comments are locked)
10|1200 characters needed characters left

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

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

[2]: http://www.microsoft.com/downloads/en/details.aspx?familyid=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=en
more ▼

answered Jun 10, 2011 at 03:03 PM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

Many Thanks for the solution
Jun 12, 2011 at 09:36 PM Naveen Kumar
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x945
x117
x67

asked: Jun 09, 2011 at 08:29 PM

Seen: 1806 times

Last Updated: Jun 10, 2011 at 12:13 AM