Solution
Configuration Management
To recall the configuration management strategy, you want to start with an environment variable that points you to an XML configuration file. The configuration file modifies a SQL Server connection manager that is used by two more package configurations.
You start by setting up your server. To begin, you must create a system environment variable. You typically create environment variables under your computer’s Advanced System Properties window. The environment variable information should be as follows:
Variable Name: SSIS_PDS_CONFIG_FILE Variable Value: C:\SSIS_PDS\CH02 Framework\~CA SSIS_PDS_Management_Framework.dtsConfig
Next, you must create the XML configuration file. You can do so by going into BIDS and creating an XML configuration that will replace the connection string of a connection manager that points to the SSIS_PDSdatabase, or you can use the XML provided in Figure 2-4. Put this XML information into a file namedSSIS_PDS_Management_Framework.dtsConfig, and place the file under the directory C:\SSIS_PDS\CH02 Framework.
Figure 2.4. Figure 2-4
Now that you’ve prepped the server, you can work in SSIS. You want to end up with a set of configurations in a package that will allow you to dynamically configure information from a SQL Server. Figure 2-5 shows what the final product will look like.
Figure 2.5. Figure 2-5
Now that you know where you’re going, let’s walk through the steps to build it by starting with a blank SSIS package, named SSIS_PDS_Template.dtsx. Before setting any configurations, you create the connection to the database where all your configurations and log entries will exist. In this example, the database is namedSSIS_PDS. You’ll create a standard OLE DB connection to a local server using that database. After you create theSSIS_PDS connection manager, you can set up the package configurations.
You can enter the Package Configurations Organizer by going to the SSIS menu in BIDS, or right-clicking on the design area in the Control Flow and selecting the Package Configurations option. Select the checkbox to enable package configurations and add a configuration of type “XML configuration file.” By selecting the second option of “Configuration location is stored in an environment variable,” you can create an indirect configuration to use your environment variable. Select SSIS_PDS_CONFIG_FILE from the drop-down list, and you’ve created your first package configuration.
NOTE
If you don’t see SSIS_PDS_CONFIG_FILE in the drop-down list, you may need to close and reopen BIDS. Any new environment variables will not be available to open applications. Even if you make a change to an existing environment variable and you already have BIDS open, be sure to close and reopen it to reload the correct value.
The next configuration is a SQL Server configuration, which uses the SSIS_PDS Connection. When asked to select a Configuration table, choose the New button and click OK to create the configuration table. Or you can use the following code to create the table, and then select the dbo.SSISConfigurations table in the drop-down list.
USE [SSIS_PDS]
GO
CREATE TABLE [dbo].[SSIS Configurations]
(
ConfigurationFilter NVARCHAR(255) NOT NULL,
ConfiguredValue NVARCHAR(255) NULL,
PackagePath NVARCHAR(255) NOT NULL,
ConfiguredValueType NVARCHAR(20) NOT NULL
)
You then add a Configuration filter. Because this is the system-level configuration, call the filterCommonConfigurations. Using this filter fetches all configuration entries that match that filter name to adjust the package at runtime. Depending on whether you’ve created this table before, when you go to the next screen, you may see a dialog window with the message shown in Figure 2-6. If so, choose the Reuse Existing button to prevent overwriting existing configurations. If the next screen asks you to select configurations, select any one property because you must select at least one configuration. It is not important which one, because you will manage configurations directly in the database table you just created.
Figure 2-6
You can then add a third SQL Server configuration that will manage the package-specific configurations. The individual developer needs to make this choice, and it is possible that there will be no package-specific configurations at all. If there are, you can follow the same steps as creating the system-level configuration, but instead of using CommonConfigurations as the filter name, use the package name.
NOTE
The package configurations discussed must be in this exact order, because each preceding configuration affects the current configuration. If you create the configurations in the wrong order, you can move them up or down by using the arrows on right side of the Package Configurations Organizer.
Creating two levels of SQL Server configurations introduces a layered effect, where the package applies the system-level configurations that use the CommonConfigurations filter first. Next, the package applies the package-level configurations with the package filter, overriding the system-level configurations if needed.
One place where this feature would come in handy is if you have a connection string with server and database information at a company level, but a specific user name and password to use for this particular package. The connection string would get applied for all packages and would not overwrite the username needed for your package. You are also ensuring that no matter what happens at a system level, your package will have the last say in what values it should use.
Up to this point, you’ve told the package to read the configurations, but you haven’t actually created any configurations for the package to read. Here are the steps to configure a connection manager:
-
Add a variable to the package. Every time you want to create a connection manager, you must add a matching variable. With your mouse focused on the Control Flow design area, create a new variable named connAdventureWorksDW2008. Set the data type to String and leave the value as an empty string.Figure 2-7 shows the variable window.
Figure 2.7. Figure 2-7
-
Now you can add the connection manager directly to the package, which will be an OLE DB connection to the AdventureWorksDW2008 database named the same. On the properties windows of the connection manager, select the ellipses next to the Expressions property. Set the ConnectionString property to@[User::connAdventureWorksDW2008]. Figure 2-8 shows the full Property Expressions Editor window. This property tells the connection manager to use whatever value is in the connAdventureWorksDW2008variable when the package runs.
Figure 2.8. Figure 2-8
-
Last (but certainly not least), you must add a configuration for the variable. You can add this directly into the dbo.SSISConfigurations table by running the following INSERT statement:
INSERT INTO [dbo].[SSIS Configurations] ([ConfigurationFilter] ,[ConfiguredValue] ,[PackagePath] ,[ConfiguredValueType]) VALUES ('CommonConfigurations' ,'Data Source=localhost;Initial Catalog=AdventureWorksDW2008;~CA Provider=SQLNCLI10.1;Integrated Security=SSPI;' ,'\Package.Variables[User::connAdventureWorksDW2008].Properties[Value]' ,'String')
Any time you need to add a configuration at the system or package level, you must follow the previously described steps to configure a connection manager. You must also run this script on each of your environments, and, at least in the case of connections, the ConfiguredValue field will probably be different on each environment. Fortunately, you only need to do this the first time someone uses that connection or variable. After that time, it will be available for everyone to use, and will be configured automatically, as long as the developer names the variable or connection manager the same as in the database.
You have now successfully set up a package to use package configurations, read configurations from your configuration management system, and use a layered effect to apply the appropriate configurations.
The next section examines the logging of events that happen during package execution.
Logging and Auditing Mechanism
In the “Design” section, you named the objects needed to hold your logging and auditing information. This section looks at the code for creating the objects and adding data, beginning with descriptions of the tables and what data each column contains. Then, the discussion moves on to the stored procedures, and explains the logic used to populate the tables.
2.3.2.1. Storage and Tables
A number of custom tables are used to store the logging and auditing information. Each of them is examined here in detail, and you can get a script that creates all tables from this book’s companion Web site www.wrox.com). Run that script in the SSIS_PDS database, so the tables will be available when you start working with your SSIS package. As an example of one table in the creation script, the following is the script to create the Package table:
CREATE TABLE [dbo].[Package](
[PackageID] [int] IDENTITY(1,1) NOT NULL,
[PackageGUID] [uniqueidentifier] NOT NULL,
[PackageName] [varchar](255) NOT NULL,
[CreationDate] [datetime] NOT NULL,
[CreatedBy] [varchar](255) NOT NULL,
[EnteredDateTime] [datetime] NOT NULL DEFAULT getdate(),
CONSTRAINT [PK_Package] PRIMARY KEY CLUSTERED (
[PackageID] ASC
))
GO
The Package table contains an IDENTITY field that you use to link to other tables. The PackageGUID, PackageName, CreationDate, and CreatedBy columns come from the package’s properties. TheEnteredDateTime column will always be set to the date when the record was inserted. Table 2-1 shows the data type information and data from a sample record.
| Column Name | Data Type | Sample Data |
|---|---|---|
| PackageID | int | 2 |
| PackageGUID | uniqueidentifier | 18535AC3-45E2-4A54-B794-D9ADF9892486 |
| PackageName | varchar(255) | SSIS_PDS_LoadMonthlyInventoryData |
| CreationDate | datetime | 2009-05-10 02:23:06.000 |
| CreatedBy | varchar(255) | JMMCONSULTING\Jessica Moss |
| EnteredDateTime | datetime | 2009-05-11 05:06:56.820 |
Similar to the Package table, the PackageVersion table consists of an IDENTITY column, which, in this case, is used to identify a particular record. It also contains a foreign key to the Package table to identify which package has changed. The remaining columns are directly taken from the package’s properties, except forEnteredDateTime, which records the date the record was inserted. Table 2-2 shows the data type information and sample data.
Although the Package and PackageVersion tables are updated at package runtime, they contain general information about the state of the packages. The rest of the tables reviewed here are specific to package executions.
The first table is the BatchLog table. As previously discussed, batches are created by parent packages. If you have no parent-child package relationships, each package will create its own batch. This table contains an identity to indicate a unique batch, the time the master package started, and the time the master package completed, along with a status field. If any of the children packages failed, or the master package itself failed, the batch will have failed. Table 2-3 lists the data types and sample information for the BatchLog table.
| Column Name | Data Type | Sample Data |
|---|---|---|
| BatchLogID | int | 9 |
| StartDateTime | datetime | 2009-05-11 05:31:11.000 |
| EndDateTime | datetime | 2009-05-11 05:31:11.000 |
| Status | char(1) | S |
The PackageLog table is the center of the design. One record in this table constitutes one execution of a package. It links to the Batch table to be able to group packages together into a batch. It also links to thePackageVersion table, so you know which version of the package is running. The ExecutionInstanceID, MachineName, UserName, StartDateTime, and EndDateTime columns are provided from the package’s properties. Finally, the Status column will contain either an “S” for Success, “F” for Failure, or “R” for Running.Table 2-4 shows the data type information and data from a sample record.
| Column Name | Data Type | Sample Data |
|---|---|---|
| PackageLogID | int | 10 |
| BatchLogID | int | 9 |
| PackageVersionID | int | 6 |
| ExecutionInstanceID | uniqueidentifier | 9C48CFA8-2735-4811-A035-83D74717F95E |
| MachineName | varchar(64) | JMMCONSULTING |
| UserName | varchar(64) | JMMCONSULTING\Jessica Moss |
| StartDateTime | datetime | 2009-05-11 05:31:23.000 |
| EndDateTime | datetime | 2009-05-11 05:31:36.360 |
| Status | char(1) | S |
The PackageErrorLog table has a zero-to-many type relationship with the PackageLog table. In other words, for every record in the PackageLog table, you could have no records in the PackageErrorLog, or you could have multiple records. Having records is completely dependent on whether the package threw any error events. If the package did throw an error, this table links to the PackageLog table, and includes the SourceName, SourceID, ErrorCode, and ErrorDescription from the package’s properties. The LogDateTime field contains the time that this record was inserted. Table 2-5 shows the data type information and sample data.
The PackageTaskLog table contains an IDENTITY field to uniquely identify a record and a link to the PackageLogtable. The remaining columns come from the task properties within the package. Similar to the PackageErrorLogtable, this table can have a zero-to-many type relationship with the PackageLog table, depending on the number of tasks that the package contains. Table 2-6 lists the data type information and sample data for this table.
Table 2-6
The PackageVariableLog table also has a zero-to-many type relationship with the PackageLog table, because it only comes into play if a variable has the Raise Change Event flag set and the variable’s value changed. If that situation does happen during the execution of the package, the information will be logged from the package’s information with the time that the record was inserted. The PackageVariableLog table also contains an IDENTITYfield and the link to the PackageLog table. Table 2-7 shows the data type information and data from a sample record.
2.3.2.2. Stored Procedures
Now take a look at the logic in the stored procedures used to load data into these tables. This discussion of each stored procedure shows the script to create that stored procedure, and then how to execute it from within the package. All creation scripts should be executed in the SSIS_PDS database to give the package access to them.
In passing data between packages, the package needs several variables. To ensure availability later, create these variables now, as shown in Table 2-8.
Control Flow Stored Procedures
The control flow calls the LogPackageStart and LogPackageEnd stored procedures. To run them, you use Execute SQL Tasks in your SSIS package. You should place one Execute SQL Task before all other tasks in the control flow, and the other Execute SQL Task after all other tasks in the control flow, as shown in Figure 2-9.
Figure 2-9
The LogPackageStart stored procedure contains the most complex logic of the set. Following is the code needed to create the script:
CREATE PROCEDURE [dbo].[LogPackageStart]
( @BatchLogID int
,@PackageName varchar(255)
,@ExecutionInstanceID uniqueidentifier
,@MachineName varchar(64)
,@UserName varchar(64)
,@StartDatetime datetime
,@PackageVersionGUID uniqueidentifier
,@VersionMajor int
,@VersionMinor int
,@VersionBuild int
,@VersionComment varchar(1000)
,@PackageGUID uniqueidentifier
,@CreationDate datetime
,@CreatedBy varchar(255)
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @PackageID int
,@PackageVersionID int
,@PackageLogID int
,@EndBatchAudit bit
/* Initialize Variables */
SELECT @EndBatchAudit = 0
/* Get Package Metadata ID */
IF NOT EXISTS (SELECT 1 FROM dbo.Package WHERE PackageGUID =
@PackageGUID AND PackageName = @PackageName)
Begin
INSERT INTO dbo.Package (PackageGUID, PackageName, CreationDate, CreatedBy)
VALUES (@PackageGUID, @PackageName, @CreationDate, @CreatedBy)
End
SELECT @PackageID = PackageID
FROM dbo.Package
WHERE PackageGUID = @PackageGUID
AND PackageName = @PackageName
/* Get Package Version MetaData ID */
IF NOT EXISTS (SELECT 1 FROM dbo.PackageVersion
WHERE PackageVersionGUID = @PackageVersionGUID)
Begin
INSERT INTO dbo.PackageVersion (PackageID, PackageVersionGUID, VersionMajor,
VersionMinor, VersionBuild, VersionComment)
VALUES (@PackageID, @PackageVersionGUID, @VersionMajor, @VersionMinor,
@VersionBuild, @VersionComment)
End
SELECT @PackageVersionID = PackageVersionID
FROM dbo.PackageVersion
WHERE PackageVersionGUID = @PackageVersionGUID
/* Get BatchLogID */
IF ISNULL(@BatchLogID,0) = 0
Begin
INSERT INTO dbo.BatchLog (StartDatetime, [Status])
VALUES (@StartDatetime, 'R')
SELECT @BatchLogID = SCOPE_IDENTITY()
SELECT @EndBatchAudit = 1
End
/* Create PackageLog Record */
INSERT INTO dbo.PackageLog (BatchLogID, PackageVersionID, ExecutionInstanceID,
MachineName, UserName, StartDatetime, [Status])
VALUES(@BatchLogID, @PackageVersionID, @ExecutionInstanceID, @MachineName,
@UserName, @StartDatetime, 'R')
SELECT @PackageLogID = SCOPE_IDENTITY()
SELECT @BatchLogID as BatchLogID, @PackageLogID as PackageLogID, @EndBatchAudit as
EndBatchAudit
END
GO
It starts by checking to see whether matching entries in the Package and PackageVersion tables exist based on the input arguments. If not, it will create appropriate new entries and store the identity fields to be used in thePackageLog table. Before you get that far, you must check the batch status. If a BatchLogID was passed in, you know you are already part of a batch and do not need to create a new record. If there is no BatchLogID, it will come in as 0, and the stored procedure will create a new record in the BatchLog table. You can then take theBatchLogID along with the PackageVersionID values you just created (and/or retrieved) to insert the start of the execution into the PackageLog table. The statuses for both the BatchLog and PackageLog tables will be set to “R” for Running.
To execute this stored procedure, set the first Execute SQL Task to use the SSIS_PDS connection manager that you have already manipulated when working with configurations. In the SQLStatement property, enter the following code:
exec dbo.LogPackageStart @BatchLogID = ? ,@PackageName = ? ,@ExecutionInstanceID = ? ,@MachineName = ? ,@UserName = ? ,@StartDatetime = ? ,@PackageVersionGUID = ? ,@VersionMajor = ? ,@VersionMinor = ? ,@VersionBuild = ? ,@VersionComment = ? ,@PackageGUID = ? ,@CreationDate = ? ,@CreatedBy = ?
Remember that the last statement of the stored procedure is a SELECT statement that returns information for the package. You must tell the Execute SQL Task to retrieve that data, which you can do by setting the ResultSetproperty to “Single row.” At this time, your Execute SQL Task Editor screen should like Figure 2-10.
Figure 2.10. Figure 2-10
Next, you must set the input parameters that the stored procedure needs. On the Parameter Mapping menu, add parameters and fill out the values exactly as shown in Figure 2-11. These variables map to the question marks in your SQLStatement, and must be in the correct order to run successfully.
Figure 2-11
To capture the data returned in the ResultSet, you map returned column names to the existing variables in your package on the Result Set menu. Set these values to match Figure 2-12.
Figure 2.12. Figure 2-12
The LogPackageEnd stored procedure records the time that the package finished execution, and marks the status as “S” for success in the PackageLog table. If the EndBatchAudit flag is flipped, it also marks the batch status to “S” and records the time in the BatchLog table. If an error occurred during the package and batch, this stored procedure will not be called, and those columns will be updated by the next stored procedure to be reviewed,LogPackageError. The creation script contains this logic:
CREATE PROCEDURE [dbo].[LogPackageEnd]
( @PackageLogID int
,@BatchLogID int
,@EndBatchAudit bit
)
AS
BEGIN
SET NOCOUNT ON
UPDATE dbo.PackageLog
SET Status = 'S'
, EndDatetime = getdate()
WHERE PackageLogID = @PackageLogID
IF @EndBatchAudit = 1
Begin
UPDATE dbo.BatchLog
SET Status = 'S'
, EndDatetime = getdate()
WHERE BatchLogID = @BatchLogID
End
END
GO
To set up the LogPackageEnd stored procedure, you follow similar logic to the LogPackageStart stored procedure. Using the second Execute SQL Task in the package, you set the connection to SSIS_PDS. You can then put the following code into the SQLStatement:
exec dbo.LogPackageEnd @PackageLogID = ? ,@BatchLogID = ? ,@EndBatchAudit = ?
The final Execute SQL Task Editor screen should like Figure 2-13.
Figure 2.13. Figure 2-13
This stored procedure doesn’t return any information, so you do not need to set a ResultSet. The only setup you have left is on the Parameter Mapping menu. Set the values to look like Figure 2-14.
Figure 2-14
Event Handler Stored Procedures
This section takes a look at the stored procedures called when an event happens. If an event handler is created for an event, every time the event occurs, the event handler code executes. You can use the same types of tasks that you used in the control flow, including Execute SQL Tasks and Script Tasks. Following are the stored procedures that fall under this category:
-
LogPackageError
-
LogTaskPreExecute
-
LogTaskPostExecute
-
LogTaskVariableValueChanged
To work with the event handlers, you want to click on the Event Handlers tab across the top of the package. All the information you need should be scoped at a package level, so ensure that the Executable drop-down list shows the package name. You select the event that you desire in the “Event handler” drop-down list. As you work with each new event, you must create the design area. If you see the message shown in Figure 2-15, click the link to open the design area that is similar to the control flow. If, at any time, you decide that you do not want that event handler anymore, click the Delete button next to the “Event handler” drop-down list.
Figure 2.15. Figure 2-15
The LogPackageError stored procedure is only called when an error has occurred. In that case, you insert the error information into the PackageErrorLog table, and set the PackageLog and BatchLog tables” statuses to “F” for Failure with the time of insertion. The logic for LogPackageError is as follows:
CREATE PROCEDURE [dbo].[LogPackageError]
( @PackageLogID int
,@BatchLogID int
,@SourceName varchar(64)
,@SourceID uniqueidentifier
,@ErrorCode int
,@ErrorDescription varchar(2000)
,@EndBatchAudit bit
)
AS
BEGIN
SET NOCOUNT ON
INSERT INTO dbo.PackageErrorLog (PackageLogID, SourceName, SourceID,
ErrorCode, ErrorDescription, LogDateTime)
VALUES (@PackageLogID, @SourceName, @SourceID, @ErrorCode,
@ErrorDescription, getdate())
UPDATE dbo.PackageLog
SET Status = 'F'
, EndDatetime = getdate()
WHERE PackageLogID = @PackageLogID
IF @EndBatchAudit = 1
Begin
UPDATE dbo.BatchLog
SET Status = 'F'
, EndDatetime = getdate()
WHERE BatchLogID = @BatchLogID
End
END
GO
This stored procedure will also be run through an Execute SQL Task, but is located on the OnError event handler, scoped at the package level. The Executable field should have the package name selected, and the “Event handler” field should have OnError selected. This tab should look like Figure 2-16.
Figure 2-16
In the Execute SQL Task’s properties, you must set the Connection to use SSIS_PDS connection manager. You can then set the SQLStatement to use the following code:
exec dbo.LogPackageError @PackageLogID = ? ,@BatchLogID = ? ,@SourceName = ? ,@SourceID = ? ,@ErrorCode = ? ,@ErrorDescription = ? ,@EndBatchAudit = ?
Because the stored procedure doesn’t return any data, you can leave the ResultSet field set to the value of None. After you complete those steps, the Execute SQL Task’s Editor screen should look like Figure 2-17.
Figure 2-17
You then set up your input parameters in similar fashion to the LogPackageEnd Execute SQL Task. Figure 2-18shows the mappings that you need to make on the Parameter Mapping menu.
Figure 2.18. Figure 2-18
The LogTaskPreExecute stored procedure executes every time a new task starts running. As long as the task is not the PreExecute event of the package container, or the auditing Control Flow tasks, the stored procedure will simply insert information into the PackageTaskLog table. Following is the logic in the creation script:
CREATE PROCEDURE [dbo].[LogTaskPreExecute]
( @PackageLogID int
,@SourceName varchar(64)
,@SourceID uniqueidentifier
,@PackageID uniqueidentifier
)
AS
BEGIN
SET NOCOUNT ON
IF @PackageID <> @SourceID
AND @SourceName <> 'SQL LogPackageStart'
AND @SourceName <> 'SQL LogPackageEnd'
INSERT INTO dbo.PackageTaskLog (PackageLogID, SourceName, SourceID,
StartDateTime)
VALUES (@PackageLogID, @SourceName, @SourceID, getdate())
END
GO
The location of the LogTaskPreExecute stored procedure is in the OnPreExecute event handler. It should be scoped at the package level. Drag over an Execute SQL Task to the design window, and set the connection to useSSIS_PDS. Put the following code in the SQLStatement field:
exec dbo.LogTaskPreExecute @PackageLogID = ? ,@SourceName = ? ,@SourceID = ? ,@PackageID = ?
At this time, the Execute SQL Task Editor screen should look like Figure 2-19.
Figure 2-19
You then configure your input parameters on the Parameter Mapping menu. Set them up to look like Figure 2-20.
Figure 2-20
The LogTaskPostExecute stored procedure will update the record just inserted by the LogTaskPreExecute stored procedure with the time that the task completed. Following is the logic used:
CREATE PROCEDURE [dbo].[LogTaskPostExecute]
( @PackageLogID int
,@SourceID uniqueidentifier
,@PackageID uniqueidentifier
)
AS
BEGIN
SET NOCOUNT ON
IF @PackageID <> @SourceID
UPDATE dbo.PackageTaskLog
SET EndDateTime = getdate()
WHERE PackageLogID = @PackageLogID AND SourceID = @SourceID
AND EndDateTime is null
END
GO
This stored procedure acts exactly the same as the LogTaskPreExecute stored procedure, except that its Execute SQL Task will be on the OnPostExecute event handler. In the Execute SQL Task’s Editor screen, set the connection to SSIS_PDS, and add the following code to the SQLStatement field:
exec dbo.LogTaskPostExecute @PackageLogID = ? ,@SourceID = ? ,@PackageID = ?
The Execute SQL Task Editor should match Figure 2-21.
Figure 2.21. Figure 2-21
You are not returning any information, so you do not need to modify anything on the Result Set menu, but you do need to set up your input arguments. Modify the Parameter Mapping menu to look like Figure 2-22.
Figure 2.22. Figure 2-22
The final event handler stored procedure is LogVariableValueChanged. This procedure acts slightly differently than the others you have seen thus far. The logic of the stored procedure is straightforward, in that it just inserts a record into the PackageVariableLog table. Following is the insertion logic:
CREATE PROCEDURE [dbo].[LogVariableValueChanged]
( @PackageLogID int
,@VariableName varchar(255)
,@VariableValue varchar(max)
)
AS
BEGIN
SET NOCOUNT ON
INSERT INTO dbo.PackageVariableLog(PackageLogID, VariableName,
VariableValue, LogDateTime)
VALUES (@PackageLogID, @VariableName, @VariableValue, getdate())
END
GO
The LogVariableValueChanged stored procedure is called from the OnVariableValueChanged event handler, scoped to the package level. For this stored procedure to work, you must add an additional task, a Script Task, before the Execute SQL Task, as shown in Figure 2-23.
Figure 2.23. Figure 2-23
The LogVariableValueChanged stored procedure inserts the value of the variable after it changes into thePackageVariableLog table. Because the variable could be of many different data types, converting the value to a string is important. In your Script Task, select your language of choice in the ScriptLanguage field. Starting with SSIS 2008, you have a choice of either Microsoft Visual Basic 2008 or Microsoft Visual C# 2008. Use Microsoft Visual Basic for this example.
In the ReadOnlyVariables field, select the System::VariableValue option, and in the ReadWriteVariablesfield, select the User::strVariableValue option. These options give the script the appropriate access to those variables. Figure 2-24 shows the completed Script Task Editor screen.
Figure 2.24. Figure 2-24
After the Script screen is configured, you can work on the actual script by clicking the Edit Script button. When variables are accessed from within the package, they are returned as an object, so you can easily use the.ToString function to convert all types of data. If the .ToString function does not work, you will catch the exception that occurs and record the value “Unable to Convert to String:” with the reason why it failed. Following is the Visual Basic code to replace the Main function in the Script Task:
Public Sub Main()
Try
Dim sVarValue As String = Dts.Variables("VariableValue").Value.ToString
Dts.Variables("strVariableValue").Value = sVarValue
Catch ex As Exception
Dts.Variables("strVariableValue").Value = "Unable to Convert to String:" _
& ex.Message
End Try
Dts.TaskResult = ScriptResults.Success
End Sub
Connect the Execute SQL Task to the Script Task. It should only execute if the Script Task succeeds, so ensure that a green precedence constraint is between the two tasks. Now set up the Execute SQL Task to use theSSIS_PDS connection, and put the following code into the SQLStatement property:
exec dbo.LogVariableValueChanged @PackageLogID = ? ,@VariableName = ? ,@VariableValue = ?
After it has been configured, the Execute SQL Task Editor screen should look like Figure 2-25.
Figure 2.25. Figure 2-25
The last step is to set up the variables to be used in the Parameter Mapping menu. Map them to look like Figure 2-26.
Figure 2.26. Figure 2-26
You’ve set up all the code and stored procedures needed to implement logging and auditing. The next section takes a look at the culmination of all your efforts: the template package.
2.3.3. Template Package
When it comes to creating your template package, you’ve already seen most of what needs to occur. You will need to develop and then install your template package for your fellow developers to use.
Development
Follow the configuration design exactly for the environment variable, XML configuration, and first SQL Server configuration. Do not include the second SQL Server package configuration, because developers will create it on a per-package basis.
The next step is to add as many connection managers and variables that you can imagine your developers might use. This step enables you to control the values that are configurable up front. Also add those entries to theSSISConfigurations table. If anyone comes to you with new configurations after you’ve distributed the template, you’ll need to modify the template and redistribute it again. The more you can do up front, the easier your life will be down the road.
You also need to follow all steps listed for the logging and auditing portion. You may not need to include all variables in the audit trail of values changing. If you do not want a variable included, do nothing to it. If you do want it to be included, you need to set the “Raise event when variable value changes” property on the variable toTrue.
Another step needed to support the batching process is to add a final package configuration of type Parent Package Variable. Choose the Parent Variable of BatchLogID to configure the Value of the current BatchLogID. The final screen should look like Figure 2-27. This Parent Package Variable passes the BatchLogID from the parent package to the child package to ensure that the children know they should be in the same batch.
Figure 2-27
Set the security of the package as the final step in the template package. Although you may have reasons to choose otherwise, the most common framework value used is DontSaveSensitive. Because you pass all information in from the SQL Server configuration at either a system-wide or package-specific level, you get that sensitive information from the database, and do not need to keep it in the package. Whichever method you decide to use, now is the time to set that property.
2.3.3.2. Installation
After you’ve developed the template package, you must install it on all developer’s machines. Copy the package to the %ProgramFiles%\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItemsdirectory.
The %ProgramFiles% directory is an environment variable that redirects you to the proper drive for your Program Files folder (and would typically resolve to C:\Program Files).
When the file is copied into each developer’s template folder, the developer can use the template by right-clicking the project and selecting Add New Item. Then, the developer selects the filename for the template. A new package will be created that is an exact duplicate of the one created earlier.
The problem with this newly created package being exactly like the original package is that the new package has the same ID and Name as its parent. This same predicament occurs when you copy and paste a package to clone a package in Solution Explorer. This feature becomes an issue when you have multiple packages logging to the same database. The logging determines whether this package is entirely new based on a new name or new ID.
You can easily fix the problem by generating a new PackageID in BIDS. You can do so by going to the Properties window for the package and selecting <Generate New ID> for the ID property. You can also do it with thedtutil.exe utility by using the –I switch. You can also modify the Name property to match the name you chose for the package.
Other Considerations
You must keep a few other considerations in mind as you’re implementing your SSIS management framework. They are not necessary to have a complete management framework, but may provide different amounts of value to different organizations.
2.3.4.1. Customizations
Although this chapter has introduced a standard SSIS management framework, you may want to customize your framework. Following are a couple of variations you may include:
-
Batch logic modifications
-
Multiple environments on a server
One way to customize your framework is to modify the batching logic. The standard framework includes the parent-child package batch, where all packages are called from a parent that already has a batch ID fall under the same batch. You can modify the logic in the LogPackageStart stored procedure to pull batch information based on different logic. One reason you may want to do so is for a long-running package that you don’t want to call from a master package. You could have it run separately, and pull the current batch ID at that time to use.
The framework is built on the idea that all packages on a server will want to pull the same configuration information. In the scenario where you have multiple environments on one server, you’ll need to modify this logic somewhat. You have the option to only run certain environments during certain times and modifying the XML configuration at that time. You could also have multiple environment variables on the server where there is one per environment. The package would know which one to use, based on a variable passed in through the command-line executable.
Other variations may be needed based on your organization.
ETL Process Framework
ETL packages, whose core purpose is to move data. If you are creating many ETL packages, creating an ETL process framework may be beneficial to your organization. This framework would track the metadata of the data flow, including from where the data came, any manipulations needed, and the destination of the data, along with execution statuses and states.
You do not track this type of data in the SSIS management framework because it is a different focus area. This management framework helps to configure and monitor SSIS, no matter the purpose of the package.
Process Owner
Having one person (or small group) that owns the framework is important. The implementation works best if this person doesn’t report to the developers using the framework. This person is responsible for controlling the rules governing the framework process, including deployments and modifications. This person needs to maintain the configuration database, and make any needed modifications to the SSIS template. This person needs to mediate any conflicting needs, and respond timely to requests. This person will also most likely be the person training new developers (or groups) on how to work with the framework.
Reporting
Rather than training your developers and support team in understanding the logging tables, you can create queries or reports for them to use that will enable them to monitor their packages. Following is an example of a very simple query that returns the execution times of all packages with the most recent runs at the top of the list:
select pkg.PackageName
,convert(time,pkglog.EndDateTime - pkglog.StartDateTime) as DurationSeconds
,pkglog.Status
from dbo.PackageLog pkglog
join dbo.PackageVersion pkgvers on pkglog.PackageVersionID=pkgvers.PackageVersionID
join dbo.Package pkg on pkgvers.PackageID=pkg.PackageID
order by pkglog.StartDateTime desc
s has been discussed in this chapter, you will initially face a number of problems when developing packages. By utilizing an SSIS management framework, you can avoid these issues and increase your developer throughput. The framework described here is a starting point, with variations referenced throughout. If an addition is not described, it still may be the most appropriate for your organization and should be included.
The first step any organization should take is to put an SSIS management framework in place. Following that, you should decide on a set of standards, including how to deploy, store, and scale your packages.
Victoria Little BI Notebook 
Can you please fixe problems with pictures, there is no longer figures and they are very usefull to understand.
thank you
Hi, yah I will updated soon, thanks for that!