Passage de paramètres à des packages SSIS

Pour gérer l'exécution des packages SSIS et les valeurs des paramètres d'éxécution à leur fournir, une méthode est de stocker tous ces paramètres dans des tables de  base de données. Voici comment faire...

Il peut arriver que certains packages doivent être lancés à différentes reprises avec des valeurs différentes de paramètres. Je vais donc prévoir les tables pour stocker les différentes exécutions et leurs jeux de paramètres.

Je crée une base de données SSISEngine et deux tables : SSISExec contient la liste des exécutions, SSISParameters contient la liste des paramètres et leurs valeurs pour ces exécutions.

USE master
GO
CREATE DATABASE SSISExecEngine
GO
USE [SSISExecEngine]
GO
CREATE TABLE [dbo].[SSISExec](
[ExecId] [int] IDENTITY(1,1) NOT NULL, -- le N° d'exécution a appeler pour exécuter mon package
[Executable] [varchar](50) NULL, -- le nom du fichier DTSX
CONSTRAINT [PK_SSISExec] PRIMARY KEY CLUSTERED
(
[ExecId] ASC
))
GO
CREATE TABLE [dbo].[SSISParameters](
[ParameterId] [int] IDENTITY(1,1) NOT NULL, -- la primary key
[ExecID] [int] NULL, -- le N° d'exécution (en jointure avec la table précédente)
[ParameterName] [varchar](50) NULL, -- le nom de la variable tel qu'elle est dans le package
[ParameterValue] [varchar](50) NULL, -- la valeur à utiliser pour la variable
CONSTRAINT [PK_SSISParameters] PRIMARY KEY CLUSTERED
(
[ParameterId] ASC
))
ALTER TABLE [dbo].[SSISParameters] WITH CHECK ADD CONSTRAINT [FK_SSISParameters_SSISExec] FOREIGN KEY([ExecID])
REFERENCES [dbo].[SSISExec] ([ExecId])
GO

Je lancerai mon package en précisant l'ID d'éxécution par un passage de paramètres à une variable SSISExec. Ainsi mon package connaîtra quel jeu de paramètres il devra aller charger :

dtexec" /f "TestPackage.dtsx" /SET \Package.Variables[User::SSISExec].Properties[Value];"2" /REPORTING E

Côté SSIS, il me faut charger les variables du package avec les valeurs fournies dans la table pour cette exécution. Un moyen efficace est d'utiliser une tâche de script. Voici un exemple en VB, qui effectue le chargement des variables du package à partir de la table en effectuant les transformations de type nécessaires. Seules les variables de package dont le nom matche seront chargées :

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Data.OleDb

<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

Enum ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum

Public Sub Main()
Try
Dim ht As New Hashtable(20)
Dim c As New OleDbConnection( _
Dts.Connections.Item("SSISExecEngine").ConnectionString)
Dim sqlCommand As New OleDbCommand( _
"SELECT ParameterName, ParameterValue FROM dbo.SSISParameters WHERE ExecID = " + CStr(Dts.Variables("User::SSISExec").Value), c)

c.Open()
Dim sqlResult As OleDbDataReader
sqlResult = sqlCommand.ExecuteReader()
While sqlResult.Read()
If Dts.VariableDispenser.Contains( _
"User::" + sqlResult.GetString(0)) Then
ht.Add(sqlResult.GetString(0), sqlResult.GetString(1))
Dts.VariableDispenser.LockForWrite("User::" + sqlResult.GetString(0))
End If
End While
sqlResult.Close()

Dim vars As Variables
Dts.VariableDispenser.GetVariables(vars)

Dim de As DictionaryEntry
For Each de In ht
'vars(de.Key).Value = de.Value
vars(de.Key).Value = Convert.ChangeType(de.Value, vars(de.Key).DataType)
Next
vars.Unlock()

Dts.TaskResult = ScriptResults.Success

Catch ex As Exception
Dim m As String = ex.Message
While Not ex.InnerException Is Nothing
ex = ex.InnerException
m += vbCrLf + ex.Message
End While
Dts.Log(m, 0, Nothing)
Dts.TaskResult = ScriptResults.Failure
End Try

End Sub

End Class

En extrapolant sur cette méthode, il est envisageable d'assoir sur ce système toute la gestion de l'exécution : une table schedule, une tâche planifiée qui relève toutes les minutes les exécutions à lancer, des tables de journalisation, etc...

Commentaires

1. Le mardi 8 janvier 2013, 14:17 par PGeiger

Hello Arian.
Excellent post. Je n'avais jamais envisagé cela sous cette forme.
En parlant de forme, justement pour la forme, ton exemple est en VB.net et non en C# : Chacun l'aura corrigé...
Cordialement,

Philippe