Transfer data from one instance/database to another instance/database in SQL Server with SSIS

There are many occasions where you need to transfer data from one instance/database of SQL Server to another instance/database of SQL Server. May be archiving, may be for ware house etc.
This is really very much needed utility, not only that, I have came across this question many times in different forums so though to explain this in my blog.
Today, I am not going into much details of each aspect, just transfer data from one source of SQL Server to another source.
Well, open new project from your VS2008. Project type “Business Intelligence Projects” and template should be “Integration services project”.
As soon as you create new project, drag “DataFlow” from tool box to “control flow” which is your work area. See image below for more detail

To configure that “dataflow” double click on that so that you will be redirected to the “Data Flow” tab, right beside “Control Flow” tab.
Now, take Ado.Net Source and Ado.Net Destination task from tool box and drop it into your work area. Connect Ado.Net Destination with Ado.Net Source. Now we will configure both tasks one by one.
Double click on Ado.Net Source task to configure it.
Note: I assume our source is AdventrueWorks database and HumanResouce.Employee table and destination is DataBaseNew on same server. Destination table should be created as “HREmployee” in destination database.
Create your database connection by clicking on “New” button in “Ado.net connection manager”.
From “Data Access Mode” select “Table or View” and from “Name of the table or View” should be “HumanResouce.Employee”. Configuration should look like this:

 Now, double click on “Ado.Net Destination” task. Set the destination database. If you already have table in destination database than select it or click on “new” and create one. Finally click on OK button and run the package by hitting “F5” and confirm whether data is there in your destination database.
This is really very basic example, I will post few more advanced article for this in very near future.
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
Advertisements

For Loop container example in SSIS with C# script

Looping is one of the powerful tools since very long in each programming language. It has no different concept in SSIS too. Here in SSIS, For Loop container defining repetitive control flow in a package.
Generally in looping, three steps are very important. 1.) Initialization of loop variable 2.) condition for exiting the loop 3.) Increment/Decrement. In SSIS too, you have to be familiar with three steps only in order to execute For Loop Container.
 Let us now see the small example.
Create new project in BIDS (Business Intelligence Developer Studio) for this example and add two variables in the variable window.
1.)    “Count” type of Int32 with value of 5
2.)    “Increment” type of Int32 with value of 0
Once you are done with adding variable, drag For Loop container from tool box and drop it to the work area.  To configure loop container, double click on that.
Now, set following properties in “For Loop Editor”
1.)    InitExpression to @Increment=1
2.)    EvalExpression to @Icrement<@Count
3.)    AssignExpression to @Increment=@Increment+1
See the second paragraph of this article where I have mentioned three important steps for looping in programming language, same kind of three steps with different name we are setting up in “For Loop” container here in SSIS.
For more details, look at below screen shot.

Actually we are going to show message box for each iteration of “For Loop” container. Our Increment variable is set to 1 and it will run until it becomes greater than count variable which is 5. Show for displaying the message we are going to use “Script Task”.
Once you setup “For Loop” container, drag “Script Task” from tool box and put it inside “For Loop” container, double click on “Script Task” to configure it. We are going to use “Increment” variable inside the script task to display which iteration is going on. For using Increment variable, we have to select that variable in Script task. So, in “ReadWriteVariables” property of script task should have “Increment” variable, you can select that variable by clicking on Ellipse button besides “ReadWriteVariables” property. Once you set it, click on “Edit Script” button to write down script.
For more details, look at image below:

Once, you will click on “Edit Script” button, you will find one script edition and find “Main” method, which is our entry point, in that script editor. Put below given code in Main method and close “Script Editor” and click “OK” button in “Script Editor” dialog box.
System.Windows.Forms.MessageBox.Show(“Hi from SSIS, right now loop counter is at “ +  Dts.Variables[“Increment”].Value.ToString(), “information”);
            Dts.TaskResult = (int)ScriptResults.Success;
Now, you are ready to run this simple application by hitting “F5”.
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

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

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

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

Create text file from Stored Procedure output in SSIS with Flat File Destination – SQL Server 2008

This is quite an obvious need in SSIS where you wanted to call Stored Procedure and keep its output in text file. This is one of the common and easy task to do in SQL Server Integration Services.  Let us not talk more and do some action.
Before we move ahead in BIDS, let us create one Stored Procedure which we will be called from SSIS. I will be using table and records from my previous article. Table name is StudDetail. You can create table script from here.

Now, herewith, I am giving the SP created based on StudDetail table.

create proc usp_StudDetail 
as 
begin 
 select StudentID,PassYear,Grades from StudDetail 
end 
GO
Now, Open new project in BIDS and in “Control Flow” tab of your Package.dtsx file drag and drop one “Data Flow” task. Double click to “Data Flow” task to configure it then you will be redirected to “Data Flow” tab of your package.dtsx file.
–Take one “OLEDB Source” task from tool box and drop it to your package.
–double click on OLEDB Source task to configure it.
–From “OLEDB Connection Manager”, set your database connection
–From “Data Access Mode” drop down, select “SQL Command”
–put “exec usp_studdetail” (without double quote) in “SQL Command Text” and click on OK.
Now, you are done with configuring OLEDB Source task and configuration screen should seems like this:

–Now drag “Flat File Destination” task from tool box to your work area.
— Drag the Green arrow from OLEDB Source to the Flat File Destination task and drop it to create precedence
–double click on “Flat File Destination” to configure it.
–click on “New” button to create “Flat File Connection Manager” if it doesn’t exists and select “Delimited file” radio button and press OK button.
–Now, from the dialog box, you have to set two property, “Connection Manager Name” and “File Name” along with path.
Screen should look something like this:

Ok, Now you are done, just run the package by hitting F5 and you will see file will be created at the path you have specified.
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

PIVOT task in Data Flow Transformation in SSIS 2008

Well, I have written so many articles for PIVOT in my blog but today I am going to show you PIVOT task in Microsoft SQL Server Integration Services 2008 (SSIS).
BTW, before we start PIVOT in SSIS, if anybody would like to have a look at PIVOT examples with TSQL, than kindly follow below link:
Now back to the point. Let us move ahead with our PIVOT Task in SSIS. Before we go to BIDS and start working on SSIS, we have to have some data which we can PIVOT so let us create one table, insert some data in SQL Server database first.
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
Select * from StudDetail
So, now you are ready to go.  Please create new project in BIDS and put one Data Flow Task in “Control Flow” tab. Double click on it to configure. As soon as you double click on “Data Flow Task” from “Control Flow” tab. You will be landed to “Data Flow” tab.
Now, drag and drop OLEDB Source task and configure it with the database in which you have created table “StudDetail”.

Select “SQL Command” in “Data Access” mode and use below given TSQL query.

SELECT StudentID ,PassYear,Grades  FROM StudDetail
Note: If you don’t know how to configure “OLEDB Source”, kindly click here.
Now, drag and drop “Pivot” task below “OLEDB Source” and Drag the green (on the left) data flow output to the Pivot component and drop it to create precedence.
Now we are going to configure “Pivot” task and this is the trickiest part in this article. Once you double click on PIVOT, you will get “Advanced Editor for Pivot” dialog box with three tabs.
1.)    “Component Properties”
2.)    “Input Columns”
3.)    “Input and Output Properties”
You don’t need to do anything in first tab.
Check weather all three columns are selected in second tab (Input Columns).
Now, go to 3rd tab, named “Input and Output Properties” and expand
Pivot Default Input->Input Columns
You will see all three columns there 1.) StudentID 2.) PassYear 3.) Grades. we have to configure “Pivot Usage” property for that. Set 1 for first column 2 for second column and 3 for third column.
Also remember the “LineAgeID” property for each column which will be used later and plays an important role.

Now, expand Pivot Default Output->Output Column. You won’t find any column there so far, just add three columns there by clicking “Add Column” Button.
1.)    StudentID
2.)    [08-09]
3.)    [09-10]
Output of Column StudentID would come from StudentID column in Input section. Do you remember LineAgeID property of StudentID column in above screen shot? It is 17 there so “Source Column” property of “StudentID” in output should be 17, for more details, see below screen shot.


Now, let us set property for [08-09] and [09-10] columns in output.  LineAgeID property for column Grades in Input section is 23 right now so [08-09] will have 23 in SourceColumn property and if value of PassYear would be 08-09 than it should come in our output column [08-09] so PivotKeyValue property should be 08-09.  Look at the screen shot for more details:

Same way, properties should be set for column [09-10] too.
Now, you are ready to have your output, you can have your output in screen or in any file like flat file, spreadsheet etc.  Right now I just wanted to see the results on screen.
Create one Variable of Object type, named “Results”. If you don’t know how to create variable, please read this article.
Now, I am taking one more task “Recordset Destination”, double click on that to configure it.  In a “VariableName” property, select the “Results” variable of Object type which you have just created.  Go to “Input column” tab to see whether all three columns are selected and click on “OK” button.
Since I wanted to see results in screen (Grid View), double click on precedence (Green Arrow) between Pivot task and Recordset Destination,  go to “Data Viewers” tab, click on “Add” button, now select “Grid” and press OK button two times and run your package by hitting F5.

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