SSIS Package Logging

Logging is important for SSIS package debugging and auditing. Here I will be describing the process of logging in SSIS packages.

Logging is the process to capture run-time information about a package, helping you to audit and troubleshoot a package every time it is run. SSIS provides log providers that you can use to implement logging in packages. SSIS provide five different types of log providers,

·         SSIS log provider for SQL Server

·         SSIS log provider for Windows Event Log

·         SSIS log provider for XML Files

·         SSIS log provider for Text Files

·         SSIS log provider for SQL Server Profiler

Here I will be using SSIS log provider for SQL Server, which will create a table in the SQL Server that gives log details of the package and tasks within the package.

The logging option can be seen by right clicking anywhere on the design surface of the SSIS package or in the SSIS menu. When you click on the logging option, the following window pops up Fig 1. There will be SSIS package and the various tasks in SSIS package on the left. On the right hand side within the window you have two tabs Providers and Logs, Details. When you choose the Provider type there are different types of providers available:

 Fig 1

·         SSIS log provider for SQL Server

·         SSIS log provider for Windows Event Log

·         SSIS log provider for XML Files

·         SSIS log provider for Text Files

·         SSIS log provider for SQL Server Profiler

Select SSIS log provider for SQL Server, once this is done check the box next to the option SSIS log provider for SQL Server, this gives us the option to create the connection manager or use existing connection manager, then  choose the database connection where you want to store the log records. When SSIS package runs it creates a table called SYSDTSLOG90. In the details tab, choose the Events that need to be tracked, look at the Fig 2 below for reference.

Fig 2

Click the save button will save the logging settings to an xml file, which you can reuse the for other packages.

Once this is done, click the OK button, Logging is now enabled for the SSIS package. Once the package is executed, look for the table SYSDTSLOG90 in the database that you gave in the connection manager. The SYSDTSLOG90 table has columns called source (contains the name of each task which is executed), message which contains more detailed message about the task, event (which contains the event that were chosen to be audited). The table also has columns called start time and end time which indicates the start and end time of each task. These SYSDTSLOG90 is described below. 

Table Name	Field	Field Type	Primary / Unique  / Foreign Key	Description & Purpose
SYSDTSLOG90				This table Contains one row for each logging entry that is generated by packages or their tasks and containers at run time
	Id	int	PK(NOT NULL)	The unique identifier of the logging entry
	Event	sysname	NOT NULL	The name of the event that generated the logging entry
	Computer	nvarchar(128)	NOT NULL	The computer on which the package ran when the logging entry was generated
	Operator	nvarchar(128)	NOT NULL	The user name of the person who ran the package that generated the logging entry
	Source	nvarchar(1024)	NOT NULL	The name of the container or task in which the log event occurred
	Sourceid	uniqueidentifier	NOT NULL	The GUID of the package; the For Loop, Foreach Loop, or Sequence container; or the task in which the log event occurred
	Executionid	uniqueidentifier	NOT NULL	The GUID of the execution instance of the executable that generated the logging entry
	Starttime	datetime	NOT NULL	The time at which the container or task starts to run
	Endtime	datetime	NOT NULL	The time at which the container or task stops to run
	Datacode	int	NOT NULL	An An optional integer value that typically contains a value from the DTSExecResult enumeration that indicates the result of running the container or task: 0 - Success 1 - Failure 2 - Completed 3 - Canceled
	Databytes	image	NULL	A byte array that identifies a return value
	Message	nvarchar(2048)	NOT NULL	A description of the event and the information associated with the event

Deploying SSIS Packages on to Production Server

Deploying SSIS Packages on to Production Server

Performance of packages is one of the main factors considered during the ETL process. To get best performance out of SSIS packages and work with large amount of data business data, we must deploy package onto a high performance production server. Here I will show you how to deploy the packages from development environment to the production environment.

Packages can be deployed by building a deployment manifest and using deployment utility and package installation wizard. After deploying packages on to the server they can be started manually or can be scheduled to run automatically using SQL Server Agent.

Creating Deployment Utility

Configure the SSIS project to generate the manifest file used for deploying the packages to the deployment server. In order to do this we need to change the properties of the project to be deployed.

To do this from solution explorer, right click on the project and select properties.

This opens the project property pages dialog box.  On the left in the configuration properties select Deployment Utility. On the right select the create deployment utility and set it to True.

 When we build the project from the solution explorer or build menu the files are created in the project’s bin\deployment folder. There you can see all the packages of the project and a file with name <Project name>. SSISDeploymentManifest which will launch package installation wizard.

If you are deploying these packages on the production server you need to copy this deployment folder to the production server.

Configuring Installation Wizard

1.       Now double click on the manifest file to open the installation wizard.

2.       Click next on the welcome page

3.       Select the type of deployment. In SSIS we can do two types of deployment File System Deployment and SQL Server deployment.

File System Deployment: This option is used if you want to install the SSIS packages on a specified folder on the system.

SQL Server Deployment: This option is used if you want to install SSIS packages on SQL Server. This option writes the package content to the MSDB system database. This option is most secure and preferable because everything is written to the database catalog rather than file system and packages can be restored by simply backing up and restoring the MSDB database.

4.       Here I am selecting SQL Server Deployment option and click next.

5.       Specify the target server name.

6.       Click next three times keeping the default configurations on each page and click finish.

7.       Now your deployment is successful.

Connecting to Integration Services

1.       To connect to integration services, open SQL Server Management Studio and in the object   explorer and choose Integration Services.

2.       Now connect to the server that you specified as target server above and click connect.

3.       After you connect you can see the server name and two folders. [Running Packages and Stored Packages]

4.       Expand the Stored Packages folder; there you can see two folders, File System and MSDB.

5.       If you have chosen File System Deployment then you can see your packages there else expand the MSDB to find the deployed packages.