Update a variable in SSIS

Question

Post reply

Update a variable in SSIS

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

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

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

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 More actions · Answer 2

(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: 246986 More actions · Answer 3

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:

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 4

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 More actions · Answer 5

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 More actions · Answer 6

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 More actions · Answer 7

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 More actions · Answer 8

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: 246986 More actions · Answer 9

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

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel