Automating SSIS Deployment

After we had the data warehouse built in our automated build, we needed a way to automatically deploy our SSIS packages and then call them.  You can use a tool called DTUtil to manipulate Integration Services on the server.  You can create and remove directores in msdb storage for SSIS packages, sign packages – and lots of other things.

I didn’t find many examples of using DTUtil so I’ll post an example.

Here is the command to deploy a package to the server.

dtutil /DestS [servername] /FILE [filename].dtsx /COPY SQL;[foldername]\[filename] /QUIET

If you will notice, you need to put .dtsx in the /FILE argument but not in the /COPY argument.  Also, it doesn’t matter what order the arguments are in so don’t worry about that.  In the /COPY argument the SQL means that I’m copying the package to the msdb on a SQL server.  The /QUIET means that it won’t complain if the package is already on the server – it will just overwrite it.

We were able to implement this right inside our NAnt script for deploying the SSIS packages.  Next we need to call the package right after deploy.  This might involve sp_cmdshell or calling a job out of a stored procedure.  We’ll see what works best.

Mark

About these ads

13 Responses to Automating SSIS Deployment

  1. Jeff Shaver says:

    Great stuff! I ran into issues when trying to automate the execution of SSIS packages. From what I can tell SSIS is no longer a re-distributable therefore requiring a full installation/licensed version and no easy way of remote execution (why not a web service interface like Reporting Services, Microsoft?). The current team I’m on discourages software installed on the build server, which is a good rule to follow. Another limitation we ran into was in our environment SSIS is installed alongside a SQL Server 2000 instance (we have not migrated our databases over to SQL Server 2005 yet). We had to come up with a flexible solution that would allow the execution of the packages using SQL Server 2000 tools to manage the scheduling, notifications, etc. We ended up using dtexec, operating system command option to automate the processing of our warehouse and cubes.

  2. […] The Furnace Mark Garner’s Business Intelligence Blog: Ideas, Musings and Thoughts « Automating SSIS Deployment […]

  3. Wayne Morrison says:

    Did you have any luck automating the deployment of SSIS scripts with config files?

    My tests have shown that it’s not possible with dtutil (see also http://www.databasejournal.com/features/mssql/article.php/3600201, last paragraph).

    Then dtsinstall cannot be ran in quiet mode (command line mode). Seems there’s no true way of producing a fully automated solution when config files are used.

  4. Maddy says:

    Hi ,

    I have 10 packages which should be executed in an order and should be automatically executed once in a day.

    can you suggest me any such process which will help me to achieve this

    Regards,
    Maddy

  5. Narayan Pavgi says:

    Wayne,
    I am having the exact problem with automating builds for SSIS Packages that rely on XML Configuration Files. Dtutil cannot handle them. I can think of two ways out: One is to deploy with the XML Config path to whatever exists, but specify the actual path on the DTExec (at execution time) using /Config (whatever) option. Second, use Indirect Configuration using Environment Variables. Add Env Variables to every database environment box that points to the correct path.

  6. Steve Barnes says:

    Hi All,

    I’ve been doing quite a bit of work in this arena recently and have run into the same frustration with the lack of versatility regarding the Package Installation Wizard
    – Can’t seem to alter the default path for File System deployment
    – Text box in UI is a read only control so I can’t even do copy ‘n paste magic
    – At times, I have hundreds of packages to deploy
    – It’s a butt-load of button clicking to get to a very simple end result.

    All of my deployment is to the file system and each SSIS package has it’s own XML configuration file. Using a file compare tool (WinMerge) I have analyzed side-by-side the output in the Deployment directory [in the SSIS project/solution folder structure] against the .dtsx and .dtsConfig files that are ultimately “copied” to the specified destination folder. Here’s what I have discovered that the Package Installation Wizard actually does behind the scenes:

    Config File (XML)
    1. The Package Installation Wizard copies this file to the destination folder.
    2. The only modification it does to the config file is strip off the XML declaration tag from the top line (i.e. ). The rest of the config file is copied verbatim. NOTE: It does add one piece of white space between a particular tag attribute and the closing “/”. This is, of course, insignificant to an XML parser.

    DTSX File (XML)
    1. The Package Installation Wizard copies this file to the destination folder.
    2. The only modifications it does is:
    a. Generates a new VersionGUID which is an attribute to one of the DTS:Property elements within the topmost DTS:Executable root node.

    b. Modifes the ConfiguationString inner text value which is an attribute in one of the DTS:Property elements within the DTS:Configuration element.

    Thhhat’s all folks. No other modifications are performed. I would have to experiment to make sure the modifed VersionGUID was not of great significance at runtime … but I am half tempted to write my own utility that does these things … in a batch! With command line args!

    Any comments?

    P.S. I know, I know … SSIS is one finicky bugger. BUT, I’ve already developed a code generator that digs inside the internals of an SSIS package and modifes the XML to my needs. Yes, the resultant packages run just fine and can be opened in BIDS just fine.

  7. Arnstein Berg says:

    If you only want to change the path to dtsConfig from within your packages, you can do it like this:

    XmlDocument package = new XmlDocument();
    package.Load(filePath);
    XmlNamespaceManager ns = new XmlNamespaceManager(package.NameTable);
    ns.AddNamespace(“DTS”, “www.microsoft.com/SqlServer/Dts”);

    node = package.SelectSingleNode(@”/DTS:Executable/DTS:Configuration[DTS:Property/@DTS:Name=’ConfigurationType’ and DTS:Property/text()=’1′]/DTS:Property[@DTS:Name=’ConfigurationString’]”, ns);
    if (node != null)
    {
    node.FirstChild.Value = ConfigFilePath;
    package.Save(filePath);
    }

    This is generally enough if you have already made an installer that puts the files in the right destination and updates dtsConfig with the right values. Just put the above code in an System.Configuration.Install.Installer and call it as a custom action from any VS setup project.

  8. Shashi says:

    Hi Arnstein,

    I tried to use your script for changing the config file path.
    However after saving the package i cannot run it.
    It gives an error “Package failed to Load due to error. CPackage::LoadFromXML fails.”

    Have i missed something here.
    Any help will be greatly appreciated.

    thanks
    Shashi

  9. Miko says:

    set on the i.e. 3rd line:

    package.PreserveWhitespace = true;

    by default xml ignores whitespaces needed by SSIS editor. That is why LoadFromXML fails here.

  10. Tanushree says:

    Hi,
    We need to use dtutil.exe to remotely deploy packages to sql 2008 as well as 2005
    Say, I have a server A, that can be sql server 2k8 or 2k5, with SSIS installed on it.
    I have another server B from which i need to push packages to this server A.

    For this we want to use dtUtil.exe.
    Now, in sql 2005 we could just install the redistributable SMO on server B and use dtUtil.exe.
    In sql 2008 do we need to install SSIS on server B or does it have a similar redistributable which has dtUtil.exe?
    We want to avoid asking customers to buy an extra license.

    Another way would be to use the DTS.Runtime assembly to code the deployment.

    Again, do we need SSIS installed on server B, if I need to use this runtime there?
    Is it available in any redistributable for free :-)?

    Regards,
    Tanu

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

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: