CODEWITHSUNDEEP
sql-serverexcelssisetl
Avinash
Avinash

Reputation: 2183

Moving the data from Excel which have Dynamic column names to a table using SSIS

I am creating a SSIS package in which i have to move data from Excel to a table in SQL server. Excel file is like Source Assistant in data flow task.

Number columns in Excel file won't change but column names will change dynamically.

For example, In 2017 excel will look like this:

SId Name  Jan2017 Feb2017
1   name1  10      20
2   name2  30      40

in 2018, it will look like this:

SId Name  Jan2018 Feb2018
1   name1  50      60
2   name2  70      80

Once in every year I will run this Job to move the data from excel to DB. I have to movie this kind of data in to a table which have columns like below.

When i run this Job in 2017, data should be filled like below.

SId Name   Jan     Feb
1   name1  10      20
2   name2  30      40

When i run this Job in 2018, data should be filled like below.

SId Name   Jan    Feb
1   name1  50      60
2   name2  70      80

How can i handle this kind of data.

Upvotes: 2

Views: 1847

Answers (1)

Hadi
Hadi

Reputation: 37313

Solution overview

  1. Exclude column names in first row in excel connection, use sql command as data access mode and Read data from the second row

  2. Alias column names in the Excel Source -> Columns 

    F1  F2     F3     F4
SId Name   Jan    Feb

Detailed Solution

You can follow my answer at Importing excel files having variable headers it is solving a very similar case.

Upvotes: 2

Related Questions