Using an Object Variable inside xml source

SSIS and Import/Export
1

Good morning, guys.

Can you help me with a doubt ?

I use a "XML Source Task", passing a link in XML Location. This link is the return of a query created in "Execute Sql task".

When i use "Execute Sql task" I set the ResultSet as a single row, save the Result into a variable and use this variable (the link) in the XML Source.

Now the scenario changed and I have 21 rows in my Resultset and i need to do the same thing with "XML Source Task". For each row, i need to call XML Source, passing the result (which is the link) as a variable.

I Changed my variable from String to Object, changed my ResultSet to "Full Resul set" and the Reusult is going to the variable.

My problem began when I tried to configure my new object variable in the xml source. It didn't appear.

My options are:

  1. Set 21 variables (one for each row/link) and use each one in the "XML Source Task", in a hard code mode.

  2. I tried to concatenate all the rows in just one, but didn't got success.
    I don't know if this is the correct way.

I know that I have to use "foreach loop container" to do this, but don't know how. I appreciate if you guys can help me using this task too.

Best regards.

2

Hey guys.

Well, now the business analyst is saying that the codes won't change dinamically. So I can use hard code in my project.

Actually, i finished the development.

I did it with 2 JOBs.

JOB1 reads from 21 different XML Sources, does some simple data conversions and populates the staging table.

JOB2 does the insert from the staging to the destiny table.

Thank you very much for your patience and sorry for my english. :slight_smile:

Regards.