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.

Changing a Connection Manager’s Conenction String using C# – SSIS 2008

A SSIS package can be run using C#, or basically any CLR language. The piece of code depicted in the following image calls a package, tries to execute it, and prints any error or exception that occurs dutring execution of the package to output:

Simple and easy!

Now suppose our package has 2 connection managers: one named “OLTP Source” and another one named “DW Destination”. And let’s suppose that OLTP Source connection manager is created prior to DW Destination, and therefore it is listed first in the Connection Managers pane of BIDS.

The connection managers in a package are accessible in C# code using Connections collection. They are accessed using an index to enumerate in the collection. For example, Pkg.Connections[0] points to the first connection manager listed in the Connection Managers pane. And the connection string of each connection manager can be set by editing connection manager’s ConnectionString property.

The following line of code sets the connection string of the second connection manager in the package (in our example, DW Destination):

pkg.Connections[1].ConnectionString = “Data Source=localhost;Initial Catalog=DW;Integrated Security=SSPI”;

This scenario can be used to update any property of any object in the package when the package is going to be run using a CLR language.

SQL Server 2000 stored procedure and SSIS 2008 OLE DB Source

What is the first thing that comes to your mind as a Microsoft BI expert, when you think of a data migration tool? Undoubtedly SSIS!

As you all know, SSIS is a great tool for migrating data from legacy systems and databaseas into newer and more robust places and repositories to better serve knowledge and data workers.
Yesterday, I was assigned to a task which was about getting the list of custoemrs from SQL Server 2000 into SQL Server 2008, using SSIS 2008. It didn’t seem something extraordinary at first: all I needed to do was to get an OLE DB source into my Data Flow Task, have a lookup table to filter existing records in the table, and pass the new rows into the destination table. I then realized that not all the customers need to be migrated, and there is a stored procedure written in SQL Server 2000 to return the list of the customers that should be migrated. The SP works this way: gets the Id of a principal as input, and extracts the list of customers for the principal based on some pre-defined business logic.
What I needed to be able to do was to pass the principal ID as my package’s parameter to SP, and use the result returned by SP in my source query’s WHERE clause. Passing parameter to package and then use it in t-sql query is nothing difficult. It gets messy when you need to use that parameter in a nested query: using IN keyword in the main query and using SP in the internal query to build a temporary table for IN keyword.
I have only one word for doing this thing: Impossible!
I tried different types of calling a SP in another query’s WHERE clause. I even got the SP’s code and put it into my t-sql query. But as you may know, SSIS’s OLE DB source does not support parameters in nested queries.
So, I tried to design a strategy for doing this. A strategy to get rid of the parameterized nested query.
What I did is, I added a Execute SQL Task to my package, which gets executed before my Data Flow task. The role of this Execute SQL Task is to get the list of customer IDs from the source database by executing the SP, and store them in a temporary table in destination SQL Server 2008 using this piece of code:

IF Object_Id('TemporaryTablet', 'U') IS NOT NULL
DROP TABLE TemporaryTable
CREATE TABLE TemporaryTable(CustID int)
INSERT INTO TemporaryTable EXECUTE dbo.Sql2000SP ? --? is the parameter passed to SP

Then, I connect this Execute SQL Task to my Data Flow task. This way, the new Data Flow task is not paramtereized anymore because the parameter, which is the principal ID, is used in previous step to pull the list of customers into the temporary table using Execute SQL Task. This is the code that gets data in my OLE DB Source:

SELECT Column1, Column2, Column3
FROM OriginalTable
WHERE CC.CustId IN (SELECT * FROM TemporaryTable)

Bingo! It works without any problem: there is no parameter to be passed to SP or the nested query to replicate SP’s logic in OLE DB Source! 🙂