question

liton avatar image
liton asked

SSIS Foreach Loop export to same file

I am trying to export 2 files (customers and orders) from two different tables. The tables will change constantly and I will have to export data that has both customer and order records. Also, I will have to update the records that I exported at the end of the run. I am thinking about creating a SQL Execute Task and store the ID in a full result set and use foreach loop to export the IDs that are in Full Result Set and at the end update them to “Exported”. But I’m having problem exporting the data in the same file and I also want column name to be in the first row. Is there a way I can achieve this? Thanks.
ssisforeach-loop
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

nidheesh.r.pillai avatar image nidheesh.r.pillai commented ·
Ok, and do you want your new records to show too in your exported file?
3 Likes 3 ·
liton avatar image liton commented ·
Records will be inserted/updated constantly. I will be exporting customer records first and at the same time new records might be added while I'm exporting customer file.
0 Likes 0 ·
liton avatar image liton commented ·
I do not because it might have incomplete data. Also, I don't want orphan data in order file. In other word, I don't want to send anything on the second file that does not have a linking id in the first file. I also want to update the records that were exported at the end of the run hence I want to capture the IDs at the beginning of the run.
0 Likes 0 ·

1 Answer

·
nidheesh.r.pillai avatar image
nidheesh.r.pillai answered
Okay, as far as I have understood, you wish to take all the data onto a file, and then at the end, compare that data with your two tables to see if there are any records to be updated. In my opinion, due to the high trasactions mentioned, you should plan to stage your data first to staging tables initially via ETL, then perform an update based on the key values of your stage table and your base tables, and then use the stage table to export data onto your file via ETL. To see the column names as well, see one solution I provided in this [link][1]. Hope that helps! [1]: https://ask.sqlservercentral.com/questions/120341/how-can-i-create-pipe-delimited-text-file-in-ssis.html
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.