SSRS Overview

SQL Server Reporting Services Overview
This is my first article of SSRS and I would like to start from SSRS overview.

Microsoft SQL Server 2008 Reporting Services provides a complete, server-based platform designed to support a wide variety of reporting needs enabling organizations to deliver relevant information wherever needed across the entire organization.

Microsoft SQL Server Reporting Services (SSRS) is an enterprise reporting platform which supports traditional reports and interactive reports delivered over the Web or through custom applications. SSRS is data-source independent. In SSRS, Multi-dimensional data sources (Cubes) can also be used.

Using SSRS, reports can be exported in a variety of formats, including Excel worksheets and Portable Document Format (PDF) files.

Also reports can be scheduled to deliver through mail on scheduled dates to selected audiences.

Reports can be published to Report Server (http://ReportServer/reports) and accessed through browser.

The server component in SSRS is managed through a Web services infrastructure accessible using Simple Object Access Protocol (SOAP). The client consuming the report might be as simple as a Web browser pointing to a Report Server URL. Reporting Services also has an API that allows reports to be embedded in other applications.

Foreach Loop Container in SSIS

The Foreach Loop Container defines a repeating control flow in a package. The loop implementation is similar to Foreach looping structure in programming languages but in a package, looping is enabled by using a Foreach enumerator. The Foreach Loop container repeats the control flow for each member of a specified enumerator.

I have already mentioned about Foreach Loop Enumerators in my previous article. Now I'll explain Foreach Loop Container using Foreach File enumerator. I'll move data of multiple excel sheets into a database table.

1. To begin, I have created three Excel Spreadsheets and store these sheets at location D:\SSIS\Hari\DataFiles.
These files have sample data for my example. Below is thesnapshot of file 2010-06-20.xlsx:









2. Open existing project or create new project using BIDS. I will use existing project Sample saved at location D:\SSIS\Hari\Sample (which I have used for other articles as well). Right click on SSIS Packages folder in Solution Explorer and click New SSIS Package. Rename it with ForeachLoopContainer.dtsx as shown below:













3. Drag and drop Foreach Loop Container from Toolbox. Add a package variable FileName of String type and assign default value D:\SSIS\Hari\DataFiles\2010-06-20.xlsx. I'll use this variable to store File Name of each iteration.

4. Drag and drop Data Flow Task inside Foreach Loop Container and rename it with DFT - Load Data From Excel.

5. Double click the Foreach Loop container to open Foreach Loop Editor to configure the Foreach Loop container. Click on Collection and provide the folder that contains the files to enumerate (in our example D:\SSIS\Hari\Sample), specify a filter for the file name and type *.xlsx and specify the fully qualified file name as retrieve file name as shown below:

6. Click on Variable Mappings and Select variable User::FileName from dropdown box. Corresponding Index for this variable will be 0. Click OK to proceed and save changes.

7. Add Excel Connection Manager to read data from Excel sheets:- Right click on Connection Managers area and select New Connection... and select EXCEL as type and click on Add button. Enter D:\SSIS\Hari\DataFiles\2010-06-20.xlsx in Excel File Path and make sure that checkbox First row has column names is checked. Click OK to proceed and save changes.

8. Config ConnectionString with FileName variable:- Click on Excel Connection Manager --> go to properties --> Click on Expression --> This will open Property Expression Editor. Select ExcelFilePath from Property dropdown box. Click on Expression and Drag and drop User::FileName from Variables. Click OK two times and save changes.

9. Add OLEDB connection manager to load data into database table: Right click on Connection Managers area and select New OLEDB Connection... This will open Configure OLE DB Connection Manager. Click on New button. Enter Server name and database name in respective boxes as shown below:

10. Double click on Data Flow Task to go to Data Flow tab. Drag and drop Excel Source from Data Flow Sources and double click on it to open Excel Source Editor. Select Excel Connection Manager in OLEDB connection manager and Sheet1$ in Name of the Excel sheet dropdown boxes.

11. Click on Columns in left side window. This will display all the External Columns available in excel and Output Columns. Click OK to process and save changes.

12. Drag and drop OLEDB Destination from Data Flow Destinations. Click on Excel Source to see green and red output lines. Connect green output line to OLEDB Destination. Now double click on OLEDB Destination to open OLE DB Destination Editor. Select local.Test as OLE DB connection manager. Click on New... button to create Destination table. It will open Create Table wizard. You can edit T-SQL statement as per your need. You can change the name of table if you wish. Click OK to proceed.

13. Click on Mappings to map source and destination columns. It will automatically map the columns by their matching names. However you can map the columns manually as well. Click OK to process and save changes.

14. We are done with package development. Finally package looks one as shown below:

Now its time to execute the package and check the result.

Go to package location D:\SSIS\Hari\Sample\SSIS-Sample1 --> Right Click on package ForeachLoopContainer.dtsx and click SQL Server 2008 Integration Services Package Execution Utility. This will open Package Execute Utility wizard.

Click on Execute button. A new wizard Package Execution Progress will open. You can see progress of package execution in this wizard.

Now you can check the data of all excel files into database table as shown below:

Sequence Container in SSIS

The Sequence Container defines a control flow that is a subset of the control flow in a package. Sequence containers group the package into multiple separate control flows, each containing one or more tasks and containers that run within the overall package control flow.
There are some benefits of using a Sequence container which are mentioned below:

• Provides the facility of disabling groups of tasks to focus debugging on one subset of the package control flow.
• Managing multiple tasks in one location by setting properties on a Sequence Container instead of setting properties on the individual tasks.
• Provides scope for variables that a group of related tasks and containers use.

For more information about using Sequence Containers, see Different ways of using Sequence Containers
Using Sequence Containers lets you handle the control flow in more detail, without having to manage individual tasks and containers. For example, you can set the Disable property of the Sequence container to True to disable all the tasks and containers in the Sequence container.

If a package has many tasks then it is easier to group the tasks in Sequence Containers and you can collapse and expand Sequence Containers.
Note: You can also create task groups which collapse and expand using the Group boxthis is a design-time feature that has no properties or run-time behavior.

Now I'll explain you how to use sequence Container in a package.
1. To begin, right click on SSIS Packages folder in Solution Explorer and click New SSIS Package. Rename it with SequenceContainer.dtsx as shown below:

2. Add a package variable DayOfWeek

3. Drag and drop Script Task from toolbox. This task will be used to initialize DayOfWeek variable with current day of week. Rename Task name as Script Task - Set DayOfWeek.

4. Double click on Script Task to open Script Task Editor. Enter User::DayOfWeek in ReadOnlyVariables property.

5. Click on Edit Script... and write below code in Main() function of ScriptMain.cs:

I have just attached an image of code to avoid formatting problems of the code.

6. Drag and Drop 7 Sequence Container tasks from Toolbox and rename the task on Week Days e.g. "SC - Sunday", "SC - Monday" etc.

I am not placing any controls flow items inside Sequence Container to narrow down the example and focus more on how to use sequence container.

7. Connect all these Sequence Containers to Script Task. Now double click on green arrow to open Precedence Constraint Editor. Select Evaluation operator as Expression and Constraint and Expression as @DayOfWeek == "Sunday". Click OK to close and save changes. Tthe Expression for remaining task will be differ as per week day e.g. @DayOfWeek == "Monday" for Monday and so on.

8. Save the package. Now right click on the package in Solution Explorer and execute the package.

9. This package will execute only one sequence container as per expression set for the precedence constraints.

This is just an example of how we can use Sequence Container. But we can use this in many ways which depends on the requirement.

For Loop Container in SSIS

The For Loop Containers are used for repeating control flow in SSIS packages. The Loop implementation is very similar to the For Loop structure in programming languages. In each iteration of the loop, the For Loop container evaluates an expression and repeats its workflow until the expression evaluates to False.

The For Loop Container uses the following elements to define the loop:

• An optional initialization expression that assigns values to the loop counters.
• An evaluation expression that contains the expression used to test whether the loop should stop or continue.
• An optional iteration expression that increments or decrements the loop counter.

The following example shows a For Loop container displaying Current Value of Expression through Message Box.
1. To Begin, Open SSIS Project or create new Project through BIDS. Add a new package and rename it with ForLoopContainer.
2. Add two package variables - StartValue and EndValue, both of integer type.

3. Drag and drop For Loop Container from toolbox and double click on it to open For Loop Editor. Set InitExpression, EvalExpression, and AssignExpression with @StartValue, @StartValue <= @EndValue and @StartValue = @StartValue + 1 respectively as shown below:

This loop repeats five times because StartValue is set to 1 and EndValue is set to 5.
4. Drag and drop Script Task inside For Loop Container and double click to open Script Task Editor.
Type User::StartValue in ReadOnlyVariables property of script Task editor. Now Click on Edit Script... button to write code

5. Write following code in Main() function to display a message box for current value of StartValue variable in loop iteration:
MessageBox.Show("StartValue = " + Dts.Variables["User::StartValue"].Value.ToString());

Main function will look like this:


6. Close script task editor and save changes. Now right click on Package in Solution Explorer and click Execute to execute the package.

The package will show Message box 5 times before completion.

Thats all. We can apply any logic inside For Loop Container as per business requirement.

SSIS Containers

Integration Services Containers
SSIS Containers are controls (objects) that provide structure to SSIS packages. Containers support repeating control flows in packages and they group tasks and containers into meaningful units of work. Containers can include other containers in addition to tasks.

Types of SSIS Container
SSIS provides four types of containers. I'll explain these containers with example in my following Posts.
The following table lists the container types:

Container Type	Container Description	Purpose of SSIS Container
Foreach Loop Container	This container runs a Control Flow repeatedly using an enumerator.	To repeat tasks for each element in a collection, for example retrieve files from a folder, running T-SQL statements that reside in multiple files, or running a command for multiple objects.
For Loop Container	This container runs a Control Flow repeatedly by checking  conditional expression (same as For Loop in programming language).	To repeat tasks until a specified expression evaluates to false. For example, a package can send a different e-mail message seven times, one time for every day of the week.
Sequence Container	Groups tasks as well as containers into Control Flows that are subsets of the package Control Flow.	This container group tasks and containers that must succeed or fail as a unit. For example, a package can group tasks that delete and add rows in a database table, and then commit or roll back all the tasks when one fails.
Task Host Container	Provides services to a single task.	The task Host container encapsulates a single task. But this task is not configured separately in SSIS Designer. It is configured when you set the properties of the task it encapsulates.

Containers are fundamental to the operation of transactions, checkpoints and event handlers. Each container has some common properties that affect the usage of these features. Understanding these properties and what they do helps a lot in the developing SSIS packages.

Property	Description
DelayValidation	A Boolean value that indicates whether validation of the container is delayed until run time
Disable	A Boolean value that indicates whether the container runs
DisableEventHandlers	A Boolean value that indicates whether the event handlers associated with the container run
FailPackageOnFailure	A Boolean value that specifies whether the package fails if an error occurs in the container.
FailParentOnError	A Boolean value that specifies whether the parent container fails if an error occurs in the container.
IsolationLevel	The isolation level of the container transaction. The values are Unspecified, Chaos, ReadUncommitted, ReadCommitted, RepeatableRead, Serializable, and Snapshot.
MaximumErrorCount	The maximum number of errors that can occur before a container stops running.
TransactionOption	The transactional participation of the container. The values are NotSupported, Supported, Required.

reference: bol

SSIS Part 3 - Creating new SSIS Package

In this section I will walk through creating a simple SSIS package from scratch. As an example I will generate an Excel spreadsheet that can be used to manually enter a sales forecast by city and state. The city and state information will be extracted from the AdventureWorksDW database and output to an Excel spreadsheet.
In the sections, we will walk through the following steps:

1. Create a new SSIS package and discuss some of the package properties
2. Add Connection Managers for our data source and destination
3. Add tasks to the package Control Flow
4. Add tasks to the package Data Flow
5. Execute the package in BIDS

1. Creating a new SSIS Package
To begin, launch BIDS by selecting SQL Server Business Intelligence Development Studio from the Microsoft SQL Server 2008 program group. Click File --> New --> Project on the top level menu to display the New Project dialog. Select Business Intelligence Projects as the project type, then Integration Services Project as the template; fill in the dialog as shown below:

After creating this new project and solution, you can navigate to the Sample folder in Windows Explorer and see the following:

The Sample folder holds solution file (SSIS-Sample1.sln) and contains the SSIS-Sample1 folder which holds the Project. The Sample folder was created as a result of clicking Create directory for solution.

You will see the following in the Solution Explorer which is located in the top right corner of the window:

By default a new SSIS package is added when you create an Integration Services Project; you can right click on it and rename it.
To add a new SSIS package, right click on the SSIS Packages node under the SSIS-Sample1 project and select New SSIS Package from the popup menu. A new package Package1.dtsx will be created. Rename this with SalesForecastInput.

 
2. Connection Managers
This section is our second step in creating a simple SSIS package from scratch. SSIS packages typically interact with a variety of data sources. There is a Connection Managers area on the design surface where you can specify each data source that you will access.
In this section we will add two data sources to our package - one to access the AdventureWorksDW database and another to access our Excel spreadsheet.

To add the AdventureWorksDW connection manager, simply right click inside the Connection Managers area then choose New OLEDB Connection… from the popup menu. The Configure OLEDB Connection Manager wizard will be displayed; click the New button to display the Connection Manager dialog and fill it in as follows:

 

In my case the AdventureWorksDW database is on my local machine. Change the Server name property as necessary for your environment. Click the Test Connection button to make sure you can connect to the database. Click OK to complete this step.

 

To add a connection manager for our Excel spreadsheet, right click inside the Connection Managers area then choose New Connection from the popup menu, then select EXCEL from the Add SSIS Connection Manager dialog. The Excel Connection Manager dialog will be displayed; enter a file name as shown below:

We are now finished adding the necessary Connection Managers to our package.

3. SSIS Control Flow
This is our third step in creating a simple SSIS package from scratch. Control Flow contains the various tasks that the package will perform.

 

Add Data Flow Task

For our current package, we need a Data Flow task. Drag and drop the Data Flow Task from the Toolbox onto the Control Flow designer.

 

Right click inside the Data Flow Task rectangle then select Rename from the popup menu. Enter Create Sales Forecast Input Spreadsheet as the new name for the task.

 

4. SSIS Data Flow
This is our fourth step in creating a simple SSIS package from scratch. Data Flow designer contains the various tasks that will be performed by a Data Flow task specified in the Control Flow.
To begin, click on the Data Flow tab (or double click on Data Flow Task) in the designer and you should see the empty Data Flow designer.

 

Add Data Flow Task
For our current package we want to retrieve some data from the AdventureWorksDW database and output it to an Excel spreadsheet. To do this, we need an OLE DB Source and an Excel Destination. In addition, we need to add a column to the Data Flow; this column will be the numeric column in the spreadsheet where the user enters the forecast amount. Drag and drop an OLE DB Source, Derived Column, and Excel Destination from the Toolbox onto the Data Flow designer. After doing so the Data Flow designer should look like this:

 

The red icons inside of the tasks are an indicator that there is some configuration required. Right click on the OLE DB Source then select Edit from the popup menu. This will open the OLE DB Source Editor on the Connection Manager page. Select SQL Command in Data access mode and write following query in SQL command:

SELECT

     G.GeographyKey

     ,G.SalesTerritoryKey
     ,G.City
     ,G.StateProvinceName AS [State]
     ,G.PostalCode
FROM dbo.DimGeography AS G (NOLOCK)
INNER JOIN dbo.DimSalesTerritory AS T (NOLOCK)

     ON T.SalesTerritoryKey = G.SalesTerritoryKey

WHERE T.SalesTerritoryCountry = 'United States'
ORDER BY G.StateProvinceName,G.City

 

The SQL command text contains a simple query to retrieve the list of cities and states in the United States.

Click on Columns to display the column mappings as shown below:

 

By default all columns returned from the query are selected and the Output Column names are the same. The Output Column names can be edited. Click OK to complete the OLE DB Source Editor.

 

You will now see a green and a red line dangling from the OLE DB Source task on the Data Flow designer as shown below:
 

The green line is the normal data flow and the red line is the error flow. Drag the green line and connect it to the Derived Column task. Notice that the red icon is now gone from the OLE DB Source task and it is now properly configured.

 

The Derived Column task allows us to add a column to the Data Flow. Right click on it, select Edit, and then fill in the Derived Column transformation Editor as shown below:
 

We have now added a new currency column to the data flow with a name of Forecast, and a value of 0. Click on the Derived Column task and drag the green arrow to connect to the Excel Destination task.

Right click the Excel Destination task and select Edit from the popup menu. The Excel Destination Editor dialog will be displayed. Click the New button next to the Name of the Excel Sheet to display the Create Table dialog. The Create Table dialog allows us to create a new table in the Excel spreadsheet. The columns and their types are determined by the data flow. We configured an OLE DB Source task that executes a query and a Derived Column task that added the Forecast column to the data flow. You can edit the CREATE TABLE script if you like. Click OK on the Create Table dialog and the Excel Destination Editor Connection Manager page will look like this:
 

We configured the Excel Connection Manager in step two of this tutorial. Click on Mappings to display the Mappings page as shown below:
 

The above dialog sets the Destination Column names to the Input Column names by default.

At this point we have completed the Data Flow. Note that there are no longer any red icons in the task rectangles. We will proceed to the final step in this section of the tutorial and execute the package.

 

5. Executing a Package in BIDS

This is our fifth and final step in creating a simple SSIS package from scratch. Here we will execute the package within BIDS.

To begin, right click on the SSIS package SalesForecastInput.dtsx and select Execute Package from the popup menu.

 

While the package is running you will see each task turn green if it completes successfully or red if it fails.

Here is the Control Flow after the package has run successfully:

Here is the Data Flow after the package has run successfully:

We can see the execution result by click on Execution Results tab.
We can open the Excel spreadsheet that the package created and see the following (only a portion of the spreadsheet is shown):

The above spreadsheet shows the columns from our query plus the Forecast column which we added with the Derived Column task.


This completes our tutorial section on creating a simple SSIS package.

T-SQL Puzzle 4 - Can we rollback after TRUNCATE?

You know that TRUNCATE is DDL command and we can't rollback DDL commands as per BOL.
So what will be the output of below query:

CREATE TABLE Test(Col int)
GO

INSERT INTO Test (Col)
VALUES (1), (2), (3)

SELECT * FROM Test

BEGIN TRAN
   TRUNCATE TABLE Test
   SELECT * FROM Test
ROLLBACK TRAN
SELECT * FROM Test

DROP TABLE Test

T-SQL Puzzle 3 - Syntax Behaviour

What will be the out of below SELECT statements:

SELECT 1.Columns
SELECT 1.ColumnName
SELECT (1.)ColumnName
SELECT '1.'ColumnName
SELECT 1.#ColumnName
SELECT $1.ColumnName
SELECT 1.[ a%^& .ColumnName]

Has anyone seen this behavior?
Do you have any idea what's happening?

SSIS Part 2 - Creating SSIS packages using Import & Export wizards

SQL Server Management Studio (SSMS) provides Import and Export Wizards which can be used to transfer data from one source to another. You can choose from a variety of source and destination data source types, select tables to copy or specify your own query to extract data, and save this as an SSIS package. I guess the Import and Export Wizard is a good starting point for learning about SSIS packages so I will walk through the steps to run these wizards:

1. Export Wizard

SSMS provides the Export Wizard task which can be used to copy data from one data source to another. Here I'll go through the Export Wizard to export data from a SQL Server database to an Excel Sheet.

Note: I am taking example of AdventureWorksDW to explain. You can download the AdventureWorksDW sample database from the CodePlex.

Below are the steps to create SSIS Package using Export Wizard

Step1: Launch SSMS and connect to the Database Engine. For demonstration purposes I am using AdventureWorksDW database. Right click on the AdventureWorksDW database in the Object Explorer, select Tasks, and then Export Data… from the context menu to launch the Export Wizard. Click Next to advance past the Welcome dialog (if shown).

Step2: Choose a Data Source. The Choose a Data Source dialog allows you to specify the source of your data. Since we are running the Export wizard, the dialog will be displayed with the values already filled in as shown below:

Step3: Click on Next to choose a destination. The Choose a Destination dialog allows you to specify the destination data source for the data you are exporting. For example we will export our data to Excel so we will use this Excel spreadsheet as the destination. Fill in the dialog as follows:

Step4: Click Next to Specify Table Copy or Query dialog. The Specify Table Copy or Query dialog allows you to choose whether to export data by selecting tables and/or views from the data source or specifying a query to extract data. Select Copy data from one or more tables or views option and click Next to proceed to the Select Source Tables and Views dialog.

Step5: The Select Source Tables and Views dialog allows you to select the tables and views that you want to export. For our demonstration we are going to select the DimGeography table as shown below:

You can click the Preview… button to view the first 100 rows of the data in the data source.

You can click the Edit Mappings… button to review the column mappings from the data source to the data destination. You can click the option to drop and recreate the table in the destination data source; by default this option is unchecked.
You can click the Edit SQL… button to review and/or edit the SQL to create the table in the destination data source. Click OK twice to return to the Select Source Tables and Views dialog, then click Next to proceed to the Save and Execute Package dialog.

Step6: The Save and Execute Package dialog gives you options to perform the export operation and to create an SSIS package and save it to SQL Server or the file system as shown below:

Step7: Click Next to proceed to the Save SSIS Package dialog. The Save SSIS Package is invoked if you chose to save your export operation as an SSIS package on the Save and Execute Package dialog. Fill in the dialog as shown below:

Step8: Click Next to the Complete the Wizard dialog. The Complete the Wizard dialog shows a summary of the options that we have chosen for the export operation. Now click Finish to execute the SSIS package. Once the command is over, you will see the number of rows transferred from Source to Destination.

We are done! You can open the Excel spreadsheet and view the table that was exported. You can also view the package at location D:\SSIS.