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

Changing data source programatically does not transfer data

I have created a SSIS package that transfer data from a Foxpro database to an instance of SQL Server 2005 Express. I used the wizard to create the package but I load and execute the package within a custom application that I have written in C#.

The way the custom application is intended to work is that the user can have the database in any location on the computer and all he has to do is specify the location then the application programatically changes the location of the source on the package that it has loaded and then execute it. When I initially run the package the first time (using the original path), it works fine and transfers the data. However, every subsequent time I run the application and specify a different path, the database on the SQL Server side gets created as expected but the data is not transfered!

Where am I going wrong? Do I need to save the package after I modify the source then reload and run it again or do i need to change something else in the Data Flow to make this work?

I think this may be the wrong approach. You can parameterize the package at execution time with the connection string of the source and destination. This is better, and easier, than modifying the package structure.

-Jamie

|||

That is exactly what I am doing Jamie. I am changing the Connection string property of the source connection. Still doesn't work. Check below the code snippet:

// Load the package

string sLPFPackage = @."C:\projects\CCS_DTS\CCS_DTS\LPFMigration.dtsx";

Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();

Package oLPFPackage = app.LoadPackage(sLPFPackage, null);

oLPFPackage.UpdateObjects = true;

// Modify the source and target data locations

sDataSource = "Data Source=" + sLPFDataFolder + ";Provider=VFPOLEDB.1;Collating Sequence=MACHINE;";

ConnectionManager oDataConn = oLPFPackage.Connections["SourceConnectionOLEDB"];

DtsProperty prDataSource = oDataConn.Properties["ConnectionString"];

prDataSource.SetValue(oDataConn, sDataSource);

// Execute the package

oLPFPackage.Execute();

Any ideas?

|||

Actually that is exactly what I am suggesting you do NOT do. The package can be parameterized from the command-line when executed via dtexec.exe without actually changing the package itself. I don't know much about executing package from an application but I have no doubt that you will be able to do the same.

Explore the /SET option of dtexec.exe for more information: http://msdn2.microsoft.com/en-us/library/ms162810.aspx

-Jamie

|||Thanks jamie. Let me check it out. I will let you know if it helped.