CODEWITHSUNDEEP
sqlsql-serverssis
Justin Samuel
Justin Samuel

Reputation: 1083

SSIS - Any other solution apart from Script Task

Team,

My objective is to data load from Excel to Sql Tables using SSIS. However the excels are quite dynamic i.e. their column count could vary OR the order of existing columns may change. But the destination table will be the same...

So I was contemplating on few options like:
1) Using SQL Command in "Excel Source" - But unfortunately I have to keep "first row as header" setting as false(To resolve the issue of Excel Connection Mngr sensing the datatype as numeric based on first few records). So the querying based on header doesnt work here.

2) The other oprtion in my mind is Script Task and write C# code to read excel based on the columns I know. So in this case the order and insertion/deletion of new columns won't matter.

Suggest me whether Script Task is the only option available for me? Any other simple way to achieve the same in SSIS? Also if possible suggest me a reference for the same.

Thanks, Justin Samuel.

Upvotes: 4

Views: 1493

Answers (2)

brian
brian

Reputation: 3695

If you need to automate the process, then I'd definitely go with a script component / OleDbDataAdapter combo (you can't use a streamreader because Excel is a proprietary format). If not, go with the import wizard.

If you try to use a connection manager based solution, it's going to fail when the file layout changes. With the script component / OleDbDataAdapter combo, you can add logic in to interpret the fields and standardize the record layout before loading. You can also create an error buffer and gracefully push error values to it with Try / Catch.

Here's some links on how to use the script component as a source in the data flow task:

http://microsoft-ssis.blogspot.com/2011/02/script-component-as-source-2.html http://beyondrelational.com/modules/2/blogs/106/posts/11126/ssis-script-component-split-single-row-to-multiple-rows.aspx

Upvotes: 2

tempidope
tempidope

Reputation: 863

This could be done easily using "Import and Export Data" tool available with SQL Server.

Step 1: Specify your Excel as source and your SQL Server DB as destination.

Step 2: Provide necessary mappings.

Step: 3 In the final screen, you can specify to "Save as SSIS Package" and to File System. A relevant dtsx SSIS package would be created for you.

After the SQL Server Import and Export Wizard has created the package and copied the data, you can use the SSIS Designer to open and change the saved package by adding tasks, transformations, and event-driven logic.

(Since it works based on Header, order should not matter. And if a particular column is missing, it should automatically take NULL for that)

Reference: http://msdn.microsoft.com/en-us/library/ms140052.aspx

Upvotes: 1

Related Questions