Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

DTS to SSIS upgrades and the File System Task

  • 7 August 2013
  • Author: Kathi Kellenberger
  • Number of views: 6780
  • 0 Comments

The company I work for has a product called DTS xChange that converts DTS packages to SSIS. Even Microsoft recommends this product over the conversion wizard provided with SSIS.  I was fortunate enough to work on a DTS upgrade project last year shortly after joining Pragmatic Works so I got to see DTS xChange in action. I was impressed!

One task type in DTS that you should manually replace once the package is converted is ActiveX scripts. As long as you are working with pre-2012 SSIS, the ActiveX scripts will work, but to avoid hassles later, you should take care of them during your DTS upgrade. Luckily, most of the functionality that DTS developers have included in these scripts can be replaced with built in SSIS tasks. By the way, the DTS xChange documentation gives lots of advice on dealing with the ActiveX scripts. DTS didn’t have a way to do file system type tasks like archiving files, so many developers used the ActiveX scripts to perform that type of work.

Luckily, SSIS has the File System Task that can move, copy, rename, and delete files and directories. It is easy to use, but one aspect might trip you up. The File System Task Editor set for Move file is pictured below.

To configure this task for moving a file, you must set a SourceConnection that points to the file you want to move and DestinationConnection for where you want the file to end up. To keep things simple, I’ll use hard-coded paths here for my Connection Managers.

To set up the source connection, click the dropdown box and select to bring up the Connection Manager properties. Make sure that Existing file is chosen and Browse to the file. Click OK.

Now click the dropdown box next to the destination connection and select . But what Usage type to choose? Let’s try Create file since the file doesn’t exist in the archive folder at this point.

Unfortunately, when running the package, I get an error:

 

To fix this, make sure to use Existing folder as the Usage type.  When configuring the destination configuration manager, think about the destination. If the destination is a folder, make sure you specify that.

DTS xChange is a life saver when you need to convert hundreds of packages. Remember, that DTS is not supported at all in SQL Server 2012, so you need to get those packages converted now!

 

Print
Categories: SQL Server
Tags:
Rate this article:
No rating

Please login or register to post comments.