?
Solved

Can I access the values from the dataset that has been passed to For each loop container outside for each loop container in SSIS 2012

Posted on 2016-11-01
11
Medium Priority
?
39 Views
Last Modified: 2016-11-07
HI!

I have a select statement that gets a list of columns from a table and this dataset is passed to a for each loop container and the each column is assigned to a variable created at the package level.

The question is, can the value of these variables be accessed outside the for each loop container. If so, what values do they hold? Is it the most recent value?

If my for each loop container fails, I should update a column in a table based on the value of a column that has been passed and is being accessed in a for each container.

Any code/logic/examples are greatly appreciated!

Thanks a million in advance.
0
Comment
Question by:amukta
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
11 Comments
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41869407
Have you tried Script task. Seems like you have a complex business req. for these kind of things Script task is the best option.
0
 

Author Comment

by:amukta
ID: 41869529
Not sure! how the script task would solve the problem. I'm trying to access the variables that have been assigned from an object dataset passed to a for each loop. I'm trying to access them outside the for each loop container. I'm not able to access them outside the for each loop, is there any property I'd need to set?
0
 
LVL 16

Expert Comment

by:Megan Brooks
ID: 41869579
You should be able to access the variables anywhere within the scope in which they are defined. They should contain the last values assigned to them, although you will need to test and see exactly what that is after exiting the loop.

If the variables are defined at the scope of the For Each container then they will not exist outside that container. Package level variables, on the other hand, will be available anywhere in the package.

You can use the Variables window to see the scope of variables, and to move them from one scope to another.
0
On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41870005
Good Suggestion from Megan. Try if this works for you.
0
 

Author Comment

by:amukta
ID: 41870132
Hi! Megan,

Thank you very much! But here's what happening!

I have a SQL statement in the execute SQL task, that pulls a list of files that start with say 'ABC' and today's date. At any given time, the SQL statement pulls atleast 4 files. This dataset is passed to a FOR EACH LOOP ADO enumerator and then if the package fails in any part of the for each loop container it is supposed to insert rows into a table saying failure on the file that's being currently looped through, if a row does not already exist in the table, for that file and if exists update. When I try inserting rows for failure on a file, it is inserting the name of the next file or randomly any of the 4 files, other the name of the current file. If I just  try updating instead of inserting, if the row does not exist, it does nothing or updates a row that was existing with the maximum date.

Is there any property I should be setting or is there anything that I'm missing.

Greatly appreciate your help! It's very urgent!

Best Regards,
Amukta
0
 
LVL 16

Expert Comment

by:Megan Brooks
ID: 41870885
There should be some point within the For Each loop, such as at the beginning of an iteration, where you have the name of the file that should appear in the error output, should the current iteration fail. Can you copy that value to another variable that is outside the scope of the For Each container and available to the error handling component, and that is not otherwise updated each iteration?
0
 
LVL 29

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 1000 total points
ID: 41871576
Hi Amukta,

Why dont you try Script task. There you can write your own custom code.
0
 

Author Comment

by:amukta
ID: 41872031
HI! Megan,

I've tried that, but looks like it doesn't seem to work.

Thanks for your help!
0
 

Author Comment

by:amukta
ID: 41872033
Thanks Pawan for your input as well! But I'm not sure, how it can be handled in a script task outside the for each loop container and it resolves this issue.
0
 
LVL 16

Accepted Solution

by:
Megan Brooks earned 1000 total points
ID: 41872436
You can use a script task within the For Each container to capture the current values of loop variables and copy them to other variables defined at a higher scope level. That way, the values present during the most recent iteration of the loop will be preserved even when the loop exits. Is that what you are looking for?
0
 

Author Closing Comment

by:amukta
ID: 41877998
Thank you very much! That's exactly what I was looking for.It works great!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server  2012 Release with lots of Enhancements in Database Engine functions, SSIS, SSRS and some of new services like Data Quality Server and Master Data Service. Of particular interest, and the focus of this Article is SSIS. So, time to elab…
My client has a dictionary table. They're defining a list of standard naming convention. Now, they are requiring my team to provide us a mechanism how to match new incoming data with existing data in their system.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses
Course of the Month12 days, left to enroll

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question