Home > Articles > Data > SQL Server

  • Print
  • + Share This
Like this article? We recommend

Summary

The package that we created is small and simple for simplicity. It demonstrates how to create your packages using the DTS Object Model, and it teaches you how to create your own packages through code. We have only covered the basic objects needed for most packages. Almost every package must use the Package, Connection, Task, and Step objects to do any work. These objects will allow you to create packages to import and export data, and to transform data between any two sources—even between two sources that are the same (manipulating data within an Excel file, for example). In the third and final article, we will go over some other DTS objects, and show you how to use them in a real-world scenario.

An easy way to get started is to create a package in the DTS Designer and then save it as a VB file. Then, you can open the VB file, add the DTS references (uncomment and add the password to any Connections, if needed, because DTS will not script passwords), and the file should run. Then you can change the code to suit your needs. In fact, this is how this sample package was created.

Table 2 All Properties Shown In the Code and Discussed In the Article

OBJECT

PROPERTY

Package2

Name

Description

PackagePriorityClass

LineageOptions

TransactionIsolationLevel

RepositoryMetadataOptions

LogServerFlags

PackageType

MaxConcurrentSteps

WriteCompletionStatusToNTEventLog

FailOnError

UseTransaction

AutoCommitTransaction

LogToSQLServere

FailPackageOnLogFailure

ExplicitGlobalVariables

UseOLEDBServiceComponents

Name

 

Connection2

ConnectionProperties("Integrated Security")

ConnectionProperties("Persist Security Info")

ConnectionProperties("Application Name")

ConnectionProperties("Extended Properties")

Name

Description

DataSource

UserID

Password

Catalog

UseTrustedConnection

ID

ConnectionTimeout

Reusable

ConnectImmediate

ConnectionProperties("Integrated Security")

 

Step2

Name

Description

ExecutionStatus

TaskName

RelativePriority

ScriptLanguage

CommitSuccess

RollbackFailure

AddGlobalVariables

CloseConnection

ExecuteInMainThread

IsPackageDSORowset

JoinTransactionIfPresent

DisableStep

FailPackageOnError

Name

 

Task

Name

Description

SourceConnectionID

DestinationConnectionID

SourceObjectName

DestinationObjectName

ProgressRowCount

MaximumErrorCount

FetchBufferSize

UseFastLoad

InsertCommitSize

AllowIdentityInserts

FirstRow

LastRow

DataPumpOptions

FastLoadOptions

ExceptionFileOptions

ExceptionFileColumnDelimiter

ExceptionFileRowDelimiter

 

Transformation2

Name

TransformFlags

ForceSourceBlobsBuffered

ForceBlobsInMemory

InMemoryBlobSize

TransformPhases

 

Transformation2

TransformServerProperties

oTransProps("Text")

oTransProps("Language")

oTransProps("FunctionEntry")

Transformation2.TransformServerProperties

 

Name

Ordinal

Flags

Size

DataType

Precision

NumericScale

Nullable


You can link to the code file here.

  • + Share This
  • 🔖 Save To Your Account