Wednesday, April 17, 2013

Change SSIS Package Store location


SSIS stored the packages on 2 locations, on MSDB DB and on the file system.

If you're using the file system, it's very important to make sure that your package store location
points to the location of your packages.


The default packages location is:

C:\Program Files\Microsoft SQL Server\90\DTS\Packages
C:\Program Files\Microsoft SQL Server\100\DTS\Packages
C:\Program Files\Microsoft SQL Server\110\DTS\Packages

and the file that needs to be edited, by default located at :

(2005) C:\Program Files\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.ini.xml
(2008) C:\Program Files\Microsoft SQL Server\100\DTS\Binn\MsDtsSrvr.ini.xml
(2012) C:\Program Files\Microsoft SQL Server\110\DTS\Binn\MsDtsSrvr.ini.xml

Just make sure to search for "MsDtsSrvr.ini.xml", you might have installed SQL server on a different
drive or location.


Edit that XML file to point to your current location....

After you modify the service configuration file, you must restart the service to use the updated service configuration.

Please refer to this post on how to restart the SSIS service. http://www.jasonyousef.com/2013/03/restart-ssis-service.html

now refresh your SSIS FIle System Folder.


 Now, I can refer you to this article if you need to get the list of these packages http://www.simple-talk.com/sql/t-sql-programming/the-tsql-of-text-files/

Hope that helps!