Changing the owner of a user-defined data type

Hi Guys

Wonder if you could help me.

Basically I produce an accounts package that uses a SQL 2000 DB as the
RDBMS. I always instruct users to login as 'sa' and the relevant
password when doing an update to my program, as sometimes I need to do
database changes for new stuff.

Found that one of my users has not only logged in with their login
name (in this case Edward), but have also made this login a 'db owner'
so that when I created 2 new user-defined data types they belong to
Edward rather than dbo.

This must have happened a long time ago, but now that they want to
move Edward round the roles and/or delete him from a copy of the
database that they have, they can't because he's the owner of these
user-defined types.

This brings me to the reason for my post, how can I change the owner
from Edward to dbo for these data types? I found an article on
technet of how to do this, but when it suggests changing my
user-defined type to standard format it doesn't seem to work.

Any ideas?

Rgds

RobbieHi

I don't think there is a way to do this. If there are dependencies on the
UDT they will all need to be removed, then you can drop and re-create it.

John

"Robbie" <google@.astraaccounts.co.uk> wrote in message
news:f588d13a.0310062351.46e1f3f3@.posting.google.c om...
> Hi Guys
> Wonder if you could help me.
> Basically I produce an accounts package that uses a SQL 2000 DB as the
> RDBMS. I always instruct users to login as 'sa' and the relevant
> password when doing an update to my program, as sometimes I need to do
> database changes for new stuff.
> Found that one of my users has not only logged in with their login
> name (in this case Edward), but have also made this login a 'db owner'
> so that when I created 2 new user-defined data types they belong to
> Edward rather than dbo.
> This must have happened a long time ago, but now that they want to
> move Edward round the roles and/or delete him from a copy of the
> database that they have, they can't because he's the owner of these
> user-defined types.
> This brings me to the reason for my post, how can I change the owner
> from Edward to dbo for these data types? I found an article on
> technet of how to do this, but when it suggests changing my
> user-defined type to standard format it doesn't seem to work.
> Any ideas?
> Rgds
> Robbiesql

Changing the owner of a user-defined data type

Hi Guys
Wonder if you could help me.
Basically I produce an accounts package that uses a SQL 2000 DB as the
RDBMS. I always instruct users to login as 'sa' and the relevant
password when doing an update to my program, as sometimes I need to do
database changes for new stuff.
Found that one of my users has not only logged in with their login
name (in this case Edward), but have also made this login a 'db owner'
so that when I created 2 new user-defined data types they belong to
Edward rather than dbo.
This must have happened a long time ago, but now that they want to
move Edward round the roles and/or delete him from a copy of the
database that they have, they can't because he's the owner of these
user-defined types.
This brings me to the reason for my post, how can I change the owner
from Edward to dbo for these data types? I found an article on
technet of how to do this, but when it suggests changing my
user-defined type to standard format it doesn't seem to work.
Any ideas?
Rgds
RobbieI have never changed the owner of a UDT, but if it can be done, then
sp_changeobjectowner would the be sp to do it.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Robbie" <google@.astraaccounts.co.uk> wrote in message
news:f588d13a.0310062353.3dba1c6e@.posting.google.com...
> Hi Guys
> Wonder if you could help me.
> Basically I produce an accounts package that uses a SQL 2000 DB as the
> RDBMS. I always instruct users to login as 'sa' and the relevant
> password when doing an update to my program, as sometimes I need to do
> database changes for new stuff.
> Found that one of my users has not only logged in with their login
> name (in this case Edward), but have also made this login a 'db owner'
> so that when I created 2 new user-defined data types they belong to
> Edward rather than dbo.
> This must have happened a long time ago, but now that they want to
> move Edward round the roles and/or delete him from a copy of the
> database that they have, they can't because he's the owner of these
> user-defined types.
> This brings me to the reason for my post, how can I change the owner
> from Edward to dbo for these data types? I found an article on
> technet of how to do this, but when it suggests changing my
> user-defined type to standard format it doesn't seem to work.
> Any ideas?
> Rgds
> Robbie

Changing the ConnectionString property for a File Connection Manager

I have a package that I plan to run against about 700 databases to look for anomalies. I have several package variables in place that are passed in at runtime. One of them will hold the path and filename of the error log for the current database in process. I want each database to generate it's own error log for documentation and research purposes. However, when I run the package, it continues to use the path and filename that I entered when I created the File Connection Manager. I am trying to update that value in a Script Task by using the ConnectionManager class and setting the value for the "ConnectionString" property. This method is working for the OLEDB Connection Manager (which tells the package which Access database to process), but not for my File Connection Manager. Please help!

DO

Don't try and use a script task to do this. Use expressions: http://blogs.conchango.com/jamiethomson/archive/2006/03/11/3063.aspx

-Jamie

|||Thanks, you've done it again.

Changing table selections

Hello,

I created a package using the import/export wizard in SSIS, that loads data from one database to the other. I am trying to find out how I can add and remove the tables that were originally selected when the package was created. I opened the package in BIDS, and I could not find that particular option. I know you can do this in 2000/DTS...

Any help would be appreciated...

Thank you,

David

That should be part of the data flow. when you open the package in BIDS; click in the dataflow tab.

Notice that is the new table has a diffrent structure; you will need to 'refresh' the metadata in the other components in the dataflow.

|||You may have a Transfer Objects task in your package, instead of a data flow. If you click that and hit F4 to bring up the Properties window, one of the properties is Database Objects. You can alter the list of items from there.

Changing SSIS property defaults

To make certain SSIS features work there are many properties that need to be set over and over for most containers in the package. For example with Checkpoint\Restart, you need to set (in most cases) all of the tasks' FailPackageOnFailure to True. If you miss one, the package may not restart properly and you might never know. There are other situations where as a development team we want certain properties to be usually set the same but differently from the SSIS default.

Is there a way to control the defaults that the SSIS IDE uses?

I remember back in classic VB that if you wanted to change the defaults of a bare form you could create a template form adjusted the way you like and put it in a templates folder. Then new forms added to a project would be based on the template form.

No worries. The same thing exists for SSIS:

SSIS: Location for your package templates
(http://blogs.conchango.com/jamiethomson/archive/2005/11/12/2380.aspx)

It won't allow you to set default properties of tasks though - which I think is what you are after.

-Jamie

|||

Yes, I was hoping to set defaults for packages and tasks. Any recommendations? Our DTS developers are balking at the multitude of properties needed to be set to implement certain features.

What do you name the templates that you put in the templates folder so that they are used in the IDE? Package1.dtsx?

Thanks.

|||

Chopaka wrote:

Yes, I was hoping to set defaults for packages and tasks. Any recommendations? Our DTS developers are balking at the multitude of properties needed to be set to implement certain features.

Sorry, that's not possible. It would be nice if it were though - perhaps you could suggest it at Microsoft Connect.

Whenever they build the ability to reuse pre-configured tasks then you will be able to do it. I wouldn't expect that until v3 at the earliest though.

Chopaka wrote:

What do you name the templates that you put in the templates folder so that they are used in the IDE? Package1.dtsx?

Whatever you like!

-Jamie

Changing SSIS package - new user question

I saved a simple export/import package from the import/export wizard to a .dtsx file/package. Now I want to change the package to delete/truncate all of the tables before adding data. (since this has 80 tables it would have been a pain to edit each of them individually)

How can I easily go about doing this with the SSIS package. I can't find out where this would actually be executed.

Also, how can I actually view the code behind the SSIS package.

Thanks.

fcb wrote:

I saved a simple export/import package from the import/export wizard to a .dtsx file/package. Now I want to change the package to delete/truncate all of the tables before adding data. (since this has 80 tables it would have been a pain to edit each of them individually)

How can I easily go about doing this with the SSIS package. I can't find out where this would actually be executed.

Execute SQL Task

fcb wrote:

Also, how can I actually view the code behind the SSIS package.

Thanks.

Right-click on the package in BIDS-->View Code

-Jamie

|||I assume I have to place the Execute SQL Task in the For Each Loop Container. Do I just place a truncate table statement in the SQL Statement property? Does the "for each loop container" ensure it truncates every table or is there some other coding that has to be done?

I don't know whether its the view I'm in but I can't actually find the View Code selection when I right click on the package.
|||

fcb wrote:

I assume I have to place the Execute SQL Task in the For Each Loop Container. Do I just place a truncate table statement in the SQL Statement property? Does the "for each loop container" ensure it truncates every table or is there some other coding that has to be done?

Well, why not just write 80 delete statements in a single task. It'll take a while but you'l only have to do it once!

fcb wrote:

I don't know whether its the view I'm in but I can't actually find the View Code selection when I right click on the package.

Try clicking the "View Code" button in the solution explorer

-Jamie

Changing SQL Server Agent settings on the fly?

Hello all,

I have created a simple package that imports data from a flat file into a database. To run the package I'm using a SQL Server Agent Job. The location of the file is stored as a connection string in the Connection Managers tab in the SQL Server Agent Job.

Is there a way to change this connection string programmatically? If not programmatically, is there a way to change this setting right before I execute the package. I want to change the location of this file based on user input. Also, I'm executing the package using the sp_start_job stored procedure to run the job.

Thanks in advance for any advice!

-Dwayne

You could just create the entire job on the fly, they can be scripted in T-SQL.

If you want to keep the job, then you could use sp_update_jobstep to update the step command. Now I have not looked at the command for the SSIS subsystem, but I'd recommend use the CmdExec subsystem and calling DTEXEC myself, the logging is much better. Even MS recommend that approach in one of their KB's now. BTW could read it here first, illustrates why - http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html

Finally leave the job alone, and store the value in a table. You could manually use the Exec SQL Task to query the value, or perhaps better still use a SQL server based configuration. Have a look in Books Online about configurations, they are very useful, and I suggest you use them anyway to help manage connection details.

Changing package's owner

Dear All,
Previosly we were using NT 4.0 domain called Domain_A. Recently we imployed
Active Directory and moved all domain accounts to a new domain, Domain_B. We
are using SQL Server 2000 database installed on Win 2K box. Programers are
connecting to SQL Server with trusted connection. When User_A (programer's
account) created a package, the owner of the package appeared as User_A.
Now, the new account of the same user is User_B. I changed owner in the
"owner" field in table "sysdtspackages", from User_A to User_B, so the owner
of the same package appears as User_B. Now, the problem is that User_B can
not save its own package - where owner is User_B, but previously was User_A.
Pops up a message that only sysadmins and owners can make changes to the
package (allthow User_B appears to be an owner).
Did anyone encountered this kind of problem?
Thanks in advance.
BilBil
Try
sp_reassign_dtspackageowner [@.name =] 'name',
[@.id =] 'id',
[@.newloginname =] 'newloginname'
"Bil" <biljanat@.nbrm.gov.mk> wrote in message
news:%23BepIEK0EHA.1204@.TK2MSFTNGP10.phx.gbl...
> Dear All,
> Previosly we were using NT 4.0 domain called Domain_A. Recently we
imployed
> Active Directory and moved all domain accounts to a new domain, Domain_B.
We
> are using SQL Server 2000 database installed on Win 2K box. Programers are
> connecting to SQL Server with trusted connection. When User_A (programer's
> account) created a package, the owner of the package appeared as User_A.
> Now, the new account of the same user is User_B. I changed owner in the
> "owner" field in table "sysdtspackages", from User_A to User_B, so the
owner
> of the same package appears as User_B. Now, the problem is that User_B can
> not save its own package - where owner is User_B, but previously was
User_A.
> Pops up a message that only sysadmins and owners can make changes to the
> package (allthow User_B appears to be an owner).
> Did anyone encountered this kind of problem?
> Thanks in advance.
> Bil
>|||Uri,
Thank you very much. This resolved my problem. This is undocumented stored
procedure.
Thanks a lot.
Regards,
Bil
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23MHRBHK0EHA.3072@.TK2MSFTNGP11.phx.gbl...
> Bil
> Try
> sp_reassign_dtspackageowner [@.name =] 'name',
> [@.id =] 'id',
> [@.newloginname =] 'newloginname'
>
>
> "Bil" <biljanat@.nbrm.gov.mk> wrote in message
> news:%23BepIEK0EHA.1204@.TK2MSFTNGP10.phx.gbl...
> imployed
> We
> owner
> User_A.
>

changing order of DTS package

Hi everyone,

I have a DTS package that exports table from SQL Server to an Oracle database. However, I want to change the order in which the tables get exported because of referential integrity. I can find no way in the DTS designer to do that! IS there a way to change this order other than specifying a querY for each and every table!

Thanks,

PankajIf you're using Data Driven Query task in your package, - no. You'll need to replace it with individual table transfer tasks per table, where you can specify the order in which each table is being transfered. It's more tedious to create, but gives you much more control.

Changing global variable based on day of the week

I'm new to SSIS so please be gentle...

I'm creating a package that needs to go to an FTP site (FTP Task), download a file, unzip it and then process a series of table loads for the 12 text files that will be unzipped. My problem is that the zip file is a date (yyyymmdd.zip) which is normally the previous day of execution EXCEPT on Mondays when it would be the previous Friday's date. My thought is that IF (magic question) I could determine the day of the week in the SSIS package, I know that Tuesday-Friday is just a formatting exercise of getdate()-1 and Monday would be getdate()-3 but I can't seem to find a way (function?) that will allow me to determine the day of the week?

Thanks in advance!

Is that the only file on the site? If so it is easier to just download it using a wildcard *.zip. Then use a for each loop to process each file in the folder which will pick up the filename for you.

Even if it's not it's probably better to check all files on the site and process those which you haven't processed before.

What happens on bank holidays.

|||

Thanks for replying. Unfortunately it is not the only file (they keep them for archival purposes so there are hundreds). Is there an easy way to track which files I've processed (flag/switch) or are you suggesting a directory comparison or storing file names in a table somehow (not sure how to compare?)?

If there is no file, it just emails a failure notice and the process would be run manually as it is now (not the most efficient way but we can get to bells and whistles later).

You can disregard this question. I actually found the solution in a posting from "killerless" on 8/30/2006. He/she actually did pretty much the exact expression I needed to calculate the day. I love forums! :-)

|||

I would use a ForEachLoop conatiner with an expression in FileSpec property. The FileSpec property overrides the 'files' entry in the conatiner GUI. The expression should have the logic to get the right file name based on the system or execution date. I think sothing like this should work:

Datepart("dw", @.[User::CurrentDate] ) == 2 ?
(DT_WSTR,4)DATEPART("YYYY", @.[User::CurrentDate]) + RIGHT("0" + (DT_WSTR,2)DATEPART("MM", @.[User::CurrentDate]),2) + RIGHT("0" + (DT_WSTR,20) DAY(DATEADD("dd", -3, @.[User::CurrentDate])),2) + ".zip"
:
(DT_WSTR,4)DATEPART("YYYY", @.[User::CurrentDate]) + RIGHT("0" + (DT_WSTR,2)DATEPART("MM", @.[User::CurrentDate]),2) + RIGHT("0" + (DT_WSTR,20) DAY(DATEADD("dd", -1, @.[User::CurrentDate])),2) + ".zip"

It says someting like: if its friday; then substarct 3 days otherwise substract 1.

Put all the logic inside of the container. Let me know if you want the sample package.

|||It looks like my answer got too late |||Sorry! I do appreciate the effort!

Changing destination database for SSIS Package

Hi,

I have a small problem. I've gone through the SSIS wizard and created a dtsx file which imports data from an access file into a SQL Server 2005 database. It has been set to delete existing rows and enable identity insert.

I then edited the .dtsx package in SQL Server Management Studio and added an environment variable configuration to allow me to change the destination database. In the script which runs the dtsx, here is what I have (it's an x64 system, so hi have to use DTExec):

"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /file e:\testimport.dtsx /set \Package.Connections[DestinationConnectionOLEDB].Properties[InitialCatalog];newdatabasename

and here is the error I get:

Description: The configuration environment variable was not found. The envir
onment variable was: "InitialCatalog". This occurs when a package specifies an e
nvironment variable for a configuration setting but it cannot be found. Check th
e configurations collection in the package and verify that the specified environ
ment variable is available and valid.

I got the package.connections.etc etc path from originally creating the environment variable as an xml config file, then I could open the config file and see what the path was...

Any help would be appreciated :)

Change the database name in the ConnectionString property instead.|||

Can you help me with the syntax for that? Do I have to create an environment variable for connectionstring instead of initialcatalog?

I've tried this:

E:\>"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /file e:\testimport.dtsx /set \Package.Connections[DestinationConnectionOLEDB].Properties[ConnectionString];"Data Source=(lo
cal);Initial Catalog=TestCo_T_TWC;Provider=SQLNCLI;Integrated Security=SSPI;Auto
Translate=false;"

And i guess it doesn't like the quotes or something, because it causes a syntax error, but when i try it without quotes, i get:

option "Source=(local);Initial" is not valid.

|||You may want to read through this post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1043984&SiteID=1|||

Hi,

Thanks for the help. I read through that thread and it is very close to answering my question. What I don't get though is how to punctuate the connection string. In the examples from the other post, they didn't have a long connection string with spaces in it, so it didn't have any punctuation (or the ones that did were the ones that were causing the OP trouble, so it's hard to say if his punctuation was correct). Can you help me with where to put my quotation marks please?

E:\>"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /file e:\testimport.dtsx /set \Package.Conne
ctions[DestinationConnectionOLEDB].Properties[ConnectionString];"Data Source=(local);Initial Catalog=TestDB;Provider=SQLNCLI;Integrated Security=SSPI;Auto Translate=false;"

The above gives me the error:

Argument ""\Package.Connections[DestinationConnectionOLEDB].Properties[ConnectionString];Data Source=(local);Initial Catalog=TestDB;Provider=SQLNCLI;Integrated Security=SSPI;Auto Translate=false;"" for option "set" is not valid.

You can see it appears to have taken my quotes from around the connection string and put them around the entire /set argument... So how do I fix that?

|||I think, you should surround each part of the SET argument in double quotes. Embedded quotes can (should?) be escaped.

/SET "parameter-to-set";"value-with-\"escaped double quotes\""

So, try:

/SET "\Package.Connections[DestinationConnectionOLEDB].Properties[ConnectionString]";"Data Source=(local);Initial Catalog=TestDB;Provider=SQLNCLI;Integrated Security=SSPI;Auto Translate=false;"|||

Hi Phil,

Thanks for your help. That didn't work either, it leads to the same error as before:

Argument ""\Package.Connections[DestinationConnectionOLEDB].Properties[Connectio
nString];Data Source=(local);Initial Catalog=TestDB;Provider=SQLNCLI;Integ
rated Security=SSPI;Auto Translate=false;"" for option "set" is not valid.

Which is wierd because it is ignroing the double quotes I put between the package.connections etc etc and the data source= etc etc

|||Try this instead of /SET... This should work, because the Execute Package Utility generated it for me:

/CONNECTION DestinationConnectionOLEDB;"\"Data Source=(local);Initial Catalog=TestDB;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;\""|||

Thanks Phil!

It's too late for me to name my firstborn after you, but I still really appreciate your help today!

Cheers

Changing destination database for SSIS Package

Hi,

I have a small problem. I've gone through the SSIS wizard and created a dtsx file which imports data from an access file into a SQL Server 2005 database. It has been set to delete existing rows and enable identity insert.

I then edited the .dtsx package in SQL Server Management Studio and added an environment variable configuration to allow me to change the destination database. In the script which runs the dtsx, here is what I have (it's an x64 system, so hi have to use DTExec):

"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /file e:\testimport.dtsx /set \Package.Connections[DestinationConnectionOLEDB].Properties[InitialCatalog];newdatabasename

and here is the error I get:

Description: The configuration environment variable was not found. The envir
onment variable was: "InitialCatalog". This occurs when a package specifies an e
nvironment variable for a configuration setting but it cannot be found. Check th
e configurations collection in the package and verify that the specified environ
ment variable is available and valid.

I got the package.connections.etc etc path from originally creating the environment variable as an xml config file, then I could open the config file and see what the path was...

Any help would be appreciated :)

Change the database name in the ConnectionString property instead.|||

Can you help me with the syntax for that? Do I have to create an environment variable for connectionstring instead of initialcatalog?

I've tried this:

E:\>"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /file e:\testimport.dtsx /set \Package.Connections[DestinationConnectionOLEDB].Properties[ConnectionString];"Data Source=(lo
cal);Initial Catalog=TestCo_T_TWC;Provider=SQLNCLI;Integrated Security=SSPI;Auto
Translate=false;"

And i guess it doesn't like the quotes or something, because it causes a syntax error, but when i try it without quotes, i get:

option "Source=(local);Initial" is not valid.

|||You may want to read through this post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1043984&SiteID=1|||

Hi,

Thanks for the help. I read through that thread and it is very close to answering my question. What I don't get though is how to punctuate the connection string. In the examples from the other post, they didn't have a long connection string with spaces in it, so it didn't have any punctuation (or the ones that did were the ones that were causing the OP trouble, so it's hard to say if his punctuation was correct). Can you help me with where to put my quotation marks please?

E:\>"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /file e:\testimport.dtsx /set \Package.Conne
ctions[DestinationConnectionOLEDB].Properties[ConnectionString];"Data Source=(local);Initial Catalog=TestDB;Provider=SQLNCLI;Integrated Security=SSPI;Auto Translate=false;"

The above gives me the error:

Argument ""\Package.Connections[DestinationConnectionOLEDB].Properties[ConnectionString];Data Source=(local);Initial Catalog=TestDB;Provider=SQLNCLI;Integrated Security=SSPI;Auto Translate=false;"" for option "set" is not valid.

You can see it appears to have taken my quotes from around the connection string and put them around the entire /set argument... So how do I fix that?

|||I think, you should surround each part of the SET argument in double quotes. Embedded quotes can (should?) be escaped.

/SET "parameter-to-set";"value-with-\"escaped double quotes\""

So, try:

/SET "\Package.Connections[DestinationConnectionOLEDB].Properties[ConnectionString]";"Data Source=(local);Initial Catalog=TestDB;Provider=SQLNCLI;Integrated Security=SSPI;Auto Translate=false;"|||

Hi Phil,

Thanks for your help. That didn't work either, it leads to the same error as before:

Argument ""\Package.Connections[DestinationConnectionOLEDB].Properties[Connectio
nString];Data Source=(local);Initial Catalog=TestDB;Provider=SQLNCLI;Integ
rated Security=SSPI;Auto Translate=false;"" for option "set" is not valid.

Which is wierd because it is ignroing the double quotes I put between the package.connections etc etc and the data source= etc etc

|||Try this instead of /SET... This should work, because the Execute Package Utility generated it for me:

/CONNECTION DestinationConnectionOLEDB;"\"Data Source=(local);Initial Catalog=TestDB;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;\""|||

Thanks Phil!

It's too late for me to name my firstborn after you, but I still really appreciate your help today!

Cheers

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.