Looping through Excel rows in SSIS and Perform CRUD Instructions

SSIS is a really good tool for performing ETL Transactions but have you ever wondered whether you can use this tool to perform CRUD operations from a data source? What if the datasource is from an Excel, this adds more complexity to the given problem, how would I loop through the rows of an Excel spreadsheet? Well, I was in this situation last week when someone gave me an Excel Spreadsheet to perform updates on a database table and those updates may include Add, Edit or Delete items. I tried to Google for this solution but I could not find any that would satisfy my needs so we have to do it from scratch and here is how I made it.

First, let's take into assumption that you have an Excel spreadsheet that looks like this:

You have columns for an action to perform, an ID and the value you want to store. So in this case, ADD means you need to add this to the database table with the correct ItemId and Values, DELETE means you need to delete the item with the given ItemID on the database table and UPDATE means you update the value of the item with the given ItemId on the database table.

Now this is how your database table would look like:

Lets start, assuming that you have Integration Services Project opened in Visual Studio starting form a new package lets create the flow.

1. Converting an Excel Source to a Recordset Destination

Drag the Data Flow task from your SSIS Toolbox, configure it by going to the Data Flow Tab.

Now drag an Excel Source and the Recordset Destination. Connect them together.

Double click on the Excel source then configure the connection, click on New and create a new Excel Connection by selecting the Excel spreadsheet with the data you want to process.  Choose the appropriate settings.

Select the sheet name where your data is located.

Preview the data to double-check:

Go to columns and tick each column you need for processing.

Create variables to store your data in, right-click on your Data Flow Task canvas and select variables.

Create the following variables all under package scope:

• Action, String Data Type – this will store Action Column Data
• ItemId, Double Data Type – this will store ItemId Column Data
• Value, String Data Type – this will store Value Column Data
• RecordsetOutput, Object Data Type – this will store all Excel Items

Now configure your Recordset Destination, go to the custom properties, then select the variable name.  Choose the RecordsetOutput, this is where your Excel rows will be saved.

First part done.

2. Loop through each Recordset Item in SSIS

Now you have saved your Excel rows in a recordset, let's now loop though each item to perform the tasks you need.

Now drag the Foreach Loop Container into your project, connect it with the Data Flow Task.

Configure the variables needed for the operation. Double click the Foreach Loop Container then choose collection. Select the Foreach ADO Enumerator on the Enumerator option, then select the RecordsetOutput variable for your source data. Select Enumeration mode to be Rows in the first table.

Map your individual variables to assign column values. Go to Variable Mapping and assign the variables according to Index values. These index values are important and it is ordered on how the recordset destination configured it, not how it is on Excel. To verify, go to your Recordset Destination -> Input Columns, so in this example, it looks like this:

So Action maps to Index 0, ItemID Maps to Index 2 and Value maps to Index 1.

Now drag a sequence container and just leave it blank and name it landing. This will be the landing area of your variables and defines what to do next.

Now you had stored row data on each variables, let's create your control flows.

3. Create your Control Flow in SSIS

This section will be something like your if then else or switch statement but in SSIS.

Now drag three Execute SQL Task and name it Add, Edit and Delete. Connect them to the Landing Sequence Container, then configure each Precedence Constraint (green arrows):

Double click the Green Arrow for the ADD SQL Task, then on the Evaluation Operation, choose Expression then on Expression choose the Action Variable and it must be Equal to “ADD”. This means that the ADD SQL Task will only be triggered when the Action is equal to “ADD”, otherwise it would leave it.

So in summary, the expressions would be like this:

• ADD : @[User::Action]==”ADD”
• EDIT : @[User::Action]==”EDIT”
• DELETE : @[User::Action]==”DELETE”

Now you created your logic flow, let's create Execute task for each condition.

4. Send SSIS Variable Values in Execute SQL Task

Now go to your ADD Execute SQL Task, in the Parameter Mapping Section map your parameters. For the ItemId, we create a parameter called @ItemId and for Value we create @Value. We also need to define the proper Data Type.

Now go to the General Settings then create a new ADO.NET connection type to your database. Choose Direct input as SQLSourceType then on your SQLStatement type this:

Insert INTO MyTable (MyId, Value) values (@ItemId, @Value)

I guess for the UPDATE and DE<code>LETE, you know what to do.

That’s it, the final look of your SSIS would be something like this:

Filed under: CodeProject, Database, Programming, Tools Tagged: Microsoft SQL Server, SQL Server Integration Services