Dyanamic Column Mapping in SSIS

Question

I have a excel source and sql database is the destination. The problem here is excel column names changes every week. They may add new columns or re-name the columns /sometimes they drop some columns.So whenever there is a change in excel columns the ssis throws the error and i had to manually map them again. Is there any way i can do it automatically? Please help

If columns in the Excel file are added, do you want new columns to be added to your SQL table? If they are dropped, do you want the SQL table columns dropped? If the Excel columns are renamed, do you want to rename the SQL columns as well, or just map to the old column name? Please provide some examples of the changes if you can. — Bert Wagner, Jul 03 '14 at 14:50
Answering your question. If added new columns then yes i want to add them as new column in sql. If dropped in excel-no it should not be dropped in sql. if renamed then -No, it should map the old column. — user3636924, Jul 03 '14 at 15:48

Vikramsinh Shinde · Answer 1 · 2014-07-03T16:07:36.313

0

Since your source column keeps on changing, you need to write custom script where you can put your logic according to changes happens to source.

Script logic should be like, get excel file, read column headers and then infer that what to do with excel. You can then re-direct this excel to appropriate component.

edited Jul 03 '14 at 16:07

answered Jul 03 '14 at 14:48

Vikramsinh Shinde

2,742
2
23
29

Just to have an idea..Do you have any web links that shows the sample script? – user3636924 Jul 03 '14 at 15:50
Accessing Excel Via ADO.NET Using SSIS Script Task : http://www.databasejournal.com/features/mssql/article.php/3919016/Accessing-Excel-Via-ADONET-Using-SSIS-Script-Task.htm – Vikramsinh Shinde Jul 03 '14 at 16:03
1

SSIS Excel Source via Script : http://billfellows.blogspot.co.uk/2013/04/ssis-excel-source-via-script.html – Vikramsinh Shinde Jul 03 '14 at 16:04
Working with Excel Files with the Script Task: http://msdn.microsoft.com/en-us/library/ms403358.aspx – Vikramsinh Shinde Jul 03 '14 at 16:04

score 0 · Answer 2 · edited May 23 '17 at 10:26

The only way I can think of doing this is with a Script Component. Essentially, what you need to do is:

Read in the Excel file data into a DataTable; see: Reading Excel files from C#
Create an List that contains all of the column names read in from your Excel File. We will use this to compare the field names of your destination SQL table.
Create an List that contains all of the column names of your destination SQL table.
Compare your Lists with some fuzzy matching logic to see if the column names between your Excel file and SQL table are the same. The algorithms you can use for this will depend on how variable Levenshtein might be a good way to do this: http://www.dotnetperls.com/levenshtein
Based on your comparison in step 4 above, dynamically build a SQL query to write all of your ALTER TABLE/ADD COLUMN statements for the new Excel columns.
Load the DataTable from step 1 above into your SQL table, using SQLBulkCopy's ColumnMapping class to map the fuzzy matched column names from step 4. http://msdn.microsoft.com/en-us/library/System.Data.SqlClient.SqlBulkCopyColumnMapping(v=vs.110).aspx

Dyanamic Column Mapping in SSIS

2 Answers2