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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

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

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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Simple SSIS example with DataFlow, SendMail, ForEach Loop, Aggregate, Result Destination task with Microsoft Integration Services 2008 R2

Well, these days, football fever has covered whole globe so thought to include example related to football in this example. This is just an example; I don’t claim that the data is right regarding current football world cup.
Suppose we have a list of name and total goal hit by individual and we want an email to shoot with total goal,
average goal and highest goal from SSIS.
We are first going to create one dummy table in our database which will be used in this example.
CREATE TABLE FootBallTopScorers
(
      Name varchar(20),
      Goal int
)
GO
INSERT INTO FootBallTopScorers
SELECT ‘Gonzalo Higuaín’,3 UNION ALL
SELECT ‘Elano’,   2  UNION ALL
SELECT ‘Asamoah Gyan’, 2 UNION ALL
SELECT ‘Luis Fabiano’, 2 UNION ALL
SELECT ‘Diego Forlán’, 2
GO
SELECT * FROM FootBallTopScorers
GO
Ok, once you are done with creating above table and its data in SQL Server database. Let us close SSMS and open BIDS (Business Intelligence Development Studio) or Visual Studio 2008/2010 (if you have full version and template).
Create a new project by selecting “Business Intelligence Projects” tab and “Integration Services Project” template.
Once you see “Control Flow” tab in your project, follow the steps given below.
1.)    Drag and drop “Data Flow Task” from tool box to your “Control Flow” tab.
2.)    Rename it to “Get Goal Data” so it would be easier to remember what this “Data Flow” is going to do.
3.)    Now Drag and Drop “For each loop” task below “Get Goal Data” and put “Send Mail” task to in your “For Each loop” container  “Send Report”
4.)    Click the Data Flow Task you renamed to “Get Goal Data”, There should be a small, dangling arrow extending from the bottom of the Data Flow Task. Drag the arrow to the Send Mail title bar and drop it to create a precedence
Now, Your screen would seems like below.

 

Now, let us set some variables which will hold our data returned from SQL Server.  If you are not able to see “Variable” window, you can either right-click on designer and select “Variable” or you can go to View Menu->Other Windows -> Variable
Once you get Variable Window, You will find one small button in Variables Window’s tool box, named “Add Variable”, use it to add variable.
1.)    Add three variable named “AvgGoal”, “TotalGoal”, “HighestGoal” with datatype Int32 and default value to 0 which is by default.
2.)    By default NameSpace of variable is “User”, let us change it to “Football”. You might not be able to see “Namespace” column in “Variables” window. You can make it visible by clicking on toolbar button of “Variables” window named “choose variable columns”.
3.)    One more variable with same namespace should be added. Variable name should be “Results” and type should be “Object” which will hold complete record set return by query.
Now your variable windows would seem like this screen.
Now we are going to configure “Data Flow” task, so just double click on that “Get Goal Data” and you will be redirected from “Control Flow” to “Data Flow” tab.
1.)    From the Toolbox, drag and drop an OLEDB Source Adapter from the Data Flow Sources group of components and rename it to “Retrieve Goal Data” and double click on it.
2.)    As soon as you will “Double Click” on it, you will find “OLEDB Source Editor”, now set your SQL Server, Database. In the Data Access Mode field, select SQL Command.
3.)    Write following query in “SQL Command Text” field. Select Goal From FootBallTopScorers
Since we want aggregate data like Total, Highest (Max), Average etc., let us use one more tool of SSIS in “Data Flow” tab.
1.)    Drag and Drop “Aggregate” task from tool bar to your “Data Flow” tab, right below you OLEDB source, and rename it to “Aggregation”
2.)    Drag the green (on the left) data flow output to the Aggregate component and drop it.
3.)    Now, setup you “Aggregation” task by double click on it. You will find “Aggregate Transformation Editor” to set your output. You have to set three alia with its Operation. Mainly named as “TotalGoal”, “HighestGoal”,”AvgGoal” with Sum, Maximum and Average aggregate function in “Operator”.
Finally your screen of “Aggregate Transformation Editor” would seem like this:

Well now we can use “Data Conversion”, we in this case we are having same datatype in database as well as in Variables but if we have different datatype, we can convert it. Anyway, drag and drop “Data Conversion” task below your “Aggregation” task and double click on it to configure.

Data Conversion task should be configured like below given screen:
Now, insert “Recordset Destination” task below you “Data Conversion” task which will hold all converted data. Double click on “Recordset Destination” task to configure it.
In the VariableName property, type in “Results,” which is the name of the variable of type Object you created earlier and from “Input Column” tab, select all columns which have converted to int. see the screen below for more details.
Now, we are all set to go, 90% of the task we have done.  again go to “Control Flow” tab from your “data flow” tab. You have to set foreach  loop task by double clicking on it.
1.)    From “Collection” tab in ForEach Loop task. Set “Enumerator” property to “ForEach ADO Enumerator”
2.)    In “ADO Object                 source variables” select “Football::results” variable.
3.)    From “Variable Mapping” tab, select our three variables which are going to display the data.
Screen should be like this:
Now, you are just one step far from completing this first package.  Double click on “Send Mail” task and set credential of your SMTP server by “Smtp Connection “ Property. Set From, To email address property along with email subject.
Now go to “Expression” tab. Click on ellipses button of “Expression” property under “Expression” tab.
From “Property Expression Editor”, select “MessageSource” property and keep following text for email in that.
“Here is the GOAL summary ” +”Average ” + (DT_WSTR, 20) @[Football::AvgGoal] +
“Total Goal” + (DT_WSTR, 20) @[Football::TotalGoal] +
“Highest Goal” + (DT_WSTR, 20)@[Football::HighestGoal]
We are using all three variables in our email body. Now, run this package by hitting F5 and enjoy your first package.
If any task would get failed, it will be displayed with “RED” color and you can get an exact idea about that from “Execution Result” tab which will be there after you end up your package.
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