Posts

Foreach Item Enumerator - ForEach Loop Container - Use Case

Foreach Loop Container is one of the looping constructs available in SSIS packages. One of its enumerator options is the Foreach Item Enumerator. It enumerates through a list of items populated at design time. The list is static at execution time, i.e. you cannot load the list items dynamically from a configuration file or another source like an SQL table. It is probably due to the static nature of the item list, that this enumerator type is not used too often in packages. On the other hand, there are some scenarios where a static list can prove to be useful. For example – no need to create a database table just to pass on values for the Foreach Loop enforce a specific order to the parameters passed to the loop initialize variables values with the Item Enumerator instead of doing that in a Script task Another frequent requirement in SSIS packages is to execute a set of operations against different SQL Servers or databases. The package should change the OLE DB c...
Post a Comment
Read more

SSIS: Merge vs Union All

It seems that the Merge transformation is very similar to the Union All transformation, with the following differences: Merge can only merge 2 sources of data, whereas Union All supports more (I'm not sure how many - my instructor says 256 but there are no sources to confirm that - and MSDN doesn't mention an upper limit). Merge requires inputs to be sorted whereas Union All does not. Apart from these differences, there doesn't seem to be much else that is different. They are both partially blocking, and from my limited tests, I couldn't see any noticeable difference in performance either. Everywhere I've searched, I've found that the recommendation is to use "Merge" whenever possible, and to use "Union All" if there are more than 2 data sources, or if the source data is unsorted. My question is, why? Why would I not just use Union All everywhere? What is the point of Merge? Once again, I'm sorry if this is a very basic/newbie ...
Post a Comment
Read more

Merge Join Transformation - isSorted - How to avoid sort transformation

Try to sort data at the source itself, if the source is a flat-file, try using Windows/Linux/UNIX sort utilities or load data in DB table and the sort it in DB
Post a Comment
Read more

Multiple SQL statements in Execute SQL task

Use go statement in between 2 SQL statements. delete *  from temp go delete * from temp2 go or simply delete *  from temp delete * from temp2
Post a Comment
Read more