Update a variable in SSIS

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Raunak Jhawar (9/24/2010)


    ...some code...

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

    Great! And there is no need to store any kind of variable in this solution.

    (and admit, VB or C# is probably a lot easier than WQL :-D)

    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

    (and admit, VB or C# is probably a lot easier than WQL :-D)

    The learning process should be gradual and rising...that's what is my personal belief...try something new everyday...;-)

    Raunak J

  • Phil Parkin

    SSC Guru

    Points: 244656

    Raunak Jhawar (9/24/2010)


    (and admit, VB or C# is probably a lot easier than WQL :-D)

    -- ...try something new everyday...;-)

    One day a German beer, then a Belgian one, then Czech, ... there's so much to learn :hehe:

    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

    Phil Parkin (9/24/2010)


    Raunak Jhawar (9/24/2010)


    (and admit, VB or C# is probably a lot easier than WQL :-D)

    -- ...try something new everyday...;-)

    One day a German beer, then a Belgian one, then Czech, ... there's so much to learn :hehe:

    No beer since one month...even more I guess...:-)

    Raunak J

  • doug.milostic

    Ten Centuries

    Points: 1270

    Thanks Guys, I've sorted it out.

    I was coding this one step at a time so started with the variable and script, however, it does update the variable and the new value IS available to the next task (once you create the next task and set the precedence), obviously (durrrr for me) the variable looses its value once the package stops. So now I use a combo of variables and storing the file number in a table.

    1. I retrieve the fileno from the table and store it in a variable using an Execute SQL task.

    2. The next script taks increments the variable set in Step 1, formats the full filename and stores the filename in another variable. I also create and store a SQL Update statement with the new FileNumber to be used to update the table where the filenumber is stored. eg. "Update [TableName] Set [FileNoField] = varFileNo....."

    3. The next FTP Task uses the filename variable created in the previous step to FTP the file.

    4. The last task is a SQL Execute task that execute the SQL Statement created and stored in the variable from Step 2.

    See, easy when you know how.

    Cheers all.

  • doug.milostic

    Ten Centuries

    Points: 1270

    Hi Guys

    If i could bother for one more piece of advice to close off this task.

    The above works really well, but obviously only download a single file. i.e. the file defined by the LastFileNo variable.

    Now I would like it to loop through the 4 task described previously, until the FTP Task fails.

    How would I configure a For Loop to handle this? What would put in the EvalExpression property?

    Cheers

    Doug

  • doug.milostic

    Ten Centuries

    Points: 1270

    Dont worry guys, figured it out. Always happens just after you've posted a question.

    1. I created a variable called varMaxFilesFTP.

    2. I set InitExpression: @varMaxFilesFTP=1, EvalExpression: @varMaxFileFTP<30, AssignExpression: @varMaxFileFTP = @varMaxFileFTP + 1

    3. I set FailParentOnFailure = True and MaximumErrorCount = 1

    The net result is the For loop gets executed whilst the varMaxFileFTP variable is < 30 or until an error is encountered. If an error is encountered, then it will bail out of the For Loop and continue on to the next task in the package.

    I deliberately set the varMaxFilesFTP variable to 30 as any fiels older than 30 days are archived out of the FTP folder, so theoretically, there chould never be more than 30 files in the folder anyway.

    Cheers

  • binds

    SSC Enthusiast

    Points: 190

    Hello,

    I am also facing the problem with similar kind of Issue. I hope you can help me on this.

    My task is to store the count(which is nothing but the total records present in the specific SQL table.As they are millions of rows, it is taking

    My task includes:

    1) Getting the count(which is nothing but the total number of records present in the specific SQL table.As they are millions of rows,  it is taking a lot of time to run the count() query). I need to get the count before I write that data from data flow task(source) to the flat file destination tasks, as I need to write the total count of records as well in the flat file.

    2)I tried to use the RowCount task,and tried to save that rowcount and to further use that in the next task.But it doesn't work for me.

    3) As there are millions of rows in the sql table, it is taking 6 mins(for getting the data) and 6 mins(to get the count() of the records in before step). My manager really asked me to cut down the running time a lot. So, I am trying to run the query once for getting both the count and data(from data flow task to flat file destination).

    Can you please advise me on how step 3 works, like getting both at the same time and to reduce the running time for this whole task?

    I hope someone can help me out..

    Thanks in Advance!!

     

  • Phil Parkin

    SSC Guru

    Points: 244656

    Try using this query to get a count of rows in a table. It's super fast:

    SELECT TotRows = SUM(row_count)
    FROM sys.dm_db_partition_stats
    WHERE object_id = OBJECT_ID('{schema}.{tablename}')
    AND index_id < 2
    GROUP BY OBJECT_NAME(object_id);

    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.

Viewing 9 posts - 16 through 24 (of 24 total)

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