Changing the connection string on the fly....

Now that I have figured out how to connect to a foxpro database...

Could anyone tell me how to change the connection string on the fly?

I would like to make it possible, from the command line (using dtexec) to specify a portion of the connection string.... incorportate this portion into the connection string and then instruct the connection manager to use this connection...

For example I have a number of foxpro databases to import, they will be place in a directory structure like this:

TopLevel
TopLevel\NewYork\transactions.dbf
TopLevel\London\transactions.dbf
TopLevel\Sydney\transactions.dbf

I'd like the user to be able to specify which city's files to load each time the package is run..

dtexec /f MyPackage /set \Package.Variables[User::THECITY].Properties[Value];"London"

And when the package starts the THECITY variable would take the value "London"

But I'm not sure how to then affect the connection string property of the connection manager... there seems to be no way to specify that it take its connection string property from an expression...

So I'm lookin for some way which would cause the Connection String property of the coneection manager to become:
Driver={Microsoft Visual FoxPro Driver};sourcedb=d:\TopLevel\London;sourcetype=DBF;exclusive=No;backgroundfetch=Yes;collate=Machine;

Jsut to recap, the provider for this connection manager
is a ".Net Providers\Odbc Data Provider" which I've been forced to use becasue the OLEDb provider for Foxpro has been rendered useless by service pack 1

Thanks in advance
PJ

PJFINTRAX wrote:

Now that I have figured out how to connect to a foxpro database...

Could anyone tell me how to change the connection string on the fly?

I would like to make it possible, from the command line (using dtexec) to specify a portion of the connection string.... incorportate this portion into the connection string and then instruct the connection manager to use this connection...

For example I have a number of foxpro databases to import, they will be place in a directory structure like this:

TopLevel
TopLevel\NewYork\transactions.dbf
TopLevel\London\transactions.dbf
TopLevel\Sydney\transactions.dbf

I'd like the user to be able to specify which city's files to load each time the package is run..

dtexec /f MyPackage /set \Package.Variables[User::THECITY].Properties[Value];"London"

And when the package starts the THECITY variable would take the value "London"

But I'm not sure how to then affect the connection string property of the connection manager... there seems to be no way to specify that it take its connection string property from an expression...

So I'm lookin for some way which would cause the Connection String property of the coneection manager to become:
Driver={Microsoft Visual FoxPro Driver};sourcedb=d:\TopLevel\London;sourcetype=DBF;exclusive=No;backgroundfetch=Yes;collate=Machine;

Jsut to recap, the provider for this connection manager
is a ".Net Providers\Odbc Data Provider" which I've been forced to use becasue the OLEDb provider for Foxpro has been rendered useless by service pack 1

Thanks in advance
PJ

PJ,

It is eminently possible to put an expression onto the ConnectionString property of a connection manager. You need to access the Expressions UI via the proeprties window.

In this example you can also use the /CONN option of dtexec. Check that out as well.

-Jamie

|||Hi Jamie,
thanks for the reply...
Mustn't have had enough caffeine in my system...

I was right clicking on the connection manager and selecting 'Edit'.. and there's no way there to set up an expression to populate the connection string....
Of course I completely forgot about the properties window... with the ..sigh.. Expressions option ... right there... sigh...

Apologies...

PJ