Looping through dataset in SSIS

Looping through dataset in SSIS

I have written quite a few SSIS article so far but two days back I have received an email from one of the user where he wants to iterate through dataset like we used to loop on each records of dataset in .NET. I thought to provide him solution in form of blog article so that everyone can get benefit of it.

I am going to use my AdventureWorks2012 database in SQL Server 2012 along with Visual Studio 2010 to demonstrate this article.

Let us assume that we have two tables called “Sales.SalesOrderHeader” and “Sales.SalesOrderDetails” in our “AdventureWorks2012” database. We have one OrderID 43659” and we want to change (update) orderQty filed. If SalesOrderDetailID is even, we want to change OrderQty to 2 otherwise we want to change it to 1. We have direct TSQL query to do this task for us but we will use ForEach loop container to do this task to demonstrate how dataset works in SSIS.

Create SELECT query in New Query Window of SQL Server Management Studio in Adventureworks2012 database.

[sourcecode language=”sql”]
SELECT
SOH.SalesOrderID
,SOD.SalesOrderDetailID
,SOH.OrderDate
,SOH.DueDate
,SOD.ProductID
,SOD.OrderQty
FROM
sales.salesorderheader AS SOH JOIN sales.salesorderDetail AS SOD
ON SOD.SalesOrderID=SOH.SalesOrderID
WHERE SOH.SalesOrderID=43659
[/sourcecode]

Create one SSIS project from Visual Studio 2010 and follow the steps given below:

1.) Create variables to contain resultset going to be return from above select query and also create variable to hold value of each field inside the foreach loop. Here is the screen capture of variable window. (you can open variable window by right click on “Control Flow” tab, you will find “Variables” option in popup menu)

1Varibles

2.) Create one Execute SQL Task and create one OLEDB connection inside that for Adventureworks2012 database also have the SELECT query copy from SSMS which we have created above and paste it there. Follow the screen capture.

2ExecuteSQLTaskForSELECT

3.) Take ForEach Loop Container and set the following Property.

3ForEachLoop

4.) now move to “Variable Mappings” tab in same Foreach Loop Editor to set parameter mapping.

4ParameterMappingInForEach 

5.) now set Script task to check whether SalesOrderDetailID is even or odd.

5ScriptTask

Here is the script which you have to write down in “Main” after clicking on “Edit Script”.

[sourcecode language=”C#”]
// TODO: Add your code here
if ((int)Dts.Variables["SalesOrderDetailID"].Value % 2 == 0)
{
Dts.Variables["isOdd"].Value = true;
}
else
{
Dts.Variables["isOdd"].Value = false;
}

Dts.TaskResult = (int)ScriptResults.Success;
[/sourcecode]

6.) Now create two SQL Task and connect it with Script task created above. One connector (“Precedence Constraint”) will have “true” value and another will have “false”. I have taken screen capture of first connector with true value.

6ConnectorProperty

7.) Now both the execute SQL task for update will have UPDATE query. First UPDATE will have OrdQty value 2 as static and second will have 1. I am taking screen shot of first SQL Task inside foreach loop.

7UpdateQuery

8.) once we set all of the above value from “General” tab, we will click on “Parameter Mapping” to provide value of SalesOrderID and SalesOrderDetailID to UPDATE query. Here is the screen capture.

8UpdateQuery

9.) Now when you finally execute package, it will looks like this (if it executed successfully):

9FinalExecution

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Dynamic name of text file generated by Script Task in SSIS 2008

Today, I came up with one of the most useful script used by ETL developer. I used to see many questions about this issue so many times in different forums too.
We are going to generate text file by calling one stored procedure.  I have already written an article which generate text file as an output of Stored Procedure.  But It was using Flat File Destination task to generate text file and it was having static file name.
Here in this article we are going to use power of variables in SSIS. We will use the query and dynamic file name stored in variables. As a pre-requisite, let us create one table and stored procedure.
CREATE TABLE StudDetail
(
StudentID INT,
PassYear VARCHAR(10),
Grades int,
Increase INT,
Decrease INT
)
GO
INSERT INTO StudDetail
SELECT 1, ’08-09′, 3333,0,0 union all
SELECT 1, ’09-10′, 4252,25,0 union all
SELECT 2, ’08-09′, 2100,0,0 union all
SELECT 2, ’09-10′, 2002,0,-10
GO
create proc usp_StudDetail
as
begin
 select StudentID,PassYear,Grades from StudDetail
end
GO
After creating this table and SP, let us create one new project in BIDS for SSIS.
1.)    Add one variable in variable window named “ExecuteSP”, datatype “String”
2.)    Add another variable named “DynamicFileName”, datatype “String”. We want dynamic value for this variable so in property window, set “True” in “EvaluateAsExpression” property
3.)    Click on ellipse button in “Expression” property to set the expression which make dynamic file name. Set following string in “Expression” which will generate dynamic file name
“DynamicFileName_” + (DT_WSTR,2) Day(GETDATE()) +(DT_WSTR,2) Month(GETDATE()) +(DT_WSTR,4) Year(GETDATE()) +(DT_WSTR,4) DatePart(“mi”,GETDATE())+(DT_WSTR,4) DatePart(“second”,GETDATE())+”.txt”
To get clear idea about all these settings, look at the screen shot below.

 

Once you set up variables, now this is turn to set ado.net database connection.
In the connection manager at the bottom of the screen, right click and insert “New ado.net connection” and rename it to “AdoNetConn” from the property window. Look at the below screen shot.

 

Now, you are going to do real programming like you used to do in C# application, which is my favorite programming language. I am glad that Microsoft has given facility to write script in C# language in Microsoft Integration services 2008.
Anyway, add one script manager in your design area and double click on it to configure.  Select both variables which we have created in “ReadOnlyVariables” property and click on “Edit Script” button.  See below image for more information.

 

Now, write down following script in your MAIN method.
//storing dynamic file name in strFileName varialbe
            string strFileName = Dts.Variables[“DynamicFileName”].Value.ToString();
            //storing our TSQL in strSQL variable
            string strSQL = Dts.Variables[“ExecuteSP”].Value.ToString();
            //storing the directory in strDir where our dynamic text file will be generated.
            string strDir = “D:\\SSIS\\”;
            //getting connection string in strConn from the ado.net connection manager we have added in design time
            string strConn = this.Dts.Connections[“AdoNetConn”].ConnectionString.ToString();
            string strLine = “”;
            System.Data.SqlClient.SqlConnection sqlConn = new System.Data.SqlClient.SqlConnection(strConn);
            sqlConn.Open();
            System.Data.SqlClient.SqlCommand sqlCmd = new System.Data.SqlClient.SqlCommand(strSQL, sqlConn);
            System.Data.SqlClient.SqlDataReader dr;
            dr = sqlCmd.ExecuteReader();
            //getting column header and writing it to file
            for (int i = 0; i < dr.FieldCount; i++)
            {
                strLine = strLine + dr.GetName(i).ToString() + “|”;
            }
            strLine = strLine.Substring(0, strLine.Length – 1);
            System.IO.StreamWriter sw = new System.IO.StreamWriter(strDir + strFileName, true);
            sw.WriteLine(strLine);
            sw.Close();
            sw = null;
            //writing record set to file
            while (dr.Read())
            {
                strLine = “”;
                for (int i = 0; i < dr.FieldCount; i++)
                {
                    strLine = strLine + dr.GetValue(i).ToString() + “|”;
                }
                strLine = strLine.Substring(0, strLine.Length – 1);
                System.IO.StreamWriter sw1 = new System.IO.StreamWriter(strDir + strFileName, true);
                sw1.WriteLine(strLine);
                sw1.Close();
                sw1 = null;
            }
            // TODO: Add your code here
            Dts.TaskResult = (int)ScriptResults.Success;
This is just a sample script to demonstrate; you can make it more generic, divide it into smaller function and call that in MAIN method.
Anyway, you are now ready to run package by hitting F5 after writing script, close script edition and close “Script task editor”.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

HTTP Connection Manager and Script task to download file via HTTP in SSIS 2008 with C# syntax

Download file via HTTP connection task would have lots of coding in .NET but in SSIS this task become much much easier with the help of “HTTP Connection Manager” , “HTTP Connection Manager” is one of the connection member of rich set of stock connection in SQL Server 2008 integration services.
Let us move ahead with creating package which can download the file from HTTP. Open new SSIS project.
To add “HTTP Connection Manager” in your package, right click on “Connection Manager” window and click on “New Connection”, from the “New Connection” dialog box, select “HTTP” and click on “Add” button.
For more detail, please look at below screen shot:
Once you insert “HTTP Connection Manager”, double click on it to configure. It will open dialog box editor, you have to give the path of file you wanted to download in “Server Settings” property.I wanted to download my blog header so I am giving path of the same which is as below.

for more detail, look at below screen shot.

 

Once you are done with setup of “HTTP Connection Manager”, drag “Script Task” from tool box and drop it to the “Control Flow” which is your design area. Double click on “Script Task” to configure it and click on “Edit Script” button to write down script.
In the MAIN()  method which is our Entry Point, I am going to have following script.
Microsoft.SqlServer.Dts.Runtime.HttpClientConnection httpConn;
            Object obj;
            try
            {
                obj = Dts.Connections[“HTTP Connection Manager”].AcquireConnection(null);
                httpConn = new HttpClientConnection(obj);
                httpConn.DownloadFile(“d:\\SQLHub.jpg”, true);
            }
            catch (Exception e)
            {
                Dts.Events.FireError(1, e.TargetSite.ToString(), e.Message, “”, 0);
            }
            // TODO: Add your code here
            Dts.TaskResult = (int)ScriptResults.Success;
As soon as you copy the script there, save and close script editor and run your package by hitting F5. You will see file created at the destination path. In this case, I would found SQLHub.JPG in D drive of my system.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah