I have decided to create a Simple Series of posts designed to explain basic SSIS functionality. Having used SSIS in-depth, I sometimes overlook the fact that others are looking for a simple explanation on how to use some of the components.
Step 1: Configure an Execute SQL Task
The example I use in this post will extract information from a SQL Server database using an Execute SQL Task. More specifically I will be getting the names of all user databases for a given server. It is important to note that since there is a chance of getting more than one record back, we have selected Full result set. The image below shows the select statement and the Full result set option.
Now comes a potentially confusing part. How do I use the result set I extracted from SQL Server. You have to assign it to a variable. The image below shows how that is done. You need to create a variable of type Object and connect that variable to the result set of the Execute SQL Task. That's it! Your Execute SQL Task is now complete.
Now that we have data from our SQL Server, we need to work on each record and for that we need a Foreach Loop.
Step 2: Configure and Connect Foreach Loop
The Foreach Loop is a very versatile component enabling you to loop through all sorts of different things (ADO objects, files, variables, etc). The image below shows the 7 specific types of objects that can be looped through.
We will be looping through an ADO Enumerator. We connect the Foreach Loop by selecting the same variable we used to store the result set, User::sqloutput.
So we have the data from SQL Server being stored in the variable User::sqloutput and we have successfully connected that variable to the Foreach Loop container, but we still need to extract that data. The loop will work given what is set up, but if you wanted to actually work with the data extracted we need one more step. We need to pull the data out of the result set variable, User::sqloutput and put it into a usable format.
Select the Variable Mapping option and map each column from the select statement to the appropriate variable. Since I am only selecting the name from sys.databases, I only need a single variable to map to and that variable is User:databaseName.
You now have a simple, yet usable Foreach Loop. I have inserted a Container labeled Something to be done, as a placeholder. Hope this provides a simple example that clears away some of the cobwebs that might be out there regarding how to use SQL Server data from within SSIS