September 18, 2008 at 5:00 am
Hi
I have around 20 SSIS packages stored in MSDB that I wish to copy to a development server. What is the quickest way to do this? I can export directly via Management Studio, or I could 'Add Existing Package' via BIDS but both these methods have to be done a single package at a time. Is there a way to move all the packages at the same time?
Restoring the MSDB database onto the development server is not an option.
cheers
September 18, 2008 at 8:27 am
Hi,
I also work with around 15 packages and I have to move them from development to test and from test to production. The way I handle this is by creating a deployment utility folder.
I do not know if you know about it, so I will just explain in brief about it.
This process looks lengthy, if you just read it. It is very simple, trust me. I recommend you to practically work on it along with reading. That will give you clear understanding of how things will work in this case.
Just open BIDS, then open your project that has all the 20 packages. In the Solution Explorer, right click the name of the package and click on Properties (last option in the context menu). Then a ProjectName Property Pages window will pop up. Click on Deployment Utility and then the first option should be true, the second option should also be set to true and the third option should be bin\Deployment. Then click on Apply and click Ok.
After doing this, click on Build (above the toolbar in Visual Studio).
Then, go to the folder in your Windows Explorer. The path would be: Folder Visual Studio 2005, then Folder Projects, then Folder with your Project Name, then Folder Bin, then Folder Deployment, then click the file with the name of your Project in this Deployment Folder. When you click it, it will start a Package Installation Wizard. Select SQL Server Deployment in the Deploy SSIS Packages option, then provide the server name where you want to deploy (in your case development server), and so on and so forth. It is user - friendly.
If you have any questions, feel free to ask me. I would be glad to share the little knowledge that I have.
Good Luck.
- notes4we
September 18, 2008 at 8:44 am
Hi
Thanks for your reply. Yes, I know of the deployment utility. Unfortunately my 20 packages are only stored in MSDB on the server. I do not have a BIDS solution that contains all the packages otherwise, as you describe, I could use the deployment utility.
In this instance I have created a new BIDS solution and used 'Add Existing Solution' 20 times. Once I had my solution I used the deployment utility to put the packages on the new server. I was wondering if there was a quicker way to move packages between machines so that when someone askes me to move 200 packages between servers I don't get finger cramp!
cheers
September 18, 2008 at 8:56 am
Hi,
I don't particularly like the deployment utility as it tries to do things that you don't necessarily expect.
I ended up writing a generic package which loops through a folder looking for dtsx files (this could equally loop through package names in a table) and for each one, it executes the dtutil.exe command utility.
Not the easiest way to do it, and would be useful if the tool could take an argument like the FTP "mget" or "mput".
HTH
Kindest Regards,
Frank Bazan
September 18, 2008 at 9:16 am
Hi,
Oh. So, you do not have a Solution file in BIDS.
If you just have your packages in MSDB, then I also do not know the easiest way to do this.
Kindly let us know if you figure out some easy way and I shall also try to look if I get something for it.
Thank you,
notes4we
September 18, 2008 at 10:05 am
Hi,
I know DTUTIL utility can help...I haven't used this you need to find more information on this...
dtutil:
You can use the dtutil command prompt utility to manage existing packages at the command prompt. You can access packages that are stored in the SQL Server msdb database, the SSIS Package Store, and the file system, and perform tasks such as copying, deleting, moving, and signing packages. You can also verify that a specified package exists.
The dtutil command prompt utility includes the following features:
Remarks in the command prompt, which makes the command prompt action self-documenting and easier to understand.
Overwrite protection, to prompt for a confirmation before overwriting an existing package when you are copying or moving packages.
Console help, to provide information about the command options for dtutil.
If the utility accesses a package that is stored in msdb, the command prompt may require a user name and a password. If the instance of SQL Server uses SQL Server Authentication, the command prompt requires both a user name and a password. If the user name is missing, dtutil tries to log on to SQL Server using Windows Authentication.
The dtutil command prompt utility does not support the use of command files or redirection.
For more information, see dtutil Utility.
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlcmpt9/html/6c7975ff-acec-4e6e-82e5-a641e3a98afe.htm
VG
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply