The stored procedure calls the DTS package to import large batches of data.

Stored Procedure execution DTS package

 Set     @ Shellsql     =     '  Dtsrun/S"  '     +     @ Serverip     +  '  "/U" '     +     @ User     +     '  "/P"  '     +     @ Pwd     +    '  "/N" gz_parsefile_city "/a" type_id ":" 19 "="  '     +     Cast  (  @ Type_id    As     Varchar  )
  +     ' "/A" FILENAME ":" 8 "="  '     +     @ File_name     +     '  "/A" cityid ":" 19 "="  '     +     Cast  (  @ Cityid    As     Varchar  )  +     '  "  '  


  Exec  Master .. xp_mongoshell  @ Shellsql 

 

For details about how to use the parameters, refer to the content copied from SQL Server help below:

 

Dtsrun Utility

DtsrunThe utility executes the packages created with data conversion Service (DTS. Data transmission packages can be stored in Microsoft SQL Server 2000MSDBDatabase, COM Structured Storage file, or SQL Server meta data services.

Syntax

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 ]
[ / Global_variable_name: typeid = Value ]
[ /L Log_file_name ]
[ /W Nt_event_log_completion_status ]
[/Z] [ /! X ] [ /! D ] [ /! Y ] [ /! C ]
]

/?

Displays command prompt options.

~

Specify the following parameter values in hexadecimal format. And/S,/U,/P,/N,/G,/V,/M,/FAnd/ROption. The encrypted value increases the security of the command used to execute the DTS package, because the server name and password are invisible. Use/! YConfirm the encrypted command.

/S SERVER_NAME[\Instance_name]

Specify the SQL server instance to connect. SpecifySERVER_NAME. SpecifySERVER_NAME\Instance_name.

/UUser_name

Is the login ID used to connect to the SQL server instance.

/PPassword

The password specified by the user used with the logon ID.

/E

Specifies the trusted connection (password not required ).

/NPackage_name

The name assigned to the package when the data transmission package is created.

/G Package_guid_string

The package ID assigned to a data transmission package when it is created. The package ID is guid.

/VPackage_version_guid_string

The version ID assigned to the data transmission package when it is saved or executed for the first time. Each time a DTS package is modified, it is assigned a new version ID. The version ID is guid.

/MPackage_password

The optional password assigned to the data transmission package when it is created.

/FFilename

The name of the structured storage file that contains the DTS package. IfSERVER_NAME, Then execute the DTS package retrieved from SQL Server and add the package to the structured storage engine.

/RRepository_database_name

Name of the database that contains the data transmission package. If no name is specified, the default database name is used.

/ Global_variable_name: typeid = Value

Specifies a global variable for a package, whereTypeid= Type identifier of the global variable data type. The entire parameter string can be referenced. This parameter can be used repeatedly to specify multiple global variables.For information about the available types of identifiers of global variables, see Table 1 below..

To use this command to set global variables, you must have the package owner permission or the package must be saved in a state where DTs password protection is not enabled. If you do not have the owner permission, you can specify a global variable, but the value used will be the value set in the package, instead of/The value specified by the command switch.

/L Log_file_name:

Specify the name of the package log file.

/W Windows_event_log

Specify whether the application is in WindowsProgramThe completion status of the write package execution in the log. SpecifyTrueOrFalse.

/Z

Indicates that the dtsrun command line is encrypted using SQL Server 2000 encryption.

/! X

Blocks the execution of the selected DTS package. Use this command parameter when you want to create an encrypted command line without executing the package. If this option is not specified, the DTS package will be executed immediately.

/! D

Delete a data transmission package from an SQL server instance. This package will not be executed. Deleting a specific DTS package from a structured storage file is not feasible. Need to use/FAnd/SOption to overwrite the entire file.

/! Y

Displays the encrypted command used to execute the data transmission package, but does not execute this command.

/! C

Copy the command used to run the DTS package to the Microsoft Windows clipboard. This option can also be used/! XAnd/! Y.

Table 1:

Data Type	Type ID
INTEGER (small)	2
Integer	3
Real (4-byte)	4
Real (8-byte)	5
Currency	6
Date	7
String	8
Boolean	11
Decimal	14
INTEGER (1-byte)	16
Unsigned int (1-byte)	17
Unsigned int (2-byte)	18
Unsigned int (4-byte)	19
INTEGER (8-byte)	20
Unsigned int (8-byte)	21
Int	22
Unsigned int	23
Hresult	25
Pointer	26
Lpstr	30
Lpwstr	31