Looping through Excel rows in SSIS and perform CRUD Instructions

By | October 29, 2013

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 cant find any that will satisfy my needs so we have to do it from scratch and here is how I made it.

First lets take into assumption that you have an Excel spreadsheet that looks like this.

01 Excel

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.

02 SQL

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.

03 Dataflow Task

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

04 Dataflow Task 2

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.

05 Configure Excel Connection

Select the sheet name where your data is located.

06 Configure Excel Connection

Preview the data to double-check

07 Preview Data

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

08 Columns

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

09 Create 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

10 Variables

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.

11 Recordset Destination

First Part done.

2. Loop through each Recordset Item in SSIS

Now you have saved your Excel rows in a recordset, lets 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

12 Foreach Loop

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.

13 Configure Foreach Loop

Map your individual variables to assign column values.  Go to Variable Mapping and assign the variables according to Index values.  This 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.

14 Index Order

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

15 Variable Mapping

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.

16 Landing

Now you had stored row data on each variables, lets 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)

17 Execute SQL

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 triggered when the Action is equal to “ADD” otherwise it would leave it.

18 Precedence Expression

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 create Exceute 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.

19 Parameter Mapping

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)

20 General Setting SQL Task

I guess for the UPDATE and DELETE you know what to do.

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

21 SSIS Final Layout

Leave a Reply