Search the contents of SSIS packages

SSIS packages that are deployed to SQL Server or SSIS package Store are stored in msdb..sysdtspackages90 table. Not only SSIS packages but the Maintenance plans are also stored in this table.

Now suppose you are asked to list out all SSIS packages and Maintenance plans:

That update dbo.Sales table (to check dependency of a table).

That use the script file located at “D:\FTP\” folder (to check dependency of a file).

Those fetch data from database SourceDB or use login UserA (to check dependency of a source or user).

If you have not more than a dozen packages than you can check by opening each one in BIDS but if there are hundreds of packages than this approach is a very troublesome.

The better approach is to list all packages in which the particular table, connection or login is used. That would shorten the list of packages that you need to open for more details. For this purpose you can use the msdb..sysdtspackages90 table as below:

 

SELECT [name] 

FROM msdb..sysdtspackages90

WHERE CONVERT(VARCHAR(MAX), CONVERT (VARBINARY(MAX), packagedata))

      like '%dbo.Sales%' --change the literal for your search

How to start and stop windows service from SSIS

To start or stop a windows service from SSIS package add a Script Task and in design script page use the below code. Replace the ComputerName and ServiceName accordingly.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports System.ServiceProcess

Public Class ScriptMain

      Public Sub Main()

        Dim controller As New ServiceController

        Dim serviceStatus As ServiceControllerStatus

        Dim conter As Integer

        'Code below is to atop Window services.

        controller.MachineName = "ComputerName"

        controller.ServiceName = "ServiceName"

If ((controller.Status.Equals(serviceStatus.Running)) Or (controller.Status.Equals(serviceStatus.Paused))) Then

            controller.Stop()

            controller.Refresh()

      End If

        'Code below is to start Window services.

If ((controller.Status.Equals(serviceStatus.Stopped)) Or (controller.Status.Equals(serviceStatus.StopPending))) Then

            controller.Start()

            controller.Refresh()

      End If

Tips to improve performance of Data Flow tasks

SSIS does not have any performance evaluation or monitoring tool. So it is hard to correctly identify the culprit part of a poorly performing package. So proactive approach is the better than reactive and we should implement the best approach while creating a package. Following are few methods that a developer should know in advance to decide the best approach.

1.     Replace the Sort Data Flow item by using ORDER BY

2.     Optimize OLEDB destination using Fast Load

3.     Using CDC and MERGE

4.     Configure the Lookup cache modes

Avoid the asynchronous transformation as much as possible

It is a bit confusing if you know asynchronous processing in Service Broker or an interface application because in these platforms asynchronous processing is implemented for parallel and faster execution. But in SSIS asynchronous means that input records are not processed individually. Output is derived from the full or a part of input recordset. For example in Sort operation the first record can be decided only after checking all records.

 

The one of most important strength of SSIS is it’s buffer-oriented architecture to efficiently load and manipulate datasets in memory. The benefit of this in-memory processing is that you do not need to physically copy and stage data at each step of the data integration. Rather, the data flow engine manipulates data as it is transferred from source to destination. But because of asynchronous transformation SSIS looses its in-memory processing strength. Because asynchronous transformation task’s uses extra memory and the output recordset is buffered in separate memory than the input recordset. All blocking tasks (Aggregate and Sort) and partially blocking tasks (Merge, Merge Join, and Union All) are Asynchronous transformation.

 

Design Description of Alternative 1	Design Description of Alternative 2
In this design, a Script Component generates 100,000,000 rows that first pass through a lookup. If the lookup fails because the source value is not found, then an error record is sent to the Derived Column transformation where a default value is assigned to the error record. After the error processing is complete, the error rows are combined with the original data set before loading all rows into the destination.	Like Design Alternative 1, this design uses the same Script Component to generate 100,000,000 rows that pass through a lookup. Instead of handling lookup failures as error records, all lookup failures are ignored. Rather, a Derived Column transformation is used to assign values to the columns that have NULL values for the looked up column.
With two execution trees in this scenario, the biggest performance bottleneck is related to the extra copy of the data in memory created for the Partially Blocking Union All transformation.	The performance of this solution is approximately 21% faster than Design Alternative 1. With one execution tree in this scenario, the operations are consolidated and the overheard of copying data into a new buffer is avoided.

Configure the Lookup cache modes

Cache Mode specify the caching of data in lookup task. We can specify one of following three lookup mode for an Lookup task:

1.     Full cache

2.     Partial cache

3.     No cache

In full cache all data from loop table are loaded into local cache in advance. In Partial cache mode, lookup table is queried for every new value and the fetched record is cached for further use. In No cache mode lookup table is queried for each record and fetched record from lookup table is not cached. 

Let us understand the hit and load statistics by an example:

Suppose we have two table Invoice and Customer. Invoice table has CustomerID that is foreign key, referencing to the CustomerID of Customer table. Suppose

Number of records in Invoice table = 100,000

Number of records in Customer table = 10,000

Count of distinct CustomerID in Invoice table = 2,000

Now following is the hit-load statistics in different cache modes:

Full cache:

Number of query hit to Customer table = 1

Number of records fetched from Customer table = 10,000

Partial cache:

Number of query hit to Customer table = 2,000

Number of records fetched from Customer table = 2,000

No cache:

Number of query hit to Customer table = 100,000

Number of records fetched from Customer table = 100,000

Using CDC and MERGE

Note: This method is applicable only for data transfer cases where data source is a table from SQL Server 2008 server.

Data flow tasks are mostly used to keep the destination table updated same as source table. The common approach to implement this process as following:

1.     Load all the data from source into a temporary table.

2.     Truncate the destination table

3.     Load the data from temporary table to destination table.

The biggest problem of this approach is transfer of full data from source to destination and for large amount of data this is a major performance concern.

SQL Server 2008 introduced two concets that we can use to optimize this process. One is CDC that is applicable at source and another is MERGE that is applicable at destination of data transfer.

CDC (Change Data Capture): This is an easy and better method to log and track all changes for a table. To start using CDC we have to set enable it at two levels: database and table. To enable CDC on a database execute the sys.sp_cdc_enable_db stored procedure. In a CDC enabled database any table can be configured to enable CDC by using sys.sp_cdc_enable_table <table_name> stored procedure. That’s all to capture all changes of a table. All the changes for a table are stored in a automatically cgenerated table named cdc.<schema_name>_<table_name>_CT

The above screenshot show that when CDC is enabled for table dbo.TestCDC then a system table cdc.dbo_TestCDC_CT is automatically created to store all changes.

Now in SSIS instead of fetching all data from source table we can use the CDC table to get only the changes. For example:

SELECT * FROM cdc.dbo_TestCDC_CT WHERE __$operation IN (1,2,4)

The value 1, 2, and 4 for column __$operation refers to the type of record change Deleted, Inserted and Updated respectively.

MERGE: This is another new feature introduced in SQL Server 2008 that enables us to accomplish multiple INSERT, UPDATE, and DELETE operations in a single statement.Prior to SQL Server 2008, this process required both a Lookup transformation and multiple OLE DB Command transformations. The Lookup transformation performed a row-by-row lookup to determine whether each row was new or changed. The OLE DB Command transformations then performed the necessary INSERT, UPDATE, and DELETE operations. In SQL Server 2008, a single MERGE statement can replace both the Lookup transformation and the corresponding OLE DB Command transformations.

To use the MERGE statement in a package, follow these steps:

1.     Create a Data Flow task that loads all changed data using CDC, transforms, and saves the source data to a temporary or staging table.

2.     Create an Execute SQL task that contains the MERGE statement.

3.     Connect the Data Flow task to the Execute SQL task, and use the data in the staging table as the input for the MERGE statement.

Optimize OLEDB destination using Fast Load

When you insert data into your target SQL Server database, use minimally logged operations if possible. When data is inserted into the database in fully logged mode, the log will grow quickly because each row entering the table also goes into the log. This is implemented using Fast Load options in OLEDB Destination Editor.
Therefore, when designing Integration Services packages, consider the following:

• Try to perform your data flows in bulk mode instead of row by row. By doing this in bulk mode, you will minimize the number of entries that are added to the log file. This reduction will improve the underlying disk I/O for other inserts and will minimize the bottleneck created by writing to the log.

• Set a value for Maximum insert commit size. This option decides the size of data loading transaction. Specify the batch size that the OLE DB destination tries to commit during fast load operations. The default value of 214,748,3647 indicates that all data is committed in a single batch after all rows have been processed. With the default value the transaction log of database can grow up to the limit of disk size and can cause full transaction log error. The bigger problem may arise if you have implemented transaction and the task fails after a large amount of data load. In this case all data load will be rolled back and that may decrease the server performance significantly. So set the value of Maximum insert commit size to a moderate value like 10,000.

 

Replace the Sort Data Flow item with ORDER BY

For some data operations like Merge and Merge Join transformations require sorted inputs. SSIS provide a task “Sort” to sort the input recordset. As we know sorting is a blocking process and degrade the package performance. The better solution is to create the sorted recordset using ORDER BY clause at data source task. This would improve the performance because of two reasons. First it split the load of sorting on the source connection server. If the source server is different than SSIS server than it would help in distribute the processing load on both servers. Second it can use the indexes of source tables to get the sorted recordset. Now when we have pre-sorted source data, we can provide a hint for downstream components that the data is sorted. To provide a hint that the data is sorted, we have to do the following tasks:

• Set the IsSorted property on the output of an upstream data flow component to True.

• Specify the sort key columns on which the data is sorted.

SSIS Checkpoint

•          Checkpoints enable a failed SSIS package to be restarted at the task where the execution was ended.

•          Checkpoint is used to avoid repeating the downloading and uploading of large files or to avoid repeating the tasks that consumes system resources heavily.

•          Checkpoints are enabled by setting the package’s SaveCheckpoints property to True in the SSIS package properties.

•          Once checkpoints are enabled, you also need to tell the SSIS package where to write the checkpoint data. To do this, you must supply a filename to the CheckpointFileName property.

•          In addition, the way SSIS treats running packages where there is an existing checkpoint file is controlled by the CheckpointUsage property. Available option for this property are Never, Always and IfExists.

 

Let us understand the behavior of checkpoint by an example:

Create a new package in SSIS project and change the SaveCheckpoints property to True and CheckpointFileName property to “C:\SSIS_checkpoint.xml”. Add three “Execute SQL Tasks”. The statements and control flow is as below:

Task A: Select 1/1

Task B: Select 1/0 - Which will introduce an error

Task C: Select 1/4

Now execute the SSIS package. It will failed at Task B as below:

Now update SQL statement of the Task B to Task C: Select 1/2 and re-execute the package. This time package will start at Task B as below:

Comparison between T-SQL and SSIS expression

An Expression is a combination of identifiers, literals, functions, and operators that returns a single data value. We are very familiar with functions and operators in T-SQL. But writing an expression in SSIS is not exactly same as in T-SQL. But that is not so much different also to worry about. If we know the difference between the functions and operators of T-SQL and SSIS then we can save our time of learning the SSIS expression separately.

 

Operators: Besides the following 5, all other operators are same in T-SQL and SSIS.

 

	T-SQL	SSIS
Logical AND	AND	&&
Logical OR	OR	||
Logical Not	NOT	!
Equal	=	==
Conditional	IF boolean_expression           expression1 ELSE  expression2	boolean_expression ? expression1 : expression2

 

Functions: In SSIS, functions of following 4 categories are supported.

·         Mathematical functions: To perform calculations based on numeric input values provided as parameters to the functions and return numeric values. Following mathematical functions only are supported in SSIS. Functionality of these functions is same in SSIS and T-SQL:

ABS	LN	SIGN
EXP	LOG	SQUARE
CEILING	POWER	SQRT
FLOOR	ROUND

 

·         String functions:  To perform operations on string or hexadecimal input values and return a string or numeric value.

	T-SQL	SSIS
Search a string in another	CHARINDEX	FINDSTRING
Remove leading trailing spaces	LTRIM and RTRIM	TRIM

 

Following other string functions are supported in SSIS. Functionality of these functions is same as T-SQL:

HEX	REPLACE	RTRIM
LEN	REPLICATE	SUBSTRING
LOWER	REVERSE	UPPER
LTRIM	RIGHT

 

·         Date and time functions: To perform operations on date and time values and return string, numeric, or date and time values. Only one T-SQL date function DATENAME is not supported by SSIS. All other functions are same in T-SQL and SSIS.

·         System functions.

 

Few other differences are as following:

 

	T-SQL	SSIS
Data type conversion	CAST, CONVERT	(DATA_TYPE) Expression
Behavior of NULL	Determines whether or not a given expression is NULL.	Returns a null value of a requested data type.