September 14, 2022 at 12:09 pm
Hello, I'm trying to create excel files that hold data for each Invoice Id by using a Foreach Loop Container (FLC).
I have successfully created the source query that lists all the distinct invoice Id's with results held in a variable (Result Set).
Then successfully configured FLC to be a Foreach ADO enumerator and picking up the variable created above and creating another variable in the mapping.
Also created a data flow task, which uses a parameterised query (on invoice Id) and connected it to an excel destination.
I can get the data flow task to write data by per invoice Id into a single excel file, the next challenge is to split out the data into various excel files by invoice id and this is where I need help with.
I've attached an image showing my package.
Thank you.
September 14, 2022 at 2:11 pm
So close! Add a step after the Excel file is generated which renames it to something meaningful – Invoices_z.xlsx, where 'z' represents the InvoiceId, for example.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
September 14, 2022 at 4:14 pm
Timing is everything. I have a similar project except it uses flat files with header and detail records. My problem was that the header would not get saved before the detail was starting to load. This caused a foreign key violation. I tried a few things like a script with for each loop to slow it down. I think I finally split up the load with one task to load the headers and another to load the detail. I would be interested in seeing more of your process. Can you show your full SSIS package flow.
September 14, 2022 at 4:59 pm
Timing is everything. I have a similar project except it uses flat files with header and detail records. My problem was that the header would not get saved before the detail was starting to load. This caused a foreign key violation. I tried a few things like a script with for each loop to slow it down. I think I finally split up the load with one task to load the headers and another to load the detail. I would be interested in seeing more of your process. Can you show your full SSIS package flow.
How can you get a FK violation while creating a flat file?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
September 15, 2022 at 3:28 pm
I miss read the question. I am importing a flat file with header and detail records to the database. Strike my answer.
September 16, 2022 at 8:40 am
Thanks Phil, I'm not sure if a rename task is what I need.
Currently all data is produced into one file.
I want to have a separate excel file which has data based on per invoice id. I'm expecting the use of expressions in the excel connection manager which would sit within the loop.
Also, I've have created alternative solution which makes use of script task and I can get it that method to do what I need to, the only issue is, it produces xls format, but I need it in xslx format - I've posted this issue on a different area...
https://www.sqlservercentral.com/forums/topic/script-task-and-excel-format-syntax
So a solution to either method would be great.
September 16, 2022 at 10:38 am
Now it is my turn not to understand. In your very first post, you said this:
I can get the data flow task to write data by per invoice Id into a single excel file
My assumption was that you are looping around your Invoice Ids to do this.
If yes, then my idea works fine: send data for one invoice Id to Excel target file, rename target file, then process next invoice Id.
If not, what does your Foreach loop do?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
September 16, 2022 at 2:21 pm
Does the Excel file exist at the beginning of the process, or is it created during the process?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
September 16, 2022 at 2:33 pm
the file already exists, which only has headers
September 16, 2022 at 2:38 pm
If the file rename task is added after the Data Flow, doesn't that achieve your aim?
If a new empty target Excel 'template file' has to be copied over after the rename, so be it. It's been a long time since I wrote to Excel from SSIS and I can't remember whether this is required.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
September 16, 2022 at 2:41 pm
OK, so the process could be
2. Copy file to <filename>_<invoiceId>.xlsx
3. Overwrite existing target file with empty except headings 'template' file
4. Process next InvoiceId
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply