Idea Transcript
www.tobuku.com
RUN SSIS FROM VB6 Level:
April 2011 By : Feri Djuandi
√ Beginner
Intermediate
Expert
Platform : MS Visual Basic 6, MS SQL Server 2008
I was involved in a pretty big project to migrate SQL Server 2000 databases to SQL Server 2008. It was indeed an interesting and challenging work not only because there were gaps between those two versions that demanded special tricks and technique to transfer the objects; but also the art of managing the compatibility after the migration of the existing legacy applications which were developed using old technology like Visual Basic 6. We noticed, one of the issues was ensuring the DTS packages should be useable in SQL Server 2008 and reducing the manual conversion efforts as much as possible as we had a huge number of DTS packages (more than 1000!!) which almost impossible if we wanted to rewrite the script one by one into SQL Server Integration Services (SSIS) packages within a short period of time. Another challenge is to make possible the legacy Visual Basic 6.0 (VB6) applications to run the DTS package from SQL Server 2008. We’re talking about the 10 year technology gap. Most of the Application Programming Interfaces written for SQL Server 2008 are based on .NET framework which not possible for VB6 applications to use it. The story is becoming scarier, but don’t let the mountain beat you. If you are interested of how we faced it – just keep reading this article, you may find something very useful how to survive from this situation.
CONVERTING DTS PACKAGES INTO SSIS We notice that there are many differences between DTS and SSIS like architecture, features, programmability, systems interconnectivity and so forth. But we’re grateful that MS SQL Server 2008 still opens a room for the backward compatibility for the SQL Server 2000. The database engine allows the legacy DTS packages to be imported with no conversion requirement. Although the DTS packages are no longer able to be modified after imported, however it is enough. All we need is only to put the SQL Server 2000’s DTS packages into SQL Server 2008 and ready to run from there. Converting DTS packages is quite straight forward: export the packages from SQL Server 2000 and then import them into SQL Server 2008. The following steps show how to do that.
-1-
www.tobuku.com
Exporting DTS packages: 1. Open SQL Server Enterprise Manager and connect to the SQL Server 2000. 2. Open a DTS package, don’t do anything but select the Save As menu. Choose the Location as Structured Storage File. Determine the file name and the location in the computer where you want to store the exported package. Notice the extension of file name is DTS.
3. Close the DTS package. 4. Repeat above steps for the other packages one by one.
-2-
www.tobuku.com
Importing DTS packages: 1. Open SQL Server Management Studio and connect to the Database Engine of a SQL Server 2008. 2. Expand the nodes after you find Data Transformation Services. Right-click to open a pop-up menu, then select Import Package File.
Locate an exported DTS package earlier. 3. When the import is success, the DTS package will appear under the “Data Transformation Services” node.
4. Repeat those steps for the other the DTS packages. At this moment you cannot execute the package yet. To run the DTS package, first we must create a SSIS package that executes the DTS package because the DTS package cannot be run directly – it must be passed through the SSIS package.
-3-
www.tobuku.com
1. Open the SQL Server Business Intelligence Development Studio and create an Integration Services project. 2. In the package design window, open the Toolbox to add the Execute DTS 2000 Package Task.
3. Double-click on the task to open a property window. Enter the SQL Server 2008 instance name in the SQL Server column. Select the Storage Location as SQL Server. Select the imported DTS package from the Package Name column. See the picture in the next page for better understanding.
-4-
www.tobuku.com
4. Press OK when done. 5. Save and give a name for the SSIS package, in this example it is named as Package.dtsx. The package may be tested now by running it.
-5-
www.tobuku.com
The last step is to import the DTSX file that just created earlier into the SSIS server. 1. Open the SQL Server Management Studio and connect to the Integration Services of a SQL Server 2008. 2. Expand the tree until you find the Stored Packages. Right-click on MSDB and select the Import Package menu.
3. In the dialog window, select the Package Location as File System. Supply the Package path with the location of the DTSX file (in our example is the Package.dtsx file) in your computer. Finally specify the Package Name as you wish, it can be a different name from the DTSX file.
4. Press OK to save import the DTSX file.
-6-
www.tobuku.com
5. Try to test again the SSIS package by selecting the Run Package menu.
Repeat all those steps above for the other DTS packages. In the mean time, we will continue the explanation on how to run the SSIS package from the VB6 application.
-7-
www.tobuku.com
EXECUTING DTS PACKAGES FROM VB6 At its time VB6 was the hero in the large community of software developers around the world. The Component Object Model (COM) was the dominant platform used by the major software vendors and individual developers. Now the face of the world has changed. Gradually COM platform is left behind and now we have arrived in the new era of .NET framework. Microsoft equips the software developers with a full set of Software Development Kit (SDK) based on the .NET framework to develop applications for SQL Server 2008 including for the SSIS programming. Unfortunately there is no way for the old VB6 to utilize the SDK. So if the current VB6 applications are capable to run the SQL Server 2000’s DTS, after the packages are migrated to SQL Server 2008, the program can no longer do the same thing with the old way. The application must be modified to conform to the technology shift. Although VB6 does not mix with .NET components, luckily Microsoft provides a DLL that can be used to work with the SSIS packages. I suppose it is something called “COM interop wrapper” – I am not sure, but it doesn’t matter as far this component working fine with VB6. NOTE: There is an alternative option to run the SSIS package from VB6 using the DTEXEC command line utility. The program can be called using Shell() function. The utility is not discussed in this article. If you are interested with the topic, suggested to search the references that easily to find from various sources. The DLL file is available if MS SQL Server 2008 is installed on your computer, at least the Client tools. If the tool is installed correctly, the DTS.dll file should exist under the ..\Program Files\Microsoft SQL Server\100\DTS\Binn.
-8-
www.tobuku.com
Now let’s start to open Visual Studio 6.0 program to create a VB project. 1. Create a new Standard EXE program. 2. Open the Project | References menu and add the Microsoft DTS Runtime. This reference is equivalent with the DTS.dll explained earlier.
There are two functions that can be used to load a SSIS package stored in the server: LoadFromDtsServer LoadFromSQLServer Frankly speaking I don’t know exactly the differences of these functions, but from what I understand LoadFromDtsServer is used to run the SSIS package using Windows Authentication; while LoadFromSQLServer is to run the SSIS package using SQL Server Authentication. NOTE: There is the third function, LoadPackage to load a SSIS package directly from the DTSX file rather than the SSIS server. We will not discuss this function in this article.
-9-
www.tobuku.com
The syntax of the function is as follow: Public Function LoadFromDtsServer ( _ bstrPackagePath As String, _ bstrServerName As String, _ bLoadNeutral As Boolean, _ pEvents As IDTSEvents100 _ ) As IDTSPackage100 Parameters
Type
bstrPackagePath
String
bstrServerName
String
bLoadNeutral
Bool
pEvents
IDTSEvents100
Source: http://msdn.microsoft.com/en-us/library/bb500383.aspx Public Function LoadFromSQLServer ( _ bstrPackagePath As String, _ bstrServerName As String, _ bstrServerUserName As String, _ bstrServerPassword As String, _ bLoadNeutral As Boolean, _ pEvents As IDTSEvents100 _ ) As IDTSPackage100 Parameters
Type
bstrPackagePath
String
bstrServerName
String
bstrServerUserName
String
bstrServerPassword
String
bLoadNeutral
Bool
pEvents
IDTSEvents100
Source: http://msdn.microsoft.com/en-us/library/bb523394.aspx
- 10 -
www.tobuku.com
Applying those functions is quite straight forward. Please look into these example scripts, you will find it pretty easy to understand. Private Sub Command3_Click() Dim Dim Dim Dim Dim
pkg As New DTSLib.Package app As New DTSLib.Application pkgResults As DTSLib.DTSExecResult pEvents As DTSLib.IDTSEvents100 pkgContainer As DTSLib.IDTSContainer100 'SQL Server 2008
Set pkg = app.LoadFromDtsServer("\MSDB\Package 2000", "SQL2K8", True, pEvents) pkgResults = pkg.Execute() End Sub
Private Sub Command4_Click() Dim Dim Dim Dim Dim
pkg As New DTSLib.Package app As New DTSLib.Application pkgResults As DTSLib.DTSExecResult pEvents As DTSLib.IDTSEvents100 pkgContainer As DTSLib.IDTSContainer100 'SQL Server 2008
Set pkg = app.LoadFromSQLServer("Package 2000", "SQL2K8", "sa", "", True, pEvents) pkgResults = pkg.Execute() End Sub
You might want to add error handling routines in addition to the example script above so the program behaves more appropriately when errors occur.
- 11 -