MS SQL Server 2000 Data Transformation Services Deployment

server| Data | conversion
1. Target

MS SQL Server for large data transmission, most of us will use the data transmission services. Now suppose, in the development environment, we've designed the DTS package and it's working well, and the next thing we're going to do is migrate and deploy this DTS packet. So, we need to save the DTS package in the design environment into a structured storage file, and the file is imported into the MS SQL Server in the target environment, and finally the job is added to allow MS SQL Server angent to perform the data transfer work in the way we expect it to do this DTS package. One problem that needs to be addressed is that in the target environment, the source and destination of the DTS transfer will change, and it needs to be configured.

2. The solution

2.1. DTSRun

DTSRun is a Microsoft-supplied command to execute DTS packages (including structured storage, stored in SQL Server, or packages stored in Meta Data services).

Usage of dtsrun:
DTSRun
[/?]|
[
[
/[~]s Server_name[\instance_name]
{{/[~]u user_name [/[~]p password]} |/e}
]
{
{/[~]n Package_name}
| {/[~]g package_guid_string}
| {/[~]v package_version_guid_string}
}
[/[~]m Package_password]
[/[~]f filename]
[/[~]r Repository_database_name]
[/a Global_variable_name:typeid=value]
[/L Log_file_name]
[/w Nt_event_log_completion_status]
[/z] [/! X] [/! D] [/! Y] [/! C
]
Refer to Microsoft's data for specific usage. It is important to note here that through the "/a global_variable_name:typeid=value" option, we can pass multiple custom parameters to the DTS package, in the context of the DTS package deployment, we use this option to tell the DTS package server name, user name, Password and other database connection information. Through "/! Y option to obtain the encrypted dtsrun parameter.



2.2. Re-design DTS package

To process dtsrun incoming custom parameters, the DTS package needs to be redesigned. We can add an ActiveX Script task, use VB Script or Java Script for DTS programming in an ActiveX Script task, and define the process to set the ActiveX Script task to one of the first tasks. Because the DTS COM object thread pattern is inconsistent with the ActiveX Script Task host, you may have to set the ActiveX Script task Workflow property to execute on the main package thread, otherwise the call error might occur.

The following example is a script in an ActiveX Script task. The DTS package in the example contains a database connection object named "DbConnection."



' ************************************************************************ ' Visual Basic ActiveX Script ' *********** Function Main () Dim sdbdatasource Dim Sdbcatalog Dim Sdbuserid Dim sDBPassword Dim bdbusetrusted Dim Solapserver Dim Solapcatalog Dim Opackage Dim Oconnection Dim OTask Dim OC Ustomtask ' gets dtsrun incoming custom parameter Sdbdatasource = DTSGlobalVariables ("Dbdatasource"). Value Sdbcatalog = DTSGlobalVariables ("Dbcatalog"). Value Sdbuserid = DTSGlobalVariables ("Dbuserid"). Value sDBPassword = DTSGlobalVariables ("Dbpassword"). Value bdbusetrusted = DTSGlobalVariables ("dbusetrusted"). Value solapserver = DTSGlobalVariables ("Olapserver"). Value Solapcatalog = DTSGlobalVariables ("Olapcatalog"). Value ' Gets the technique of the current DTS package object Set opackage = DTSGlobalVariables.parent ' Gets the data connection object in the package set oconnection = Opackage.connections ("Dbcon Nection ")" Configure data connection information for the data source object If bdbusetrusted Then oconnection.usetrustedconnection = bdbusetrusted Else Oconnection.userid = SdbuseRID Oconnection.password = sDBPassword end If Oconnection.datasource = Sdbdatasource Oconnection.catalog = SDBCatalog Set oconnection = Nothing ' configuration with much information, here is OLAP Analysis Service Processing task Set otask = Opackage.tasks ("Dtstask_dtsolapprocess.certificate") set Ocustomtask = Otask.customtask Set otask = Nothing Ocustomtask.properties ("Treekey"). Value = solapserver & "\" _ & Solapcatalog & "\cubefolder\certificate" Set ocustomtask = Nothing ' returns success status Main = DTSTaskExecResult_SuccessEnd Function



2.3. Save as a structured storage file

This process is fairly simple and can be accomplished through Enterprise Manager. The Save as a structured storage file is the DTS package we want to distribute.

2.4. Import to MS SQL Server

We need to implement this process through DTS programming. It should be emphasized that the following example needs to be invoked correctly in the ApartmentState STA thread.

public void Go () {DTS. Package2class pkg = new DTS. Package2class (); Dts. Application app = new DTS. ApplicationClass (); Deletes a DTS package try {Pkg that already exists from SQL Server. RemoveFromSQLServer (DBServer, Dbsuserid, Dbspassword, DTS. Dtssqlserverstorageflags.dtssqlstgflag_default, "", "", pkgname); Catch {}//Get package information for the DTS package (structured storage file) to be distributed, which assumes that only one version of DTS is included in the package. SavedPackageInfos infos = pkg. Getsavedpackageinfos (Uncfile); Dts. SavedPackageInfo info = infos. Item (1); Object obj1 = null; Object obj2 = null; String spkgid = info. PackageID; String Sverid = info. VersionID; String spkgname = info. PackageName; Load the structured storage file pkg. LoadFromStorageFile (Uncfile, Pkgpwd, Spkgid, Sverid, Spkgname, ref obj1); Save to PKG in SQL Server. SaveToSQLServerAs (Pkgname, DBServer, Dbsuserid, Dbspassword, DTS. Dtssqlserverstorageflags.dtssqlstgflag_default, Pkgownerpwd, Pkgoperatorpwd, "", ref obj2, FALSE); Pkg. Uninitialize (); Check that successful DTS is saved. PackageSQLServer pkgsqlserver = App. GetPackageSQLServer (DBServer, Dbsuserid, Dbspassword, DTS. DtssqlservErstorageflags.dtssqlstgflag_default); Dts. PackageInfos INFs = Pkgsqlserver.enumpackageinfos (Pkgname, True, ""); if (INFs. EOF) throw new Exception ("DTS package import to database failed.") "); Dts. PackageInfo inf = INFs. Next (); Mpkgid = INF. PackageID; Mverid = INF. VersionID;}

2.5. Add Job

Adding jobs can be done in a traditional way, with SQL statements, without detailed instructions. MS SQL Server provides a range of stored procedures to modify jobs to achieve the user's expected results.

3. Summary

The article has reached our goal in this. Overall, the implementation of the 2.2 step is difficult, there are many difficulties in the implementation process, there are some of the answers in the newsgroup, and other steps to see MSDN can be easier to resolve.