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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » How to Dynamically Change Oledb Source and...

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

Rate Topic

Display Mode

Topic Options

Author

Message

avdhut.k

Posted Thursday, October 04, 2012 12:09 AM

Valued Member

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 4:48 AM
Points: 56, Visits: 136

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!

Post #1368166

Abu Dina

Posted Thursday, October 04, 2012 2:24 AM

SSC-Addicted

Group: General Forum Members
Last Login: Today @ 3:02 AM
Points: 484, Visits: 2,127

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.

-----------------------------------
http://www.SQL4n00bs.com

Post #1368226

CozyRoc

Posted Friday, October 12, 2012 3:24 PM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 7:59 PM
Points: 1,106, Visits: 2,113

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/

Post #1372373

Evil Kraig F

Posted Friday, October 12, 2012 4:09 PM

SSCertifiable

Group: General Forum Members
Last Login: Saturday, May 18, 2013 10:09 PM
Points: 5,658, Visits: 6,100

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables
Twitter: @AnyWayDBA

Post #1372378

« Prev Topic | Next Topic »

Permissions