SSIS DB Connection Test
- Vincent C. Onyia
- Feb 19, 2018
- 2 min read
The excitement around developing a solution from scratch can cause us to overlook some very important steps. The step I am about to discuss is a preliminary one; in which many BI developers underrate.
The majority of unforeseen abends of an SQL Integration Services (SSIS) package is caused by obstructions and/or the lack of proper communication between the data source(s) and their destination(s). This is why it is not just a good idea, but of great development practice to include a Connection Test Task in your Control Flow prior to committing any sort of Database transaction. The Connection Test is a DB transaction of its own, but its intent is to provide you feedback on whether other transactions to that same Database will succeed or fail. This allows us to control the outcome of each and every execution of that package, eliminating the element of surprise.
In this demo, I have created a new Solution with multiple Database connections through Connection Managers and multiple packages (.dtsx files). The execution of these packages is controlled by a Main package (Main.dtsx)
In the Main.dtsx package, I created a Script Task, a couple of Execute Package Task to run the other packages I have created, and an Execute SQL Task to log possible connection error messages.
I have also created 2 variables - "ConnectionSucceeded" which is of boolean type and "ErrorMessage".
In the Script Task (Note: Script Language is C#), I have included my two variables above.
Below is the code from the Script Task -
In summary, the code loops through each connection string in the Connection Manager and registers the information for each of them. Then tries to acquire a successful connection to each of the Databases.
When the connection is successful, it prints out a "Connection acquired successfully..." message and moves on to the next.
If any of the connections were not successful, a "Failed to acquire connection..." message is printed and stored in a variable "errorMessage" which then, in concatenation with other texts, is passed into the package variable - ErrorMessage.
The code then declares the Boolean variable - "failure" as false.
Regardless of whether the variable - "failure" is true or false, the Script Task always submits is result as a "Success".
The "ConnectionSuceeded" variable is given a value "false" if the value of variable "failure" is true and "true" if the value of the variable "failure" is false.
On execution completion, the path succeeding the Script Task relies on a Constraint Editor Expression which takes into account the value of the "ConnectionSucceeded" variable.
If the variable is true as you can see above, the path executes the Execute Package task next. If the variable is set to false, the value of the "ErrorMessage" variable collected in the Script Task is the written to a custom Error Log Table I built, and the package is completed successfully. This is done by an Execute SQL Task by passing the variable value as a parameter.
In this case, all my Connections were successful, and the package execution is complete:
Comments