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

Question

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

Viewing 15 posts - 31 through 45 (of 63 total)

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

Paul Paiva SSCrazy Points: 2237 More actions · Answer 1

Excellent article, Andy.

SSIS can be daunting at first, even for experienced DTS developers. You have provided what is missing in Microsoft documentation.

I'm looking forward to other SSIS articles you may wish to write. For the record, I'm using SSIS 2008, but I know that your 2005 articles can translate easily.

Paul

- Paul

http://paulpaivasql.blogspot.com/

bmburch Grasshopper Points: 11 More actions · Answer 2

I would like to request some clarification/assistance. Is this suppose to displace a dialog box with one entries, and not iterate through the remaining rows? This is what I get when I follow the instruction of this article. I get the same when I download the example and run the package.

I was hoping this would assist me in develop a method to place all the rows of a query into the body of an email (through mail task); I am still stuck at getting the rows (all of them) instead of getting the first row and the last row.

Any help would be appreciated.

Brandon

scott elkin Grasshopper Points: 13 More actions · Answer 3

Just wanted to thank you for the article. This gave me EXACTLY what I needed.

Shairal-763732 SSC-Addicted Points: 436 More actions · Answer 4

Excellent article! I have SO much to learn! Question – I want to set up variables to use in a Send Mail Task. I created the variable up to the point where you put it in a Script Task. I put a Send Mail Task into a Foreach Loop Container, however when I run the package I get “Error: Failed to lock variable "xxx@xxx.com" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created."

Can I use these variables in a Send Mail Task? Is there a trick that I’m overlooking? Any help would be appreciated.

Andy Leonard SSChampion Points: 10144 More actions · Answer 5

Thanks all for the feedback!

Shairal, it is very easy to define a variable at some unintended scope. Most of the time, package-scoped variables will meet your need. Check the Variables window and see if the variable scope is set to the package.

:{> Andy

Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics

Shairal-763732 SSC-Addicted Points: 436 More actions · Answer 6

Thanks for the reply Andy. It is a package-scoped variable, however I had left the 'Value' part of the variable blank. After I entered some sample into the Value field it worked like a charm. Thanks again for your article - I couldn't have accomplished my package without it! 😀

hans2mike Newbie Points: 5 More actions · Answer 7

Well i would like to say nice post there,

but one of the downfall of ssis so far experienced on using it, is its inability to work pretty well with variables of object type..

The whole foreach loop what it does is iterate through a one column of one row at a time, why not just iterate through the whole row, and give out the row, instead of giving out a single column at a time, this clearly frustrates. In order to get a row, like what i have to do , you need to declare as many variables as there are the numbers of columns in the row..and later concatenate these values in the script task, which is ridiculous..

yosiasz SSCommitted Points: 1699 More actions · Answer 8

greetings

BOL says

In SQL Server 2005 Integration Services (SSIS), expressions can be used to define conditions for CASE statements, create and update values in data columns, assign values to variables, update or populate properties at run time, define constraints in precedence constraints, and provide the expressions used by the For Loop container.

But how do you assign a value to a variable in an expression.

Thank you

Andy Leonard SSChampion Points: 10144 More actions · Answer 9

Hi Yosiasz,

There are several ways to put values into variables. One easy way is to supply the value in the Value column of the Variables dialog.

Another way is to select a value (or values) from a database using a query in an Execue SQL Task. If the query returns a scalar or a single row of date, you configure the ResultSet property to Single Row Resultset. Then on the Result Set tab, you can assign the returned results to variables.

Yet another method is to use a Script Task to push a value into an SSIS variable.

Hope this helps,

Andy

Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics

yosiasz SSCommitted Points: 1699 More actions · Answer 10

thank you very much all. I got it working ! I assigned a value to a variable using an object variable to hol the "recordset" then index 0 to refer to a field in a For Each loop. Now I am dumping Excel file to a share then attach it in email. Wow! This stuff is delicious!

bpimenta SSC Enthusiast Points: 169 More actions · Answer 11

Hy Andy,

Your article is very good so thanks for that.

Here is my problem:

I have a data file and a control file. The data file has N rows and the control files has that RowCount value, so if they are the same it's OK to load the data, otherwise the package will not load anything.

I loaded the data into an Object variable DataTable using a flat file connection and a record set destination.

I also loaded the value RowValue into a package variable.

My idea problem is how do I compare the two off them? More exactly how can I read a specific column from an object Variable?

I was trying to use a script for this, but with no success, can you help me on this one?

Thanks in advance,

Bruno Pimenta

Aleksey Urusov-142798 Grasshopper Points: 16 More actions · Answer 12

Aleksey Urusov-142798

Grasshopper

Points: 16

January 13, 2010 at 1:40 pm

#1103290

Thanks a lot. Good article.

grxcls SSC Enthusiast Points: 195 More actions · Answer 13

Excellent article. I applied your example to my situation and it greatly reduced the time the SSIS package ran and eliminated errors and problems I was having.

We are looking for more articles from you, Andy.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 14

Nice refresher with the article republish. Thanks

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Julien.Chappel SSC Journeyman Points: 96 More actions · Answer 15

It is a very good solution. Could you please advise if the record set contains hundreds of thousand of records with up to a hundred or more columns (I am uploading data like this numerous times as part of our customer support) is it still economical to use Object data type for capturing the result set.

Thank you

Julien