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