0

Hello i am building a SSIS package which get the delta snapshot date's and processed them to the SCD 2 Dimension. Everything goes fine except the "Foreach ADO enumerator" which caused duplicate SNAPSHOTS except the last row. To realize this i look at the following source:

How can i fix the problem of the double loop.

This package is developed in the following enviroment:

  • Windows 2008 Server R2 Enterprise
  • SQL Server 2012
  • BIDS 2013

The pictures below shows how this is implented:

enter image description here enter image description here

SSIS Variable enter image description here

enter image description here

Erik hoeven
  • 1,442
  • 6
  • 26
  • 41
  • I don't quite understand your question but... enumerating ADO objects inside SSIS seems to be a coders approach to a database issue. I bet you could do all this in a stored procedure more efficiently and reliably. – Nick.Mc Jul 28 '16 at 12:24
  • Nick! The question is how is it posible that the foreach ADO enumerator loops more then there are rows set to the system.object in SSIS. I understand that it can be more efficient in T-SQL but that is not my question!! – Erik hoeven Jul 28 '16 at 12:29
  • The problem could be in the loop or it could be that SRC SNAPSHOT_SOURCE has more than one row - that's your first step in troubleshooting - establish how many times the loop really runs and how many rows are in SRC SNAPSHOT_SOURCE each time. What query is in SRC SNAPSHOT_SOURCE and is it guaranteed to return only one row each time? – Nick.Mc Jul 28 '16 at 23:54
  • Nick, I am sure that there are NO duplicate row in the LOAD_SNAPSHOTS. I assume that a system.object is an array in C#. So in bids there is no way to look at the content of the system.object. If I have 6 rows in my object the ADO ... loops 11 times (2 times each row except the last) – Erik hoeven Jul 31 '16 at 14:51
  • I think you need to take the time to establish counts of each object. You could add a script task that initially displays the record count of your ado object. Then you'll know with certainty that count. Then you could add a variable inside your for loop that increments each loop, and include that in your data flow and write it to your table. Then you'll know exactly where each of your records are coming from. From your description there is no obvious answer. I can only suggest these troubleshooting steps (and you will learn more about SSIS in the process). – Nick.Mc Jul 31 '16 at 23:13
  • I still don't know how many records are in SRC SNAPSHOT_SOURCE and how it is generated. I don't fully understand your process but I expect you would at least need to apply a parameter to this rather than select from it each time. I always load SCD's with stored procedures. Its a lot less 'disconnected' and and much faster than a row by row method built in SSIS. – Nick.Mc Jul 31 '16 at 23:15
  • Nick, you are right normaly (from source to datavault) we use storedprocedure for default Type 2 load strategy based on snapshots. To reduce the load on the server. – Erik hoeven Aug 02 '16 at 11:41

0 Answers0