SSIS server validation
- Any change in database schema will break the associated SSIS package.
- Generally each change is responsible to identify dependent components to be included in the change scope.
- But what about any missed dependencies. E.g. Recent production issue.
- Such broken SSIS can be identified in Pre-Prod only if pre-prod is well controlled and identical copy of prod environment.
- Process is required to identify packages impacted by dependencies changes like database schema.
- Proactive validation of SSIS package could help to identify the issue and reduce the system downtime.
High level architecture
The solution is capable of validating all ssis packages for sql server version 2008 on-wards. Instead of publishing the full source code below is the pseudo code and sql scripts to validate the packages.
- Create C# console base project which takes the server name you want to validate. The app calls the web service to validate the SQL server (validate SSIS packages on that server).
- Create web api project and expose following methods (Job Controller)
- Validate – start the validation process by creating SQL agent job dynamically on target server
- ValidationFinished – this will be invoked by job when validation is finished.
- GetValidationStatus – this will provide the SQL agent job status currently validating server
The Validate method works as follows..
- Get the SQL Server version
private Version GetServerVersion(string serverName)
{
var connectionString = string.Format("Server={0};Database=master;Trusted_Connection=True;", serverName);
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
return new Version(connection.ServerVersion);
}
}
- Create interface IProcessor and implement it for sql server 2008 and 2012 as they both have different way to validate package
- Based on server version get the eligible concrete implementation.
- The validation engine will create the validation job on target sql server. for each proxy account, the script will create job step to trigger a network share package running under proxy account. (for details see createValidationJob.sql)
- Each validation step gets the package scheduled under the current account and validate the package, populate the results back on central server. and in the end extract the job history and delete the job.)
- The network deployed ssis package uses same engine to validate 2008 and 2012 via IProcessor since validation is done differently for both servers.
The common scripts used for both servers are here (download and rename doc to zip)
The SQL server 2008 validation is done via DTEXEC with /validate command (download and rename doc to zip)
The SQL 2012 validation is done via EXECUTE SSISDB.catalog.validate_project stored proc. (download and rename doc to zip)
The centralized server which keeps the status of SSIS validation looks like
The font end to display the data was build on angular 2 API..
Pixytech 