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.
Ok, and do you want your new records to show too in your exported file?
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.
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
15 People are following this question.
