SSIS Containers

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.

 

SSRS Expressions

SSRS Expressions:

Reporting Services Expressions:

In Reporting Services, Expressions are used frequently in reports to control content and report appearance. Expressions are used throughout the report definition to specify or calculate values for parameters, queries, filters, report item properties, group and sort definitions, text box properties, bookmarks, document maps, dynamic page header and footer content, images, and dynamic data source definitions.

Expressions begin with an equal (=) and are written in Visual Basic. Expressions can include a combination of constants, operators, and references to built-in values (fields, collections, and functions), and to external or custom code. Expressions can be one of the following two types:

• Simple - An expression that is a single reference to an item in a built-in collection, such as, a dataset field, a parameter, or a built-in field. Simple expressions appear on the design surface and in dialog boxes in brackets, such as [FieldName], which represents the underlying expression =Fields!FieldName.Value. You can type simple expressions directly into a text box on the design surface and the corresponding expression text is set as the value of a placeholder inside the text box.


• Complex - An expression that includes more than a simple reference. Complex expressions appear on the design surface as <>. You can create complex expressions in the Expression dialog box or type them directly into the Property pane.

Examples of SSRS Expression

Using Built-in Fields

Display  Report Execution Time in a textbox:

="Report Execution Time: " & Globals!ExecutionTime

Display Page No in a textbox:
="Page " & Globals!PageNumber & " of " & Globals!TotalPages

Similar way you can use other built-in Fields in expressions e.g. Report Folder, Report Name, ReportServerUrl, UserID, Language etc. as shown below:

Date & Time Functions

The Today() function provides the current date. The expression  =Today() can be used in a text box to display the date on the report, or in a parameter to filter data based on the current date. This function will return date in M/d/yyyy 12:00:00 AM format. You can use Format function to display required format. Some of the popular date formats are given below:

Expression	Output
=FORMAT(Today(),"M/d/yy")	8/23/10
=FORMAT(Today(),"MM-dd-yyyy")	08-23-2010
=FORMAT(Today(),"MMM-dd-yyyy")	Aug-23-2010
=FORMAT(Today(),"MMMM dd, yyyy")	August 23, 2010
=FORMAT(DateField,"MMM dd, yyyy hh:mm:ss")	Aug 23, 2010 01:43:33
=FORMAT(DateField,"MMM dd, yyyy HH:mm:ss")	Aug 23, 2010 13:43:33
=FORMAT(DateField,"MMM dd, yyyy HH:mm:ss.fff")	Aug 23, 2010 13:43:33.587
=FORMAT(DateField,"MMM dd, yyyy hh:mm:ss tt")	Aug 23, 2010 01:43:33 PM

Note: FormatDateTime function can also be used to format the date field e.g. =FormatDateTime(Fields!BirthDate.Value, DateFormat.ShortDate)

DateAdd - Returns a Date value containing a date and time value to which a specified time interval has been added. this function can be used in an expression to add/substract time(day, month, year, sec etc.) from given date field:
=DateAdd(DateInterval.Month, 6, Parameters!StartDate.Value)

DateDiff - Returns a Long value specifying the number of time intervals between two Date values.
=DateDiff("yyyy",Fields!BirthDate.Value,Today())


DatePart - Returns an Integer value containing the specified component of a given Date value.
=DatePart("q",Fields!BirthDate.Value,0,0)

=DatePart(DateInterval.Quarter,Fields!BirthDate.Value, FirstDayOfWeek.System, FirstWeekOfYear.System)

There are many other Date &Time functions which can be used expression:

String Functions

• Combine more than one field by using concatenation operators and Visual Basic constants. The following expression returns two fields, each on a separate line in the same text box:

=Fields!FirstName.Value & vbCrLf & Fields!LastName.Value

•Format dates and numbers in a string with the Format function.

=Format(Parameters!StartDate.Value, "M/D") & " through " & Format(Parameters!EndDate.Value, "M/D")

•The Right, Len, and InStr functions are useful for returning a substring, for example, trimming DOMAIN\username to just the user name. The following expression returns the part of the string to the right of a backslash (\) character from a parameter named User:

=Right(Parameters!User.Value, Len(Parameters!User.Value) - InStr(Parameters!User.Value, "\"))

The following expression results in the same value as the previous one, using members of the .NET Framework System.String class instead of Visual Basic functions:
=User!UserID.Substring(User!UserID.IndexOf("\")+1, User!UserID.Length-User!UserID.IndexOf("\")-1)


• Join - Display the selected values from a multivalue parameter
=Join(Parameters!MyParameter.Value,",")

•The Regex functions from the .NET Framework System.Text.RegularExpressions are useful for changing the format of existing strings, for example, formatting a telephone number. The following expression uses the Replace function to change the format of a ten-digit telephone number in a field from "nnn-nnn-nnnn" to "(nnn) nnn-nnnn":
=System.Text.RegularExpressions.Regex.Replace(Fields!Phone.Value, "(\d{3})[ -.]*(\d{3})[ -.]*(\d{4})", "($1) $2-$3")


There are many other function which can be used in expression as shown below:

Conversion Functions

You can use Visual Basic functions to convert a field from the one data type to a different data type.

• 
  
  The following expression converts the constant 100 to type Decimal in order to compare it to a Transact-SQL money data type in the Value field for a filter expression: =CDec(100)

• The following expression displays the number of values selected for the multivalue parameter MyParameter: =CStr(Parameters!MyParameter.Count) 

Decision Functions

The IIF function returns one of two values depending on whether the expression is true or false. The following expression uses the iif function to return a Boolean value of True if the value of Total exceeds 100. Otherwise it returns False:

=IIF(Fields!Total.Value > 100, True, False)

Use multiple IIF functions (nested IIFs) to return one of three values depending on the value of PercentComplete. The following expression can be placed in the fill color of a text box to change the background color depending on the value in the text box.

=IIF(Fields!PercentComplete.Value >= 10, "Green", IIF(Fields!PercentComplete.Value >= 1, "Blue", "Red"))

A different way to get the same functionality uses the Switch function. The Switch function is useful when you have three or more conditions to test. The Switch function returns the value associated with the first expression in a series that evaluates to true:

=Switch(Fields!PercentComplete.Value >= 10, "Green", Fields!PercentComplete.Value > 1, "Blue", Fields!PercentComplete.Value = 1, "Yellow", Fields!PercentComplete.Value <= 0, "Red",)

A third way to get the same functionality uses the Choose function. The Choose function uses the first parameter as an index to one of the remaining function parameters. The first parameter must be an integer. If the background color of a text box in a table is set to this expression, the value of MyIndex controls the color.

=Choose(Fields!MyIndex.Value,"Red","Green","Yellow")

Check the value of the PurchaseDate field and return "Red" if it is more than a week old, and "Blue" otherwise. This expression can be used to control the Color property of a text box in a report item:

=IIF(DateDiff("d",Fields!PurchaseDate.Value, Now())>7,"Red","Blue") 

 

Cascading Parameters in SSRS 2008

Cascading parameters provide a way of managing large amounts of report data. You can define a set of related parameters so that the list of values for one parameter depends on the value chosen in another parameter. For example, the first parameter is independent and might present a list of product categories. When the user selects a category, the second parameter is dependent on the value of the first parameter. Its values are updated with a list of subcategories within the chosen category. When the user views the report, the values for both the category and subcategory parameters are used to filter report data.

In this post we will walk through the steps of building a report and with query parameters for both product category and subcategory items. Then we will develop individual datasets to provide values for the cascading parameters.

I will use AdventureWorks2008R2 database for this article. You can install this database from codeplex.

STEP 1:
Open your existing Report Server project or create a new Report Server project. Add a new report and rename it as CascadingParameters.rdl. Now create a Data Source for AdventureWorksDW2008R2 database. For more information about creating shared Data Source, Click Creating Shared Data Source in SSRS 2008.

STEP 2:
Create three data sets as mentioned below:

1. dsProductCategory: This Data Set will be used to create a parameter for Product Categories. Use below query for this data set:
SELECT ProductCategoryID,Name AS ProductCategory

FROM Production.ProductCategory (NOLOCK)
ORDER BY Name

 2. dsProductSubcategory: This Data Set will be used to create a Cascading Parameter for Product Subcategories. Use below stored procedure for this data set.

CREATE PROC procProductSubcategory
(
   @ProductCategoryID varchar(1000)
)
AS
BEGIN
   SELECT ProductSubcategoryID,Name AS ProductSubcategory
   FROM Production.ProductSubcategory (NOLOCK)
   WHERE ProductCategoryID IN (SELECT Value
   FROM [dbo].[SplitMultivaluedString](@ProductCategoryID,','))
   ORDER BY Name
END
-------- UNIT TESTING ------------------
-- EXEC procProductSubcategory '1'
-- EXEC procProductSubcategory '1,2'
-- EXEC procProductSubcategory '1,3'
----------------------------------------
GO

Note: Function SplitMultivaluedString is used to split comma seperated values of parameter @ProductCategoryID. Click Function to Split Multi-valued Parameters to get T-SQL script of this function.

Result of EXEC procProductSubcategory '1,3' is shown below:

3. dsProduct: This data set will give the list of all the products based on the Product SubCategories selected at run time. Use below stored procedure for this data set:

CREATE PROC procProductsList
(
   @ProductSubcategoryID varchar(1000)
)
AS
BEGIN
   SET NOCOUNT ON

   SELECT
      PC.Name ProductCategory
      ,PS.Name ProductSubcategory
      ,P.Name ProductName,ProductNumber
      ,Color,Size,[Weight]
   FROM Production.Product P (NOLOCK)
   INNER JOIN Production.ProductSubcategory PS
     ON PS.ProductSubcategoryID = P.ProductSubcategoryID
   INNER JOIN Production.ProductCategory PC
      ON PC.ProductCategoryID = PS.ProductCategoryID
   WHERE P.ProductSubcategoryID IN (SELECT Value
    FROM [dbo].[SplitMultivaluedString](@ProductSubcategoryID,','))
END
-- EXEC procProductsList '6,7,8,18,30,36'
GO

Result of EXEC procProductsList '6,7,8,18,30,36' is shown below:

STEP 3:

Once you create above data sets, you can see two parameters created automatically - ProductCategoryID and ProductSubcategoryID as shown below:

Now double click on parameter ProductCategoryID to open Report Parameter Properties window. In General, change the Prompt value as Product Category and check Allow multiple values checkbox. In Available Values, Select Get values from a query option and select dsProductCategory in Dataset, ProductCategoryID in Value field and ProductCategory in Label field. Now click on Default Values, Click on Get values from a query option and select dsProductCategory in Dataset, ProductCategoryID in Value field. Finally click OK to save changes.

Similarily change the properties of parameter ProductSubcategoryID. In General, change the Prompt value as Product Subcategory and check Allow multiple values checkbox. In Available Values, Select Get values from a query option and select dsProductSubcategory in Dataset, ProductSubcategoryID in Value field and ProductSubcategory in Label field. In Default Values, Click on Get values from a query option and select dsProductSubcategory in Dataset, ProductSubcategoryID in Value field.

Finally click OK to save changes.

STEP 4:

In report Design, drag and drop a table control and map with dsProduct data set. select all the columns from dsProduct in the tables and format this as shown below:

Thats all. We are done with a report having one cascading parameter. Click on Preview to view the report. Now you can see that values of Parameter Product Subcategory depends on the parameter Product Category. By Default, Product Category parameter has (Selected All) so Product Subcategory will also display all the values.

Values of Product Subcategory parameter will be filtered as per selection of the values of Product Category parameter, which will filter the actual report data because Product Subcategory parameter is used to filter report data.

Creating SSIS Template Package

One of the most desired feature in SSIS packages development is re-usability. Being a developer, you may need standard packages that can be re-used during different ETL development. In SSIS, this can be easily achieved using template features. SSIS template packages are the re-usable packages that one can use in any SSIS project any number of times.

You can reuse these items when you use a package template to create a new package. You may want to reuse the following items in a package template: 

1. Connection Managers and Log Providers: Log Provider is common thing in almost all the packages. You can create a package that includes a connection manager and a log provider. You can also use that package as a template for other packages.
2. Common Variables and Configurations: In most of the packages you may use common variables and same configurations.
3. Event Handlers: You may need to use same event handlers or error handling across ETL packages in a project.
4. Send Mail tasks: You can create a package that contains an SMTP connection manager, a Send Mail task, and a property expression to build the Subject line. Use this package as a template to create other packages that notify you by e-mail when the package runs successfully or generates an error.
5. Common Task: I have seen many projects where many tasks are common across 70% packages e.g. Execute SQL Task, Data Flow elements, and Control Flow elements. It may differ project to project but its always good idea to encapsulate the common task in a tamplate and use it wherever required. 

How to create SSIS Template Package
Below are the steps to create a new package template in SQL Server Business Intelligence Development Studio:
1. Start SQL Server Business Intelligence Development Studio. Click File, point to New, and then click Project.
2. In the New Project window, click Business Intelligence Projects, and then click Integration Services Project under Visual Studio installed templates, type a name for the project, and then click OK.

3. Add the items that you want from the Toolbox to the Package.dtsx file. Once you are done with pachake template development, Click File, and then click Save Selected Items.
Note: Give a package name that describes the functionality of the package.

4. Click File, and then click Save Copy of PackageName As. Here filename is the name of package.
In the Save Copy of Package dialog box, click File System in the Package location box, type the following path in the Package path box, and then click OK. In this path, drive is the hard disk where Microsoft Visual Studio is installed:
C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItems

Note: Type the path of the Visual Studio 2005 folder in the Package path box in case you didn't use the default location to install Visual Studio.

How to use the Package template in other Project or Solution
1. Start SQL Server Business Intelligence Development Studio. Click File, point to New, and then click Project.
2. In the New Project window, click Business Intelligence Projects, and then click Integration Services Project under Visual Studio installed templates, type a name for the project, and then click OK.

3. In Soultion Explorer, right click on Project name, click on Add and then New Item... Under Visual Studio installed templates, click the template that you want, type a name for the template, and then click Add. In the below screen-shot, MyPkgTemplate is a template I saved in my system.

Note: The default name for the template in the Name box is the name of the template plus an incremented counter. For example, if the template name is MyPkgTemplate.dtsx, the default name is MyPkgTemplate1.dtsx.

Table Partition

Table Partition:

Table partitioning is a data organization scheme in which table data is divided across multiple data partitions or ranges according to values in a table column.

Benefits of table partitioning

There are numerous benefits of table partitioning:

• To improve the scalability and manageability of large tables and tables in Database and Data Warehouse
• Database and Data Warehouse that would benefit from easier roll-in and roll-out of table data
• Furthermore, if a large table exists on a system with multiple CPUs, partitioning the table can lead to better performance through parallel operations.
• A table with varying access patterns might be a concern for performance and availability when different sets of rows within the table have different usage patterns.

 The steps for creating a partitioned table include the following:

1. Create a partition function to specify how a table or index that uses the function can be partitioned.
2. Create a partition scheme to specify the placement of the partitions of a partition function on filegroups.
3. Create a table or index using the partition scheme.

 
Below are the steps to creation Horizontal Table Partition

-- Create the partition function

CREATE PARTITION FUNCTION PartitionFunctionMonthly (int)
AS RANGE RIGHT
FOR VALUES (20100101, 20100201, 20100301)
GO

-- Add the partition scheme
CREATE PARTITION SCHEME PartitionSchemaMonthly
AS PARTITION PartitionFunctionMonthly
ALL TO ( [PRIMARY] )
GO

 -- Create a simple table
CREATE TABLE PartitionTable (
   DateKey int NOT NULL,
   CustomerKey int NOT NULL,
   SalesAmt money,
CONSTRAINT PKPartitionTable PRIMARY KEY NONCLUSTERED
   (DateKey, CustomerKey)
)
ON PartitionSchemaMonthly(DateKey)
GO

------------------------------------
-- Unit Testing of Partitions
------------------------------------
-- Add some rows

INSERT INTO PartitionTable (DateKey, CustomerKey, SalesAmt)
SELECT 20091201, 1, 5000 UNION ALL
SELECT 20100101, 2, 3000 UNION ALL
SELECT 20100215, 7, 6000 UNION ALL
SELECT 20100331, 5, 3000 UNION ALL
SELECT 20100415, 8, 6000
GO

-- A query accesses the entire table, exactly as you'd expect.

SELECT * FROM PartitionTable
GO

--Query partition contents:

SELECT
$partition.PartitionFunctionMonthly(DateKey) AS [Partition#],
COUNT(*) AS RowCount,
Min(DateKey) AS MinDate,
Max(DateKey) AS MaxDate
FROM PartitionTable
GROUP BY $partition.PartitionFunctionMonthly(DateKey)
ORDER BY [Partition#]
GO

 

Sequence Container in SSIS - Different ways of using Sequence Containers

SSIS: Integration Services includes the Sequence container, which makes it simple to divide the control flow in a package into groups of tasks and containers that you can manage as a unit.

Using Sequence containers lets you handle the control flow in a package in more detail, without having to manage individual tasks and containers.

If a package has many tasks, it can be helpful to group the tasks in Sequence containers.

I'll explain different scenarios where Sequence Container play an important role to implement business rules.

Scenario 1:

• Sequence Task SC1 must execute first. SC1 may have many task with or without precedence constraints. The group of these task must execute prior to any other tasks in the package.
• Sequence Task SC2 must start after completion of SC1. SC2 must execute irrespective of Failure/Success of SC1.
• Sequence Task SC3 contains Maintenance Plan Tasks and should be followed by SC2. SC3 must execute only after successfully execution of SC2. 

Scenario 2:

• Sequence Task SC1 must execute first.
• Sequence Task SC2 must start based on logical condition. This condition can be based on package variables. 
• Sequence Task SC3 contains Maintenance Plan Tasks and should be followed by both SC1 and SC2. SC3 must execute after successfully execution of SC1. SC3 should execute regardless of SC2 but if SC2 executes, SC3 must execute after SC2.

  Scenario 3: This is real scenario in many BI applications.

• SC1 must execute prior to other task. This Sequence Container may contain many tasks to pull data from heterogeneous data sources.
• SC2 should be followed by SC1. This can be used to Insert/Update dimension tables and fact tables along with intermediate tables (if any). This container may further contain Sub - Sequence Containers e.g. SC2(a) to handle dimension Insert/Update and SC2(b) to update Intermediate and Fact tables.
• SC3 should be followed by SC2. This may contains SSAS Tasks to Build/Process Cubes. This container may also have Sub - Sequence Containers e.g. SC3(a) to check whether Current Month partition exists or not. If Yes than Do Nothing Else Create Current Month Partitions using Script task. SC3(b) can be used to Process Dimensions and Current Month Partitions.
• SC4 can be used for Maintenance Plan tasks. If SC3 successfully completed (Evolution operation - Expression and Constraint) than take Backup of Cube, Shrink Datamart and Take Backup of Datamart.
• SC5 can be used for Recovery Plan. If SC3 fails, Restore Cube from previous & Latest Backup available..
  

However, there are many more ways of utilizing Sequence Containers. Further, things depends on complexity of business requirement rules defined.

Displaying Dynamic Columns in SSRS Report

Problem: How to display selected columns dynamically in SSRS reports.
Example: A report contains more than 30 fields. Some users want to see only 5 fields, some users 10 fields, and other may want to see 20 fields.
Solution: Add a Report Parameter having the values as the name of all the fields of dataset. Now set hidden expression for each column of the tabular report.

Here is the solution with an example:

STEP1:
Create a report with required dataset. Drag and drop table control and select dataset fields.
In my example, I have following fields in the dataset: Year, Quarter, Month, Date, Product Name, Customer Name, Sales Region, Sales Country, Order Number, Sales Amount.

STEP2:
Create a dataset dsColumns using below query:
SELECT 1 ID, 'Year' AS ColumnName UNION
SELECT 2 ID, 'Quarter' AS ColumnName UNION
SELECT 3 ID, 'Month' AS ColumnName UNION
SELECT 4 ID, 'Date' AS ColumnName UNION
SELECT 5 ID, 'Product Name' AS ColumnName UNION
SELECT 6 ID, 'Customer Name' AS ColumnName UNION
SELECT 7 ID, 'Sales Region' AS ColumnName UNION
SELECT 8 ID, 'Sales Country' AS ColumnName UNION
SELECT 9 ID, 'Order Number' AS ColumnName UNION
SELECT 10 ID,'Sales Amount' AS ColumnName


STEP3:
Create a new parameter with name pDisplayFields and Promt Display Columns as shown below:

In Available Values of Report Parameter Properties wizard, select Get values from a query, select dsColumns in Dataset, ColumName in value field and label field.

In Default Values of Report Parameter Properties wizard, select Get values from a query, select dsColumns in Dataset, ColumName in value field.

STEP4:
Now you have to set the expression to display the colummns which are selected in the pDisplayColumn parameter. Right click on First Column (Year in my example) and click Column Visibility...

Write following expression in Show or hide based on an expression of Column Visibility wizard:
=IIF(InStr(JOIN(Parameters!pDisplayFields.Value,","),"Year")>0,False,True)

Now repeat this expression for all the columns by modify the expression for the respective column name accordingly.

Thats all. Now preview the report. You will see all the columns by default.

Now select required columns in Display Column parameter to modify the report layout at run time.

Cheers!!!

Foreach Loop Enumerators

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.

SSIS provides the following enumerator types:

• Foreach File Enumerator to enumerate files in a folder. The enumerator can traverse subfolders. For example, you can read all the files that have the *.csv file name extension in the folder and its subfolders.
• Foreach Item Enumerator to enumerate items that are collections. For example, you can enumerate the names of executables and working directories that an Execute Process task uses.
• Foreach ADO Enumerator to enumerate rows in tables. For example, you can get the rows in an ADO recordset.
• Foreach ADO.NET Schema Rowset Enumerator to enumerate the schema information about a data source. For example, you can enumerate and get a list of the tables of your SQL Server database.
• Foreach From Variable Enumerator to enumerate the object that a specified variable contains. The enumerable object can be an array, an ADO.NET DataTable, an Integration Services enumerator, and so on. For example, you can enumerate the values of an array that contains the name of Servers.
• Foreach Nodelist Enumerator to enumerate the result set of an XML Path Language (XPath) expression. For example, this expression enumerates and gets a list of all the products of Electronics type: /products/product[@type='Electronics'].
• Foreach SMO Enumerator to enumerate SQL Server Management Objects (SMO) objects. For example, you can enumerate and get a list of the views in a SQL Server database.

Different ways to Execute SSIS Packages

We have the following ways to execute SSIS packages:

• DTExec Command Line Utility
• DTExecUI Windows Application
• SQL Server Agent

DTExec Command Line Utility
SQL Server provides the command line tool DTExec.exe which can be used to execute an SSIS package. DTExec can be run from a Command Prompt or from a batch (.BAT) file.
To begin, open a Command Prompt and navigate to the project folder as shown below (I am taking an example of SalesForcastInput package from local directory "D:\SSIS\Hari\Sample\SSIS-Sample1\SalesForcastInput.dtsx"):

Now type the following command to execute the SalesForecastInput.dtsx package:

DTEXEC /FILE SalesForecastInput.dtsx

To see the complete list of command line options for DTEXEC, type following:
DTEXEC   /? 


It is not necessary to navigate command prompt to package directory before executing DTExec command; You can give the full path of your package as shown below:
DTExec /f "D:\SSIS\Hari\Sample\SSIS-Sample1\SalesForcastInput.dtsx" /conf "D:\SSIS\Hari\Sample\SSIS-Sample1\SalesForcastInput.dtsConfig" /M -1

Here, /f (/File) parameter used to load the package that is saved in the file system in your system. Likewise, /conf (/ConfigFile) parameter used to load the configuration file that is saved in the file system in your system. /M (/MaxConcurrent) specifies the number of executable files that the package can run concurrently.

Click here for more information about DTExec utility and it's parameters.


DTExecUI Windows Application

SQL Server includes the Windows application DTExecUI.exe which can be used to execute an SSIS package. DTExecUI provides a graphical user interface that can be used to specify the various options to be set when executing an SSIS package. You can launch DTEXECUI by double-clicking on an SSIS package file (.dtsx).
To begin, navigate to the your project folder. Double-click on the package (For instance, SalesForecastInput.dtsx in my example) and you will see the following multi-page dialog displayed:

As you can see there are many settings available when you use this utility. As a general rule you can simply click the Execute button to run your package. You can also fine tune your execution by clicking through the various screens and entering your own settings. After changing the settings click on Command Line which will show you the DTExec command line based on the settings you have chosen.
Note: If you have a configuration file which is not configured at package level, then do not forget to add the configuration file through Configurations setting.


Scheduling SSIS Package through SQL Server Agent

SQL Server Agent includes the SQL Server Integration Services Package job step type which allows you to execute an SSIS package in a SQL Server Agent job step. This can be especially handy as it allows you to schedule the execution of SSIS package so that it runs without any user interaction.
To begin, open SQL Server Management Studio (SSMS), connect to the Database Engine, and drill down to the SQL Server Agent node in the Object Explorer. Right click on the Jobs node and select New Job from the popup menu. Go to the Steps page, click New, and fill in the dialog as shown below:



In the above example, the SSIS package to be executed is deployed to SQL Server (i.e. the MSDB database). You can also execute packages deployed to the file system or the SSIS package store. Note that the Run as setting is the SQL Agent Service Account. This is the default setting although from a security standpoint it may not be what you want. You can setup a Proxy that allows you to give a particular credential permission to execute an SSIS package from a SQL Server Agent job step.

Trigger SSIS package when files available in a Folder

Trigger SSIS package when files available in a Folder

Recently I have goon through a scenario where my client needs to run the SSIS package when the files placed in folder. To run the package automatically when the file exists we have an external tool called FileWatcherTask which is a great user friendly (be aware that FileWatcherTask can cause memory leak and it’s been discussed in the SSIS community. It sounds like you can avoid using FWT altogether) but we can find similar task in Control Flow Tool box, which is WMI Event watcher task. 

Different Approaches to run the Package when file exists.

• Use the WMI Event Watcher task to trigger the SSIS packages as files arrive.
• Create a Windows service that uses WMI to detect file arrival and launch packages.

Let’s discuss each of the available approach.

In this post I would like to show how to WMI Event Watcher task and other approaches to run SSIS package automatically when a file exists.

WMI Event Watcher Task:

WMI Event Watcher Task makes SSIS to wait for and respond to certain WMI events that occur in the operating System.

Scenarios where we can use WMI Event Watcher Task:

• To watch a directory for a certain file to be written.
• To watch for the CPU to be free.
• Wait for a given service to start.
• Wait for the memory of a server to reach a certain level before executing the rest of the package or before transferring files to the server.

The task uses a subset of SQL language which is WQL language; it allows us to view information through WMI.
My example is moving a file from Source folder to Archive folder with a FileSystem Task, WMI Event Watcher will pass the execution to FileSystem task when a file placed in Source Folder.
I took two variables to pass the Source Folder path dynamically.

1. User:: Quote = “
2. User::SourcePath = F:\\\\DellMeSoon\

Drag WMI Event Watcher task to the Control Flow Design pane, double click on the task.
In WMI Event Watcher Task Editor, we will need to create WMI Connection Manager, leave the Server name and Namespace options and select the check box to Use Windows Authentication
Click on Test button to test the Connection.


Select WqlQuerySourceType as DirectInput and for WqlQuerySource keep . (dot)

Under Expressions tab and select WQLQuerySource property and use the following WQL Query as Expression.

Expression:

"SELECT * FROM __InstanceCreationEvent WITHIN 10 WHERE TargetInstance ISA "+@[User::Quote]+ "CIM_DirectoryContainsFile" + @[User::Quote]+"
AND TargetInstance.GroupComponent= "+@[User::Quote]+ "Win32_Directory.Name=\\" + @[User::Quote]+ @[User::SourcePath] + @[User::Quote]+ @[User::Quote]

There are three distinct classes:

• __InstanceCreationEvent class, representing creation of a new object of a WMI class,
• __InstanceDeletionEvent class, representing deletion of an existing object of a WMI class,
• __InstanceModificationEvent class, representing modification to an existing object of a WMI class.

These classes are designed with the intention of being paired up with another WMI class, providing the ability to monitor changes to its objects.
WITHIN keyword set the interval between the event taking place and its notification being delivered to the WMI-based application (WMI Event Task, in this case).
WHERE clause includes the ISA keyword followed by the name of a monitored class.

Other properties includes:

ActionAtEvent: Specify whether the WMI event logs the event and initiates an SSIS action, or only logs the event.

AfterEvent: Specify whether the task succeeds or fails after it receives the WMI event, or if the task continues watching for the event to occur again.

ActionAtTimeout: Specify whether the task logs a WMI query time-out and initiates an SSIS event in response, or only logs the time-out.

AfterTimeout: Specify whether the task succeeds or fails in response to a time-out, or if the task continues watching for another time-out to recur.

NumberOfEvents: To specify the number of events to watch for.

Timeout: Specify the number of seconds to wait for the event to occur. A value of 0 means that no time-out is in effect.

Drag FileSystem task to move the File from Source folder to the Archive folder once available.

Now, execute the package. WMI Event watcher task will start watching the Source Folder till the file available. The task will be in execution mode and watch the source folder for every nth time which we gave in the WQL Query.

When the file is placed in the source folder WMI Event Watcher task will pass the execution to the next connected task.

Keep the package in Infinity loop for continuous watching of Source Folder.

Some Important SQL Scripts

 SQL Server Jobs Execution information

Hi ,

Here is the script to find the SQL Server Job Execution information. This information can also be found in the Job History/Job Activity Monitor windows in SSMS.

                   SELECT      [JobName]   = JOB.name,

   4:             [Step]      = HIST.step_id,

   5:             [StepName]  = HIST.step_name,

   6:             [Message]   = HIST.message,

   7:             [Status]    = CASE WHEN HIST.run_status = 0 THEN 'Failed'

   8:                                WHEN HIST.run_status = 1 THEN 'Succeeded'

   9:                                WHEN HIST.run_status = 2 THEN 'Retry'

  10:                                WHEN HIST.run_status = 3 THEN 'Canceled'

  11:                                END,

  12:             [RunDate]   = HIST.run_date,

  13:             [RunTime]   = HIST.run_time,

  14:             [Duration]  = HIST.run_duration

  15: FROM   msdb..sysjobs JOB

  16: INNER JOIN  msdb..sysjobhistory HIST ON HIST.job_id = JOB.job_id

  17: WHERE HIST.run_date=convert(varchar,getdate(),112)

  18: ORDER BY    HIST.run_date, HIST.run_time 

  19:  

  20: /* WHERE    JOB.name = '<job name>' 

  21:    WHERE HIST.run_date='<yyyymmdd>' */

  22:  

  23:  

List of Database Objects in SQL Server

Hi, I would like post on SQL Scripts which I use mostly to get list of database objects like Tables,Views,Triggers,Functions etc.,
List of Views in a database

   1: /*************************List of views in DB************************/

   2: -- using INFORMATION_SCHEMA:

   3: SELECT TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,VIEW_DEFINITION 

   4: FROM INFORMATION_SCHEMA.Views

   5: ORDER BY TABLE_NAME;

   6:  

   7: SELECT TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE 

   8: FROM INFORMATION_SCHEMA.TABLES 

   9: WHERE TABLE_TYPE='VIEW'

  10: ORDER BY TABLE_NAME;

  11:  

  12: -- using the sp_tables system stored procedure:

  13: EXEC sp_tables @table_type = "'VIEW'";

  14:  

  15: -- using the system tables:

  16: SELECT name

  17: FROM sysobjects

  18: WHERE xtype = 'V';

  19:  

  20: -- using the catalog views:

  21: SELECT name FROM sys.views;

List of Tables in a database

   1: /*******************List of user defined tables in DB***************/

   2: -- using sp_tables stored procedure:

   3: EXEC sp_tables @table_type = "'TABLE'";

   4:  

   5: -- using INFORMATION_SCHEMA:

   6: SELECT TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE 

   7: FROM INFORMATION_SCHEMA.TABLES

   8: WHERE TABLE_TYPE = 'BASE TABLE'

   9: ORDER BY TABLE_SCHEMA,TABLE_NAME;

  10:  

  11: -- using the system tables:

  12: SELECT name

  13: FROM sysobjects

  14: WHERE xtype = 'U';

  15:  

  16: -- using the catalog views:

  17: SELECT NAME FROM sys.tables;

List of Users in a database

   1: /*******************List of Users in DB*********************/

   2: SELECT name FROM sysusers;

List of Indexes on a Table

   1: /*******************List indexes on Table***************************/ 

   2: EXEC sp_helpindex '<table with schema>';

List of Constraints on a table and its details

   1: /*******************List Constraints on Table*************************/ 

   2: EXEC sp_helpconstraint '<table with schema>';

   3:  

   4: /**********************Constraint Information*************************/

   5: SELECT k.table_name,

   6: K.TABLE_SCHEMA,

   7:  k.column_name field_name,

   8:  c.constraint_type,

   9:  CASE c.is_deferrable WHEN 'NO' THEN 0 ELSE 1 END 'is_deferrable',

  10:  CASE c.initially_deferred WHEN 'NO' THEN 0 ELSE 1 END 'is_deferred',

  11:  rc.match_option 'match_type',

  12:  rc.update_rule 'on_update',

  13:  rc.delete_rule 'on_delete',

  14:  ccu.table_name 'references_table',

  15:  ccu.column_name 'references_field',

  16:  k.ordinal_position 'field_position'

  17:  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE k

  18:  LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS c

  19:  ON k.table_name = c.table_name

  20:  AND k.table_schema = c.table_schema

  21:  AND k.table_catalog = c.table_catalog

  22:  AND k.constraint_catalog = c.constraint_catalog

  23:  AND k.constraint_name = c.constraint_name

  24: LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc

  25:  ON rc.constraint_schema = c.constraint_schema

  26:  AND rc.constraint_catalog = c.constraint_catalog

  27:  AND rc.constraint_name = c.constraint_name

  28: LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu

  29:  ON rc.unique_constraint_schema = ccu.constraint_schema

  30:  AND rc.unique_constraint_catalog = ccu.constraint_catalog

  31:  AND rc.unique_constraint_name = ccu.constraint_name

  32:  WHERE k.constraint_catalog = DB_NAME()

  33:  AND k.table_name = '<table name>'

  34:  AND k.TABLE_SCHEMA='<schema name>'

  35:  AND k.constraint_name = '<Constraint Name>'

  36:  ORDER BY k.constraint_name,

  37:  k.ordinal_position;

List of Columns in a Table and its details

   1: /********************************List of Table fields******************/

   2: --Using system tables 

   3: SELECT c.name,o.name

   4:  FROM sys.columns c

   5:  JOIN sys.objects o ON c.object_id = o.object_id

   6:  WHERE o.name = '<Table Name>'

   7:  AND o.schema_id=<Schema Name>

   8:  

   9: -- with INFORMATION_SCHEMA:

  10: SELECT column_name,TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME

  11:  FROM INFORMATION_SCHEMA.COLUMNS

  12:  WHERE table_name = '<Table Name>'

  13:  AND TABLE_SCHEMA='<Schema Name>';

  14:  

  15: --Using System procedures for details information of table

  16: EXEC sys.sp_help '<Table with Schema>';

List of Triggers in a database and its details

   1: /*********************List of Triggers for a given table****************/

   2: --Using sysobjects table

   3: SELECT o.name

   4:  FROM sysobjects o

   5:  WHERE xtype = 'TR'

   6:  --AND OBJECTPROPERTY(o.id, 'IsMSShipped') = 0

   7:  AND object_name(parent_obj) = '<Table Name>';

   8:  

   9: --Using sys.triggers table

  10: SELECT name

  11:  FROM sys.triggers

  12:  WHERE is_ms_shipped = 0

  13:  AND object_name(parent_id) = '<Table Name>';

  14:  

  15:  --using System procedure

  16:  EXEC sys.sp_helptrigger '<Table Name with Schema>';

  17:  

  18: --detail information of a Trigger

  19: SELECT sys1.name trigger_name,sys2.name table_name,c.text trigger_body

  20: ,c.encrypted is_encripted,

  21:        CASE

  22:         WHEN OBJECTPROPERTY(sys1.id, 'ExecIsTriggerDisabled') = 1 

  23:         THEN 0 ELSE 1 END trigger_enabled,

  24:        CASE

  25:         WHEN OBJECTPROPERTY(sys1.id, 'ExecIsInsertTrigger') = 1 THEN 'INSERT'

  26:         WHEN OBJECTPROPERTY(sys1.id, 'ExecIsUpdateTrigger') = 1 THEN 'UPDATE'

  27:         WHEN OBJECTPROPERTY(sys1.id, 'ExecIsDeleteTrigger') = 1 

  28:         THEN 'DELETE' END trigger_event,

  29:        CASE 

  30:         WHEN OBJECTPROPERTY(sys1.id, 'ExecIsInsteadOfTrigger') = 1 

  31:         THEN 'INSTEAD OF' ELSE 'AFTER' END trigger_type

  32: FROM sysobjects sys1

  33: JOIN sysobjects sys2 ON sys1.parent_obj = sys2.id

  34: JOIN syscomments c ON sys1.id = c.id

  35: WHERE sys1.xtype = 'TR';

  36:  

List of Functions in a Database

   1: /*****************List of Function in DB***************************/

   2: --Using System tables

   3: SELECT name

   4: FROM sysobjects

   5: WHERE xtype IN ('TF', 'FN', 'IF','AF');

   6:  

   7: -- with INFORMATION_SCHEMA:

   8: SELECT ROUTINE_CATALOG,ROUTINE_SCHEMA,ROUTINE_NAME,ROUTINE_TYPE

   9:       ,DATA_TYPE,ROUTINE_BODY,ROUTINE_DEFINITION

  10: FROM INFORMATION_SCHEMA.ROUTINES

  11: WHERE routine_type = N'FUNCTION';

  12:  

  13: --To modify function

  14: EXEC sys.sp_helptext '<Function Name>'

List of Procedures in a database

   1: /*****************List of Procedures in DB***************************/

   2: --Using System Tables

   3: SELECT name

   4: FROM sysobjects

   5: WHERE xtype IN ('P', 'RF', 'X', 'PC');

   6:  

   7: SELECT name FROM sys.procedures;

   8:  

   9: -- with INFORMATION_SCHEMA:

  10: SELECT ROUTINE_CATALOG,ROUTINE_SCHEMA,ROUTINE_NAME,ROUTINE_TYPE

  11:       ,DATA_TYPE,ROUTINE_BODY,ROUTINE_DEFINITION

  12: FROM INFORMATION_SCHEMA.ROUTINES

  13: WHERE routine_type = N'PROCEDURE';

  14:  

  15: --To Modify Procedures

  16: EXEC sys.sp_helptext '<Procedure Name>'

Number of character occurrences in String

To test, execute the function and ..

Convert alphanumeric to numeric

Below script is to convert alpha numeric mobile numbers to numeric

here I have given mobile number as ‘1-800-MSFT’
Result is:

Generate insert statements of records in a table

We may need to transfer records from Development to Production, we have many ways to do that, let me show how to generate ‘Insert statements’ using StoredProcedure for the required SQL Server table from which we need to transfer data.
download ‘GenerateInsertStatements‘ script to generate ‘Insert Statement’ of a SQL Server Table

Let me show how it works…

Script need two parameters ‘table name’ and ‘schema name’, if we submit those two parameters it will generate ‘Insert statements’ for the given table.



Another case here is to consider IDENTITY column while generating insert data script. Let’s revise the above example by considering Id as identity column.



I have attached the procedure, just try using the procedure in your free time…

Know about costliest query running right now

• By Memory usage

• By Query cost.

Scripts to find details about ‘logins’ & ‘Roles’

Script to find server level logins and role assigned

Script to find database users and roles assigned

Script to find Object level permission for user databases

Execute batch of .sql scripts in a folder.

• This is the script to execute batch of .SQL scripts.
• Scripts are located in the C:\SQL Scripts directory and they have a file extension of .sql.
• Enable xp_cmdshell property.

Script to do regular backup of a Database

This script is used to do regular backups of a given database when running as a scheduled sql job. It appends the date to each backup to prevent conflicts.

Clear the transaction logs of a database

• During development these can get pretty excessive.

• This will stop the transaction logs from growing too large. It is also a good idea to do regular backups of these logs (which shrinks them anyway)