Backup or transfer SSIS Packages

by Darren Green 3 Nov 2008 22:04

How can you backup your SSIS packages? I've been asked several times, and the answer is it depends.

Where do you store your packages?

SSIS Package Store

The SSIS package store is just a folder on disk, so regular file system backups should suffice, or you can backup that folder specifically. By default it is %ProgramFiles%\Microsoft SQL Server\90\DTS\Packages. It is possible that multiple folders can be used, or the default is changed. This can be explored further by reviewing the SSIS service configuration file %ProgramFiles%\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.ini.xml. For more information see Configuring the Integration Services Service. Restoration will depend on the capabilities of your file backup method and software.

SQL Server (MSDB)

The SQL Server store uses a table in the msdb database. For SQL Server 2005 this is dbo.sysdtspackages90, and for SQL Server 2008 this is dbo.sysssispackages. No extra work is required as msdb should already be included as part of your regular database backup routine. Regular backup and restore options apply but bear in mind msdb is a system database. Knowing the tables means we can manipulate the rows of data directly which offers some useful options such as moving rows via T-SQL, or other data access technologies including SSIS itself.

File System

This is perhaps the most common storage location used, and with good reason. It is easy to use and fits well with the code project style development paradigm we have with SSIS compared to traditional SQL Server object deployment and management. SSIS certainly fits better into this model, and we can use regular file system backups again. 

SSIS Backup Tool

For those of you that remember DTS I wrote a rather handy tool DTSBackup. Whilst it was widely used by myself and many others, you could get by quite happily without it, but it gave people comfort, and more importantly it gave people more control and certainly faster and more granular recovery options. With SSIS there was a major development process change, lead by the new tools, to a code project style of development. This means that the primary backup should be your source code repository along with your release management process. The change in development paradigm between DTS and SSIS meant I didn't see a strong need for a SSIS port of the tool. Whilst I haven't written anything as polished or accomplished for SSIS, I have put together a sample application. Whilst I don't see a need for backups this code is still a useful example of transferring packages as part of a deployment or maintenance process.

Here is one of the core methods implemented in the application. The application is a simple Windows Forms application and is aimed more at getting you started rather than be a polished application.

/// <summary>
/// Import all packages from a file system folder into a SQL Server (MSDB) store.
/// </summary>
/// <param name="folder">The source file system folder path.</param>
/// <param name="server">The target SQL Server name.</param>
/// <returns>The number of packages transferred.</returns>
public static int ImportToSqlServer(string folder, string server)
{
    // Validate parameters
    if (string.IsNullOrEmpty(folder))
    {
        throw new ArgumentNullException(folder);
    }
    if (string.IsNullOrEmpty(server))
    {
        throw new ArgumentNullException(server);
    }

    int counter = 0;
    Application application = new Application();

    // Get package files
    string[] files = Directory.GetFiles(folder, "*.dtsx");

    foreach(string file in files)
    {
        // Load and save package
        using (Package package = application.LoadPackage(file, null))
        {
            application.SaveToSqlServer(package, null, server, null, null);

            counter++;
        }
    }

    return counter;
}

The simple form allows you to import and export packages form SQL Server:

SSIS Backup Tool UI

Sample Code Project (8KB) SSISBackupSample.zip

Comments (2) -

4/7/2010 12:47:56 PM #

Salvatore Costantino

Have used your DTS tool works great.

I have not been able to get the SSIS tool to run.  What do I need and how do I run it?

Salvatore Costantino

4/8/2010 9:04:18 AM #

Darren

Salvatore, this is not really a full tool, certainly nothing as accomplished as DTS Backup (http://www.sqldts.com/dtsbackup) was. This is really just some sample code to illustrate the concepts, so you will need Visual Studio 2005 (or greater) to compile and the C# sample code.

Darren United Kingdom

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

RecentComments

Comment RSS
SQLIS | Row Count Plus Transformation

Row Count Plus Transformation

by Darren Green 5 Jun 2012 15:09

As the name suggests we have taken the current Row Count Transform that is provided by Microsoft in the Integration Services toolbox and we have recreated the functionality and extended upon it. There are two things about the current version that we thought could do with cleaning up

  1. Lack of a custom UI
  2. You have to type the variable name yourself

In the Row Count Plus Transformation we solve these issues for you.

Another thing we thought was missing is the ability to calculate the time taken between components in the pipeline. An example usage would be that you want to know how many rows flowed between Component A and Component B and how long it took. Again we have solved this issue.

Credit must go to Erik Veerman of Solid Quality Learning for the idea behind noting the duration. We were looking at one of his packages and saw that he was doing something very similar but he was using a Script Component as a transformation. Our philosophy is that if you have to write or Copy and Paste the same piece of code more than once then you should be thinking about a custom component and here it is.

The Row Count Plus Transformation populates variables with the values returned from;

  1. Counting the rows that have flowed through the path
  2. Returning the time in seconds between when it first saw a row come down this path and when it saw the final row.

It is possible to leave both these boxes blank and the component will still work.

 

All input columns are passed through the transformation unaltered, you are not permitted to change or add to the inputs or outputs of this component.

Optionally you can set the component to fire an event, which happens during the PostExecute phase of the execution. This can be useful to improve visibility of this information, such that it is captured in package logging, or can be used to drive workflow in the case of an error event.

Properties

Property Data Type Description
OutputRowCountVariable String The name of the variable into which the amount of row read will be passed (Optional).
OutputDurationVariable String The name of the variable into which the duration in seconds will be passed. (Optional).
EventType RowCountPlusTransform.EventType The type of event to fire during post execute, included in which are the row count and duration values.

RowCountPlusTransform.EventType Enumeration

Name Value Description
None 0 Do not fire any event.
Information 1 Fire an Information event.
Warning 2 Fire a Warning event.
Error 3 Fire an Error event.

Installation

The component is provided as an MSI file which you can download and run to install it. This simply places the files on disk in the correct locations and also installs the assemblies in the Global Assembly Cache as per Microsoft’s recommendations.

You may need to restart the SQL Server Integration Services service, as this caches information about what components are installed, as well as restarting any open instances of Business Intelligence Development Studio (BIDS) / Visual Studio that you may be using to build your SSIS packages.

For 2005/2008 Only - Finally you will have to add the transformation to the Visual Studio toolbox manually. Right-click the toolbox, and select Choose Items.... Select the SSIS Data Flow Items tab, and then check the Row Count Plus Transformation in the Choose Toolbox Items window. This process has been described in detail in the related FAQ entry for How do I install a task or transform component?

We recommend you follow best practice and apply the current Microsoft SQL Server Service pack to your SQL Server servers and workstations, and this component requires a minimum of SQL Server 2005 Service Pack 1.

Downloads

The Row Number Transformation is available for SQL Server 2005, SQL Server 2008 (includes R2) and SQL Server 2012. Please choose the version to match your SQL Server version, or you can install multiple versions and use them side by side if you have more than one version of SQL Server installed.

Row Count Plus Transformation for SQL Server 2005

Row Count Plus Transformation for SQL Server 2008

Row Count Plus Transformation for SQL Server 2012

Version History

SQL Server 2012

Version 3.0.0.6 - SQL Server 2012 release. Includes upgrade support for both 2005 and 2008 packages to 2012.
(5 Jun 2012)

SQL Server 2008

Version 2.0.0.5 - SQL Server 2008 release.
(15 Oct 2008)

SQL Server 2005

Version 1.1.0.43 - Bug fix for duration. For long running processes the duration second count may have been incorrect.
(8 Sep 2006)

Version 1.1.0.42 - SP1 Compatibility Testing. Added the ability to raise an event with the count and duration data for easier logging or workflow.
(18 Jun 2006)

Version 1.0.0.1 - SQL Server 2005 RTM. Made available as general public release.
(20 Mar 2006)

Screenshot

Row Count Plus Transformation Editor dialog

Troubleshooting

Make sure you have downloaded the version that matches your version of SQL Server. We offer separate downloads for SQL Server 2005, SQL Server 2008 and SQL Server 2012.

If you get an error when you try and use the component along the lines of The component could not be added to the Data Flow task. Please verify that this component is properly installed.  ... The data flow object "Konesans ..." is not installed correctly on this computer, this usually indicates that the internal cache of SSIS components needs to be updated. This is held by the SSIS service, so you need restart the the SQL Server Integration Services service. You can do this from the Services applet in Control Panel or Administrative Tools in Windows. You can also restart the computer if you prefer. You may also need to restart any current instances of Business Intelligence Development Studio (BIDS) / Visual Studio that you may be using to build your SSIS packages.

Once installation is complete you need to manually add the task to the toolbox before you will see it and to be able add it to packages - How do I install a task or transform component?

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

RecentComments

Comment RSS