Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««34567»»

SSIS 101: Object Variables, ResultSets, and Foreach Loop Containers Expand / Collapse
Author
Message
Posted Monday, August 02, 2010 10:20 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 1:39 PM
Points: 388, Visits: 1,034
Hi Paul,

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

Andy


Andy Leonard
CSO, Linchpin People
Follow me on Twitter: @AndyLeonard
Post #962315
Posted Monday, August 02, 2010 10:22 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 3:24 AM
Points: 125, Visits: 251
Hi Andy

Thanks for your reply.

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

Thanks
Paul
Post #962319
Posted Monday, August 02, 2010 10:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 24, 2014 6:31 AM
Points: 7, Visits: 42
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
Post #962335
Posted Monday, August 02, 2010 10:45 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 1:39 PM
Points: 388, Visits: 1,034
Hi Paul,

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

Andy


Andy Leonard
CSO, Linchpin People
Follow me on Twitter: @AndyLeonard
Post #962336
Posted Tuesday, August 03, 2010 3:04 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 3:24 AM
Points: 125, Visits: 251
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
Post #962651
Posted Tuesday, August 03, 2010 6:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 24, 2014 6:31 AM
Points: 7, Visits: 42
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
Post #962738
Posted Tuesday, August 03, 2010 7:17 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 3:24 AM
Points: 125, Visits: 251
Thanks, Bruno

Yes, I can do that fine...

Best wishes
Paul
Post #962773
Posted Wednesday, September 08, 2010 1:17 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 11:55 PM
Points: 848, Visits: 766
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
Post #982078
Posted Friday, December 10, 2010 10:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 16, 2012 4:40 PM
Points: 1, Visits: 2
Excellent Article, is exactly what I was looking.

Thank you, so much.
Post #1033121
Posted Saturday, March 12, 2011 11:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 01, 2011 4:18 AM
Points: 7, 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.
Post #1077341
« Prev Topic | Next Topic »

Add to briefcase «««34567»»

Permissions Expand / Collapse