How to Dynamically Change Oledb Source and Excel File in Excel Destination.

Question

How to Dynamically Change Oledb Source and Excel File in Excel Destination.

avdhut.k

SSCrazy

Points: 2834
More actions
October 4, 2012 at 12:09 am

#268750

Hi,
I am Using DataFlow Task in For Loop Container,Now In Control Flow Task I am Converting my Sql Data into Excel File.
That is Oledb Source From Query and Excel Destination.
Now,I want Sql source Query to be dynamically change based on the iteration of For Loop
and also Excel File also change accordingly for the same.
Can any anyone tell how to do this.
Thanks in Advance!

Viewing 4 posts - 1 through 3 (of 3 total)

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

Abu Dina SSChampion Points: 14155 More actions · Answer 1

avdhut.k (10/4/2012)
Now,I want Sql source Query to be dynamically change based on the iteration of For Loop
and also Excel File also change accordingly for the same.

Sorry but I don't think this is possible in SSIS.

---------------------------------------------------------

It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn

COZYROC One Orange Chip Points: 28499 More actions · Answer 2

Can you elaborate what will your source SQL statement contain? Are you going to refer to the same columns in each iteration of your loop or the columns will vary?

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 3

avdhut.k (10/4/2012)
Now,I want Sql source Query to be dynamically change based on the iteration of For Loop

A mutable source is not available without a third party component, and then you'll have to review what's out there and figure out what works for you and what pitfalls they have. SSIS with standard components does not allow for metadata changes of the source during runtime. You can change the source of the data (IE: same exact structure in two databases/servers) but you can't change the metadata of the data itself.

Excel File also change accordingly for the same.

This you MIGHT be able to do in a Script Component (destination) by including the VBA library for Excel and programmatically dropping in every one of the inbound rows. I sure as heck wouldn't try it though, the complexity is overwhelming.

Out of curiousity, why the loop? Why not just build out the dataflows you need since the metadata is always changing anyway?

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA