views:

301

answers:

2

What is the best way to read system variables from Script Component.

Tried as below: Works fine when is User variable

    base.PreExecute();
    IDTSVariables100 variables = null;
    VariableDispenser.LockForRead("System::ContainerStartTime");
    VariableDispenser.GetVariables(out variables);
    auditTimeStamp = Convert.ToDateTime(variables[1].Value);
    System.Windows.Forms.MessageBox.Show(auditTimeStamp.ToString());
    variables.Unlock();

when tried to read System variable throws following error:

Script Component has encountered an exception in user code: Project name: SC_0bfc7da1c6fe4b83bc124b87eb4178e5 Exception from HRESULT: 0xC0010009

at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSVariables100.get_Item(Object Index) at ScriptMain.PreExecute() at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PreExecute()

any clues please.

Thanks

A: 

You could create a user variable that is evaluated as an expression that references the system variable. Something like this

@[System :: PackageName].
Steve Homer
A: 

Ok got this done as

        #region Class Variables

        int jobId;
        DateTime auditTimeStamp;
        IDTSVariables100 variables;
        const string tableName = "ORGANISATION_PROVIDER"; 

        #endregion

        public override void PreExecute()
        {
            #region On PreExecute - Get the JOB ID passed - COMMON
             base.PreExecute();
             variables= null;
             VariableDispenser.LockForWrite("System::ContainerStartTime");
             VariableDispenser.GetVariables(out variables);
             auditTimeStamp = Convert.ToDateTime(variables[0].Value);
             variables.Unlock();

           #endregion
        }

This works fine..not sure what i have done wrong previously.

Nev_Rahd