Post reply

Deploying SSIS to production

  • July 9, 2009 at 12:04 pm

    #384962

    I have a dtsx package that uses the "Execute Package Task" to run other dtsx packages. Once you add this other dtsx package to be executed, it creates a listing under the "Connection Managers" tab at the bottom of the page.

    Then if I double click the connection, it will open an editor window where the "Usage Type" is set to "Existing File" and the "File" is set to the complete path of the dtsx file such as C:\Projects\ProjectName\PackageName.dtsx

    My problem arises when I go to deploy multiple dtsx packages to a productions server as the production server has a completely different file path where the dtsx packages will be deployed to.

    Is there any way that I use some sort of variable that can be used for multiple dtsx packages so I only have to change this variable once per environment so that it will be something like C:\Projects\ProjectName\ or E:\DeploymentFolderName. Then I would only have to change the path in all of the dtsx packages from C:\Projects\ProjectName\PackageName.dtsx to VariableName\PackageName.dtsx.

    Is this possible or must I change the path in each of my dtsx packages that call a different dtsx package?

    Thanks

  • July 9, 2009 at 4:32 pm

    #1022768

    One possible solution is to use environment variable, which you will setup once on each server where your packages will execute and will contain the path to your SSIS packages.

    ---
    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

  • July 9, 2009 at 5:15 pm

    #1022781

    I have a different question, is it your policy to store that packages in the file system and not in the "Integration Server" (msdb), if you stored them there then you could use the (local) refernce for server name and not have to worry so much about paths. I do generate paths in my Integration server though..

    Also you could use a package configuration to handle this..

    CEWII

  • July 10, 2009 at 7:17 am

    #1023009

    I am fairly new to SSIS so I do not have a policy or favorite way on whether to store the SSIS packages on the file system or in the msdb. From the bit I have read on the Internet, some people say one way and others say the opposite. I believe that if I want to use a SQL Server job to schedule and execute the package then I needed to deploy the packages into SQL Server rather than the file system so I am doing that. Am I correct on this scheduling part? Which storage method do you suggest?

    I also previously briefly looked online at configuration file that contains an environment variable but I thought you needed a separate configuration file for each dtsx package. Is this correct? If so, does the environment variable only work for that dtsx package or does it also work for all dtsx packages that are executed by the parent dtsx package?

  • July 10, 2009 at 11:22 am

    #1023232

    I believe the storage location to be as much a function of preference as it is policy. In most cases I store them in the package store, I build structures (hierarchical directories) in the package store to seperate up different packages from another, unlike DTS where all the packages are in the same "directory", I might have a DataWarehouse directory and then Dim and Fact directories under that. The only limitation to storing packages in the package store is tied to Clustered SQL, this is the case where MS says to use the file system. You can run a package from the file system just the same as from the package store.

    Something to keep in mind about config files, you can have one per package, or multiples, or you can share them accross packages. When you share them though every object referenced in them MUST exist in the package and with the same name or you get an error. Example, package 1 has a config file that controls the connection string for 2 connections, we'll call them "Connection 1" and "Connection 2", package 2 wants to use that same connection information so when you change it you only have to change it once.. So Package 2 is configure to use that config file, but package 2 has the names "Connection1" and "Connection2", which don't match the config file, it would error on the config load. Now you have an easy fix, just rename them in Package 2 so that they match the names from Package 1, under the hood the names are not relavent and the references between connections and the components that use them in SSIS is done by GUID (at least the MS ones).

    The way I have done it is that I create a config file PER connection and ALL packages that use that connection have the same connection manager name. I had a 14 package BI ETL process that had 15 connections, each package used 1-7 of these connections, but never the same combination, now when a DB moves, we only adjust the config file for that DB and all 14 packages go to the right place..

    CEWII

  • July 10, 2009 at 1:36 pm

    #1023305

    Thanks for all of the info. I will experiment with the configuration files this coming week to see how it works.

  • July 13, 2009 at 6:05 pm

    #1024191

    Good luck, I'm sure it will work out for you..

    CEWII

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply