Posts Tagged ‘DTS’

How to Edit DTS Packages in SQL Server 2008 R2

I’m on the verge of migrating a few DTS packages from a legacy application, there’s no point in re-writing them since the application will be phased out within the next 6-12 months so I figure that if I can migrate them ‘as is’ I’ll save myself some time and a few headaches.  SQL Server Management Studio (SSMS) provides a menu option under Management to Open, Import or Migrate DTS packages…

Initially if you try to use these features this you may receive the error:

“SQL Server 2000 DTS Designer Components are required to edit DTS Packages.  Install the special Web download, “SQL Server 2000 DTS Designer Components” to use this feature.  (Microsoft.SqlServer.DtsObjectExplorerUI)”

To get around this you’ll need to follow a couple of steps:

1. Install the Microsoft SQL Server 2005 Backward Compatibility Components – you can get these as part of the Microsoft SQL Server 2008 Feature Pack (for some reason it’s not included in the 2008 R2 Feature Pack).  [x86] [x64] [ia64].
2. Install the Microsoft SQL Server 2000 DTS Designer Components – since DTS support was depracated in SQL Server 2008 you have to get these from the Microsoft SQL Server 2005 Feature Pack.  [SQLServer2005_DTS.msi]
3. Ensure that the PATH Environment Variable lists the SQL 2000 directory before the 2008 R2 one – I have to thank Ramoji Ryali’s Fundas for this solution, the correct version of sqlgui.dll will be used as long as:”C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\”
   is before:
   “C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\”

After that, everything seemed to work fine – I did happen to log off and log back on again at some point so I’m not 100% sure if that was required or whether it was just coincidence – I definitely did not have to reboot though.

SQLBits V: Highlights from Friday Afternoon

Having attended the SQL Bits conference in Newport last month I decided to collate the little tips and tricks I learned at the event, in the challenging post-lunch session on Friday Thomas Kejser gave a talk on Designing I/O Systems for SQL Server – my notes from this talk are either short, sharp facts or references to thing I plan to research further…

The most important metrics are Throughput (measured in MB/sec or IOPS) and Latency (milliseconds).

A 10K RPM disk will give 100-130 IOPS, a 15K RPM disk will give 150-180 IOPS.

Performance can be increased by short-stroking disks, if you build a single partition on a drive using only 10% of it’s capacity this partition will reside physically on the outer edge of the disk.  Doing this reduces the amount of head movement required to read the data and improves performance.

Windows Server 2008 handles disk alignment automatically but for earlier versions the  offset should be 1024kb.

For testing the effect of configurations on performance use SQLIO.EXE or IOMeter, for stability testing use SQLIOSIM.

Check out the Pre-Deployment Best Practices Whitepaper from Microsoft.

The last talk of the day was by Allan Mitchell and was initially titled Common Integration Services Problems but was changed late-on to a more introductory Solving Problems in ETL using SSIS.  The only take-away I got from the talk was to check out a tool called BIDS Helper, I have no criticism of Allan though – he’s a good speaker but in this case I already understood the bulk of the content so it was hard to stay focused.