Post reply

Reading System Variables in the Script Task

  • July 25, 2008 at 10:04 am

    #154564

    Hello,

    I have an SSIS VB.NET Script Task that is a starting task in an SSIS package.

    Since the package could be executed either independently, or as a step/task of a bigger package, VB.NET script needs to read the name of the package.

    I tried to reference the system variable in 2 ways:

    1) ... = CStr(Dts.Variables("System::PackageName").Value

    2) ... = CStr(Dts.Variables("PackageName").Value)

    I tried to include it into the list of Read-Only Variables of the Task, or do without it.

    So far, nothing helps me with running the package in the Debug mode from VS 2005.

    How could I retrieve the value of System::PackageName variable in VB.NET Script task?

    Thanks in advance.

  • July 25, 2008 at 10:08 am

    #848547

    Create a user variable. Make it an expression variable and make the expression the system variable you want to see. Include this variable in your read-only variable list.

  • July 25, 2008 at 10:39 am

    #848571

    Sorry to hear you say that, I find it great.

    It is way beyond DTS, and considering it comes with the SQL Server license it's quite a bargain compared to other options.

    It just takes some time to get used to.

  • July 25, 2008 at 10:47 am

    #848585

    Sorry... I didn't mean to actually post my previous message (which deleted) Just fustrated. I have been having nothing but problems trying to make basic tasks work in SSIS. I am no expert with SSIS, but come on... don't make me jump thought hoops to get a PackageName....

    Another example. I want to send a email with a the log file attachement. I can't do this becasue the process locks the file and does not allow it to be attached. Jump through 10 hoops and it will work.

    I am still learning about SSIS.. but it seems that is should be more straightforward...IMO.

  • July 25, 2008 at 1:25 pm

    #848677

    Thanks, Michael,

    I did try your suggestion.

    I created a user variable: User::gvExprPN, and

    I set the property "EvaluateAsExpression" equal to True,

    and I set expression equal to @[System :: PackageName].

    Then, I added the variable User::gvExprPN to the list of Read-Write variables of the VB.NET Script Task.

    Still, when I launch the package from VS 2005, I am getting a message stating that it is not possible to lock some Read-Write variables.

    What do I miss in this User Variable / Expression setup?

    Thanks in advance.

    Alex

  • July 29, 2008 at 9:06 am

    #849873

    I would like to thank everybody who was willing to help.

    I found the idea of the working solution in one of the postings on MS SSIS message board.

    Instead of static package variable locking, I have used dynamic package variable locking with VariableDispenser. In this way, it is possible to lock both User variables and System variables.

    Now, I have another question. It looks like the usage of dynamic locking mechanism for package variables in one script task forces the use of dynamic locking in all further package script tasks.

    Am I correct here?

    Thanks in advance.

    Alex

  • December 23, 2008 at 9:08 am

    #916841

    I had the same issue. My problem is the proposed solutions wouldn't work since I need the values from the System Vars later when they wouldn't be available. For instance, I needed to send an email with the first error message and first errored task, but this email is sent at the end of the package when those values are no longer in scope (ErrorDescription variable I believe is only available in the OnError handler) or could possibly have been overwritten with additional task failures since those events occurred.

    So here is what I hacked up: Create a Execute SQL Task like so:

    Select ? As ErroredTaskName, ? As ErrorMessage

    Then set the Parameter mapping to be System:SourceName for arg 0 and System:ErrorDescription as arg 1.

    Make the Execute SQL Task return a single row result set.

    Set the Result Set Columns of ErroredTaskName and ErrorMessage to load into your local variables.

    Uggh. It is a hack I know, but the only way I found to move the contents of System variables into user ones.

    I'm actively looking for a better way, I am sure there is one. I had hoped that using the:

    Dts.Variables("LocalVar").Value = Dts.Variables("System:SourceName").Value would work.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply