Variable Mappings in SSIS ForEach Loop

Question

I'm populating an ado net dataset in SSIS 2008 with a stored procedure. The resultset contains 21 columns. When using a For Each loop in SSIS, it appears that if I want the first two columns, then the 20th column I can't use index 0,1, 19. It appears that I have to use create mappings for 2-18 if i want to use index 19. Is there a way to only use the fields you want?

Thanks In Advance.

Answer 1

Works fine, I'm not sure what you're running into.

I created a basic package with an Execute SQL Task feeding into a Foreach Loop Container.

My source query is ugly but it satisfies my laziness

SELECT 
*
FROM
(
    SELECT TOP 21
        CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS int) AS rn
    FROM    
        sys.all_columns AS AC
) D
PIVOT
(
    max(rn)
    FOR rn IN ([1]
,   [2]
,   [3]
,   [4]
,   [5]
,   [6]
,   [7]
,   [8]
,   [9]
,   [10]
,   [11]
,   [12]
,   [13]
,   [14]
,   [15]
,   [16]
,   [17]
,   [18]
,   [19]
,   [20]
,   [21]
)
) P;

I push that full resultset into an SSIS Variable called rsResults of type Object.

Within my Foreach Loop Container, I map variables Col00, Col01 and Col19 into ordinal positions 0, 1 and 19

Biml all the things

If you have the free addon to Visual Studio, BIDS Helper you can recreate my solution with a few clicks. Biml is the business intelligence markup language, think of it as the operating system for BI. Here we'll use some XML to describe the SSIS package we want.

1. Add new .biml file to your SSIS project
2. Fix the third line there to point to a valid database
3. Right click on the biml file and select "Generate SSIS Package"
4. Profit

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <!-- UPDATE ME -->
        <OleDbConnection Name="CM_OLE" ConnectionString="Data Source=localhost\dev2014;Initial Catalog=tempdb;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;" />
    </Connections>
    <Packages>
        <Package ConstraintMode="Linear" Name="so_29220275">
            <Variables>
                <Variable DataType="Object" Name="rsResults" />
                <Variable DataType="Int32" Name="Col00">-1</Variable>
                <Variable DataType="Int32" Name="Col01">-1</Variable>
                <Variable DataType="Int32" Name="Col19">-1</Variable>
                <Variable DataType="String" Name="QuerySource"><![CDATA[SELECT 
*
FROM
(
    SELECT TOP 21
        CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS int) AS rn
    FROM    
        sys.all_columns AS AC
) D
PIVOT
(
    max(rn)
    FOR rn IN ([1]
,   [2]
,   [3]
,   [4]
,   [5]
,   [6]
,   [7]
,   [8]
,   [9]
,   [10]
,   [11]
,   [12]
,   [13]
,   [14]
,   [15]
,   [16]
,   [17]
,   [18]
,   [19]
,   [20]
,   [21]
)
) P;
]]></Variable>
            </Variables>
            <Tasks>
                <ExecuteSQL ConnectionName="CM_OLE" Name="SQL - gen data" ResultSet="Full">
                    <VariableInput VariableName="User.QuerySource" />
                    <Results>
                        <Result VariableName="User.rsResults" Name="0"></Result>
                    </Results>
                </ExecuteSQL>
                <ForEachAdoLoop SourceVariableName="User.rsResults" ConstraintMode="Linear" Name="FELC 3 of 20">
                    <VariableMappings>
                        <VariableMapping VariableName="User.Col00" Name="0" />
                        <VariableMapping VariableName="User.Col01" Name="1" />
                        <VariableMapping VariableName="User.Col19" Name="19" />
                    </VariableMappings>
                    <Tasks>
                        <ExecuteSQL ConnectionName="CM_OLE" Name="SQL - do nothing">
                            <DirectInput>SELECT 1;</DirectInput>
                        </ExecuteSQL>
                    </Tasks>
                </ForEachAdoLoop>
            </Tasks>
        </Package>
    </Packages>
</Biml>