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

Question

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

Viewing 15 posts - 46 through 60 (of 63 total)

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

petehughes247 SSC Enthusiast Points: 100 More actions · Answer 1

Great article Andy, thanks!

I'm new to SSIS (2008) - last used DTS in SQL Server 2000 - oh man, what a change!!! :hehe:

I have a series of SQL files that I would like to execute, and dump the resultset of each out to a different tab on the same Excel workbook. Sounds simple, but can I do it?! SSIS is just frustrating the hell out of me with it's counter-intuitivity (if that's such a word?!).

It would be a great help if someone could point me in the right direction 🙂 please 🙂 I already have no hair left to tear out :w00t:

Pete

bpimenta SSC Enthusiast Points: 169 More actions · Answer 2

SOLUTION: I have already solved this issue.

1) Load the data into a tempTable

2) Load the file value to the MyObjectVariable

3) After loading the object variable with the value read from the file I used a Foreach Loop to read from the object variable:

- Enumerator -> Foreach ADO Enumerator

- ADO object source variable -> MyObjectVariable

4)Then using a script I compared the two variable values:

- the row count from the tempTable (a simple count(*))

- the value from the file retrieved with the previous step

Hope this helps someone with the same issue.

Any doubts feel free to drop a reply,

Thanks

Jarom Richardson SSC Enthusiast Points: 195 More actions · Answer 3

Andy, Thanks for the article. It is just what I needed. All of the SSIS books I have use the file enumerator in all of their examples and do not even touch on the looping through a result set which I think is much more important than a file enumerator. But anyway, thanks again and keep it up.

saad.subhan Newbie Points: 9 More actions · Answer 4

saad.subhan

Newbie

Points: 9

July 7, 2010 at 9:15 am

#1189677

Thanks Andy! This is a very useful example!

PaulSp SSCrazy Points: 2499 More actions · Answer 5

Hi

Excellent article, Andy.

I hope someone is still watching this. I'm just upgrading from DTS and need all the help I can get...

I want to eventually use this code for email scripts - taking values from the resultset.

I've made everything work - and it looks just as in the article - with the green for the resultset and two yellows in the foreach loop. However, I only get the first value, as shown, but no iterations. What am I doing wrong..?

Many thanks in anticipation

Paul

Andy Leonard SSChampion Points: 10113 More actions · Answer 6

Hi Paul,

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

Andy

Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics

PaulSp SSCrazy Points: 2499 More actions · Answer 7

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 SSC Enthusiast Points: 169 More actions · Answer 8

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 SSChampion Points: 10113 More actions · Answer 9

Hi Paul,

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

Andy

Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics

PaulSp SSCrazy Points: 2499 More actions · Answer 10

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 SSC Enthusiast Points: 169 More actions · Answer 11

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/[/url]) if want to take a look it might have something to help you out.

Complements,

Bruno

PaulSp SSCrazy Points: 2499 More actions · Answer 12

Thanks, Bruno

Yes, I can do that fine...

Best wishes

Paul

Ian C0ckcroft Hall of Fame Points: 3373 More actions · Answer 13

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 Grasshopper Points: 11 More actions · Answer 14

Excellent Article, is exactly what I was looking.

Thank you, so much.

david.stein-1140863 SSC Journeyman Points: 85 More actions · Answer 15

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.