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() )