SSIS - Check the availability of Data Source

When the tables in the DW are truncated and populated on regular basis, most of the time the truncate script will run first and then the data population from Source to destination will happen. What if the Data Source is not available in mid of the process or at any circumstances? 

 The truncation will occur and the destination will be empty, to avoid this we can check the availability of the source before initiating the truncation or any other tasks. 

To do so, we can use the following steps:

1.       Create variable in the Package (Package level scope) to which will be used to flag that the source is available or not.

2.       Create a script task with the following script in (it will check whether the source is available or not & update the variable)

                Script task:

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Public Sub Main()

        Dim ConMgr As ConnectionManager = Dts.Connections("<SourceConn>")

        Dim ConStr As String = ConMgr.ConnectionString

        Try

            ConMgr.AcquireConnection(Nothing)

            Dts.Events.FireInformation(1, "", "Connection acquired Successfully on" + ConMgr.Name, "", 0, False)

            Dts.Events.FireInformation(2, "", "Connection String=" + ConStr, "", 0, False)

            Dts.Variables("SourceAvailability").Value = "y"

            Dts.TaskResult = ScriptResults.Success

        Catch ex As Exception

            Dts.Events.FireInformation(1, "", "Connection Failed on" + ConMgr.Name, "", 0, False)

            Dts.Events.FireInformation(2, "", "Connection String=" + ConStr, "", 0, False)

            Dts.Variables("SourceAvailability").Value = "n"

            Dts.TaskResult = ScriptResults.Success

        End Try

      End Sub

End Class

3.       Use the precedence Constraint to write the expression as shown in fig . (Double click on the green arrow to go the Precedence constraint Editor) 

Ie, This precedence constraint will let the execution to flow to the next tasks only if the source is available.

Courtesy: got idea from http://consultingblogs.emc.com .