project references for DTS package code in VB??? 1

[kskinne]

I have a DTS package written to a visual basic module. Now I am trying to make some changes to it, and compile it into an EXE file. What references do I need in my VB project to work with the code that SQL created for this DTS package?

Thanks
Kevin

 

[AlexCuse]

I have never written a package that way before, but I do know to execute a package saved on your SQL Server (which is where I imagine you plan to put it?) from VB you need a reference to the 'Microsoft DTSPackage Object Library'. Or you can use the command line utility DTSRun, but I don't like that method.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[AlexCuse]

ps - if you need help moving the .dts file to your SQL Server, have a look at this:

http://www.sqldts.com/242.aspx

Works OK for me.

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[kskinne]

adding a reference to Microsoft DTSPackage Object Library worked - thank you Alex

Incidentally here's what I'm doing: I created a DTS package when I was using the data import wizard in SQL Enterprise Manager. I then was executing the DTS package from there for each separate text file that I was importing, but before I ran the package, I was renaming the text file so that it matched the name of the source file referred to in the DTS package.

I decided to try and save a new DTS package only this time right it to a VB module, what I want to try and do is add some lines to the code for this DTS package, that will prompt me with an inputbox to enter a file name, and then have that file name be my Data Source. Then compile this module into an EXE file, and run my DTS package from that?

Hopefully that will work. Also I would like to set it up so that when it imports the data, for each record inserted, it records the name of the text file that referred to above, in one of the columns in the table. Is this possible?

If needed I can post the code

Thanks,
Kevin

 

[AlexCuse]

You can do that, you just need to assign the source file name using vbScript (or you can pass a global variable from your front end app). I may be able to track down an example of how to change it if you need it.

Good Luck

Alex

Ignorance of certain subjects is a great part of wisdom

 

[kskinne]

Here's the code that SQL generated in VB when I created the DTS package:

Option Explicit
Public goPackageOld As New DTS.Package
Public goPackage As DTS.Package2
Public MyTxtFileName As String
Private Sub Main()
Set goPackage = goPackageOld

goPackage.Name = "NBCCDR (2)"
goPackage.Description = "NBC CDR FILE IMPORT"
goPackage.WriteCompletionStatusToNTEventLog = False
goPackage.FailOnError = False
goPackage.PackagePriorityClass = 2
goPackage.MaxConcurrentSteps = 4
goPackage.LineageOptions = 0
goPackage.UseTransaction = True
goPackage.TransactionIsolationLevel = 4096
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
MyTxtFileName = InputBox("Enter file name, without the .csv extension", "File Name")
Set oConnection = goPackage.Connections.New("DTSFlatFile")

oConnection.ConnectionProperties("Data Source") = "C:\Documents and Settings\kskinner\Desktop\CDRs\" & MyTxtFileName & ".csv"
oConnection.ConnectionProperties("Mode") = 1
oConnection.ConnectionProperties("Row Delimiter") = vbCrLf
oConnection.ConnectionProperties("File Format") = 1
oConnection.ConnectionProperties("Column Delimiter") = ","
oConnection.ConnectionProperties("File Type") = 1
oConnection.ConnectionProperties("Skip Rows") = 0
oConnection.ConnectionProperties("Text Qualifier") = """"
oConnection.ConnectionProperties("First Row Column Name") = False

oConnection.Name = "Connection 1"
oConnection.ID = 1
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "C:\Documents and Settings\kskinner\Desktop\CDRs\" & MyTxtFileName & ".csv"
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>"

goPackage.Connections.Add oConnection
Set oConnection = Nothing

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

Set oConnection = goPackage.Connections.New("SQLOLEDB")

oConnection.ConnectionProperties("Persist Security Info") = True
oConnection.ConnectionProperties("User ID") = "sa"
oConnection.ConnectionProperties("Initial Catalog") = "World_Alliance"
oConnection.ConnectionProperties("Data Source") = "IBMSERVER"
oConnection.ConnectionProperties("Application Name") = "DTS Import/Export Wizard"

oConnection.Name = "Connection 2"
oConnection.ID = 2
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "IBMSERVER"
oConnection.UserID = "sa"
oConnection.ConnectionTimeout = 60
oConnection.Catalog = "World_Alliance"
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>"

goPackage.Connections.Add oConnection
Set oConnection = Nothing

'---------------------------------------------------------------------------
' create package steps information
'---------------------------------------------------------------------------

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

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

Set oStep = goPackage.Steps.New

oStep.Name = "Copy Data from 01000001[1] to [World_Alliance].[dbo].[LD_Usage] Step"
oStep.Description = "Copy Data from 01000001[1] to [World_Alliance].[dbo].[LD_Usage] Step"
oStep.ExecutionStatus = 1
oStep.TaskName = "Copy Data from 01000001[1] to [World_Alliance].[dbo].[LD_Usage] Task"
oStep.CommitSuccess = False
oStep.RollbackFailure = False
oStep.ScriptLanguage = "VBScript"
oStep.AddGlobalVariables = True
oStep.RelativePriority = 3
oStep.CloseConnection = False
oStep.ExecuteInMainThread = False
oStep.IsPackageDSORowset = False
oStep.JoinTransactionIfPresent = False
oStep.DisableStep = False
oStep.FailPackageOnError = False

goPackage.Steps.Add oStep
Set oStep = Nothing

'---------------------------------------------------------------------------
' create package tasks information
'---------------------------------------------------------------------------

'------------- call Task_Sub1 for task Copy Data from 01000001[1] to [World_Alliance].[dbo].[LD_Usage] Task (Copy Data from 01000001[1] to [World_Alliance].[dbo].[LD_Usage] Task)
Call Task_Sub1(goPackage)

'---------------------------------------------------------------------------
' Save or execute package
'---------------------------------------------------------------------------

'goPackage.SaveToSQLServer "(local)", "sa", ""
goPackage.Execute
goPackage.UnInitialize
'to save a package instead of executing it, comment out the executing package line above and uncomment the saving package line
Set goPackage = Nothing

Set goPackageOld = Nothing

End Sub


'------------- define Task_Sub1 for task Copy Data from 01000001[1] to [World_Alliance].[dbo].[LD_Usage] Task (Copy Data from 01000001[1] to [World_Alliance].[dbo].[LD_Usage] Task)
Public Sub Task_Sub1(ByVal goPackage As Object)

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

Dim oCustomTask1 As DTS.DataPumpTask2
Set oTask = goPackage.Tasks.New("DTSDataPumpTask")
Set oCustomTask1 = oTask.CustomTask

oCustomTask1.Name = "Copy Data from 01000001[1] to [World_Alliance].[dbo].[LD_Usage] Task"
oCustomTask1.Description = "Copy Data from 01000001[1] to [World_Alliance].[dbo].[LD_Usage] Task"
oCustomTask1.SourceConnectionID = 1
oCustomTask1.SourceObjectName = "C:\Documents and Settings\kskinner\Desktop\CDRs\" & MyTxtFileName & ".csv"
oCustomTask1.DestinationConnectionID = 2
oCustomTask1.DestinationObjectName = "[World_Alliance].[dbo].[LD_Usage]"
oCustomTask1.ProgressRowCount = 1000
oCustomTask1.MaximumErrorCount = 0
oCustomTask1.FetchBufferSize = 1
oCustomTask1.UseFastLoad = True
oCustomTask1.InsertCommitSize = 0
oCustomTask1.ExceptionFileColumnDelimiter = "|"
oCustomTask1.ExceptionFileRowDelimiter = vbCrLf
oCustomTask1.AllowIdentityInserts = False
oCustomTask1.FirstRow = 0
oCustomTask1.LastRow = 0
oCustomTask1.FastLoadOptions = 2
oCustomTask1.ExceptionFileOptions = 1
oCustomTask1.DataPumpOptions = 0

Call oCustomTask1_Trans_Sub1(oCustomTask1)


goPackage.Tasks.Add oTask
Set oCustomTask1 = Nothing
Set 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
Set oTransformation = oCustomTask1.Transformations.New("DTS.DataPumpTransformCopy")
oTransformation.Name = "DirectCopyXform"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4

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

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

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

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

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

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("Col004", 4)
oColumn.Name = "Col004"
oColumn.Ordinal = 4
oColumn.Flags = 32
oColumn.Size = 255
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

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

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("Col006", 6)
oColumn.Name = "Col006"
oColumn.Ordinal = 6
oColumn.Flags = 32
oColumn.Size = 255
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("Col007", 7)
oColumn.Name = "Col007"
oColumn.Ordinal = 7
oColumn.Flags = 32
oColumn.Size = 255
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("Col008", 8)
oColumn.Name = "Col008"
oColumn.Ordinal = 8
oColumn.Flags = 32
oColumn.Size = 255
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("Col009", 9)
oColumn.Name = "Col009"
oColumn.Ordinal = 9
oColumn.Flags = 32
oColumn.Size = 255
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("Col010", 10)
oColumn.Name = "Col010"
oColumn.Ordinal = 10
oColumn.Flags = 32
oColumn.Size = 255
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("Col011", 11)
oColumn.Name = "Col011"
oColumn.Ordinal = 11
oColumn.Flags = 32
oColumn.Size = 255
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("Col012", 12)
oColumn.Name = "Col012"
oColumn.Ordinal = 12
oColumn.Flags = 32
oColumn.Size = 255
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("Col013", 13)
oColumn.Name = "Col013"
oColumn.Ordinal = 13
oColumn.Flags = 32
oColumn.Size = 255
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("Col014", 14)
oColumn.Name = "Col014"
oColumn.Ordinal = 14
oColumn.Flags = 32
oColumn.Size = 255
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("Col015", 15)
oColumn.Name = "Col015"
oColumn.Ordinal = 15
oColumn.Flags = 32
oColumn.Size = 255
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("Col016", 16)
oColumn.Name = "Col016"
oColumn.Ordinal = 16
oColumn.Flags = 32
oColumn.Size = 255
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("Col017", 17)
oColumn.Name = "Col017"
oColumn.Ordinal = 17
oColumn.Flags = 32
oColumn.Size = 255
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("Col018", 18)
oColumn.Name = "Col018"
oColumn.Ordinal = 18
oColumn.Flags = 32
oColumn.Size = 255
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("Col019", 19)
oColumn.Name = "Col019"
oColumn.Ordinal = 19
oColumn.Flags = 32
oColumn.Size = 255
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("Col020", 20)
oColumn.Name = "Col020"
oColumn.Ordinal = 20
oColumn.Flags = 32
oColumn.Size = 255
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("Col021", 21)
oColumn.Name = "Col021"
oColumn.Ordinal = 21
oColumn.Flags = 32
oColumn.Size = 255
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("Col022", 22)
oColumn.Name = "Col022"
oColumn.Ordinal = 22
oColumn.Flags = 32
oColumn.Size = 255
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("Col023", 23)
oColumn.Name = "Col023"
oColumn.Ordinal = 23
oColumn.Flags = 32
oColumn.Size = 255
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("Col024", 24)
oColumn.Name = "Col024"
oColumn.Ordinal = 24
oColumn.Flags = 32
oColumn.Size = 255
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("Col025", 25)
oColumn.Name = "Col025"
oColumn.Ordinal = 25
oColumn.Flags = 32
oColumn.Size = 255
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("Col026", 26)
oColumn.Name = "Col026"
oColumn.Ordinal = 26
oColumn.Flags = 32
oColumn.Size = 255
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("Col027", 27)
oColumn.Name = "Col027"
oColumn.Ordinal = 27
oColumn.Flags = 32
oColumn.Size = 255
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("Col028", 28)
oColumn.Name = "Col028"
oColumn.Ordinal = 28
oColumn.Flags = 32
oColumn.Size = 255
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("Col029", 29)
oColumn.Name = "Col029"
oColumn.Ordinal = 29
oColumn.Flags = 32
oColumn.Size = 255
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("Col030", 30)
oColumn.Name = "Col030"
oColumn.Ordinal = 30
oColumn.Flags = 32
oColumn.Size = 255
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("Col031", 31)
oColumn.Name = "Col031"
oColumn.Ordinal = 31
oColumn.Flags = 32
oColumn.Size = 255
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("Col032", 32)
oColumn.Name = "Col032"
oColumn.Ordinal = 32
oColumn.Flags = 32
oColumn.Size = 255
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("Col033", 33)
oColumn.Name = "Col033"
oColumn.Ordinal = 33
oColumn.Flags = 32
oColumn.Size = 255
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("Col034", 34)
oColumn.Name = "Col034"
oColumn.Ordinal = 34
oColumn.Flags = 32
oColumn.Size = 255
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("Col035", 35)
oColumn.Name = "Col035"
oColumn.Ordinal = 35
oColumn.Flags = 32
oColumn.Size = 255
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("Col036", 36)
oColumn.Name = "Col036"
oColumn.Ordinal = 36
oColumn.Flags = 32
oColumn.Size = 255
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

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

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

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

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

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

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("BTN", 4)
oColumn.Name = "BTN"
oColumn.Ordinal = 4
oColumn.Flags = 104
oColumn.Size = 10
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("Orig_Number", 5)
oColumn.Name = "Orig_Number"
oColumn.Ordinal = 5
oColumn.Flags = 104
oColumn.Size = 15
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("Term_Number", 6)
oColumn.Name = "Term_Number"
oColumn.Ordinal = 6
oColumn.Flags = 104
oColumn.Size = 15
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

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

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

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

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("Orig_City", 9)
oColumn.Name = "Orig_City"
oColumn.Ordinal = 9
oColumn.Flags = 104
oColumn.Size = 15
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("Orig_County", 10)
oColumn.Name = "Orig_County"
oColumn.Ordinal = 10
oColumn.Flags = 104
oColumn.Size = 50
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

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

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("Orig_Lata", 12)
oColumn.Name = "Orig_Lata"
oColumn.Ordinal = 12
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("Orig_lkCountry", 13)
oColumn.Name = "Orig_lkCountry"
oColumn.Ordinal = 13
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("Term_City", 14)
oColumn.Name = "Term_City"
oColumn.Ordinal = 14
oColumn.Flags = 104
oColumn.Size = 15
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("Term_County", 15)
oColumn.Name = "Term_County"
oColumn.Ordinal = 15
oColumn.Flags = 104
oColumn.Size = 50
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

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

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("Term_Lata", 17)
oColumn.Name = "Term_Lata"
oColumn.Ordinal = 17
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("Term_lkCountry", 18)
oColumn.Name = "Term_lkCountry"
oColumn.Ordinal = 18
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("Product_Type", 19)
oColumn.Name = "Product_Type"
oColumn.Ordinal = 19
oColumn.Flags = 104
oColumn.Size = 20
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

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

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("Access_Type", 21)
oColumn.Name = "Access_Type"
oColumn.Ordinal = 21
oColumn.Flags = 104
oColumn.Size = 10
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("Payphone_Charge", 22)
oColumn.Name = "Payphone_Charge"
oColumn.Ordinal = 22
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 6
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("CIC", 23)
oColumn.Name = "CIC"
oColumn.Ordinal = 23
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

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

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("Orig_LECCode", 25)
oColumn.Name = "Orig_LECCode"
oColumn.Ordinal = 25
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("Term_LECCode", 26)
oColumn.Name = "Term_LECCode"
oColumn.Ordinal = 26
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("AccountCode", 27)
oColumn.Name = "AccountCode"
oColumn.Ordinal = 27
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("PinDigits", 28)
oColumn.Name = "PinDigits"
oColumn.Ordinal = 28
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("AccountNumber", 29)
oColumn.Name = "AccountNumber"
oColumn.Ordinal = 29
oColumn.Flags = 104
oColumn.Size = 10
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

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

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("RateCode", 31)
oColumn.Name = "RateCode"
oColumn.Ordinal = 31
oColumn.Flags = 104
oColumn.Size = 10
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("RateCodeFrom", 32)
oColumn.Name = "RateCodeFrom"
oColumn.Ordinal = 32
oColumn.Flags = 104
oColumn.Size = 20
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("CalculatedDuration", 33)
oColumn.Name = "CalculatedDuration"
oColumn.Ordinal = 33
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 5
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("DurationForRating", 34)
oColumn.Name = "DurationForRating"
oColumn.Ordinal = 34
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 5
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("TotalCDRRevenue", 35)
oColumn.Name = "TotalCDRRevenue"
oColumn.Ordinal = 35
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 6
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("Dial_Plan", 36)
oColumn.Name = "Dial_Plan"
oColumn.Ordinal = 36
oColumn.Flags = 104
oColumn.Size = 20
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oTransProps = oTransformation.TransformServerProperties


Set oTransProps = Nothing

oCustomTask1.Transformations.Add oTransformation
Set oTransformation = Nothing

End Sub


As you can see I declared a public string variable called MyTxtFileName, and I added an InputBox to prompt the user to set the value of this variable to the name of the text file they want to import. They there are three places in the code that refer to this file as the 'data source', and I changed it so it used the value of this variable as the name of the file.

This is all I have so far. I am currently working on this in Visual Basic 6.0.

Would there be an easier way to do this? For example is there a way to do this from within SQL, perhaps through Enterprise Manager, where you can have a DTS package already set up, but have it prompt you for the name of the source file when you execute it?

That way I could skip this process in VB, then the only other thing would be getting it to populate one of the columns in the table with the name of the imported text file.

Any other help you can give me I'd appreciate

Thank you
Kevin

 

[kskinne]

I apologize but apparently it will not let me post the entire code - is there a way to attach a file to a post, or is there a way to format that code differently so it all fits in the post?

Sorry
Kevin

 

[AlexCuse]

You can place it in [ignore]

 typing code here....

[/ignore] tags, which will display:

 typing code here....

I don't have time to read all of that, but if you are going to be setting a whole ton of different text file connections, I think you should read up on the dynamic properties task.

Have a look here, the gents at sqldts.com will walk you through creating a package to import every text file in a specified directory. You can modify what they are doing there to suit your needs.

http://www.sqldts.com/246.aspx

If you have any further questions, check out this forum too: forum961

There are some really sharp people in there who may not necessarily frequent this forum (which is more geared towards T-SQL).

If you run into any problems, post there and I am sure someone will be able to help you.

Good luck!

ALex

Ignorance of certain subjects is a great part of wisdom

 

[kskinne]

Thank you for the help Alex I appreciate it