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.