Populate variables with dynamic values

DataTable dtTableDataDefinition = new DataTable();

OleDbDataAdapter adapter = new OleDbDataAdapter();

adapter.Fill(dtTableDataDefinition, Dts.Variables["User::dsDataDefinitionConfig"].Value);

            foreach (DataRow row in dtTableDataDefinition.Rows)

            {

                if (Dts.Variables.Contains(row["DataDefinitionKey"]))

                {

                    Variables vars=null;

                    Dts.VariableDispenser.LockOneForWrite(row["DataDefinitionKey"].ToString(), ref vars);

vars[row["DataDefinitionKey"].ToString()].Value = row["DataDefinitionKeyvalue"].ToString();

vars.Unlock();

                }

            }

                     Dts.TaskResult = (int)ScriptResults.Success;

Exclusive Application Lock

Get Exclusive Application Lock: 

DECLARE @ProcResult int = 0;

-- Get an exclusive application lock on the package execution resource, to prevent other executions of the same package from doing the same

EXEC @ProcResult = sp_getapplock @Resource = @PackageResourceName,

                                 @LockMode = 'Exclusive',

                                 @LockOwner = 'Session',

                                 @LockTimeout = 0; -- Do not wait for the lock

IF @ProcResult < 0

    RAISERROR('Failed to acquire application lock on %s. sp_getapplock returned %d', 16, 1, @PackageResourceName, @ProcResult);

Here @PackageResourceName is a string specifying a name that identifies the lock resource. The application must ensure that the resource name is unique. The specified name is hashed internally into a value that can be stored in the SQL Server lock manager

Release Exclusive Application Lock: 

DECLARE @ProcResult int = 0;

-- Release a previously acquired exclusive application lock on the package execution resource

EXEC @ProcResult = sp_releaseapplock @Resource = @PackageResourceName,

                                    @LockOwner = 'Session';

IF @ProcResult < 0

RAISERROR('Failed to release application lock on %s. sp_releaseapplock returned %d', 16, 1, @PackageResourceName, @ProcResult);

Make sure the "RetainSameConnection" property on the Database connection manager  ( used to execute the above commands) is set to True.

Matching Data Types

Data Types :

SQL Server                                                                   SSIS

1. DATE                                                                        DT_DBDate

CurrentDateTime String expression

You can generate a timestamp based unique value  using following expression. Use the below string to set the expression property of a variable :

(DT_STR, 2, 1252) DAY( GETDATE() ) +  (DT_STR, 2, 1252)MONTH( GETDATE() )+  (DT_STR, 4, 1252)YEAR( GETDATE() )+ (DT_STR, 4, 1252) DATEPART( "Hh", GETDATE() )+ (DT_STR, 4, 1252) DATEPART( "mi", GETDATE() )+ (DT_STR, 4, 1252) DATEPART( "ss", GETDATE() )+ (DT_STR, 4, 1252) DATEPART( "ms", GETDATE() )

Precedence Constraints

SSIS provides an updated version of the Precedence Constraints we had in DTS. They are the connectors between any two tasks in a flow. They decide the direction of the flow .

1.These Precedence Constraints can be configured in multiple ways.

2. A task can have multiple connectors indicating the multiple tasks that can be concurrently executed following its own completion.

3. If you wish to add a Precedence constraint, click on one task. It gives out an green arrow i.e. the precedence constraint. Drag the arrow to the next task you wish to connect to. You have now established the precedence between the two tasks. The second task will get executed on success of the first task.

4. The green arrow indicates "On Success". If you wish to add a task that should get executed on failure of a task, just right click on the green Precedence constraint and select Failure. The second task will now get executed on failure of the first task.

5. If you wish to add a task that should get executed on completion of first task, just right click on the green Precedence constraint and select Completion. The second task will now get executed on completion of the first task.

6. You can even add conditions to Precedence Constraint on which the next task will get executed.

7. Double click the Constraint, it shows you the Precedence constraint Editor.

8. You can configure multiple conditions on which your next task will get executed.

9. Evaluation operation allows you to select on which parameters you wish to configure the constraint.

If you select "Constraint", You need to select an value in the Value dropdown. Flow will move to next task based only on this value.

e.g. you select "Success" in Value dropdown, then only if the first task succeeds, the second task will get executed. If you select "Failure", then only if the first task fails, the second task will get executed.

If you select "Expression and Constraint", You need to select an value in the Value dropdown and specify a constraint in Constraint box. Flow will move to next task based on Value dropdown and successful evaluation on the Constraint.
e.g. in the image shown, the next task will get executed only if first task succeeds AND the variable "Count" is greater than zero.

10. The way a task can have multiple precedence constraints going out of it, it can even have multiple precedence constraints coming to it.

e.g. A certain task can be a joining point of two alternative path flows. So only one of its preceding task will get executed at any time and not both.

11. If however they are not alternative paths, the task may require both the previous path flows to finish before it starts execution.

12. This can be configured using the two radio buttons provided at the bottom of the Precedence Constraint Editor.

Logical AND : Indicates both the incoming tasks have to executed before this task can be executed.

Logical OR: Indicates completion of either of the incoming tasks is sufficient for the next task to executed.

e.g. In the above image, the Send Mail task will work if either of "Move File" Or "Delete File" task completes successfully.

Logging

SSIS allows you to create your custom Log files to store all the log data

1. Go to File Menu options, SSIS >>Logging

2. Select Log Provider and Click on "Add" button.

3. Configure name, Description for Logging instance.

4. Create a new log file connection by Clicking on "New Connection". Give a path for file with ".log" extension.

5. In the Containers pane on left hand side, select the package to indicate the objects to be logged.

6. Now, Do not forget to check the checkbox in the logging instance you just added (one before "DevBox" text).

7. Select the "Details" tab seen at the top.

8.Select the events you wish to log against in the Events list. Generally "OnError","OnProgress","OnTaskFailed" and "OnWarning" should suffice the need.

9.Now Click on the Advanced button given below.

10.You see a matrix of values to be logged against the events to be logged. Select the appropriate values/events that suit your need.

11. Click on "Ok" and your logging is configured well.

12. You can configure the log file dynamically by setting the Log file's connection Manager Properties. You can set the Path value to some expression or Configurable varaible value.

13. If you wish to have a dateTime based log file, set the above variable "logFilePath" to build its value from an expression with current datetime included in filename part.

Error Handling

SSIS provides advanced features for Error Handling. We can define error handling events for entire package as well individual events to handle failure in any particular control.


1. To write event handler for a particulat container, select that container object and move to Error handlers tab at the top.
2. Click on the link at the centre stating " Click here to create an 'OnError' event handler for executable ..."
3. You can write your custom code here to handle the error.
4. On Error, the package variables "ErrorCode", "ErrorDescription" and "SourceName" are populated.
a] SourceName holds the name of the task that failed.
b] ErrorCode holds the errorcode of the error that occurred.
c] ErrorDescription holds the description of the Error that occurred.

You can store all messages in an object by appending all messages thrown and notifying user at the end with one single notification.

Dts.Variables["User::ErrorMessages"].Value = Dts.Variables["User::ErrorMessages"].Value + "\r\n" + Dts.Variables["System::ErrorDescription"].Value;

5. You can use these variables to notify the users of the error that occurred.