StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Let's say that you execute the following SQL in an <code>Execute SQL Task</code> (using an ADO.NET connection) and you store the full result set in an SSIS Object variable.</p> <pre><code>select * from (select 1 as id, 'test' as description) resultSet1 ; select * from (select 2 as anotherId, 'test2' as description union select 3 as anotherId, 'test3' as description) resultSet2 </code></pre> <p>That object is actually a <a href="http://msdn.microsoft.com/en-us/library/system.data.dataset%28v=vs.110%29.aspx">System.Data.DataSet</a>, which can contain multiple result sets (accessible via the <a href="http://msdn.microsoft.com/en-us/library/system.data.dataset.tables%28v=vs.110%29.aspx">Tables</a> property). Each of those result sets is a <a href="http://msdn.microsoft.com/en-us/library/system.data.datatable%28v=vs.110%29.aspx">System.Data.DataTable</a> object. Within each result set (or <a href="http://msdn.microsoft.com/en-us/library/system.data.datatable%28v=vs.110%29.aspx">System.Data.DataTable</a>) you have <a href="http://msdn.microsoft.com/en-us/library/system.data.datatable.rows%28v=vs.110%29.aspx">rows</a>.</p> <p><img src="https://i.stack.imgur.com/QEbez.png" alt="enter image description here"></p> <p>The <code>Rows in all tables (ADO.NET dataset only)</code> and <code>All tables (ADO.NET dataset only)</code> options can be used when you need to iterate through all the result sets (instead of just the first one). The difference between the two is what objects are being enumerated over.</p> <p><strong>Rows in all tables (ADO.NET dataset only)</strong> - take all the rows of data returned from the SQL above and go through them one by one, mapping the column values to variables specified in your Variable Mappings. For the example above, you would have 3 total iterations (3 total rows). This behavior in a <code>Script Task</code> would look something like this:</p> <p><img src="https://i.stack.imgur.com/8KMVN.png" alt="enter image description here"></p> <p><strong>All tables (ADO.NET dataset only)</strong> - take all the result sets from the SQL above and go through them one by one, mapping the result set to the variable specified in Variable Mappings. For the example above, you would have 2 total iterations (2 total result sets). This behavior in a <code>Script Task</code> would look something like this:</p> <p><img src="https://i.stack.imgur.com/Lh0r5.png" alt="enter image description here"></p> <p>I've never had the need to use either one of these options, so I can't provide any specific scenarios where I've used them.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload