Could someone send me any links or information on how to loop through an ADO.NET dataset in SSIS I need step by step information please.
Thank you,
Shiva
Foreach Loop Container in SSIS
Hot Topic
- Report Headers
- Error "No Connection could be made because the target machine actively refuses it (System)"
- attribute date of type numeric to datetime...!
- Unable to deploy SSIS Package on 64 bit Enterprize
- Datareader Query Timeout
- Can I use SQL Developer edition with VWD 2005 Express?
- Report layout
- Unable to connect to Analysis Services as a client
- How to 'attach' an existing .MDF file to the local SQL Server
- How to check if row exists...
SQL Server
- Sql to FoxPro Export
- Help with setting Algorithm Paramteres
- I hate this
- Web App Failover problem
- Report Input Fields as Tree View Selectio (Hierarchy list box)
- How can I skip an initialize snapshot?
- Does the June CTP qualify for the GoLive Now program?
- Parameterized Connect String
- Creating SQL Server 2005 Mobile Edition On Desktop
- copying data to SQL Server from an Oracle database
Foreach Loop Container in SSIS
Sergey Barinov
I hope I can do a good job in explaining the requirements this time.
1. I have about 100+ records in a batch that I download every 15 minutes.
2. I have over 200+ columns in these recordset.
3. I want to process one record at a time like a transaction. If all the business rules for that particular record pass, then I go to the next record and if it fails, I write that record to an event log and move to the next record.
4. Now, there are several transformations for every record. Like for instance, Column A could be "status" and it could have values like A, P, S etc. Now I will have a lookup table for the statuses to be translated to more descriptive information like Active, Pending, Sold etc. So such transformations need to be carried out for a lot of columns.
5. Eventually, after I have my Transformed data, I will want to write each processed record to a final set of posting tables before getting written to the Production tables.
It seems to me that SSIS should easily be able to handle something like this which is a routine ETL process. But the way the foreach loop container in SSIS is structured seems VERY confusing for me now. Why do you have to go back to Control Flow to use the Foreach loop Shouldn't it be a part of the Data Flow. And for starters, how the heck would I even use the Foreach loop to break down my records into one record at a time for further transformation operations like mentioned in step (4) I really hope that you can help me with some good information in the form of articles or links to an article or diagrams. The book that I have on SSIS (the only book in the market currently) doesn't seem to be touching all the topics well enough. There is hardly anything on Foreach loop.
Thank you,
Shiva
SauravSen
Whilst not quite understanding the requirement, yes, that sounds eminently possible.
If you elaborate on the requirement then perhaps we can explain how. There are a number of different ways of controlling what operations get applied to which datasets.
-Jamie
Dave_S
Here are BOL topics that might help
http://msdn2.microsoft.com/en-us/library/ms187670.aspx
http://msdn2.microsoft.com/en-us/library/ms141724.aspx
http://msdn2.microsoft.com/en-us/library/ms140016.aspx
Some other helpful links
http://www.whiteknighttechnology.com/cs/blogs/brian_knight/archive/2006/03/03/126.aspx
Bil Click
You are right. Looping IS a routine ETL process and SSIS can do it very well. Processing a row at a time though is NOT a routine ETL process.
The ForEach loop should most definately be NOT part of the data-flow. The ForEach loop loops over a collection and executes a series of tasks for each iterated item in that collection. In your case, the collection is a recordset.
The first thing you will need to do is to populate a recordset that you can loop over. If the data is in a file, this can be done using a data-flow incorporating a recordset destination adapter. If the data is in a DB table then you could use a data-flow or the Execute SQL Task. Wherever you do it, this step (i.e. population of the recordset) must be done OUTSIDE the ForEach loop.
Once you have populated your recordset you can iterate over it using the ForEach loop.
All this is explained here: http://blogs.conchango.com/jamiethomson/archive/2005/07/04/1748.aspx
One thing I need to ask is this. Why do you need to process each row individually Why can you not apply each business rule to all rows at the same time
-Jamie
vijji
Thanks Jamie for giving a couple of answers for this problem.
Yosunu, can you give us more details on the scenario you are trying to accomplish. What are you going to do with each row from the ADO.Net recordset This will help us as we plan future functionality.
Donald
Scott Lovell
Thank you for your response. To answer your question - The purpose of processing each record at a time is to catch any exceptions per record and log them seperately. Can this be achieved even if you process them in a batch If I am updating 100 records in one shot and if something fails for the 10th record in the recordset, the whole updation process rolls back in T-SQL. Is this how SSIS would behave as well Is SSIS, can I log the error for the 10th record in this case and still process the rest of the 99 records Plus, in a sequence of processes, can I end up eliminating the record that failed at some point and not have that record processed in the steps ahead Please let me know if I am not making any sense. And also, it will be great once again if you could send me more links to tackle my issues.
-Shiva
edwin220
>3. I want to process one record at a time like a transaction. If all the business rules for that particular record pass, then I go to the next record and if it fails, I write that record to an event log and move to the next record.
That's not really an ETL scenario - it is more like a workflow or EAI scenario. In ETL you would expect to process all the records as a batch and pipeline the business rules. If you want to process one record at a time, and wait until all rules are processed on that record before reading the next one, then BizTalk may be a better option - or the Windows Workflow Foundation.
Is there some reason that you cannot pipeline the business rules - why must they be handled one row at a time Why not read the data using a source adapter in your data flow
Donald
.
nageswara rao v
Hi,
I have quite the same inquiry.
I'm willing to create a DatFlow for each of my Business Rules. But I don't want all rows to be parsed by each Business Rule. I would prefer looping in my DataSet and each row is going through each business rule sequentially.
Is that possible
Kind regards,
Sebastien
Suddhasatta
Jamie,
Using ADO enumerator to loop through an ADO.NET dataset seems stupid. Also, I have 200+ columns in my row and to keep mapping variables for each column seems insane!!
Håkan B
To explain my problem a bit further -
I have a source table with over 200 columns. I want to load this into as ADO.NET dataset and do something useful with each of the columns in the table like a lookup transformation. I want to use the ADO.NET component as it is faster than the original ADO component. Now to do something like this, don't have to use the foreach loop container to do a row by row operation If so, how do I configure the foreach loop for this task Kinda like the cursor operation in regular T-SQL. Assigning variables to 200+ columns like Jamie mentioned above seems nuts! ! In .NET, you can get the dataset using a Stored Procedure call of something and have all the columns in the output referred to as Column[0], Column[1], etc... Is something like this possible using SSIS Its either that I am not really sure on how to use SSIS or that it seems to lack functionality to replace a cursor. Sounds really obvious that people would try and accomplish what I am trying at this point for a simple ETL task where you prefer a row by row operation so either the entire list of steps for a row to get transformed is completed successfully or else all the operations rolled back due to a failure in the transformation phase for that particular row. I hope my explanation is good enough and you are able to provide me with a good answer.
-Shiva
mrfleck
OK, that kinda makes sense!. I guess!
Not necassarily in SSIS. See below.
Yes you can! You can configure error rows to be redirected elsewhere (which I think is actually one of your requirements) using a component's error output.
Absolutely. Again, direct erroring rows elsewhere will achieve this.
HTH
-Jamie
Piyush Soni
There are 2 ways, depending on your requirement.
If you want to loop over the recordset and use the iterated value as some sort of parameter to something else then do the following:
The values in each iterated row will then be available within the Foreach loop to use as you see fit.
If you want to push data from an ADO.net recordset into the pipeline then go here: http://blogs.conchango.com/jamiethomson/archive/2006/01/04/2540.aspx
-Jamie
Vomish
does not help....
i need more information on how to use the Foreach Loop container to process row by row operation of an ADO.NET dataset. i would imagine it is a simple process in SSIS. but, boy this is friggin hard.
sandeep maurya
In SSIS you can handle errors one row at a time as Jamie describes. See http://msdn2.microsoft.com/en-us/library/ms141679.aspx
What kind of errors do you expect to encounter
Donald