473,322 Members | 1,398 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

DTS coding

Hello there,

I simply need to import a text file into a sql table. Text file is fixed
width and i know the column widths. Will anyone give me the exact coding in
vb.net by using DTS please. Your help is highly appreciated.

Regards,

Anusha
Nov 21 '05 #1
3 4708
Here is a DTS routine I run from VB.Net that reads data from a delimited text
file and writes it to Sql Server. The only value my sample may have for you
is the casting of DTS object variables. All you need to do is run your DTS
job from the Sql Server DTS wizard and have it save down a DTS Bas module.
It will save down to a VB6 bas module. Then you just open that VB6 proj in
VB.Net and the VB.Net upgrade wizard will automatically come on and transform
most of the code to look similar to my code below. The only thing the wizard
won't do is to cast the DTS Vars. Make sure you have a reference to the DTS
object libary.

Note: in my code I made up my own delimiters

"||##" for each field and

"|#$," for each row --- this may be overkill but my data was real dirty

************************************************** *****
Public Sub RunDTS()
'Try
'goPackage = goPackageOld
goPackage = CType(goPackageOld, DTS.Package2)

goPackage.Name = "DTS1"
goPackage.Description = "DTS package description"
goPackage.WriteCompletionStatusToNTEventLog = False
goPackage.FailOnError = False
'goPackage.PackagePriorityClass = 2
goPackage.PackagePriorityClass = CType(2, DTS.DTSPackagePriorityClass)
goPackage.MaxConcurrentSteps = 4
goPackage.LineageOptions = 0
goPackage.UseTransaction = True
'goPackage.TransactionIsolationLevel = 4096
goPackage.TransactionIsolationLevel = CType(4096,
DTS.DTSIsolationLevel)
goPackage.AutoCommitTransaction = True
goPackage.RepositoryMetadataOptions = 0
goPackage.UseOLEDBServiceComponents = True
goPackage.LogToSQLServer = False
goPackage.LogServerFlags = 0
goPackage.FailPackageOnLogFailure = False
goPackage.ExplicitGlobalVariables = False
goPackage.PackageType = 0
Dim oConnProperty As DTS.OleDBProperty
'---------------------------------------------------------------------------
' create package connection information

'---------------------------------------------------------------------------

Dim oConnection As DTS.Connection2

'------------- a new connection defined below.
'For security purposes, the password is never scripted

'oConnection = goPackage.Connections.New("DTSFlatFile")
oConnection = CType(goPackage.Connections.New("DTSFlatFile"),
DTS.Connection2)

'oConnection.ConnectionProperties.Item("Data Source").Value =
"F:\Adonet\zDonAmbDotNet\zDonAmbData\bin\zDonNcalA rc0.txt"
oConnection.ConnectionProperties.Item("Data Source").Value =
strDataPath
oConnection.ConnectionProperties.Item("Mode").Valu e = 1
oConnection.ConnectionProperties.Item("Row Delimiter").Value = "||##"
oConnection.ConnectionProperties.Item("File Format").Value = 1
oConnection.ConnectionProperties.Item("Column Delimiter").Value =
"|#$,"
oConnection.ConnectionProperties.Item("File Type").Value = 1
oConnection.ConnectionProperties.Item("Skip Rows").Value = 0
oConnection.ConnectionProperties.Item("First Row Column Name").Value
= True
oConnection.ConnectionProperties.Item("Max characters per delimited
column").Value = 8000

oConnection.Name = "Connection 1"
oConnection.ID = 1
oConnection.Reusable = True
oConnection.ConnectImmediate = False
'oConnection.DataSource = "G:\DTSproj\bin\myTextFile.txt"
oConnection.DataSource = strDataPath
oConnection.ConnectionTimeout = 60
oConnection.UseTrustedConnection = False
oConnection.UseDSL = False

'If you have a password for this connection, please uncomment and
add your password below.
'oConnection.Password = "<put the password here>"

'UPGRADE_WARNING: Couldn't resolve default property of object
oConnection. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
'goPackage.Connections.Add(oConnection)
goPackage.Connections.Add(CType(oConnection, DTS.Connection))
'UPGRADE_NOTE: Object oConnection may not be destroyed until it is
garbage collected. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1029"'
oConnection = Nothing

'------------- a new connection defined below.
'For security purposes, the password is never scripted

'oConnection = goPackage.Connections.New("SQLOLEDB")
oConnection = CType(goPackage.Connections.New("SQLOLEDB"),
DTS.Connection2)

oConnection.ConnectionProperties.Item("Integrated Security").Value =
"SSPI"
oConnection.ConnectionProperties.Item("Persist Security Info").Value
= True
oConnection.ConnectionProperties.Item("Initial Catalog").Value =
"yourServer"
oConnection.ConnectionProperties.Item("Data Source").Value =
"CNFRKCCMC001"
oConnection.ConnectionProperties.Item("Application Name").Value =
"DTS Import/Export Wizard"

oConnection.Name = "Connection 2"
oConnection.ID = 2
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "CNFRKCCMC001"
oConnection.ConnectionTimeout = 60
oConnection.Catalog = "yourServer"
oConnection.UseTrustedConnection = True
oConnection.UseDSL = False

'If you have a password for this connection, please uncomment and
add your password below.
'oConnection.Password = "<put the password here>"

'UPGRADE_WARNING: Couldn't resolve default property of object
oConnection. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
'goPackage.Connections.Add(oConnection)
goPackage.Connections.Add(CType(oConnection, DTS.Connection))

'UPGRADE_NOTE: Object oConnection may not be destroyed until it is
garbage collected. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1029"'
oConnection = Nothing
'---------------------------------------------------------------------------
' create package steps information

'---------------------------------------------------------------------------

Dim oStep As DTS.Step2
Dim oPrecConstraint As DTS.PrecedenceConstraint

'------------- a new step defined below

'oStep = goPackage.Steps.New
oStep = CType(goPackage.Steps.New, DTS.Step2)

oStep.Name = "Copy Data from myTextFile to
[yourServer].[dbo].[tblDestination] Step"
oStep.Description = "Copy Data from myTextFile to
[yourServer].[dbo].[tblDestination] Step"
'oStep.ExecutionStatus = 1
oStep.ExecutionStatus = CType(1, DTS.DTSStepExecStatus)
oStep.TaskName = "Copy Data from myTextFile to
[yourServer].[dbo].[tblDestination] Task"
oStep.CommitSuccess = False
oStep.RollbackFailure = False
oStep.ScriptLanguage = "VBScript"
oStep.AddGlobalVariables = True
'oStep.RelativePriority = 3
oStep.RelativePriority = CType(3, DTS.DTSStepRelativePriority)
oStep.CloseConnection = False
oStep.ExecuteInMainThread = False
oStep.IsPackageDSORowset = False
oStep.JoinTransactionIfPresent = False
oStep.DisableStep = False
oStep.FailPackageOnError = False

'UPGRADE_WARNING: Couldn't resolve default property of object oStep.
Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
goPackage.Steps.Add(oStep)
'UPGRADE_NOTE: Object oStep may not be destroyed until it is garbage
collected. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1029"'
oStep = Nothing
'---------------------------------------------------------------------------
' create package tasks information

'---------------------------------------------------------------------------

'------------- call Task_Sub1 for task Copy Data from myTextFile to
[yourServer].[dbo].[tblDestination] Task (Copy Data from MytextFile to
[yourServer].[dbo].[tblDestination] Task)
Call Task_Sub1(goPackage)
'---------------------------------------------------------------------------
' Save or execute package

'---------------------------------------------------------------------------

'goPackage.SaveToSQLServer "(local)", "sa", ""
goPackage.Execute()

'UPGRADE_WARNING: Couldn't resolve default property of object
goPackage.Steps.Item. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
'tracePackageError(goPackage.Steps.Item)
tracePackageError(CType(goPackage, DTS.Package))

goPackage.Connections.Remove(2)
goPackage.Connections.Remove(1)
goPackage.Steps.Remove(1)
goPackage.Tasks.Remove(1)

goPackage.UnInitialize()

'to save a package instead of executing it, comment out the
executing package line above and uncomment the saving package line
'UPGRADE_NOTE: Object goPackage may not be destroyed until it is
garbage collected. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1029"'
'goPackage = Nothing

'UPGRADE_NOTE: Object goPackageOld may not be destroyed until it is
garbage collected. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1029"'
'goPackageOld = Nothing
'Catch ex As Exception
'MessageBox.Show(ex.Message)
'End Try
End Sub

'-----------------------------------------------------------------------------
' error reporting using step.GetExecutionErrorInfo after execution

'-----------------------------------------------------------------------------
Public Sub tracePackageError(ByRef oPackage As DTS.Package)
Dim ErrorCode As Integer
Dim ErrorSource As String
Dim ErrorDescription As String
Dim ErrorHelpFile As String
Dim ErrorHelpContext As Integer
Dim ErrorIDofInterfaceWithError As String
Dim i As Short

For i = 1 To CType(oPackage.Steps.Count, Short)
If oPackage.Steps.Item(i).ExecutionResult =
DTS.DTSStepExecResult.DTSStepExecResult_Failure Then
oPackage.Steps.Item(i).GetExecutionErrorInfo(Error Code,
ErrorSource, ErrorDescription, ErrorHelpFile, ErrorHelpContext,
ErrorIDofInterfaceWithError)
MsgBox(oPackage.Steps.Item(i).Name & " failed" & vbCrLf &
ErrorSource & vbCrLf & ErrorDescription)
End If
Next i

End Sub

'------------- define Task_Sub1 for task Copy Data from zDonNcalArc0 to
[yourServer].[dbo].[tblDestination] Task (Copy Data from myTextFile to
[yourServer].[dbo].[tblDestination] Task)
Public Sub Task_Sub1(ByVal goPackage As Object)

Dim oTask As DTS.Task
Dim oLookup As DTS.Lookup

Dim oCustomTask1 As DTS.DataPumpTask2

'UPGRADE_WARNING: Couldn't resolve default property of object
goPackage.Tasks. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
'oTask = goPackage.Tasks.New("DTSDataPumpTask")
oTask = CType(goPackage, DTS.Package).Tasks.New("DTSDataPumpTask")

oTask.Name = "Copy Data from myTextFile to
[yourServer].[dbo].[tblDestination] Task"
'oCustomTask1 = oTask.CustomTask
oCustomTask1 = CType(oTask.CustomTask, DTS.DataPumpTask2)

oCustomTask1.Name = "Copy Data from myTextFile to
[yourServer].[dbo].[tblDestination] Task"
oCustomTask1.Description = "Copy Data from myTextFile to
[yourServer].[dbo].[tblDestination] Task"
oCustomTask1.SourceConnectionID = 1
'oCustomTask1.SourceObjectName = "G:\DTSproj\bin\myTextFile.txt"
oCustomTask1.SourceObjectName = strDataPath
oCustomTask1.DestinationConnectionID = 2
oCustomTask1.DestinationObjectName =
"[yourServer].[dbo].[tblDestination]"
oCustomTask1.ProgressRowCount = 1000
oCustomTask1.MaximumErrorCount = 0
oCustomTask1.FetchBufferSize = 1
oCustomTask1.UseFastLoad = True
oCustomTask1.InsertCommitSize = 0
oCustomTask1.ExceptionFileColumnDelimiter = "|#$,"
oCustomTask1.ExceptionFileRowDelimiter = "||##"
oCustomTask1.AllowIdentityInserts = False
oCustomTask1.FirstRow = 0
oCustomTask1.LastRow = 0
'oCustomTask1.FastLoadOptions = 2
oCustomTask1.FastLoadOptions = CType(2, DTS.DTSFastLoadOptions)
'oCustomTask1.ExceptionFileOptions = 1
oCustomTask1.ExceptionFileOptions = CType(1,
DTS.DTSExceptionFileOptions)
oCustomTask1.DataPumpOptions = 0

Call oCustomTask1_Trans_Sub1(oCustomTask1)
'UPGRADE_WARNING: Couldn't resolve default property of object
goPackage.Tasks. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
'goPackage.Tasks.Add(oTask)
CType(goPackage, DTS.Package).Tasks.Add(oTask)

'UPGRADE_NOTE: Object oCustomTask1 may not be destroyed until it is
garbage collected. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1029"'
oCustomTask1 = Nothing
'UPGRADE_NOTE: Object oTask may not be destroyed until it is garbage
collected. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1029"'
oTask = Nothing

End Sub

Public Sub oCustomTask1_Trans_Sub1(ByVal oCustomTask1 As Object)

Dim oTransformation As DTS.Transformation2
Dim oTransProps As DTS.Properties
Dim oColumn As DTS.Column

'UPGRADE_WARNING: Couldn't resolve default property of object
oCustomTask1.Transformations. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
'oTransformation =
oCustomTask1.Transformations.New("DTS.DataPumpTran sformCopy")
oTransformation = CType(CType(oCustomTask1,
DTS.DataPumpTask2).Transformations.[New]("DTS.DataPumpTransformCopy"),
DTS.Transformation2)

oTransformation.Name = "DirectCopyXform"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4

oColumn = oTransformation.SourceColumns.New("ID", 1)
oColumn.Name = "ID"
oColumn.Ordinal = 1
oColumn.Flags = 32
oColumn.Size = 8000
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add(oColumn)

'UPGRADE_NOTE: Object oColumn may not be destroyed until it is
garbage collected. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1029"'
oColumn = Nothing

oColumn = oTransformation.SourceColumns.New("CN", 2)
oColumn.Name = "CN"
oColumn.Ordinal = 2
oColumn.Flags = 32
oColumn.Size = 8000
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add(oColumn)

'UPGRADE_NOTE: Object oColumn may not be destroyed until it is
garbage collected. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1029"'
oColumn = Nothing

oColumn = oTransformation.SourceColumns.New("Diagnosis", 3)
oColumn.Name = "Diagnosis"
oColumn.Ordinal = 3
oColumn.Flags = 32
oColumn.Size = 8000
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add(oColumn)

'UPGRADE_NOTE: Object oColumn may not be destroyed until it is
garbage collected. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1029"'
oColumn = Nothing

oColumn = oTransformation.SourceColumns.New("PatientConditio n", 4)
oColumn.Name = "PatientCondition"
oColumn.Ordinal = 4
oColumn.Flags = 32
oColumn.Size = 8000
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add(oColumn)
'UPGRADE_NOTE: Object oColumn may not be destroyed until it is
garbage collected. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1029"'
oColumn = Nothing

oColumn = oTransformation.SourceColumns.New("MentalStatus", 5)
oColumn.Name = "MentalStatus"
oColumn.Ordinal = 5
oColumn.Flags = 32
oColumn.Size = 8000
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oColumn = oTransformation.DestinationColumns.New("ID", 1)
oColumn.Name = "ID"
oColumn.Ordinal = 1
oColumn.Flags = 104
oColumn.Size = 255
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add(oColumn)
'UPGRADE_NOTE: Object oColumn may not be destroyed until it is
garbage collected. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1029"'
oColumn = Nothing

oColumn = oTransformation.DestinationColumns.New("CN", 2)
oColumn.Name = "CN"
oColumn.Ordinal = 2
oColumn.Flags = 104
oColumn.Size = 255
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add(oColumn)
'UPGRADE_NOTE: Object oColumn may not be destroyed until it is
garbage collected. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1029"'
oColumn = Nothing

oColumn = oTransformation.DestinationColumns.New("Diagnosis" , 3)
oColumn.Name = "Diagnosis"
oColumn.Ordinal = 3
oColumn.Flags = 104
oColumn.Size = 255
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add(oColumn)
'UPGRADE_NOTE: Object oColumn may not be destroyed until it is
garbage collected. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1029"'
oColumn = Nothing

oColumn = oTransformation.DestinationColumns.New("PatientCon dition",
4)
oColumn.Name = "PatientCondition"
oColumn.Ordinal = 4
oColumn.Flags = 104
oColumn.Size = 255
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add(oColumn)
'UPGRADE_NOTE: Object oColumn may not be destroyed until it is
garbage collected. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1029"'
oColumn = Nothing

oColumn = oTransformation.DestinationColumns.New("MentalStat us", 5)
oColumn.Name = "MentalStatus"
oColumn.Ordinal = 5
oColumn.Flags = 104
oColumn.Size = 255
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add(oColumn)
'UPGRADE_NOTE: Object oColumn may not be destroyed until it is
garbage collected. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1029"'
oColumn = Nothing
oTransProps = oTransformation.TransformServerProperties
'UPGRADE_NOTE: Object oTransProps may not be destroyed until it is
garbage collected. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1029"'
'oTransProps = Nothing

'UPGRADE_WARNING: Couldn't resolve default property of object
oCustomTask1.Transformations. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
'oCustomTask1.Transformations.Add(oTransformation)

CType(oCustomTask1,
DTS.DataPumpTask2).Transformations.Add(oTransforma tion)

End Sub
*************************************************

HTH
Rich

"Anusha H via DotNetMonster.com" wrote:
Hello there,

I simply need to import a text file into a sql table. Text file is fixed
width and i know the column widths. Will anyone give me the exact coding in
vb.net by using DTS please. Your help is highly appreciated.

Regards,

Anusha

Nov 21 '05 #2
Thank you so much Rich......

Rich wrote:
Here is a DTS routine I run from VB.Net that reads data from a delimited text
file and writes it to Sql Server. The only value my sample may have for you
is the casting of DTS object variables. All you need to do is run your DTS
job from the Sql Server DTS wizard and have it save down a DTS Bas module.
It will save down to a VB6 bas module. Then you just open that VB6 proj in
VB.Net and the VB.Net upgrade wizard will automatically come on and transform
most of the code to look similar to my code below. The only thing the wizard
won't do is to cast the DTS Vars. Make sure you have a reference to the DTS
object libary.

Note: in my code I made up my own delimiters

"||##" for each field and

"|#$," for each row --- this may be overkill but my data was real dirty

************************************************* ******
Public Sub RunDTS()
'Try
'goPackage = goPackageOld
goPackage = CType(goPackageOld, DTS.Package2)

goPackage.Name = "DTS1"
goPackage.Description = "DTS package description"
goPackage.WriteCompletionStatusToNTEventLog = False
goPackage.FailOnError = False
'goPackage.PackagePriorityClass = 2
goPackage.PackagePriorityClass = CType(2, DTS.DTSPackagePriorityClass)
goPackage.MaxConcurrentSteps = 4
goPackage.LineageOptions = 0
goPackage.UseTransaction = True
'goPackage.TransactionIsolationLevel = 4096
goPackage.TransactionIsolationLevel = CType(4096,
DTS.DTSIsolationLevel)
goPackage.AutoCommitTransaction = True
goPackage.RepositoryMetadataOptions = 0
goPackage.UseOLEDBServiceComponents = True
goPackage.LogToSQLServer = False
goPackage.LogServerFlags = 0
goPackage.FailPackageOnLogFailure = False
goPackage.ExplicitGlobalVariables = False
goPackage.PackageType = 0

Dim oConnProperty As DTS.OleDBProperty
'---------------------------------------------------------------------------
' create package connection information

'---------------------------------------------------------------------------

Dim oConnection As DTS.Connection2

'------------- a new connection defined below.
'For security purposes, the password is never scripted

'oConnection = goPackage.Connections.New("DTSFlatFile")
oConnection = CType(goPackage.Connections.New("DTSFlatFile"),
DTS.Connection2)

'oConnection.ConnectionProperties.Item("Data Source").Value =
"F:\Adonet\zDonAmbDotNet\zDonAmbData\bin\zDonNcal Arc0.txt"
oConnection.ConnectionProperties.Item("Data Source").Value =
strDataPath
oConnection.ConnectionProperties.Item("Mode").Valu e = 1
oConnection.ConnectionProperties.Item("Row Delimiter").Value = "||##"
oConnection.ConnectionProperties.Item("File Format").Value = 1
oConnection.ConnectionProperties.Item("Column Delimiter").Value =
"|#$,"
oConnection.ConnectionProperties.Item("File Type").Value = 1
oConnection.ConnectionProperties.Item("Skip Rows").Value = 0
oConnection.ConnectionProperties.Item("First Row Column Name").Value
= True
oConnection.ConnectionProperties.Item("Max characters per delimited
column").Value = 8000

oConnection.Name = "Connection 1"
oConnection.ID = 1
oConnection.Reusable = True
oConnection.ConnectImmediate = False
'oConnection.DataSource = "G:\DTSproj\bin\myTextFile.txt"
oConnection.DataSource = strDataPath
oConnection.ConnectionTimeout = 60
oConnection.UseTrustedConnection = False
oConnection.UseDSL = False

'If you have a password for this connection, please uncomment and
add your password below.
'oConnection.Password = "<put the password here>"

'UPGRADE_WARNING: Couldn't resolve default property of object
oConnection. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
'goPackage.Connections.Add(oConnection)
goPackage.Connections.Add(CType(oConnection, DTS.Connection))

'UPGRADE_NOTE: Object oConnection may not be destroyed until it is
garbage collected. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1029"'
oConnection = Nothing

'------------- a new connection defined below.
'For security purposes, the password is never scripted

'oConnection = goPackage.Connections.New("SQLOLEDB")
oConnection = CType(goPackage.Connections.New("SQLOLEDB"),
DTS.Connection2)

oConnection.ConnectionProperties.Item("Integrated Security").Value =
"SSPI"
oConnection.ConnectionProperties.Item("Persist Security Info").Value
= True
oConnection.ConnectionProperties.Item("Initial Catalog").Value =
"yourServer"
oConnection.ConnectionProperties.Item("Data Source").Value =
"CNFRKCCMC001"
oConnection.ConnectionProperties.Item("Application Name").Value =
"DTS Import/Export Wizard"

oConnection.Name = "Connection 2"
oConnection.ID = 2
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "CNFRKCCMC001"
oConnection.ConnectionTimeout = 60
oConnection.Catalog = "yourServer"
oConnection.UseTrustedConnection = True
oConnection.UseDSL = False

'If you have a password for this connection, please uncomment and
add your password below.
'oConnection.Password = "<put the password here>"

'UPGRADE_WARNING: Couldn't resolve default property of object
oConnection. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
'goPackage.Connections.Add(oConnection)
goPackage.Connections.Add(CType(oConnection, DTS.Connection))

'UPGRADE_NOTE: Object oConnection may not be destroyed until it is
garbage collected. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1029"'
oConnection = Nothing
'---------------------------------------------------------------------------
' create package steps information

'---------------------------------------------------------------------------

Dim oStep As DTS.Step2
Dim oPrecConstraint As DTS.PrecedenceConstraint

'------------- a new step defined below

'oStep = goPackage.Steps.New
oStep = CType(goPackage.Steps.New, DTS.Step2)

oStep.Name = "Copy Data from myTextFile to
[yourServer].[dbo].[tblDestination] Step"
oStep.Description = "Copy Data from myTextFile to
[yourServer].[dbo].[tblDestination] Step"
'oStep.ExecutionStatus = 1
oStep.ExecutionStatus = CType(1, DTS.DTSStepExecStatus)
oStep.TaskName = "Copy Data from myTextFile to
[yourServer].[dbo].[tblDestination] Task"
oStep.CommitSuccess = False
oStep.RollbackFailure = False
oStep.ScriptLanguage = "VBScript"
oStep.AddGlobalVariables = True
'oStep.RelativePriority = 3
oStep.RelativePriority = CType(3, DTS.DTSStepRelativePriority)
oStep.CloseConnection = False
oStep.ExecuteInMainThread = False
oStep.IsPackageDSORowset = False
oStep.JoinTransactionIfPresent = False
oStep.DisableStep = False
oStep.FailPackageOnError = False

'UPGRADE_WARNING: Couldn't resolve default property of object oStep.
Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
goPackage.Steps.Add(oStep)
'UPGRADE_NOTE: Object oStep may not be destroyed until it is garbage
collected. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1029"'
oStep = Nothing
'---------------------------------------------------------------------------
' create package tasks information

'---------------------------------------------------------------------------

'------------- call Task_Sub1 for task Copy Data from myTextFile to
[yourServer].[dbo].[tblDestination] Task (Copy Data from MytextFile to
[yourServer].[dbo].[tblDestination] Task)
Call Task_Sub1(goPackage)
'---------------------------------------------------------------------------
' Save or execute package

'---------------------------------------------------------------------------

'goPackage.SaveToSQLServer "(local)", "sa", ""
goPackage.Execute()

'UPGRADE_WARNING: Couldn't resolve default property of object
goPackage.Steps.Item. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
'tracePackageError(goPackage.Steps.Item)
tracePackageError(CType(goPackage, DTS.Package))

goPackage.Connections.Remove(2)
goPackage.Connections.Remove(1)
goPackage.Steps.Remove(1)
goPackage.Tasks.Remove(1)

goPackage.UnInitialize()

'to save a package instead of executing it, comment out the
executing package line above and uncomment the saving package line
'UPGRADE_NOTE: Object goPackage may not be destroyed until it is
garbage collected. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1029"'
'goPackage = Nothing

'UPGRADE_NOTE: Object goPackageOld may not be destroyed until it is
garbage collected. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1029"'
'goPackageOld = Nothing

'Catch ex As Exception
'MessageBox.Show(ex.Message)
'End Try
End Sub
'-----------------------------------------------------------------------------
' error reporting using step.GetExecutionErrorInfo after execution

'-----------------------------------------------------------------------------
Public Sub tracePackageError(ByRef oPackage As DTS.Package)
Dim ErrorCode As Integer
Dim ErrorSource As String
Dim ErrorDescription As String
Dim ErrorHelpFile As String
Dim ErrorHelpContext As Integer
Dim ErrorIDofInterfaceWithError As String
Dim i As Short

For i = 1 To CType(oPackage.Steps.Count, Short)
If oPackage.Steps.Item(i).ExecutionResult =
DTS.DTSStepExecResult.DTSStepExecResult_Failure Then
oPackage.Steps.Item(i).GetExecutionErrorInfo(Error Code,
ErrorSource, ErrorDescription, ErrorHelpFile, ErrorHelpContext,
ErrorIDofInterfaceWithError)
MsgBox(oPackage.Steps.Item(i).Name & " failed" & vbCrLf &
ErrorSource & vbCrLf & ErrorDescription)
End If
Next i

End Sub

'------------- define Task_Sub1 for task Copy Data from zDonNcalArc0 to
[yourServer].[dbo].[tblDestination] Task (Copy Data from myTextFile to
[yourServer].[dbo].[tblDestination] Task)
Public Sub Task_Sub1(ByVal goPackage As Object)

Dim oTask As DTS.Task
Dim oLookup As DTS.Lookup

Dim oCustomTask1 As DTS.DataPumpTask2

'UPGRADE_WARNING: Couldn't resolve default property of object
goPackage.Tasks. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
'oTask = goPackage.Tasks.New("DTSDataPumpTask")
oTask = CType(goPackage, DTS.Package).Tasks.New("DTSDataPumpTask")

oTask.Name = "Copy Data from myTextFile to
[yourServer].[dbo].[tblDestination] Task"
'oCustomTask1 = oTask.CustomTask
oCustomTask1 = CType(oTask.CustomTask, DTS.DataPumpTask2)

oCustomTask1.Name = "Copy Data from myTextFile to
[yourServer].[dbo].[tblDestination] Task"
oCustomTask1.Description = "Copy Data from myTextFile to
[yourServer].[dbo].[tblDestination] Task"
oCustomTask1.SourceConnectionID = 1
'oCustomTask1.SourceObjectName = "G:\DTSproj\bin\myTextFile.txt"
oCustomTask1.SourceObjectName = strDataPath
oCustomTask1.DestinationConnectionID = 2
oCustomTask1.DestinationObjectName =
"[yourServer].[dbo].[tblDestination]"
oCustomTask1.ProgressRowCount = 1000
oCustomTask1.MaximumErrorCount = 0
oCustomTask1.FetchBufferSize = 1
oCustomTask1.UseFastLoad = True
oCustomTask1.InsertCommitSize = 0
oCustomTask1.ExceptionFileColumnDelimiter = "|#$,"
oCustomTask1.ExceptionFileRowDelimiter = "||##"
oCustomTask1.AllowIdentityInserts = False
oCustomTask1.FirstRow = 0
oCustomTask1.LastRow = 0
'oCustomTask1.FastLoadOptions = 2
oCustomTask1.FastLoadOptions = CType(2, DTS.DTSFastLoadOptions)
'oCustomTask1.ExceptionFileOptions = 1
oCustomTask1.ExceptionFileOptions = CType(1,
DTS.DTSExceptionFileOptions)
oCustomTask1.DataPumpOptions = 0

Call oCustomTask1_Trans_Sub1(oCustomTask1)

'UPGRADE_WARNING: Couldn't resolve default property of object
goPackage.Tasks. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
'goPackage.Tasks.Add(oTask)
CType(goPackage, DTS.Package).Tasks.Add(oTask)

'UPGRADE_NOTE: Object oCustomTask1 may not be destroyed until it is
garbage collected. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1029"'
oCustomTask1 = Nothing
'UPGRADE_NOTE: Object oTask may not be destroyed until it is garbage
collected. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1029"'
oTask = Nothing

End Sub

Public Sub oCustomTask1_Trans_Sub1(ByVal oCustomTask1 As Object)

Dim oTransformation As DTS.Transformation2
Dim oTransProps As DTS.Properties
Dim oColumn As DTS.Column

'UPGRADE_WARNING: Couldn't resolve default property of object
oCustomTask1.Transformations. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
'oTransformation =
oCustomTask1.Transformations.New("DTS.DataPumpTra nsformCopy")
oTransformation = CType(CType(oCustomTask1,
DTS.DataPumpTask2).Transformations.[New]("DTS.DataPumpTransformCopy"),
DTS.Transformation2)

oTransformation.Name = "DirectCopyXform"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4

oColumn = oTransformation.SourceColumns.New("ID", 1)
oColumn.Name = "ID"
oColumn.Ordinal = 1
oColumn.Flags = 32
oColumn.Size = 8000
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add(oColumn)

'UPGRADE_NOTE: Object oColumn may not be destroyed until it is
garbage collected. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1029"'
oColumn = Nothing

oColumn = oTransformation.SourceColumns.New("CN", 2)
oColumn.Name = "CN"
oColumn.Ordinal = 2
oColumn.Flags = 32
oColumn.Size = 8000
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add(oColumn)

'UPGRADE_NOTE: Object oColumn may not be destroyed until it is
garbage collected. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1029"'
oColumn = Nothing

oColumn = oTransformation.SourceColumns.New("Diagnosis", 3)
oColumn.Name = "Diagnosis"
oColumn.Ordinal = 3
oColumn.Flags = 32
oColumn.Size = 8000
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add(oColumn)

'UPGRADE_NOTE: Object oColumn may not be destroyed until it is
garbage collected. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1029"'
oColumn = Nothing

oColumn = oTransformation.SourceColumns.New("PatientConditio n", 4)
oColumn.Name = "PatientCondition"
oColumn.Ordinal = 4
oColumn.Flags = 32
oColumn.Size = 8000
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add(oColumn)
'UPGRADE_NOTE: Object oColumn may not be destroyed until it is
garbage collected. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1029"'
oColumn = Nothing

oColumn = oTransformation.SourceColumns.New("MentalStatus", 5)
oColumn.Name = "MentalStatus"
oColumn.Ordinal = 5
oColumn.Flags = 32
oColumn.Size = 8000
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oColumn = oTransformation.DestinationColumns.New("ID", 1)
oColumn.Name = "ID"
oColumn.Ordinal = 1
oColumn.Flags = 104
oColumn.Size = 255
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add(oColumn)
'UPGRADE_NOTE: Object oColumn may not be destroyed until it is
garbage collected. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1029"'
oColumn = Nothing

oColumn = oTransformation.DestinationColumns.New("CN", 2)
oColumn.Name = "CN"
oColumn.Ordinal = 2
oColumn.Flags = 104
oColumn.Size = 255
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add(oColumn)
'UPGRADE_NOTE: Object oColumn may not be destroyed until it is
garbage collected. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1029"'
oColumn = Nothing

oColumn = oTransformation.DestinationColumns.New("Diagnosis" , 3)
oColumn.Name = "Diagnosis"
oColumn.Ordinal = 3
oColumn.Flags = 104
oColumn.Size = 255
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add(oColumn)
'UPGRADE_NOTE: Object oColumn may not be destroyed until it is
garbage collected. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1029"'
oColumn = Nothing

oColumn = oTransformation.DestinationColumns.New("PatientCon dition",
4)
oColumn.Name = "PatientCondition"
oColumn.Ordinal = 4
oColumn.Flags = 104
oColumn.Size = 255
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add(oColumn)
'UPGRADE_NOTE: Object oColumn may not be destroyed until it is
garbage collected. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1029"'
oColumn = Nothing

oColumn = oTransformation.DestinationColumns.New("MentalStat us", 5)
oColumn.Name = "MentalStatus"
oColumn.Ordinal = 5
oColumn.Flags = 104
oColumn.Size = 255
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add(oColumn)
'UPGRADE_NOTE: Object oColumn may not be destroyed until it is
garbage collected. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1029"'
oColumn = Nothing

oTransProps = oTransformation.TransformServerProperties

'UPGRADE_NOTE: Object oTransProps may not be destroyed until it is
garbage collected. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1029"'
'oTransProps = Nothing

'UPGRADE_WARNING: Couldn't resolve default property of object
oCustomTask1.Transformations. Click for more:
'ms-help://MS.VSCC.2003/commoner/redir/redirect.htm?keyword="vbup1037"'
'oCustomTask1.Transformations.Add(oTransformation)

CType(oCustomTask1,
DTS.DataPumpTask2).Transformations.Add(oTransform ation)

End Sub
*********************************************** **

HTH
Rich
Hello there,

[quoted text clipped - 5 lines]

Anusha

--
Message posted via DotNetMonster.com
http://www.dotnetmonster.com/Uwe/For...b-net/200508/1
Nov 21 '05 #3
To Rich.....

I did the exact thing you said. My code is getting loaded into Vb.Net but VB.
Net upgrade wizard doesnot appear. So what is wrong with my procedures...
Appreciate your response. Regards
--
Message posted via DotNetMonster.com
http://www.dotnetmonster.com/Uwe/For...b-net/200508/1
Nov 21 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: ganesan | last post by:
Hi Guys, Could any one knows the best coding standards styles(with variable declarations for c#) . and if any links or site with the best coding standards for .NET send me those links regards...
4
by: dotNetDave | last post by:
About three weeks ago I released the first .NET coding standards book titled "VSDN Tips & Tricks .NET Coding Standards". Here is what the famous author/ speaker Deborah Kurata says about it: ...
0
by: Berthold Höllmann | last post by:
I have a default coding header # -*- coding: iso-8859-15 -*- in my python files. I now have Problems with this settings. I swithched to Python 2.4.1 under Windows. When I import files with the...
144
by: Natt Serrasalmus | last post by:
After years of operating without any coding standards whatsoever, the company that I recently started working for has decided that it might be a good idea to have some. I'm involved in this...
7
by: Ralph Lund | last post by:
Hi. I am starting a new project with C#. I am searching for "good" coding conventions. I know that there are some coding conventions from microsoft, (but they are very extensive and not clear)....
13
by: benben | last post by:
Is there an effort to unify the c++ coding standard? Especially identifier naming. Not a big issue but it would be annoying to have to incorporate different coding styles simultaneously when...
7
by: Robert Seacord | last post by:
The CERT/CC has just deployed a new web site dedicated to developing secure coding standards for the C programming language, C++, and eventually other programming language. We have already...
3
by: editormt | last post by:
A recent poll asked if programming standards are used by development organisations... and if they are controlled. None: 20% Yes, but without control: 49% Yes, with control: 31% Participants:...
0
by: pat | last post by:
CodeCheck Coding Standard's Support As a free service to our customers we offer support in developing "rule-files" for automating corporate coding standards. If you have a coding standard that...
8
by: =?ISO-8859-1?Q?Arnaud_Carr=E9?= | last post by:
Hi all, I guess you all know how difficult it is to choose a conding standard. And even more difficult it is to explain the choice to your dev team :-) I'm looking for an "official" c++ coding...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.