In un progetto che coinvolge più database potrebbe nascere l’esigenza che i nostri SSIS debbano muoversi su una base dati piuttosto che su di un’altra, all’interno della medesima esecuzione. Ad esempio, può capitare che ogni giorno le nostre ETL abbiano la funzione di caricare particolari dati per tutte le strutture di un cliente. In questo caso è utile capire come configurare dinamicamente i Connection Manager dei nostri package.
Supponiamo di impostare due datasource, uno dei quali punta ad un database, mentre l’altro punta ad un’altra struttura (ad esempio, in un datawarehouse, il database di staging e quello datamart).
Per rendere dinamica una connessione, è buona norma, a monte, avere una configurazione di partenza. Ad esempio una tabella su di un database di configurazione centralizzato o un XML, contenenti le connection string relative al database che si vuole raggiungere.
Nell’esempio che segue, prendiamo in considerazione il database centralizzato, con una tabella contentente le stringhe di connessione ai due DB. Chiamiamo la tabella cfg.ConnectionStrings e all’interno mettiamo il codice cliente, la connection string, l’utente e la password, magari criptata.
Ecco come appare la configurazione automatica:
 
Il tutto viene messo in un Sequence container, in modo da definire una transazione in cui includere tutte le operazioni di autoconfigurazione. A questo punto dichiariamo cinque variabili interne al sequence container (clickare sul container prima di aggiungerle, in modo da definire lo scope corretto):
- oConnessioni, di tipo Object, che è l’oggetto che conterrà il resultset delle connectionstring
- Loop_DataSource, stringa, che conterrà il nome del Connection Manager da configurare
- Loop_Connessione, stringa, che conterrà la ConnectionString senza user e password
- Loop_User,stringa, l’user corrente
- Loop_Password, stringa, la password decriptata dell’user corrente
Il primo step è identificato da un Execute SQL Task, il quale non fa altro che ricavare, in base al codice cliente passato, le stringhe di connessione da considerare (nel nostro esempio saranno due, una per ogni datasource). Nel primo task deve essere impostato un resultset di output, mappato a sua volta nella variabile  oConnessioni:
 
A questo punto, all’interno della suddetta variabile avremo una collection di dati da ciclare tramite un ForEach Loop container. Facciamo doppio click sul container appena creato..
Nella finestra Collection impostiamo la proprietà Enumerator  a “For each ADO Enumerator” e selezioniamo alla voce Source Variable la variabile User:oConnessioni.
 
Ciclando sulla collection delle connectionstring è necessario definire il mapping delle variabili. Quindi spostandoci sulla finestra Variable Mapping, impostiamo posizionalmente i campi del resultset con le variabili disponibili.
 
Una volta mappate le variabili, all’interno del For each, esse saranno fruibili da ogni task ivi contenuto.
Andiamo ad aggiungere all’interno del container uno Script Task assegnandogli un nome ed una descrizione nella finestra General. Poi spostiamoci sulla finestra Script:
 
Come nell’immagine andiamo ad indicare il nome delle variabili che passeremo allo script, tramite la proprietà ReadOnly Variables (se ci dovessero servire non in sola lettura, basta specificarle nella ReadWrite Variables).
N.B. Il nome delle variabili è Case Sensitive e tutte devono essere separate da una virgola, possibilmente con al massimo uno spazio tra l’una e l’altra.
Infine premiamo il bottone Design Script.. e iniziamo a scrivere il seguente codice:

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

 

Public Class ScriptMain

 

    Public Sub Main()

        'imposto la nuova connectionstring nella connessione indicizzata dalla variabile Loop_Datasource

        Dim DataSourceName As String = Dts.Variables("Loop_DataSource").Value.ToString()

        Dim conn As ConnectionManager

 

        For Each conn In Dts.Connections

            If conn.Name = DataSourceName Then

 

                Dts.Connections(DataSourceName).ConnectionString = Dts.Variables("Loop_Connessione").Value.ToString & "User ID=" & Dts.Variables("Loop_User").Value.ToString & ";Password=" & Dts.Variables("Loop_Password").Value.ToString

                Exit For

            End If

        Next

 

        Dts.TaskResult = Dts.Results.Success

    End Sub

 

End Class


Fine dell’implementazione. In poche parole, con il semplice parametro CODICE CLIENTE, noi possiamo assegnare dinamicamente la connection string ad ognuno dei nostri DataSource. Da notare, che per avere la configurazione di un solo gruppo di connection managers, è bene definire a priori una buona naming convention, di modo che non vengano rassegnati tutti, anche quelli (come ad esempio quello del database centralizzato di configurazione) che non devono essere nemmeno sfiorati.
Inoltre, anche se questa autoconfigurazione vuole un parametro, potenzialmente, se avessimo più clienti che usano lo stesso SSIS, basta utilizzare la chiamata al SSIS da linea di comando (DTExec) oppure creare dei JOB di SQL Server ognuno dei quali lancia il pacchetto con codice cliente diverso.
A me è servita tantissimo.. spero serva anche a voi

Alla prossima!