Populate a variable of type Object in a Foreach File loop

Question

Populate a variable of type Object in a Foreach File loop

Gert.Broekhaar

SSC Enthusiast

Points: 179
More actions
February 8, 2011 at 7:38 am

#144981

Hello there,
I want to populate a variable of type Object in a Foreach File loop. The variable must represent a small table with a few columns and at each iteration in the loop a new record with relevant info must be added to it. Later on in the SSIS package, also at the control flow level, I want to inspect the contents of the variable and loop through my 'table' which can I think with a Foreach ADO Enumerator.
Problem is that I don't know how to gradually extend my table by adding records to it i.e. write to my variable in a loop. Should I do this with a Script Task within the loop? How can I do this in SSIS?
Thanks very much!
Gert

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

You might be able to do this but I think you might want to step back from the method you are proposing and tell us about the problem you are trying to solve. My feeling is there is a simpler way to do this.

CEWII

Gert.Broekhaar SSC Enthusiast Points: 179 More actions · Answer 2

Hi Elliott,

Thanks for your reply!

Here are the steps of my ETL (in a nutshell)

1. In a for each loop file container browse a particular directory on the file system and read and parse 1 or more flat files

2. Write the parsed data to a SQL table in the staging area

3. Do validations and transformations on the data in the staging area

4. Load valid data to the datawarehouse

In step 1 I log in a SQL table the files that are processed during a particular package run. Each file gets (with each iteration in the loop) an entry in the table with info about the package run, delivering party, file characteristics and an initial status "Processing...".

Steps 3 and 4 are performed on the full set of data in the staging area, so not per file! It could be that a particular file is valid and saved to the datawarehouse while an other could not be processed.

Data in the datawarehouse have a reference to the earlier mentioned log entry so at the end of the package the ultimate test if a file is processed is the existence of a reference to the log entry in the warehouse. So in the log the status of a file will be updated to 'Successful' or 'Unsuccessful'.

Indeed I can use my SQL table at the end of the package to read data from it but I wanted to save a roundtrip to this table and use the objectvariable in the package.

After all a minor issue but I wondered if it was possible to build an in-memory table in a loop in SSIS?

Thanks!

Gert

Phil Parkin SSC Guru Points: 247180 More actions · Answer 3

So, in effect, you're trying to build a file-status table in memory and then issue a single commit at the end of package execution to write it to disk?

Gert.Broekhaar SSC Enthusiast Points: 179 More actions · Answer 4

Thanks Phil!

Nearly correct!

At the end of the package I want to loop through my table variable and per file lookup if the "LogID" exists in the datawarehouse. Depending on the result I want to update the file-status in the SQL logtable.

But, once again, I wondered if I could add records to my table variable in the for each loop.

In the loop container in an Execute SQL Task (EST) I write via the result set property my newly added log entry to a temporary object variable. Then, I thought I'll put a script task after the EST and append the record in the temporary object variable to my table object variable but I don't know how to do that.

Thanks!

Gert

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 5

Unless there are a fairly large number of files (like 1000's) I don't see much of an issue just updating their status after they are processed. I am a little concerned with the method you are putting forward because it sounds like there would/could be restartability issues if for any reason the process died after the processing but before the log update. Or even a partial run.. Given these factors I wouldn't wait till the end to update status..

CEWII