Deploy SSIS package from remote computer

Discussion:

(too old to reply)

Snowmizer

2008-12-31 15:07:02 UTC

I am trying to deploy a SSIS package from my computer to a server that is
being built to replace an existing server. When I run the .manifest script
and select the default deployment path (deploying to SQL Server not File
System using Windows Authentication) I get a message:

"Could not create the directory "C:\Program Files\Microsoft SQL Server...".
Access to the path ...is denied."

I can deploy the package when logged into the new server fine. I checked the
security between the two servers and everything looks like it matches. What
security setting am I missing to make this run? One other thing my machine is
running Vista.

Thanks for the help.

Todd C

2009-01-08 18:57:01 UTC

Permalink

Can we assume that all machines are members of the same Domain?

Are you running the deployment from your machine as you?
Does your account have the same permissions on the new machine as it does on
the old one? Does it have the same SQL rights on the new instance as the old?
Are the service accounts for both instances the same? Do the service
accounts have the same rights on the two machines?

BTW, I quickly abandoned the deployment utility and went straight to
'pulling' new SSIS packages into the instance using SSMS. Doing so lets you
put them into folders. I found that the deployment utility jsut sticks it in
the root "MSDB" folder and does not allow you to deploy to a sub-folder.
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]

Post by Snowmizer
I am trying to deploy a SSIS package from my computer to a server that is
being built to replace an existing server. When I run the .manifest script
and select the default deployment path (deploying to SQL Server not File
"Could not create the directory "C:\Program Files\Microsoft SQL Server...".
Access to the path ...is denied."
I can deploy the package when logged into the new server fine. I checked the
security between the two servers and everything looks like it matches. What
security setting am I missing to make this run? One other thing my machine is
running Vista.
Thanks for the help.

Snowmizer

2009-01-08 19:18:12 UTC

Permalink

Yes I'm running the deployment utility as myself. My account does have the
same rights as the old machine.

I'm interested in your comment about abandoning the deployment utility in
favor of using the SSMS. What's the best practices for deploying SSIS
packages (where and using what method)? Do you want to deploy them to the
file system or MSDB?

Thanks.

Post by Todd C
Can we assume that all machines are members of the same Domain?
Are you running the deployment from your machine as you?
Does your account have the same permissions on the new machine as it does on
the old one? Does it have the same SQL rights on the new instance as the old?
Are the service accounts for both instances the same? Do the service
accounts have the same rights on the two machines?
BTW, I quickly abandoned the deployment utility and went straight to
'pulling' new SSIS packages into the instance using SSMS. Doing so lets you
put them into folders. I found that the deployment utility jsut sticks it in
the root "MSDB" folder and does not allow you to deploy to a sub-folder.
--
Todd C
[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]

Todd C

2009-01-08 19:34:13 UTC

Permalink

My personal preference is to deploy them to the MSDB. That way, they get
backed up with that database. Since I was the only DBA in my organization at
the time, my opinion actually COUNTED! You can use the security enhancements
and built-in roles in MSDB to fine tune who has access to run what packages,
etc but we were small enough that we didn't need it.

I tend to compartmentalize things. I'll have a different SSIS Project for
each major application or business discipline. So when I go to deploy those
packages, I want to compartmentalize them there as well. The deployment
utility in 2005 did not allow that, so'pulling' packages in through SSMS was
my answer.

Your environment or policy may dictate something else. I don't think there
is a "Best Practice" here. It's what works best for you.

Good luck.
--
Todd C

Post by Snowmizer
Yes I'm running the deployment utility as myself. My account does have the
same rights as the old machine.
I'm interested in your comment about abandoning the deployment utility in
favor of using the SSMS. What's the best practices for deploying SSIS
packages (where and using what method)? Do you want to deploy them to the
file system or MSDB?
Thanks.

Snowmizer

2009-01-08 19:39:12 UTC

Permalink

How do you pull them in via SSMS? Is that just the "Import Package" item when
you right click on "MSDB"?

Post by Todd C
My personal preference is to deploy them to the MSDB. That way, they get
backed up with that database. Since I was the only DBA in my organization at
the time, my opinion actually COUNTED! You can use the security enhancements
and built-in roles in MSDB to fine tune who has access to run what packages,
etc but we were small enough that we didn't need it.
I tend to compartmentalize things. I'll have a different SSIS Project for
each major application or business discipline. So when I go to deploy those
packages, I want to compartmentalize them there as well. The deployment
utility in 2005 did not allow that, so'pulling' packages in through SSMS was
my answer.
Your environment or policy may dictate something else. I don't think there
is a "Best Practice" here. It's what works best for you.
Good luck.
--
Todd C

Todd C

2009-01-08 19:43:01 UTC

Permalink

Yes. Follow the dialog box.

--
Todd C

Snowmizer

2009-01-08 20:51:01 UTC

Permalink

When I go through the "Import Package" and select "Browse" all I see is the
window that says "SSIS Packages" with the same folders that show up in SSMS
when I log into the server (Integration Services).

I'm assuming that in order to browse to my package I need to copy the
package from the location where it was written to the SQL Server? How much of
the project needs to be copied and does it matter where the files are copied
to?

Post by Todd C
Yes. Follow the dialog box.
--
Todd C

Todd C

2009-01-08 21:14:01 UTC

Permalink

Here is what I do:
From my local machine, I connect to the SSIS instance of a server.
Expand the "Stored Packages" node, then the "MSDB" node, then down to my
user-specific folder under it.
Right Click and select "Import Package"
Select File System and the location.
In the Package Path, click the Browse button (elipsis). This should open the
File system browser of the LOCAL machine. Since my packages have been
developed on my local machine, I navigate to My Documents\Visual
Studio...\Projects\... etc.
Locate and select an SSIS package (.dtsx file, no other stuff needed). I
don't need their configuration files because I have 'rolled my own' and that
system is quite extensive but VERY powerful. (read my blog on the subject at
ttp://toddchitt.wordpress.com/2008/06/27/ssis_configssis_config/
)

Sounds like you are missing selecting "File System" as the first option.
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]

Post by Snowmizer
When I go through the "Import Package" and select "Browse" all I see is the
window that says "SSIS Packages" with the same folders that show up in SSMS
when I log into the server (Integration Services).
I'm assuming that in order to browse to my package I need to copy the
package from the location where it was written to the SQL Server? How much of
the project needs to be copied and does it matter where the files are copied
to?

Post by Todd C
Yes. Follow the dialog box.
--
Todd C

Snowmizer

2009-01-08 21:49:11 UTC

Permalink

This post might be inappropriate. Click to display it.