INF: DTS ????? ??????????, ????????? ?? ????????

???? ?????? ???? ??????
???? ID: 242391 - ?? ???????? ?? ?????? ??? ?? ?? ???? ???? ???? ??.
??? ?? ??????? ???? | ??? ?? ??????? ????

?? ????? ??

??????

?? ???? ?? ???????? ????? ?? SQL Server 7.0 ???? ??????????????? ?????? (DTS) ?????? ?? ????????? ???? ??? ???? ?????? ???? ?? ??? ??? ??????? ??????? ?????? ????? ?? ??? ??? ????? ???????? improving ?? ??? hints ?? ?????? ???? ?? ????

SQL Server 2000 ??? ?? ???? ?? ??????? ?? ??? SQL Server ???????? ?????? ??????

???? ???????

???? ??????? ??????????? ?????

??? DTS ????? ?? ???? ??? self ??? ???? ??? ??????-???, UDLs ?? ODBC DSNs ??? ?? ??????? ??????? ??? ??????? ??? ?????? ??? ??? ??????? ??????? ?? ???? ??? ??? ?? ODBC DSN ?? ??? OLEDB UDL ?????? ?? ????? ?? ???, ?? ???????? ????? ?? ??? ?????? ???? ??? ??? ?? ????? ??? ??????? ???? ?? ??? ?? ????? ?? ??????? ?? ???????? ???? ?? ?????? ???? ??? ?? ??? ???? ?? ??? ?????????? ???

?? ?? SQL Server ???? ??????? ?? ?????, ????? (???????) ????? ??? ?? ??? ???? ????? ?? ??? ??? ?? ???? ?? ??? ????? ?? ????? ?? ??? ????? ??????? ????? ?? ??? ????? ???????? ??? ?? ?? ?? ?? ??? ?? approach ???? ?? ??? ?? caveat ?? ?? ?? ?????? ?? ?????? ?? ????? ?? ????? SQL ????? ???????? ?? ??????? ???? ?? ???? ???

?? ?? ????? ?? ???? ??? ActiveX ????????? ????? ?? ???? ????????? ??? ??-???? ??? ????? ??? ?? ??????? ???? ?? ???? ????? ?? ???? ?? ????? ?? ??????? ???? ?? ??? ???? pump ????? precedes ActiveX ????????? ????? ????? ?? ??? ???? ?? ????? ?? ??????? ????? ????? ?????? DTSGlobalVariables.Parent ???? ?? ??? ??? ????? ??????? ????? ???? ?? ??? ???? ??? ??? ????? ????? ???, ?????? ?? ?? ?? ???????????? ??????? ?????? ???

????? ??? ?? SQL ???????? ?????? ????, "ActiveX ????????? ?????", ?? ??? ActiveX ????????? ?? ??? ???? ????? ??????? ??? ????????? ???? ?? ??? ???? ?? ?????? ??:
   Function main()
   dim oPackage
   dim oConn

     'Get package object
     set oPackage = DTSGlobalVariables.parent
     'Get a handle to the desired connection.
     set oConn = oPackage.connections(1)
     'Modify the datasource.
     oConn.datasource = "newServerName"

     'Release object handles.
     set oPackage = nothing
     set oConn = nothing

    Main = DTSTaskExecResult_Success
End function
				
??? ?? ???? ???????? ????? ??? ?? ?? ???? ?? ???? ??? ?? ???? ??????, ????? ????????? ?? ??? ????? ?? ??????? ???? ?? ???? ????? ???? ?? ?? ????? ?? ??????? ?? ????? ?? ????? ?????

????? ?? ??? ???? ????????? ?? ??? ???? ?? ??? ????? ?? ??????? ???? ?? ???, DTS ???????? ?? DTS ??????? ?? ????? ???? ??? ???????? ????? ????? ?? ??? ??????? Visual Basic ????????? ?? ?? ?? ????? ?? ??? ???? ?? ?????? ??? ??????? ???? ??, ?? ?????? ?? ????? ?? ??? ????? ???????? ?? Execute ?????? ?? ??? ??????

????? ?? ??? ??? ????? ?? ??????? ?? ??????? ???? ?? ??? ??? ?????????, DTS ???????? ?? DTS ??????? ?? ????? ???? ??? ?????????? ????? ????? ?? ??? ??????? SQL ????? ?? ??????? ????? ??? ?? ??? ??? ???????? ????? ?? ??? ????? Microsoft ???????? ???? ??? ScriptPkg ?????? ?? ????? ????:
239454INF: ScriptPkg ????? ????? DTS ??? ????? ???? ?? ??? ???????? ???? ??? DTS ?????????
?? ???????? ?? ??? ??? ?? ?????? ?? ????? ??, ??? ????? ?? ?????????? ?? ????? ?? ?????? ????? ???? ?? ??? ???? ??? ??????

????? ??? ?? ????? ?? ????? ?????????, ?? ??? Visual Basic ????????? ???? ?? ???????? Microsoft DTSPackage ???????? ????????? ?? ??? ??? ?????? ???

??????????: ?? ??? ????????? Visual Basic ?? monitoring ????? ?? ????? ?????? ??? ???? ?? ??? ?? ??? ????? ????????? ?? ??? ??, ??? ????? ?? ????? ??? ????? ????? ?? ????????? ???? ?? ??? ???????? ???? ?????? ?? ?????? simultaneous ????? ???? ?? ??? ???? ?????? ?? ??? ?? ???????? ?? ?? ????? ???? Visual Basic ??? ??? ?? ???? ???

??????? ?? ?? ??? ????? ?? ????????

???? ??????? ?? ActiveX ????????? ?????, ???????? ????????? ?? ActiveX transformations ?? ?????? ?? ???? ???? ?? SQL ??? ?? ??????? ????? ??? ???? ?? ????? ???? ?? ???? ?? ???? ??? ??? ??, ???? SQL ??? ?? ??????? ??? ?? ???? ?? ?? ??????? ?? ??????? ???? ?? ??? ??? ActiveX ????????? ????? ????? ???? ?? ????? ???? ActiveX ????????? ????? ??????? ?? 'booktype' ?? ??? ?? ?????? ???? pump ????? ?? ??? SQL ??? ?? ????????? ???? ?? ??? ????? ???? ?? ???? ?? ?? ?? ?????? ??:
Function Main()
   dim oPackage
   dim oPump

        'Get a handle to the Package object.
	set oPackage = DTSGlobalVariables.Parent
	'Get a handle to the desired Datapump Task.
	set oPump = oPackage.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask   
	'Alter the datapump SQL statement.
	oPump.SourceSQLStatement = "select * from pubs..titles WHERE type _
  = ''" + DTSGlobalVariables("booktype") + "''"
	Main = DTSTaskExecResult_Success
End Function
				
???:: ???? ????? ?? ????? DTS designer ??? ????????? ???? ???? ??, ????? ??? ???? ?? ActiveX ????????? ??? ??? ????? ?????? ?? ??? ????? ???? ?? ???? ?? ????? ????? ?? ?? ActiveX ????????? ?? ?????? ?? ??? ????? ?? ??? ?? ????? ???? ???? ?????? ??? ??? ?? ??? ?? ???????? ????? ?? ????? ??? ?? ??? ??????? ???? ???? ?? ???? ????? ??? ?? ??? ?? ?? ???? '???' '?????' ?? ???? ??? ??, ?? ??? ?? ???? ??? ?????? ?? ???, DTSStep_DTSDataPumpTask_1 DTSTask_DTSDataPumpTask_1 ?? ???? ???

??? DTS ????? ???? ?? ??? passing ????????

DTSRun ????????? ???????? ??? ??? ????? ???? ?? ??? ???? ?? ???? ??? ?? ???? ?? ????? ???? ???????? ??????? ???? ????? ??-???? ??? ????? ???? ?? ??? ??????? ???, ?? ??? ???????? ?????? ???? ????? ?? ????? ?? ????????? ?????? ???? ??????? ?????? ?? ?????? ???

???:: ??? SQL Server 2000 ????? ?? ???? SQL Server 2000 ?? ??? ??????? SQL Server 7.0 ??????? ???????? ?? ??? ??? ??????? ?? ?? ???? ????? ?? ??????? ??, DTSRun ????????? ???? ?????? ???????? ??????? ?? ???? ??? /A ??? ??????? ?? ????????? ???? SQL Server 7.0 DTS ????? ??? ??? ????? ???? ?? ??? ???? ?? ????

???? ?? ??????? ??????? ??? ???? ?? ??? ?? ??? ????? ??? ?? ???? ?????? ?? ???? ActiveX ????????? ????? ???? ?? ?? ?????? ??? ?? ???? ?? "???????? ????? ?? ??? ??????? ??" ??? ??? ?????? ?????? ????? ??????? ?? ??????? ???? ?? ??? ?? ??????? ?? ?? ????? ?? ???? ?? ????
Function main()
  Dim fso	'This will serve as a handle to a file system object.
  Dim ofile	'Handle for accessing a file.

    'Get a file system object for manipulation files.
    Set fso = CreateObject("Scripting.FileSystemObject")

    'Open the text file.
    Set ofile = fso.OpenTextFile("c:\test.txt")

    'Read line from the file into the global variable.
    DTSGlobalVariables("myGlobalVar").Value = ofile.ReadLine

    'Close the "parameter" file.
    ofile.Close

    Main = DTSTaskExecResult_Success
End function
				
????? ?????? ?? ?? SQL Server ?????? ?????????? ?? ???? ?? ??? ??? ActiveX ????????? ????? ?? ????? ???? ?? ??? ???? ?????? ??? ??? my_param_table paramvalue ????? ?? ??? ?? ???? ?? ?? ??? ?? ??????? ??????? ??? ???? ?? ??? ????? ???? ??? ?? ?????? SQL ????? ?? ??? ???????? ???? ?? ??? SQL ?????? ??????? ???????? (DMO) ?? ????? ???? ??, ????? ?? ActiveX ???? ???????? (ADO) ?? ???? ???? ????? ???? ?? ????? ?? ?? ?? thing ???? ?? ???? ????
Function main()
Dim oServer 		'DMO Server object.
Dim oResult		'Result set.
Dim sParamValue

	'Create a SQLDMO server object.
	Set oServer =  CreateObject("SQLDMO.SQLServer")

	' Make a connection to the local server.
	oServer.Connect ".", "sa"

	'Select the desired row from the table.
	set oResult = oServer.Databases("pubs").ExecuteWithResults_
("select  paramvalue from  my_param_table")

	'Retrieve the first row, first column from the results.
	sParamValue = oResult.GetColumnString (1,1)

	'Set the global variable.
	DTSGlobalVariables("MyGlobalVar").Value = sParamValue

	Main = DTSTaskExecResult_Success
End function
				

Writing Custom DTS Tasks in Visual Basic

It is possible to write custom DTS tasks in Visual Basic. An example of a Custom Task in Visual Basic without a UI is available in the \Devtools\Samples\DTS directory on the SQL Server CD-ROM. Following are several common issues encountered when dealing with custom Visual Basic tasks in DTS:
  1. Custom tasks written in Visual Basic that implement an interface like DTS.CustomTaskUI MUST implement every event in the interface or they will cause an Access Violation.
  2. If you are developing a Visual Basic custom task with a UI, it is recommended that you apply SQL Server Service Pack 1 (SP1). SP1 incorporates several enhancements to ease UI development.
  3. Visual Basic Tasks are apartment threaded, while DTS is free threaded. In order to execute a task written in Visual Basic, it must be executed on the main thread or it will cause an Access Violation. DTS sets the appropriate flag by default. The user should not change this flag.

Obtaining Information on How to Code DTS Packages

In SQL 7.0, there are several samples on the CD-ROM under the \Devtools\Damples\DTS folder. These include a sample Custom Task (Visual Basic), and a Custom Transform (C++). Some sample packages are also in the self extracting executable DTSDemo.exe.

The most versatile example is ScriptPkg, which is part of Dtsdemo.exe. ScriptPkg contains the Visual Basic source code that converts a package saved to the local server into Visual Basic code. This example is best used for:
  1. Producing Visual Basic examples by scripting an existing DTS package.
  2. Showing how to code the DTS flatfile driver, which is not documented in SQL Books Online.
  3. Converting a package into text so it can be added to and tracked in SourceSafe.
The following are several known issues with the ScriptPkg example:
  1. Lookups for a data pump that are not scripted correctly.
  2. If the package is too large then the text generated cannot be treated as one function in Visual Basic due to a limitation on the number of lines in one function. You'll have to break the script file into multiple functions on your own to get Visual Basic to compile the code.

Enhancing DTS Package Performance

Using Parallel Loads

DTS is capable of running multiple tasks in parallel. However, each connection in a DTS package can only handle one thread at a time. That means that the following data pumps in a package are all serialized:

A -> B and A -> B
A -> B and A -> C
A -> B and C -> B

Whether or not you use the same or different icons for the connection makes no difference.

The only way to get parallel execution of tasks is to create multiple connections to the same database with different names. For example A -> B and C -> D are executed in parallel even if B and D are different connections to the same server and database.

Precedence constraints can be used to enforce task execution order and prevent parallelism for selected tasks as necessary.

Insert Commit Size

The most important parameter on the data pump task is theInsert Commit Size. ?? ??? ??????? ???? ?? ?? ?????? SQL ????? ?? ??? ?? ????????? ???? ?? ????? ????????? ?? ???? ?????? ????????? ?? ???? ???? ?? ???? ???????? ???? ???????? ??? ?? ?? 0; ??? ????????? ?? ?? ??? ????????? ?? ??? ???? ?? ?? safest ?????? ??????? ??? ?? ?????? ?? SQL Server rolls ???? ??? ???????? ??? ???????, ?? ?? ???? ??? ?? ???? ???? ????????? ??? ?? ???????? ???? ??, ???? ?? ??? ???? ???? ???????? ??? ?? ??? ????? ???? ?? ??????? ?? counter ???? ?? ???? ?? ???? ??? ??? ?? ??? ??? ??? ??? ?????? ?? ???? ???? ???? ?? ??? ???? ??? ???????? ?? noticeable ????? commit ?????? ???????? ?? ??? ??? ???? ?? ?? ???? ????????? ???? 0 Commit ???? ?? ???? ??, ?? ????? ???? ????? ??? ??? 10,000, ????? ?? ?? 1000 ?? 1 ??? ???? ?? ???? 1 ?????? ???? ?? ??????? ???? 1 ?????? ?? ??? ?? ?????? ???? rolled ?? ???

??? ???? ?? ??? ?? ?????? > 1 ?????? ?????? ??? ??? ??? ?????? ???? ??? ???????? commit ???? ??? ?????? ?????? ?????????, ???? ????????? ?? ?????? ?? ????????? ?? ?????? ?? ???? ???? ???

??? SQL Server 7.0, ?? ??? ?? ???? ??????? ???? ???????? ??????? ??????? ???? ??????????????????????? ???? ????? ?? ??? ??? ????

DTS ?? ?????? ??????

DTS ????? DTC ?? ?????? ???? ???? ?? ????????? ?? ??? ?????? ?????? ?? ??? ?????? ?????? ????? ??? ????? ???? ?? ??? ?????? ?? ??? ????????? ?? ??? DTC ???? ????? ?????? ??? ???? ?????, ????????? ??? ???????? ??????? DTC ?????? ???? ??????

????????? ????? ??? ?? ????????? ????????????? ??? ????

???????? ??? ??, ???????? ??? ?? ??? ????? ??? ??? ?? ?? ??? ??????? ?? ?? ??? ?? ??? DTC ?? ?????? ?? ??????? ????? DTC ????????? ??? ?? ????? enlist ???? ?? ??? ?? "????? ????????? ??? ???????" ??? ????? ?? ????? ?? ???????? ??? ??? ??? ???? ????? ???? ????? ?? ??? ???? DTC ????????? ?????? ??? "?? ??? ?? ??????????? ???? ???? ?? ???? ?????????" ?? "?????? ????????? ?? ??????."

????? ??? ?? ????? ???? ?????? Microsoft ????????? ????? (MTS)?

?????? ?????:

?? ?? ???? ????? ?? ??? ??????? ?? ??? ??????? ????? ?? ??? ??????? ?? ????? ?? ????? ?? ????? ????? ??????? ?? ?? ?? ?? ??????? ?? transformations ????? ????? ??, ????? ?????? ????? ??? ??:
?????? ?????: Microsoft OLE DB ??????? SQL ????? ?????? ????? ?? ???: ????? 'xxxxxxx' ??? ???????? ??????? ????? ????? ??? ?? ????."

???

???? ID: 242391 - ????? ???????: 03 ?????? 2010 - ??????: 2.0
???? ???? ???? ??:
  • Microsoft SQL Server 7.0 Standard Edition
??????: 
kbinfo kbmt KB242391 KbMthi
???? ?????? ????????
??????????: ?? ???? ?? ???? ??????? ?? ????? ?? Microsoft ????-?????? ?????????? ?????? ?????? ???? ??? ??. Microsoft ???? ??? ????-???????? ?? ????-???????? ????? ?????? ?? ???? ???????? ???? ?? ???? ????? ????? ??? ?? ??? ?????? ?? ???? ???? ???? ??? ????? ??. ???????, ????-???????? ???? ????? ???? ???? ???? ???. ?????, ????????, ?????-???? ?? ??????? ?? ???????? ?? ???? ???, ???? ?? ??? ?????? ???? ???? ??? ????? ??? ?? ???? ??. Microsoft ??????? ??? ???? ?? ?????? ?? ??????????, ????????? ?? ??? ?????? ?? ???? ????? ?? ???? ???????? ?? ??? ???? ????? ?? ??? ????????? ???? ??. Microsoft ????-?????? ?????????? ?? ????? ?????? ?? ?? ??? ??.
?????????? ?? ??????? ????????? ??????? ??:242391

??????????? ???

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com