Writing to multiple XL spreadsheets of the same workbook within same workbook

Question

Writing to multiple XL spreadsheets of the same workbook within same workbook

VoldemarG

Hall of Fame

Points: 3883
More actions
November 29, 2018 at 10:21 pm

#373176

I need to produce 100s of .xlsx files via the foreach loop from which the dataflow source gets parameter for its sql query.
I have no problem how to do it if XL file has one spreadhsheet only;
However, in my case it is 2 spreadsheets. each populated from a different query/different oledb source/
How can I do that? there is only one data source, and one xl destination per data flow /
Likes to play Chess

Viewing 15 posts - 1 through 15 (of 17 total)

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

tim.ffitch 25252 SSCrazy Points: 2055 More actions · Answer 1

VoldemarG - Thursday, November 29, 2018 10:21 PM
I need to produce 100s of .xlsx files via the foreach loop from which the dataflow source gets parameter for its sql query.
I have no problem how to do it if XL file has one spreadhsheet only;
However, in my case it is 2 spreadsheets. each populated from a different query/different oledb source/
How can I do that? there is only one data source, and one xl destination per data flow /

In your data flow set up a second data source and a second data destination that is linked to the 2nd sheet. There is no rule that says there can be only 1 source and 1 destination. Alternatively you could create a 2nd data flow task inside the For Each Loop.

Thom A SSC Guru Points: 99130 More actions · Answer 2

VoldemarG - Thursday, November 29, 2018 10:21 PM
How can I do that? there is only one data source, and one xl destination per data flow /

Then add a second. Like Tim mention, you can have multiple sources and destinations in the same data flow. You don't even need to have the same number of each (I have numerous packages which have several sources and 1 destination or vice versa).

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

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

Make sure that you write to the worksheets in series, rather than parallel.
Excel spreadsheets do not, in my experience, work well with parallel processing.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Thom A SSC Guru Points: 99130 More actions · Answer 4

Phil Parkin - Friday, November 30, 2018 7:19 AM
Make sure that you write to the worksheets in series, rather than parallel.
Excel spreadsheets do not, in my experience, work well with parallel processing.

I've never actually had this problem, but I've seen plenty of people post about it. I've no idea what I do differently to others, but does make me think that there is something that causes the "bad" behaviour.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Phil Parkin SSC Guru Points: 246962 More actions · Answer 5

Thom A - Friday, November 30, 2018 7:31 AM
Phil Parkin - Friday, November 30, 2018 7:19 AM
Make sure that you write to the worksheets in series, rather than parallel.
Excel spreadsheets do not, in my experience, work well with parallel processing.
I've never actually had this problem, but I've seen plenty of people post about it. I've no idea what I do differently to others, but does make me think that there is something that causes the "bad" behaviour.

Interesting. I've actually corrupted Excel spreadsheets just by attempting to read from them in parallel, so I am very careful about this now.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Thom A SSC Guru Points: 99130 More actions · Answer 6

Phil Parkin - Friday, November 30, 2018 7:40 AM
Interesting. I've actually corrupted Excel spreadsheets just by attempting to read from them in parallel, so I am very careful about this now.

Really odd. I have a package that actually reads 5 different sheets (1 of them twice) all at the same time and never had the issue. I feel like this might be something to investigate at some time to see if I can corrupt the file and see what I had to do to do it.

Maybe it's because, at least in that package, I'm using 5 script tasks to read the file and one Excel Data source... :/

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Phil Parkin SSC Guru Points: 246962 More actions · Answer 7

Thom A - Friday, November 30, 2018 7:54 AM
Phil Parkin - Friday, November 30, 2018 7:40 AM
Interesting. I've actually corrupted Excel spreadsheets just by attempting to read from them in parallel, so I am very careful about this now.
Really odd. I have a package that actually reads 5 different sheets (1 of them twice) all at the same time and never had the issue. I feel like this might be something to investigate at some time to see if I can corrupt the file and see what I had to do to do it.
Maybe it's because, at least in that package, I'm using 5 script tasks to read the file and one Excel Data source... :/

I was using multiple data sources, as far as I can remember. Take a file copy before you try that out!

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

VoldemarG Hall of Fame Points: 3883 More actions · Answer 8

and if i use 2 OLEDB sources and 2 XL destinations in same Data Flow, would it then always process in parallel?
How can I make it sequential?

Likes to play Chess

Phil Parkin SSC Guru Points: 246962 More actions · Answer 9

VoldemarG - Friday, November 30, 2018 8:09 AM
and if i use 2 OLEDB sources and 2 XL destinations in same Data Flow, would it then always process in parallel?
How can I make it sequential?

Use two data flows and link them with a precedence constraint.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

VoldemarG Hall of Fame Points: 3883 More actions · Answer 10

DO you think it is better to start doing it via a SCRIPT task right away?
because neither one of the attached scenarios works.
When i disable one data flow in a loop, each dataflow by itself works.
but if i try to exec both data flows (one for each spreadhsseet within workbook), it fails. attached.

Likes to play Chess

Thom A SSC Guru Points: 99130 More actions · Answer 11

VoldemarG - Saturday, December 1, 2018 11:06 AM
DO you think it is better to start doing it via a SCRIPT task right away?
because neither one of the attached scenarios works.
When i disable one data flow in a loop, each dataflow by itself works.
but if i try to exec both data flows (one for each spreadhsseet within workbook), it fails. attached.

I've asked this in other topics, but just to repeat myself: Please don't upload word documents with the contents of your questions; but the context of your question/answer in your reply please. Users are less likely to download your file, and thus less likely to be able to offer an answer.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Phil Parkin SSC Guru Points: 246962 More actions · Answer 12

VoldemarG - Saturday, December 1, 2018 11:06 AM
DO you think it is better to start doing it via a SCRIPT task right away?
because neither one of the attached scenarios works.
When i disable one data flow in a loop, each dataflow by itself works.
but if i try to exec both data flows (one for each spreadhsseet within workbook), it fails. attached.

If I told you that my car "doesn't work" , would you be able to suggest how to fix it?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

VoldemarG Hall of Fame Points: 3883 More actions · Answer 13

So scripting tasks is my best bet to write to multiple spreadsheets of same workbook within ForEarchLoop?

Likes to play Chess

VoldemarG Hall of Fame Points: 3883 More actions · Answer 14

The problem I cannot overcome is that my output XL file name is built in the COPY G5 template task, the task that assigns name to output XL file (and then xl file path on xl conn mgr is set to that variable) based on a current value obtained within the for each loop. IF I create a bunch of XL files in the loop like with one dataflow, then another data flow simply does not know what file name to write to (second spreadsheet of each file must be written to from a different query…).
But if I add another CopyTemplate for the second data flow then the first spreadsheet will be blank (new template copied …) and the second one normally written to. How can I overcome that ? (attached).

Likes to play Chess