SSIS SQL Server Deployment, File System Deployment, and SSIS Package Store

SSIS packages can be stored in file system, as well as SQL Server. Let’s have a look at each one of these options for storing our SSIS packages.

1- SQL Server:

When a package is deployed to SQL Server, it is actually stored in MSDB database. The easiest way to deploy a SSIS package to SQL Server is by using Package Deployment Utility:

  • Open your SSIS project in BIDS (Business Intelligence Development Studio),
  • Right-click on the project entry in solution explorer and select properties,
  • Navigate to deployment tab, and set CrteateDeploymentUtility to true,
  • Build your project.

After completing these steps, go to bin -> Deployment folder of your project’s folder and double-click on .SSISDeploymentManifet file. This will launch Package Installation Wizard,which will get you through the necessary steps to deploy your package to SQL Server:

  • In the Deploy SSIS Package page of the wizard, select SQL Server deployment and click Next:

SQL Server Deployment

  • Specify Target SQL Server page is where you must specify the instance of SQL Server to which you want to deploy your package. Insert the instance name in Server name drop-down list, select appropriate Authentication mode, and the Path for the folder in which the package will be deployed:

SQL Server Deployment

Note that the list of folders you’ll get for the Package Path is exactly the same as folders you’ll see when you connect to SSIS using SSMS. If you have a new project in hand and you need to create a new folder for this project’s packages, you should Connect to SSIS through SSMS, Right-click on MSDB folder in object explorer, and select New Folder.

  • Next page is Select Installation Folder. You can set where package and its dependencies to be stored in your hard drive by clicking the Browse button:

SQL Server Deployment

  • Last step is to click Next in Confirm Installation page:

SQL Server Deployment

Apart from package deployment utility, developers can use DTUtil comand line utility for moving a package, which I believe is more complex and time taking that this option.

Now that you have completed these steps, your package is stored in MSDB database in dbo.sysssispackages table.

2- File System:

When a package is stored in file system, the package itself and its dependencies are store in a folder in hard drive. a package can be deployed to file system easily, by copying and pasting the .dtsx file to the target folder. This is the easiest way. Package Deployment Utility and DTUtil can be used as well, like SQL Server Deployment.

If you connect to SSIS through SSMS, you’ll see a folder named File system. This is the folder for displaying the packages that are deployed to file system. But not all the packages available in your hard drive will be displayed here. The folder that SSMS will look into to list the packages is C:\Program Files\Microsoft SQL Server\100\DTS\Packages. You can add new folders to here and then copy your packages, for better management.

If you like to add new folders for your SSIS packages other than just File System and MSDB to be vieable in SSMS, you should locate the file named “MsDtsSrvr.ini.xml” and configure it. Basically what you need to do is to add a new Folder node to this XML file.

Do not forget to restart your SSIS service after making any of these changes,  otherwise you won’t see any result.

SSIS Package Store:

Some websites and blogs mention a third type of package storage named SSIS Package Store, which is not correct. SSIS Package Store is nothing but combination of SQL Server and File System deployment, as you can see when you connect to SSIS through SSMS: it looks like a store which has categorized its contents (packages) into different categories based on its manager’s (which is you, as the package developer) taste. So, don’t get it wrong as something different from the 2 types of package deployment.

Search for Objects in an instance of SQL Server

There are cases when a developer needs to look for a specific object (table, Column, etc.) inside a database. A good example of such a scenario is when you have a database that has no diagrams or PK-FK constraints assigned, and you want to find out the PK-FK relationships and how tables in the database are connected together. This happens regularly in developing a Data Warehouse and designing an OLAP solution, when you want to transfer all the tables required to the Data Warehouse. And when the database is huge in terms of number of tables and views and the naming convention is not good and understandable, this becomes crazy!

SQL Server Management Studio (SSMS) gives you the ability to export the script used to do any operation on any type of object accessible through SSMS. For example, to get the T-SQL script for creating a table you can right-click on the table, select Script Table AS -> CREATE TO -> New Query Editor Window.

You may have guessed that we can follow almost the same procedure to export all the objects in a database to a query window and look for an object, e.g. a column, using the its name. To do this follow these steps:

1- After connecting to your instance of SQL Server, right-click on the database you are going to search in, navigate to Tasks, and then Generate Scripts… . The Script Wizard will launch:

2- Click Next in the Welcome window. You will see the Select Database window. This is where you can select the database you want to create the script for and search in:

3- In the next page, you can select the options for the objects you want to script. If you are going to get the script of the database just for searching an object in it, leave the options as they are:

4- Choose Object Types page is where you can select the types of objects to script. Note that you can select only the object types of which you have at least one instance in your database. For example, you won’t see the option to script user-defined functions if there are no functions defined by users in the database:

5- Based on the selections you have made in the previous page, you will get one or more pages to select the objects to script. If you have chosen to script only tables, you will see a page to select the tables to script:

6- In the output option, you will get the options for the script mode. You can the destinations of type File, Clipboard, or Query Window. I suggest to script the definition of your objects to a Query Window, because it is more readable in SSMS:

7- Check objects listed in the Script Wizard Summary and click Finish. The Generate Script Progress is done, a new window containing the generated script in SSMS will open.You can look for the objects in here by pressing Crtl+F:

P.S. : I owe a special thanks to Hendra, a very good friend and colleague of mine. Thanks mate. 🙂