February 8, 2017 at 4:06 pm
This seems like it should be simple, but I can't seem to find the answer.
I have a multiple table query in an OLE DB data flow source that produces an intermediate result.
From the result of that query, I'd like to do two things that both involve running another SQL command the SELECTs from the result.
1) Create a flat file using a subset of the columns returned by the data flow source.
2) Summarize a different subset of the columns returned by the data flow source and update a table with the summary results.
I looked at a saving the results in a recordset variable, but then I'd need to iterate the results. Doable I suppose, but it seems tedious and slow.
I also looked at raw files for intermediate storage, but can't seem to find a way to use a raw file as input to a query that will summarize it.
I'm fairly new to SSIS, so I have a hunch the answer is right in front of me, but I can't see it. Any ideas?
Thanks,
Mike
.
February 8, 2017 at 4:31 pm
palandri - Wednesday, February 8, 2017 4:06 PMThis seems like it should be simple, but I can't seem to find the answer.I have a multiple table query in an OLE DB data flow source that produces an intermediate result.
From the result of that query, I'd like to do two things that both involve running another SQL command the SELECTs from the result.1) Create a flat file using a subset of the columns returned by the data flow source.
2) Summarize a different subset of the columns returned by the data flow source and update a table with the summary results.I looked at a saving the results in a recordset variable, but then I'd need to iterate the results. Doable I suppose, but it seems tedious and slow.
I also looked at raw files for intermediate storage, but can't seem to find a way to use a raw file as input to a query that will summarize it.I'm fairly new to SSIS, so I have a hunch the answer is right in front of me, but I can't see it. Any ideas?
Thanks,
Mike
.
First of all, this is not a dumb question and there is no obvious answer.
I think I would look at creating two versions of your base query, one for each requirement and have two separate data flows which do the work for you.
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
February 10, 2017 at 10:56 am
Thanks Phil.
I did find a workable solution. I fed the output from the OLE DB data source into a Multicast transformation, which will clone the results as many time as you like. One multicast output goes into an Aggregate transformation to perform the summary operation, another goes to the Flat File Destination to create a file containing detail.
February 10, 2017 at 11:01 am
palandri - Friday, February 10, 2017 10:56 AMThanks Phil.I did find a workable solution. I fed the output from the OLE DB data source into a Multicast transformation, which will clone the results as many time as you like. One multicast output goes into an Aggregate transformation to perform the summary operation, another goes to the Flat File Destination to create a file containing detail.
Bravo for getting to a decent solution, but watch out for those SSIS aggregate transformations: they are fully blocking and perform slowly when compared with the SQL engine.
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply