Update a variable in SSIS

  • doug.milostic

    Ten Centuries

    Points: 1270

    OK, I'm no SSIS expert, but I am trying to do something very simple which I just cant get to work.

    It seems there are lots of posts about this issue but I am doing the same as all the posts suggest, without success.

    I will make this as simple as possible in order to demonstrate the issue.

    1. Create a new SSIS Package and save it as TestPackage.

    2. Right click on the canvas of the "Control Flow" tab and click "Variables".

    3. Create a new variable from the "Variables" window and callit "varInt". Scope will default to the package name (i.e. "TestPackage"), DataType=Int32, Value = 0, ReadOnly=False.

    4. Add a "Script Task" to the Control Flow tab.

    5. Edit the Script Task and set the "ReadWriteVariables" value to "varInt".

    6. Click on "DesignScript" and add the following code.

    MsgBox(Dts.Variables("varInt").Value.ToString)

    Dts.Variables("varInt").Value = 2

    MsgBox(Dts.Variables("varInt").Value.ToString)

    Dts.TaskResult = Dts.Results.Success

    7. Save the above, then Execute the "Script Task".

    The result I get is the first message box display "0", the second message box display "2". As expected.

    If I execute the script again, unless I am missing something fundamental, I would expect the first message box to now show "2", then the second box to show "2". However, it shows me the "0" followed by the "2" again.

    I therefore assume that whilst the value of the variable is being changed successfully whilst inside the ScriptTask, the new value is not be saved upon completion of the ScriptTask. The new value assigned to the variable "varInt" will therefore not be available to other tasks in the package which is the ultimate aim here.

    How do I therefore update the value of a variable from a Script Task so that the new value of the variable is available to other task which will access the variable.

    Any help would be appreciated as I have Googled this death.

    Cheers

    Doug

  • Raunak Jhawar

    SSCoach

    Points: 15701

    The behaviour is perfectly normal. The variable is losing the scope as it is not persistent as the process terminates

    Raunak J

  • doug.milostic

    Ten Centuries

    Points: 1270

    Thanks for the reply. I guess the obvious question then is, how do I make it persistent? Or is this not possible? Maybe I totaly miss the point of variables then?

  • Raunak Jhawar

    SSCoach

    Points: 15701

    You may store the variable value Post Execution in some another variable which will be accessed in the next script task...

    But what is it that you wish to achieve

    Raunak J

  • doug.milostic

    Ten Centuries

    Points: 1270

    Basically, I need to increment the number in the variable, and then use that number (variable) in the next step (task) of the package. Here's what I am trying to achieve.

    I have an FTP folder into which an application creates files on a daily basis. I only want to download the last file created. The files are named numerically in the following format "X999999.txt" where 999999 is incremented daily. So for example today a file called "X000010.txt" may be created, tomorrow it will create "X000011.txt", the day after "X000012.txt" etc etc.

    I was considering using two variables, one to store the number used in the file name and one to store the filename itself. The package would contain two script tasks and the FTP task.

    1. Script Task 1 - Increment a variable called "varInt". For example, it may be set to "10"

    2. Script Task 2 - Pass in "varInt" as ReadOnlyVariable then format the filename based on the value of "varInt". Set the value of a second variable to store the filename. i.e "FileNameVar" would contain "X000010.txt"

    3. FTPTask - Would use "FileNameVar" as the file to FTP.

    This package would be run daily, each day downloading the file with the next sequential number as the filename.

    Cheers

    Doug

  • Raunak Jhawar

    SSCoach

    Points: 15701

    doug.milostic (9/23/2010)


    I have an FTP folder into which an application creates files on a daily basis. I only want to download the last file created. The files are named numerically in the following format "X999999.txt" where 999999 is incremented daily. So for example today a file called "X000010.txt" may be created, tomorrow it will create "X000011.txt", the day after "X000012.txt" etc etc.

    Doug,

    I have few questions:

    1.Is only one file created at remote location of multiple files

    2.Why not use the WQL Query

    Using the Windows Event Managament Instrumentation you may achieve the same easily and elegantly.

    you may google for better understanding

    Raunak J

  • doug.milostic

    Ten Centuries

    Points: 1270

    There is one file created every day, so the files accumulate in the FTP folder. They do get cleaned out, but usually only files older than 30 days are removed.

    I dont know what WQL Query is so would need to research as you suggest.

    What about storing it Post Execution? I've seen this terminology around the blogs, but no idea what it means?

    Thanks again,

  • Raunak Jhawar

    SSCoach

    Points: 15701

    Doug,

    You have 2 options at this point of time:

    Option 1: Use WQL Query and trigger on FileCreate Event

    OR

    Option 2: Use FileSystemWatcher in Script Task and trigger on FileCreate Event

    Both are logically same. Conceptually it will help you understand the more granular basics of Enterprise development.

    Raunak J

  • doug.milostic

    Ten Centuries

    Points: 1270

    Thanks Raunak, I'll look into it.

    Cheers.

  • Raunak Jhawar

    SSCoach

    Points: 15701

    Always happy to help!!:-):-):-)

    Please follow here

    OR

    Please follow here

    Raunak J

  • Phil Parkin

    SSC Guru

    Points: 244656

    I think that there is another option: store your incrementing values in a database table. Your package just needs to read/increment as necessary - then you get the data persistence you need.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Or, just to add another option:

    1. Add a script task.

    2. In the script, create a directoryinfo on the directory. (let's name it di)

    3. Use the getfiles method: di.getfiles() and store the results in an array of fileinfo's

    4. Search the fileinfo with the "highest" name.

    5. Use the path of the fileinfo for the rest of your package.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Raunak Jhawar

    SSCoach

    Points: 15701

    da-zero (9/24/2010)


    Or, just to add another option:

    1. Add a script task.

    2. In the script, create a directoryinfo on the directory. (let's name it di)

    3. Use the getfiles method: di.getfiles() and store the results in an array of fileinfo's

    4. Search the fileinfo with the "highest" name.

    5. Use the path of the fileinfo for the rest of your package.

    "highest" name???

    Raunak J

  • Phil Parkin

    SSC Guru

    Points: 244656

    Raunak Jhawar (9/24/2010)


    da-zero (9/24/2010)


    Or, just to add another option:

    1. Add a script task.

    2. In the script, create a directoryinfo on the directory. (let's name it di)

    3. Use the getfiles method: di.getfiles() and store the results in an array of fileinfo's

    4. Search the fileinfo with the "highest" name.

    5. Use the path of the fileinfo for the rest of your package.

    "highest" name???

    Based on the ascending file-naming scheme detailed in an earlier post.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Raunak Jhawar

    SSCoach

    Points: 15701

    class Program

    {

    static void Main(string[] args)

    {

    DirectoryInfo di = new DirectoryInfo(@"D:\Personal");

    FileInfo[] fi = di.GetFiles("*.msg", SearchOption.TopDirectoryOnly);

    Array.Sort<FileInfo>(fi, delegate(FileInfo a, FileInfo b) { return a.CreationTime.CompareTo(b.CreationTime); });

    }

    }

    I got it!!!!:-D:-D:-D

    Raunak J

Viewing 15 posts - 1 through 15 (of 24 total)

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