Package Deployment (SSIS 2005) - mainly MSDB-related

Normal 0 21 false false false HU X-NONE X-NONE MicrosoftInternetExplorer4

I think SSIS's package deployment methods would need a little improvement. I hope some buglike features are fixed in 2008 but I haven't used that one yet.

I set aside file system deployment for now and concentrate onto MSDB.

You have two (maybe three but you'll see) ways to deploy packages into the MSDB database:

  • using the dtutil command line utility,
  • create a DeploymentManifest and use the Package Installation Wizard,
  • manually creating folders in MSDB and importing packages in Management Studio.

Let's see the details:

1. DTUTIL

The first dtutil command I used was this: dtutil /? Big Smile It can help a lot! You can get to know the most important options.

Here are some examples for the most common deployment tasks:

  • Checking if the ‘Test’ folder exists in the MSDB database on the laptop04\dev server:
  • dtutil /SourceServer laptop04\dev /FExists SQL;\Test
  • Create a ‘Test’ folder in the root of MSDB database on the laptop04\dev server:
  • dtutil /SourceServer laptop04\dev /FCreate SQL;\;Test
  • Copy a package from file system into a specified MSDB folder with a specified name:
  • dtutil /File packagename.dtsx /DestServer laptop04\dev /Copy SQL;\Test\newpackagename
  • Move a package from one MSDB folder into another:
  • dtutil /SourceServer laptop04\dev /SQL \Test\package /DestServer laptop04\dev /Move SQL;\Prod\package
  • Check if a package exists in the specified MSDB folder:
  • dtutil /Exists /SourceServer laptop04\dev /SQL \Test\Package
  • Using SQL Server authentication method instead of Windows Authentication for delete:
  • dtutil /SQL Package /SourceUser SSIS_User /SourcePassword [password] /Delete

 You can create a customized batch file using these in the way you want.

The greatest disadvantage of using dtutil is that you cannot deploy package configurations with it Tongue Tied

If you want to solve this problem there are two ways of executing these packages:

  • using DTEXEC (with or without the GUI) where you can set which config file to use,
  • creating a scheduled job in which there is a possibility to specify which config file to use.

       (IMHO packages are generally executed in these two ways.)

 The other two ways are quite straightforward.

---------------------------------------------------------------------------------------------------

Moving packages inside the MSDB with SQL commands:

It can be done with dtutil as well but if you have to do it fast and it's not worth writing a batch file for it, the following method might come in handy.

There are two system tables in MSDB which is important now:

  • dbo.sysdtspackages90: contains the packages themselves (be sure to use the 90 postfix because that is for 2005 packages!
  • dbo.sysdtspackagefolders90: this tells you in which folders the packages are located.

We shall use the FolderID column of the sysdtspackages90 table which is a foreign key that leads to the sysdtspackagefolders90 table. You may query the packages with the related folderid this way:

SELECT p.name, p.folderid, f.foldername

FROM dbo.sysdtspackages90 p JOIN dbo.sysdtspackagefolders90 f

ON p.folderid = f.folderid

 

An example for moving packages from the MSDB root folder into the Prod folder:

UPDATE dbo.sysdtspackages90

SET folderid =

(SELECT folderid FROM dbo.sysdtspackagefolders90 WHERE foldername = 'Prod')

WHERE folderid =

(SELECT folderid FROM dbo.sysdtspackagefolders90 WHERE parentfolderid IS NULL)

 

Delete package from an MSDB folder

DELETE FROM dbo.sysdtspackages90

WHERE name = 'PackageName' AND folderid =

(SELECT folderid

FROM dbo.sysdtspackagefolders90 WHERE foldername = 'Foldername')

Using these hints I think it's easy to use these kind of queries :)

Published Wednesday, December 17, 2008 5:47 PM by MartinIsti

Comments

# dtutil, named instance and package import « Database Bulletin

Pingback from  dtutil, named instance and package import «  Database Bulletin

Powered by Community Server (Commercial Edition), by Telligent Systems