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

Check whether FileExists in SQL Server 2005/2008

Well, you might have done programming in any of the .NET flavor to check whether file is exist in particular folder or not. You might have used many classis of SYSTEM.IO namespaces and big code to check but If I tell you it is more than easy to check the file whether it is exists or not, in SQL Server than .NET, what would be your reaction? Ohh!! What are you talking about!!!!
I am not kidding, it is really very very easy, just one simple statement, no namespace, no use of class etc. etc. Have a look at it.
exec master.dbo.xp_fileexist‘d:\emps.txt’



If emps.txt would be exists in your “D” drive, you would get answer “1” in “File Exist” column or “0”. Isn’t it very easy?
Now think if you are doing some kind of T-SQL Programming and you need this value in variable, again very easy and small code snippet required for that, have a look at it again.
DECLARE @isExists INT
exec master.dbo.xp_fileexist‘d:\emps.txt’, @isExists OUTPUT
SELECT case @isExists when 1 then ‘Yes’ else ‘No’ end as isExists



Happy T-SQLing!!!
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

Where is my Open Package option of DTS in SSMS?

If you are working with SQL Server 7.0 or SQL Server 2000 since long and jump into SQL Server 2005 and/or 2008 suddenly you may be finding the option to open DTS package of SQL Server 2000 in SSMS. It was very easy from Enterprise manager of SQL Server2000. You can expand “Data Transformation Service” table under the “Database” tab, right click on that and click on “Open Package “ command. Have a look at screen capture.

But you will not be able to see “Data Transformation Service” command in SSMS directly. Isn’t it there? Answer is, it is there but at other location.

 Expand the Management object.

 Open the Legacy object.

 Right click on Data Transformation Services.

 Click on “Open Package File”

Have a look at screen capture:

However, you can open old .DTS packages in SSMS but it is better to upgrade it to .DTSX package. There is one nice article at simple-talk.com. Have a look.

http://www.simple-talk.com/sql/sql-server-2005/dts-to-ssismigration/

 

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