Split multi value column into multiple records (Rows) using SSIS Part -1 (Using Sql script)

I have a file which has two column State name and districts names (it is separated by comma). I want to load these records into the database. See Example.

  

And want to load data on below format in the database.

We are getting the file in excel.

Let’s see how we load this data into the database.

There is two way to load this data in the designation.

1.       Load Excel file in the staging table and then use execute SQL task to load these data (using sql script).

2.       Using script component task.

First, we will SQL script.

Approach

Ø  First I will the excel data in the staging table

Ø  Second I will extract that data and convert it to single column format (using SQL script) and load this data into the destination.

Loading data in the staging table

Taking Data flow task.

Double click on the data flow task.

In Data flow, I am taking Source as Excel.

Creating connection manager.

Now I am taking destination as the table.

Now creating connection manager.

See the table in the database.

Now I am running this package.

  

Oooooh, My package gets failed.

See the error.

[Excel Source [2]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "Excel Connection Manager 1" failed with error code 0xC0209303.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Here I am trying to use the excel source in SSIS in 64 bits computer. We need the set the Run64bitRunTime properties as false.

Read How to set the Run64bitRunTime properties as false: Click here.

http://bageshkumarbagi-msbi.blogspot.com/2018/03/run64bitruntime-debugging-property.html

Now I am running this package.

Oh!!!!!!!

Package executed successfully.

Now we see the records in the table.

Extract that data and convert it to single column format (using SQL script) and load this data into the destination

Now I am taking another data flow task.

Double click on the Dataflows task.

Here I am taking Source as OLEDB source

Creating connection.

In Data access mode I am selecting SQL command.

And I am writing the SQL script to split the comma separator columns into multiple rows.

Read more: http://bageshkumarbagi-msbi.blogspot.com/2017/07/split-string-to-table-using-t-sql.html

I am taking destination as OLEDB destination.

See the records in the destination table.

Now I am running this package.

Package executed successfully.

See the records in the table.