Category Archives: SSIS

Derived Column and Union All Transformation task in SSIS 2008

Many times when we are trying to get data from different sources, there is a possibility to have same key fields. To eliminate this kind of problem, “Derived Column” transformation task is very useful. For example one company has factory at one location and back office work performed from other office. Both locations has their own employee table with IDENTITY as a primary key. Obviously Identity key generally starts with 1 by default so both location would have 1,2,3 etc. in their employee table. When you try to move both the table at one single destination, you might come across with situation where employee ID 1 would have two employee and sample with many more IDs. To eliminate this situation, you can have one more column which is not exists in source table which describes where this data come from so you have unique combination of that new column and an existing EmployeeID column.
Let us see how we could perform this. Before we head towards SSIS package development, let us first create two different table in SQL Server and one blank table as a destination of these both table. You can use below script to generate those table with/without data in your SQL Server database.
create table ForDerivedColumn1
(
      EmpID INT Identity(1,1),
      EmpName varchar(10)
)
INSERT INTO ForDerivedColumn1
SELECT ‘Ritesh’ union all
SELECT ‘Rajan’
GO
create table ForDerivedColumn2
(
      EmpID INT Identity(1,1),
      EmpName varchar(10)
)
INSERT INTO ForDerivedColumn2
SELECT ‘Rushik’ union all
SELECT ‘Rajvi’
GO
create table ForDerivedColumnNewTable
(
      SourceID INT,
      EmpID INT,
      EmpName varchar(10)
)
GO
Now, open BIDS (Business Intelligence Studio) and create new SSIS project. Draw dataflow task in your “Control Tab” and double click on it to configure it which will redirect you to “Data Flow” tab. Since we have two different SQL Server table as an source, let us first create two “Ado Net Source” in our work place and select your database and table as a source. Below screen shot will show you how to configure “Ado NET Source”. I am showing example for FIRST “ADO NET Source” you have to do it for second “ADO NET Source” too with table name “ForDerivedColumn2”.
Now take two “Derived Column” transformation task and put them under “ADO NET Source” task, double click on it to configure.

Above screen capture showing you configuration for first “DerivedColumn” do it for second “DerivedColumn” task too with value 2 in SourceID column.

Once we have our dataset ready with Derived Column, we have to merge both dataset with “UnionALL” Transformation task. “UnionAll” work exactly same as “UNION ALL” in TSQL. So drag “UnionAll” transformation task below both “DerivedColumn” task and join extended green arrow from both “DerivedColumn” to “UnionAll” task.
Now, this is a time to convert the data in proper format with “DataConversion” transformation task as many time from different sources, same kind of data coming with different datatype, let us convert it with “DataConversion” task by putting it under “UnionAll” task and join extended green arrow from “UnionAll” task to “DataConversion”.

Now, we have to perform our last steps. Data is ready to go anywhere but we have to tell where it suppose to go. Since we want it to insert it in our SQL Server table, we are going to use “SQL Server Destination” task. Double click on it to configure it. Give your database and table reference to it as below image.

Once you give reference of your table, you have to go to “Mappings” tab in the same dialog box and set the mappings there so that proper data get inserted in proper place. Look at the below image for more detailed information.

Finally you are done with package now run it by hitting F5 key and check your database table whether you have actually received the data inside or not. After running if you get “Green Signal” in every task, J you are done.

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

Merge Join Transformation Task in SSIS 2008

SSIS is the very crucial tool for Data Warehouse and collecting data from different sources and merge it into single master source is the main task in Data Warehouse.  Merge Join Transformation task is one of the useful tool in SSIS to achieve this. Let us see how it works.
Well, before we start developing package, let us create two different data sources where data come from in single source. We are also going to create one blank table which will hold the data come from two different sources.
Our first source will be SQL Server table.
Our second source will be Flat file which is comma separated.
And the destination of the both sources would be in again SQL Server single table.
Script for First Source:
Create Table OrderMaster
(
      OrderID Varchar(5),
      ClientCode Varchar(10),
      ProjectName varchar(25),
      OrderDate DateTime
)
INSERT INTO OrderMaster
SELECT ‘A1001’,‘CHEM02’,‘NY WaterField’,GETDATE() UNION ALL
SELECT ‘A1002’,‘ACCU01’,‘Plainfield Soil’,GETDATE()
GO
Create table in SQL Server database and insert data in that table via above given script.
Second Source:
Create one text file in D drive with name OrderDetails.txt with following data.
OrderID,SampleNumber,Matrix
A1001,A1001-01,Water
A1001,A1001-02,Soil
A1002,A1002-01,Water
A1002,A1002-02,Water
First row in the above data is column name.
Now, let us create destination table.
Create Table OrderMasterAndDetail
(
OrderID Varchar(5),
ClientCode Varchar(10),
SampleNumber Varchar(10),
Matrix Varchar(10)
)
GO
Ok, now we are ready to start developing SSIS package. Open new project in BIDS (Business Intelligence Development Studio). Drag “Data Flow” task from tool box to your “Control Flow” tab and double click on it to configure. As soon as you will double click on that, you will be redirected to the “Data Flow” tab. Now, drag  “ADO Net Source” and “Flat File Source” task to your “Data Flow”. Let us now configure them.
Double click on “Ado Net Source” to configure it and select your database and table to configure it. Look at the below given Image for more details.
Now, double click on “Flat File Source” to configure it. From the “Flat File Source Editor” please click on “New” button to create “Flat File Connection Manager”. Now from “Flat File Connection Manager Editor” give name “OrderDetails” in “Connection Manger Name” property. By clicking on “Browse” button, select your “OrderDetails.txt” file from D drive. Don’t forget to check on the CheckBox “Column name in first data row” as our first row in text file is our column header.  For more details, look at image below.
Now drag “Sort Transformation” task from tool box to your data flow tab and repeat this step to have two “Sort Transformation” task. One is for “Ado Net Source” and second one is for “Flat File Source”. Join both sort task with appropriate green arrow and double click on it one by one to configure it and make ascending sort on “OrderID” field in both the sort task.
Now, drag the merge join task from tool box and drop it below both sort tasks. Connect green arrow from both sort task to “Merge Join” task and double click on “Merge Join Transformation” task to configure it. “OrderID” column in both the source should be work as JOIN key.  Select “OrderID”, “ClientCode” from first sort and “SampleNumber” and “Matrix” from second sort as shown in screen capture below.
Now, you are having data from both the source to your “Merge Join”. You have to send that data to specified location, in our case it is one SQL Server table we have already created with the name “OrderMasterAndDetail”. Since we want to send our merged data to SQL Server, let us now drag “Ado Net Destination” task to our dataflow and connect it to our merge join. Now, this is the time to configure “Ado Net Destination” so double click on it and select your database connection and table named “OrderMasterAndDetail”, look at the screen shot below for more information.
So, finally you are ready to run the package, hit F5 to run that and if every task seems green as below screen capture than you are all set and you have already received data in your destination table.
Check your “OrderMasterAndDetail” table in SQL Server whether you have actually received any data there or not.
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

Conditional Split Transformation Task in SSIS 2008

Well, divide data in different destination based on certain condition, is very important task in any ETL tool. SSIS 2008 is really very rich for this kind of task. One of the tool help us to do it very quickly is “Conditional Split Transformation Task” in SSIS 2008.
Let us see one very small example which will give you demonstration of how to use “Conditional Split Transformation Task” in SSIS 2008.
We will first create one table, named “SampleReceive”, generally in environmental laboratory there are few different kind of department in which sample comes for different kind of analysis. I will use three department. 1.) VOA 2.)Semi-VOA 3.) Pesticide. We will make an entry in these three departments in “SampleReceive” table and later on, we will split data in three different table respective to particular department.
So, before we head towards SSIS project, let us first create necessary table in SQL Server. Below given is T-SQL script for the same.
create table SampleReceive
(
      Department varchar(10),
      NumberofSample int,
      DateReceived datetime,
      ClientID int
)
INSERT INTO SampleReceive
SELECT ‘VOA’,10,GETDATE(),1 UNION ALL
SELECT ‘SEMI-VOA’,7,GETDATE(),2 UNION ALL
SELECT ‘Pesticide’,16,GETDATE(),1 UNION ALL
SELECT ‘VOA’,5,GETDATE(),3 UNION ALL
SELECT ‘SEMI-VOA’,3,GETDATE(),1
GO
Create Table SampleReceiveVOA
(
      Department varchar(10),
      NumberofSample int,
      DateReceived datetime,
      ClientID int
)
Create Table SampleReceiveSemiVoa
(
      Department varchar(10),
      NumberofSample int,
      DateReceived datetime,
      ClientID int
)
Create Table SampleReceivePest
(
      Department varchar(10),
      NumberofSample int,
      DateReceived datetime,
      ClientID int
)
GO
Now, let us open new project in SSIS and start making a SSIS package.  As soon as you create new project, have “Data Flow Task” on your screen and double click on it to configure. Look at the below screen shot.
Once you double click on “Data Flow Task” you will be forwarded to the “Data Flow” tab besides “Control Flow” tab.  Now, drag “Ado Net  Source” task to your screen and double click on it to configure it. Give your server credential in configuration editor and select the source table which is “SampleReceive” in our case. For more details, have a look at below given screen shot.

Once you are done with configuration of “Ado Net Source”, drag “Conditional Split Transformation” task from tool box to your work area and drag green arrow coming from “Ado Net Source” to your “Conditional Split Transformation” to connect “Conditional Split Transformation” with “Ado Net Source”.

Now, this is the time to configure “Conditional Split Transformation” so double click on it. In “Conditional Split configuration editor” You have to set three conditions for all three different departments and give it a appropriate “Output name”. have a look at image below for more clarification.

Now, we are all set to split data into three different table from our “SampleReceive” table. Have three “Ado Net Destination”  on the screen and rename it to “VOA”, “Semi-Voa”, “Pesticide” and connect all three “Ado Net Destination” from “Conditional Split Transformation” task.  When you will try to connect that green arrow from “Conditional Split Transformation” to “VOA” Ado Net destination, it will ask you for select condition, you have to select “Voa” condition which we have already defined in “Conditional Split Transformation” task. Look at screen capture below for more information.

Once you connect it (all three destinations), You have to configure all three “Ado Net Destination” . I will show you how to do it for first one, for remaining two; you can follow the same procedure.

Double click on “Ado Net Destination” task named “VOA” and configure it with your SQL server details and table named “SampleReceiveVOA” as shown in below image.

Once you will configure “VOA” table, configure all remaining two “Ado Net Destination” for respected table. Finally your diagram will looks like below image.

Now, you are ready to run, hit F5 to run your package and check whether you have really received data in all three blank table or not by running following TSQL commands in your SQL Server database from SSMS.

select * from SampleReceiveVOA
select * from SampleReceiveSemiVoa
select * from SampleReceivePest
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 Table from text file in SSIS 2008

Well, I have already written few example of how to create flat/text file from SQL Server via SSIS. You can refer those old articles at below links:
This is the time now to go reverse, above examples shows you how to generate text file from SQL Server table but now I am going to show you how to generate SQL Server table from text file.
First generate one text file in your D drive with following data.  Following data would be there in one text file, may be named as “Emp.TXT”. Data is pipe separated; you can have any other separator too like comma.
ritesh|shah|MIS
rajan|shah|account
Alka|Shah|management
Once you have your text file ready, open BIDS (Business Intelligence Development Studio), create new project for SSIS and drag “Data Flow” task from tool bar and drop it to your control area. Once you have dataflow task, double click on it to configure.
Now, important part of this article is coming, before we jump into it, let me tell you, you will need two things in order to copy your text file data to SQL Server.
1.)    As a first thing, you will need source (your text file)
2.)    You will need destination (your SQL Server)
This is just a basic understanding and minimum these much steps needed for copy from any other source to destination.
So, as a first step, we are going to have, “Flat File Source” task in our “Data Flow” tab, double click on it to configure.
In “Flat File Source Editor”, click on “New” to create new source of your text file and name it to “EmpTextFile”.
Browse the text file which we have generated in D drive with name “EMP.TXT”.
We don’t have header information in our text file so table will be created with “Column0”, “Column1” etc., if you want to give meaningful name to your columns,  click on “Advanced” tab in “Flat file connection manager editor” and give meaningful name.  In our case, we will use following name for our columns in “Column” property.
1.)    FirstName
2.)    LastName
3.)    Department
For more details, let us look at below given screen capture:
Click on OK in both the dialog box. And now take “Ado.Net Destination” task from tool box to your control area. Connect green arrow from “Flat File Source” to “Ado.Net destination” task.
Double click on “Ado.Net Destination” task to configure it. To setup database and Server, click on “New” button in “Connection Manager” area.
If you already have a table in your database, select it from drop down list of “use a table or view” , if you don’t already have table in your database, you can create new table by clicking on “New” button. For more details, have a look at screen capture given below.
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

LOOKUP Transformation task in SSIS 2008 to copy no match row from source to destination in SQL Server 2008

Yesterday I wrote one of the very simple articles to copy data from source SQL Server to destination at “Transfer data from one instance/database to another instance/database in SQL Server with SSIS”. You can use it, if task needs to be run only one time. If you want to keep running this job daily, weekly or after every certain period of time, you don’t need all data to be copied over again and again. You want to copy only those data which are not exists in destination table.
In order to achieve this task in SSIS 2008, we are going to use “LOOKUP Transformation” task between our source and destination. LOOKUP will do the job of checking source and destination. It can return match/not match data as per your need.
In order to do this job, we will need two dummy database and table in which we perform this exercise. Let us create two data ADV1 and ADV2, or you can use your two databases. Run following TSQL script.
–database 1
USE adv1
go
create table ChemicalConsume
(
      ChemID int,
      QuantityInLtr int,
      ConsumeDate datetime
)
GO
insert into ChemicalConsume
SELECT 1,10,’06/06/2010′ union all
SELECT 1,14,’06/07/2010′ union all
SELECT 2,8,’06/08/2010′ union all
SELECT 2,10,’06/09/2010′
GO
–database two
USE adv2
go
create table ChemicalConsume
(
      ChemID int,
      QuantityInLtr int,
      ConsumeDate datetime
)
GO
insert into ChemicalConsume
SELECT 1,10,’06/06/2010′
GO
Now, open BIDS and create new project for Integration Services and drop “Data Flow Task” in your work area from toolbox. To configure it, double click on that so that you will be redirected to the “Data Flow” tab.
Now, on dataflow tab, please drag “ADO NET Source” task from toolbox and drop it to your work area.
Double click on it to configure and set your database and table of ADV1 database there, for more information look at below screen shot.
Once your ADO NET Source is ready with configuration, drag “LOOKUP”  transformation task to your work area and join LOOKUP task with ADO NET SOURCE. Now, double click on “LOOKUP” to configure it.
Since, we want to copy only those rows which doesn’t exists in destination table, we have to select “Redirect rows to no match output” option from LOOKUP Transformation Editor and click on  “Connection” tab at left hand side.

 

From the connection tab, select your destination database and table. For more information, please look at the below given screen shot.

 

Now, please understand this concept, “ADO NET Source” returns row from ADV1.ChemicalConsume table which will be “Input” for our “LOOKUP” task. In the connection tab of LOOKUP task, we have made connection to “ADV2.ChemicalConsume” table as a destination so that LOOKUP will compare source and destination.
Now from “LOOKUP Transformation Editor”  click on “Column” tab to set on which columns we are going to make comparison. Our comparison will be on “ChemID” and “ConsumeDate” fields of both the tables. So let us do it.
Now, take “ASP NET Destination” from tool box and join it with green arrow of “LOOKUP” task.
As soon as you will try to join green arrow of “LOOKUP” with “ASP NET Destination” you will get on small dialog box named “INPUT OUTPUT SELECTION”. Set the drop down value as shown in below screen
Now finally double click on “ADO NET Destination”  to configure it and set your ADV2 database along with appropriate table.
Now, run your package by hitting F5 and see output of the 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

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

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