Solved

change owner of ssis package

Posted on 2009-07-14
24
617 Views
Last Modified: 2013-11-10
Is there a way that you can change the owner of an ssis package in sql 2008 - preferably on the command line.
0
Comment
Question by:david_32
  • 12
  • 12
24 Comments
 
LVL 15

Expert Comment

by:rob_farley
ID: 24865870
Two questions:
1/ What are you trying to achieve by doing this?
2/ Where does the package live (file system / MSDB)?

For example... if you're trying to make it so that someone else can see the secret info about a package, then changing the owner won't help.

Rob
0
 

Author Comment

by:david_32
ID: 24866188
I trying to change the owner of the package so that it complements the ProtectionLevel with EncyptSensitiveWithUserKey. The owner I want to change to will be a common user that all users can use to run jobs that can run any package a user has priv to run and will setup as a sql agent proxy. The developers don't want me to use Encrypt with password because it slows down the development time and and I am having trouble with ServerStorage because when I use dtutil it is telling me that  I cannot use Serverstorage because the msdb database it not at the right version. I think this is because I have sql 2005 and 2008 on the server and dtutil is getting mixed up on which msdb database it should use.

The package lives \MSDB\Data Collector.


thanks

0
 
LVL 15

Expert Comment

by:rob_farley
ID: 24867305
You should be able to tell dtutil which instance you need to talk to...
0
 

Author Comment

by:david_32
ID: 24874733
I am I run
dtutil /SourceServer <servername>/SQL <package> /ENCRYPT SQL;<package>;5 /Q

it does not work.
0
 
LVL 15

Expert Comment

by:rob_farley
ID: 24875066
Can you put the instance slash the other way around?

dtutil /SourceServer <servername>\SQL <package> /ENCRYPT SQL;<package>;5 /Q

And let me know what the error is?

Rob
0
 

Author Comment

by:david_32
ID: 24875188
/SQL  is a switch for dtutil to specify a location of the package that is why I put <package> afterwards.

Could not save package <packagename> because of error 0xC001401C.
Description: Storing or modifying packages in SQL Server requires the SSIS runti
me and database to be the same version. Storing packages in earlier versions is
not supported.

I have sql 2005 + 2008 on the box. I am not using ssis 2005 and I am running dtutil from the path of the 2008 binary




0
 
LVL 15

Expert Comment

by:rob_farley
ID: 24875756
Oh, sorry... I was thinking you meant the name of your instance was SQL, from there no being a space betwen <servername> and the slash.

Is your \DTS\Binn\msdtssvr.ini.xml pointing to the right MSDB database?

Rob
0
 

Author Comment

by:david_32
ID: 24875966
Yes it is .. I thought that was the answer but no luck :)
0
 
LVL 15

Expert Comment

by:rob_farley
ID: 24875978
Ok... so it's pointing to the 2008 instance?

And when you're in SQL Config Manager, you can see Integration Services 10.0?

Are you specifying the dtutil path explicitly? Like... .\dtutil.exe, not just dtutil (thinking that even though you're in the right folder, it may be preferring a different dtutil if there's one in the path).

Rob
0
 

Author Comment

by:david_32
ID: 24875997
yes .. I tried running dtutil both ways cd to the right folder and also running
<path>:\dtutil .......



0
 
LVL 15

Expert Comment

by:rob_farley
ID: 24876010
Can you stop all the 2005 services and see what happens?
0
 

Author Comment

by:david_32
ID: 24876028
actually I already tried that  and it did the same thing.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 15

Expert Comment

by:rob_farley
ID: 24876033
Can you get the package out (using SSMS), and then use /FILE instead?
0
 

Author Comment

by:david_32
ID: 24876060
you can't use file package storage if you want to use the ServerStorage Protection level on the package (that is the 5 bit of /ENCRYPT SQL;<package>;5 in my dtutil command)
0
 

Author Comment

by:david_32
ID: 24876062
also it is not a solution for me as I want to store the packages in the database.
0
 
LVL 15

Expert Comment

by:rob_farley
ID: 24876069
Yeah - I was thinking maybe you could pull the package into the file system, make your changes, and then push them back in.
0
 

Author Comment

by:david_32
ID: 24876083
not a bad idea except I can't change the protectionlevel to serverstorage  when the package is a file - I tried it already.
0
 
LVL 15

Expert Comment

by:rob_farley
ID: 24876115
Are you doing this on the local server, or remotely?

(if remotely, can you try it on the local server)

Just that the error message about versions suggests that there's a discrepancy between client tool and server product somewhere.
0
 

Author Comment

by:david_32
ID: 24876212
on the local server. If you run the command remotely it will work as long as there is a 2008 SQL server also on the client machine.

0
 
LVL 15

Expert Comment

by:rob_farley
ID: 24876239
Really makes it sound like the dtutil process is for a different version...

I'm clutching at straws now.
0
 

Author Comment

by:david_32
ID: 24876266
I agree.. before this thread we were considering removing sql 2005 from the current problem box so that 2008 is by itself. I probably am going to take that course of action.
0
 
LVL 15

Expert Comment

by:rob_farley
ID: 24876320
Do you have another environment that just contains SQL 2008, that you could use to test it?
0
 

Author Comment

by:david_32
ID: 24892252
I have 2008 on my client machine .. I could probably test with that.
0
 
LVL 15

Accepted Solution

by:
rob_farley earned 500 total points
ID: 24892283
Yeah - terrific. Let me know how it goes, and then we can move on with this.

Rob
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now