Developing Matt

My Technical Journal

SSIS Storage Options

leave a comment »

You can store it with your application that you call directly or you can store it in the database.  If you store it in the database you have two options: File System or MSDB

MSDB:

  • You can leverage the security roles (give user’s rights to the packages using db_dtsltuser, db_dtsoperator, db_dtsadmin  link)
  • You can use sql server’s backup and restore
  • Executing a centralized package from other packages is easier to manage.
  • Stored in a table named sysssispackages.
  • Executon speed

File system:

  • You can modify the packages directly (%Program Files%\Microsoft SQL Server\100\DTS) and open/edit it with notepad or BIDS
  • You can manage the packages with the file system (drop all the packages into the directory and it will show up)

I’m doing a combination.  We have a package that is called by other packages…and in that situation it’s easier to manage sitting on a server, so that package will sit in the msdb.  But other than that the packages are in the File System for the sole reason that I want to copy all the packages into the directory.  I want to select all my packages, copy and paste them in the location (and yes ssms sees them when I do this).  If it’s stored in the msdb I have to use the gui to import them one at a time (there might be a way with dtutil). 

Part of the reason I went this way is due to the Data Source logic they used in SSIS.  You don’t import a datasource into ssms.  Instead it syncs with the existing packages.  This means that if I want to change the datasource then I have to open up the project so that it re-syncs.  Ideally in the next version of Sql Server they will have a way to manage a centralized datasource from SSMS so that I can change the connections instead of having the connections directly saved in the packages.

more from http://msdn.microsoft.com/en-us/library/ms137916.aspx

Advertisements

Written by matt

January 30, 2009 at 2:53 pm

Posted in SSIS

Tagged with , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: