Click here to Skip to main content
12,352,294 members (56,771 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

11.2K views
6 bookmarked
Posted

Looping through Excel rows in SSIS and Perform CRUD Instructions

, 29 Oct 2013 CPOL
Rate this:
Please Sign up or sign in to vote.
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:

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, 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.

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. 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:

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, 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):

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 be 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'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.

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


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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Raymund Macaalay
Technical Lead
New Zealand New Zealand
http://nz.linkedin.com/in/macaalay
http://macaalay.com/

You may also be interested in...

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.160621.1 | Last Updated 29 Oct 2013
Article Copyright 2013 by Raymund Macaalay
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid