SQLChicken.com

SQL Server DBA Tips & Tricks

By

Pragmatic Tools: DTSxChange (Or DTS Must Die!)

Ah DTS packages…they suck. Seriously, it’s 2011. It’s been over a decade and we still have SQL 2000 and DTS packages lingering around our shops…sitting there…taunting us…asking to be upgraded but HOW? There’s SO much work having to learn SSIS, figure out what the current DTS package even does, figure out what shiny new components in SSIS are equivalent to that legacy crap we created a decade ago and recreate it on the new platform! Well fret not, we at Pragmatic Works got you covered with a nifty tool called DTSxChange that will actually convert your DTS packages to SSIS for you!

DTSxChange is a powerful tool that converts your legacy DTS packages to SSIS (can convert to SQL Server 2005 or 2008 SSIS) for you with minimal effort. In addition to saving you a ton of work, the tool actually tells you how much time (and money) you’re saving using this tool over a manual effort via the DTSxChange Profiler feature. This tool allows you to scan all of your current packages and it generates a really nice report (read also: bosses/managers/CFOs love this)

DTS sucks
Ooh shiny!

So now that you know the scope of the work you’re looking at, let’s get to the nitty gritty and start converting! The conversion portion of the software uses an easy step-by-step navigation that lets you customize all sorts of options for your conversion efforts such as destinations, checkpointing, logging options, auditing framework options, connection manager consolidation, converting child packages automatically associated with a package, etc. The nice thing about this is you can convert single packages or hundreds of packages at the same time! If you want to see some of this stuff in action you can watch the video here.

We converted the stuff and now we’re done right? NO WAY! With DTSxChange you also have the option of adding on our custom Auditing Framework which gives you tons of useful reports (Note: Use Report Viewer Application to view auditing data) so that you can easily and quickly view the state of your SSIS packages once their deployed. Here’s a listing of all of the different reports you get:

  • Which packages are currently running and which task is running inside the package
  • Historical package execution detail for selected date range (i.e. Run time, Errors, Warnings etc.)
  • Error and Warning by Task and Package
  • Run time by Task and Package
  • Extracted and Loaded Records along with their source and target information (e.g. Table/View, Sql Query, File Name, Component Name, Data Flow Name, Connection String etc.).
  • Run time Trend for several days/weeks/years by Package and Task
  • Error/Warning/ Trend by Package and Task
  • Extract/Load Trend by Package, DataFlow
  • Extract/Load Trend by Data Object (e.g. File, Table/View or Sql Query)

There are seven inbuilt reports are shipped this version and more will be added in the future release.

Report Name Description
SSIS Execution Dashboard This dashboard gives one place view of most common counters of SSIS package execution.
Package Execution Trend This report provides many useful information about trend of package runtime, task runtime and errors/warnings.
Errors/Warnings Provides detailed error/warning report including package level errors (package level errors not listed in any other report)
Recent Executions Provides information of running packages/tasks and already completed executions.
Running Packages Provides real time information of currently running packages and tasks.
Extract/Load Detail Provides extract/load detail (e.g. row count, source/target information, query, file name …) about each dataflow in each package execution.
Extract/Load Trend Provides graphical view of extract and load trend over several years, weeks, months and days.

DTSxChange helps you migrate away from those legacy DTS packages and gets you on to the latest SSIS platform in minutes as opposed to days or weeks! Don’t believe me, try it out yourself. When you download a trial version of our product it comes with 3 conversions for you to play with. Would you rather have one of the experts at Pragmatic Works do a live demo for you and your company? We can set that up as well for you by sending us a quick email. So what are you waiting for? Kiss DTS goodbye today!

Share
  • http://five.truthandignorance.com/ Hue Mullick

    If only more people today would put out ideas similar to this. Very beneficial.