I have no idea why it would be so hard to simply control the source and
destination of data within the new SSIS. In DTS I could cause the
connection to a file to change based on global variables and I could
change the destination table for a data pump the same way.

This was standard, needed functionality within DTS to handle time
iterative data e.g. c:\myfiles\filefor020105.txt,
c:\myfiles\filefor030105.txt or tblMyMonthyFact_01m, tblMonthlyFact_02m


Has anyone run into a bona-fide straightforward approach to this in
SSIS? Its really great that it has all this extensibility and what not
but quite frankly, its annoying that functionality of this nature has
either been removed or is simply undiscovereable.

Anyone with ideas or approaches please let me know? I'm trying to plan
our migration path to this platform (if any).

Best,

Edward R Hunter
erhunter (AT) comscore (DOT) com

Hello erhunter (AT) gmail (DOT) com,

Have you looked at Property Expressions?

Here's an example

http://wiki.sqlis.com/default.aspx/S...cLocation.html


Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

Hey Allan;

Sure I have, and while the flat file connection objects seem to support
them the data flow component does not seem to expose its 'OpenRowSet'
property to either expression manipulation or variables within
configurations.

Frankly, if I can get that worked out, I'm in real good shape on this.

Thanks a ton for your reply.

Best,

Edward R. Hunter

Hello ERHunter,

Would that be the OPENROWSET from the OLEDB Source Adapter?

If that is true then could you not just change the source of the statement
to be a variable?


Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

No, I'm referring to the Data Flow Component within the data flow
tasking. Its the 'destination' table for my flat file, in old DTS,
this would have been the table that was the DestinationObjectName of my
data pump. The other objects expose the properties required to change
this by variable or expression but this one does not.

I've tried setting the OpenRowSet property of this component to a
variable but it errors out.

Think 'replacing flat file to sql server table data pump using global
variables to determine both source (file) and destination (table)' that
is what I am trying to accomplish.

That help any?

~Edward

Hello ERHunter,

OK So you have a FLAT FILE moving to a SQL Server table?


The OLE DB Destination Adapter exposes the ability using "Data Access Mode"
to send to a "Table Name or View Name Variable"




Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

Sonofa....

Ok, you got me. Wow, ok, well with that little tidbit you got me back
on the road to migration to 2005. Thanks a ton I totally didnt even
see that sitting there.

Best,

Edward Hunter