SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS 101: Object Variables, ResultSets, and Foreach Loop Containers


SSIS 101: Object Variables, ResultSets, and Foreach Loop Containers

Author
Message
Andy Leonard
Andy Leonard
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2423 Visits: 1102
Hi Paul,

How many rows are being returned by the query in the Execute Sql Task?

Andy

Andy Leonard
Data Philosopher, Enterprise Data & Analytics
PaulSp
PaulSp
Mr or Mrs. 500
Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)

Group: General Forum Members
Points: 565 Visits: 286
Hi Andy

Thanks for your reply.

Just the first one. I click OK, expecting to see the next one, but nothing happens...

Thanks
Paul
bpimenta
bpimenta
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 55
Hi Paul,

I still had the received response activated on this post and hope I can help out. For your problem verify that the loop variables are well mapped and that the values do change. You can do this by simply writing to a pop-up.

Hope it helps,

Complements,

Bruno Pimenta
Andy Leonard
Andy Leonard
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2423 Visits: 1102
Hi Paul,

If your Execute Sql Task is only returning a single record, that's all that will be displayed.

Andy

Andy Leonard
Data Philosopher, Enterprise Data & Analytics
PaulSp
PaulSp
Mr or Mrs. 500
Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)

Group: General Forum Members
Points: 565 Visits: 286
Hi Andy

Thanks again for your reply.
What I was hoping to be able to do was to buzz off a lot of emails by using the 'People' rescordset and iterating through all the email addresses by using an email variable as per your 'LastName'. Is this not possible - or am I misunderstanding how it works..?

It was easy in DTS to create a loop and iterate through an address table and attach a list from another table to the body text. I'm really struggling to get my head around how it is done in SSIS 2008/10.

Hi Bruno - thanks for your reply. The ability to use popups sounds great. How do I make them work..?

Many thanks to both of you
Paul
bpimenta
bpimenta
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 55
Hi Paul,

Andy has given the solution for the "one result" problem.

As for the pop-up message I use it for debugging (sometimes it helps me understand). I use a script task in Control Flow view.

1. drag a script task object from the tool box
2. pass the variables that you want to see the result (read or write it does not matter)
3. edit the script and the code in C# is as follows, just adapt for what you need. I included a string concatenation with the variable (VariableName) value because it can come in handy:

...
public void Main()
{
MessageBox.Show("YOUR MESSAGE: " + Dts.Variables["VariableName"].Value);
Dts.TaskResult = (int)ScriptResults.Success;
}
...

hope it helps and feel free to ask :-)

I have registered very few SSIS contents in my blog (http://ismamad.blogspot.com/) if want to take a look it might have something to help you out.

Complements,

Bruno
PaulSp
PaulSp
Mr or Mrs. 500
Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)

Group: General Forum Members
Points: 565 Visits: 286
Thanks, Bruno

Yes, I can do that fine...

Best wishes
Paul
Ian C0ckcroft
Ian C0ckcroft
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1240 Visits: 863
Hi, I cant find the article. Would like to read it.

Mean time, maybe someone can help. I am getting a result set from a query. into an object variable. I need to use it in a conditional expression. if the object has data, continue. If it NULL, end the job.
objects cannot be used in an expression. Is there another way?

One way was to put a SQL statement before this section that returned a COUNT, and then use that in the precedence contraint expression. But I'd rather not.

Can it be done using the object variable?


cheers
Ian

Ian Cockcroft
MCITP BI Specialist
waynerb87
waynerb87
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 2
Excellent Article, is exactly what I was looking.

Thank you, so much.
david.stein-1140863
david.stein-1140863
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 155
ianc-1096396 (9/8/2010)
Hi, I cant find the article. Would like to read it.

Mean time, maybe someone can help. I am getting a result set from a query. into an object variable. I need to use it in a conditional expression. if the object has data, continue. If it NULL, end the job.
objects cannot be used in an expression. Is there another way?

One way was to put a SQL statement before this section that returned a COUNT, and then use that in the precedence contraint expression. But I'd rather not.

Can it be done using the object variable?


cheers
Ian


I have the same basic question. How can I determine if the object variable contains no records after my execute sql task so I can end the package, throw an error, etc.

Great article Andy.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search