Friday, March 23, 2012

Package trasfer to remote server

I have developed my ssis packages on my local system.Now i want to move them to a remote server.I have connected to the remote server over vpn.Now how do i transfer them to the server.Once i transfer i need to make few changes to the connection.What is the best way to transfer and make it work.

And then i want to schedule the packages to run automatically.

Please let me know

My first suggestion would be to try to read some of the books on-line (BOL). However, barring that, you can transfer the packages in one of a few different ways depending on how it is that you would like to store them on the remote server.

1.

a) You can simply copy the package .dtsx files to a folder on the remote server (or any remote folder that the user account running the package has access to, more on that later).

b) If you are storing it on the msdb you would connect to your ssis server (follow the following link for more information on setting up the dcom correctly to allow you to connect http://sqljunkies.com/WebLog/knight_reign/archive/2006/01/05/17769.aspx) and then click through the stored packages -> msdb and create a new folder, then right click and press import package. The rest should be pretty self explanatory.

2. What type of changes do you need to make? If they are simply connection string changes and / or variable changes you can use an xml config file, the /set values tab of the job step maintenance screen or the data connections tab (for changes to the connections). I would suggest the xml config file option. If you need to open the package and edit, you can easily do this (provided you set the correct package level protection, i.e. encrypt w/ password or do not encrypt sensitive, etc) by simply opening the package from the remote server.

3. See the following threads / technet articles on setting up a job / credentials / proxy etc.

http://support.microsoft.com/kb/912911

http://support.microsoft.com/?kbid=918760

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

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

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

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

Let us know if you have any more questions or need any more help

No comments:

Post a Comment