This article describes how to open the DTS Designer for SQL Server 2000 programmatically. It eliminates the need to open the SQL Server Enterprise Manager if you just need to edit a DTS that was saved as a structured storage file. In this article I'm building a command line utility in VB.NET that takes the full path to a dts package as a parameter and opens it in the designer. You can find its VB.NET code here.
I recently started working on a project involving the creation and modification of DTS packages that were scattered around on many different systems and file shares. What really annoyed me was the fact that you are forced to go via the SQL Server Enterprise Manager and connect to a random SQL Server, even if you wanted to open a DTS package that was saved to the file system as a structured storage file.
After some searching on the internet for a DTS API, I came across the MSDN article on Creating DTS Packages in Visual Basic. No explanation however was given on how to open the designer programmatically although the library (dtspck.dll) included an object called CDTSLegacyDesigner, which clearly was the object I was after.
The following VB.NET code shows how to load the designer with a empty design surface. You will have to add a reference to the COM component dtspck.dll first, which is installed together with the SQL Server 2000 Client Tools (such as Enterprise Manager, Query Analyzer...)
Try designer = New DTS.CDTSLegacyDesigner designer.Initialize() designer.ShowDesigner() Finally If Not designer Is Nothing Then designer.Dispose() Marshal.ReleaseComObject(designer) designer = Nothing End If End Try
DTS packages are saved as a structured storage file. A single file can contain multiple DTS packages and multiple package versions. The following simplified code shows how to retrieve the necessary information about last package version stored in the DTS. You should loop over the packageInfoColl collection and allow users to select the version they wish to open.
Dim sPackageName As String Dim sPackageID As String Dim sPackageVersionID As String Dim sPackagePathName As String 'Full pathname to DTS package Try Dim package As DTS.Package = Nothing Dim packageInfoColl As DTS.SavedPackageInfos = Nothing Dim packageInfo As DTS.SavedPackageInfo = Nothing package = New DTS.Package packageInfoColl = package.GetSavedPackageInfos(sPackagePathName) '** Example: select the last saved version packageInfo = packageInfoColl.Item(packageInfoColl.Count) sPackageName = packageInfo.PackageName sPackageID = packageInfo.PackageID sPackageVersionID = packageInfo.VersionID Finally If Not package Is Nothing Then Marshal.ReleaseComObject(package) If Not packageInfo Is Nothing Then Marshal.ReleaseComObject(packageInfo) If Not packageInfoColl Is Nothing Then Marshal.ReleaseComObject(packageInfoColl) End If End Try
When you have retrieved the packageName, packageID and packageVersion you can pass these as properties to your instance of the CDTSLegacyDesigner class as shown in the following code. The location property indicates that the DTS package should be loaded from the file system.
Try designer = New DTS.CDTSLegacyDesigner With designer .Initialize() .Location = 2 .PackageID = packageID .PackageName = packageName .ServerName = packagePathName .VersionID = packageVersionID End With '** Launch designer designer.ShowDesigner() Finally If Not designer Is Nothing Then designer.Dispose() Marshal.ReleaseComObject(designer) designer = Nothing End If End Try
Putting these code snippets together into a function application still takes some work but you can find my try on my googlepage.