Variable is not updated in SSIS For Each Loop

Question

I'm trying to create simple project in which I'd like to download XML files from given website. I have stored files names in DataBase table. What I have done looking at this tutorial: Implementing Foreach Looping Logic in SSIS is:

a. Read all distinct rows from my Table (let's call it XMLTable)
b. Assign result of this query to User variable called: nameOfFileToDownload
c. Created For Each Loop container
d. Configured to assign localy each row with file name to download to: nameFileForeachLoop variable
e. Download files from concate link as a path using HTTPManager with dynamic file name from nameFileForeachLoop variable.
f. Created XMLFlatFile connection for dummy file - I assumed after reading from above tutorial.

The problem is now that this loop container works but doesn't download files separately - still to one file which at the end is empty. My nameFileForeachLoop variable is not updated during each LOOP iteration. What's more I have noticed that during FLAT FILE creation I have only CSV and TXT extension available. I have tried many approaches but without results. Can you help me how to download XML files? For example I have following link to XML: nbp.pl/kursy/xml/c001z180102.xml What changes here is last part of this link with XML extension which I get from my XMLTable.

I have configured my components as follows:

How do you know it's not updating the variable? If you add a Script Task inside the Foreach Loop Container that simply prints the variable, similar to what I have here does the execution results show all the expected values? — billinkc
– billinkc, Commented May 4, 2018 at 15:34
I have created simple script according to your answer and now it is showing me values for that variable. It works. Thanks. — DKM
– DKM, Commented May 7, 2018 at 0:15

Ferdipux · Accepted Answer · 2018-05-04 13:34:35Z

1

You are on the right track, but need some amendements.

Do not create and configure Flat File Destination connection manager unless you are creating tables in .CSV or .TXT files. In provided example author selects data with dynamic queries and stores the results in dynamic txt files. As I understand, this is not your case.
Here are some examples how to download and save files with HTTP in SSIS. Sample download script and Review of different approaches to HTTP download.

edited May 4, 2018 at 13:34

answered May 4, 2018 at 5:59

Ferdipux

5,2891 gold badge22 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

DKM Over a year ago

Thank you for the response on my topic. I have used your examples and now created dynamic script in which I download files according to it's name. Now it is working.

Collectives™ on Stack Overflow

Variable is not updated in SSIS For Each Loop

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related