Non so come mai, ma utlimamente mi scontro con problematiche non molto documentate. Una di queste è la possibilità di lanciare l'esecuzione di Package di SQL Server Integration Services da un'applicazione ASP.NET.
Cercando in rete, ho trovato solamente due possibili soluzioni, entrambe elencate in questo post. Una soluzione richiede la chiamata ad un job di SQL Server Agent, mentre l'altra soluzione fa uso di un Web Service da pubblicare nella macchina di SQL Server.
La prima soluzione l'ho scartata in quanto non sono riuscito a trovare il metodo per poter passare dei parametri utili a valorizzare le variabili del package (mia ignoranza). Mentre la seconda richiede di installare un'ulteriore applicazione (da dover poi mantenere e ripubblicare), nella macchina di SQL Server, e quindi anche questa scartata.
Può sembrare banale, ma avere un'applicazione in più da mantenere non è sempre semplice, specialmente nel caso l'applicazione sia distribuita in diversi server, e sia presente in casa di diversi clienti.
Guardando le diverse soluzioni, mi è venuta l'idea di provare a adattare la soluzione del Web Service, ma eseguita direttamente da un server remoto. Devo ammettere che ci sono state svariate problematiche, ma alla fine, sono arrivato ad una soluzione perfettamente funzionante.
Ecco come eseguire un package di SSIS da remoto:
Per l'esecuzione del package sarà necessario creare un utente nel database (oppure utilizzarne uno di esistente) ed assegnargli il ruolo db_dtsoperator nel database msdb. Questo ruolo attribuisce all'utente i permessi di:
- Enumerate all packages.
- View all packages.
- Execute all packages.
- Export all packages.
- Execute all packages in SQL Server Agent.
Per quanto rigurda la connessione verso il database, nel web.config ho creato una ConnectionString che contiene solamente "Data Source", "User ID" e "Password", questo perchè poi tramite l'uso della classe DbConnectionStringBuilder, verrà poi parsata per recuperarne i singoli valori. Così ho un unico punto dove saranno contenute le credenziali di accesso.
1: <add
2: name="olapServerAuth"
3: connectionString="Data Source=192.168.0.100;User ID=usr;Password=p4$$w0rd;"
4: providerName="System.Data.EntityClient" />
Nell'applicazione sarà necessario referenziare la dll Microsoft.SQLServer.ManagedDTS, e aggiungere alla classe uno using a Microsoft.SqlServer.Dts.Runtime.
Il codice per eseguire il package da remoto sarà il seguente:
1: // recupero delle informazioni per l'autenticazione al server
2: DbConnectionStringBuilder cs = new DbConnectionStringBuilder();
3: cs.ConnectionString = ConfigurationManager.ConnectionStrings["olapServerAuth"].ConnectionString;
4:
5: Application integrationServices = new Application();
6: // recupero del package che deve essere presente in SQL Server
7: Package package = integrationServices.LoadFromSqlServer(
8: @"\Data Collector\Test\TestETL",
9: cs["Data Source"] as string,
10: cs["User ID"] as string,
11: cs["Password"] as string,
12: null);
13:
14: // set delle variabili
15: Variables variables = package.Variables;
16: variables["VariabileDaSettare"].Value = valoreDaSettare;
17:
18: // set delle connessioni
19: Connections connections = package.Connections;
20: connections["AdoNetAnalysisDatabase"].ConnectionString = ConfigurationManager.ConnectionStrings["AdoNetAnalysisDatabase"].ConnectionString;
21:
22: // esecuzione del package
23: DTSExecResult result = package.Execute(connections, variables, null, null, null);
24: if (result != DTSExecResult.Success)
25: ... // errore nell'esecuzione
26: else
27: ... // avvio corretto del package
Da notare che:
- DbConnectionStringBuilder: permette di recuperare ogni singolo elemento che compone una ConnectionString. Utile in questo caso per salvare i dati del server di SQL Server, il nome dell'utente di accesso, e la relativa password in un unico container, e poterli poi recuperare singolarmente.
- Application.LoadFromSqlServer: permette di recuperare un package presente in SQL Server. Se i campi username e password non vengono valorizzati, viene fatto uso della windows authentication.
- package.Variables: recupero delle variabili utilizzate nel package. Utile nel caso sia necessario modificarne il valore di default prima dell'esecuzione.
- package.Connections: recupero delle connessioni utilizzate nel package. Da notare che oltre alle stringhe di connessione, compaiono in questa lista anche le connessioni per i Flat File.
In questi elementi devono essere presenti tutti i valori richiesti, come ad esempio nel caso di una connessione OLEDB dovranno essere presenti: Provider, Application Name e Auto Translate. (per facilità controllate come sono composte nel vostro package).
- package.Execute: avvia l'esecuzione del package con le nuove variabili e connessioni impostate.
- In caso di non successo nell'esecuzione del package, in package.Errors saranno presenti gli errori.
Devo ammettere che ricostruire questo codice non è stato per niente facile, in quanto gli errori che mi si presentavano non erano proprio semplici da decifrare.
La maggiore difficoltà è stata nel capire che le connessioni dovevano riportare tutti i parametri (gli stessi che si possono vedere quando si esegue il package dal SQL Management Studio), ed infine anche il capire quali fossero i permessi minimi da dover dare all'utente per poter eseguire il tutto (per evitare problematiche di sicurezza).
Ma alla fine, tutto ha funzionato alla perfezione, e questo ripaga del tempo speso/investito in questa soluzione.