Lawrence 007


Can someone help me please.

I am having issues with the following code. This is the first time I am doing this, so it is Brand new to me. Sorry if I look and sound like an idiot.

I am having issues with 'DTS.OleDBProperties' cannot be indexed because it has no default property at the following line :oConnection.ConnectionProperties

I used DTS wizard to create my vb code and I am following this page: http://msdn2.microsoft.com/en-us/library/aa176248(SQL.80).aspx

Can someone please guide me in the right direction.


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

goPackage.Name = "Test"
goPackage.Description = "Casier"
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

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

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

oConnection.Name = "Connection 1"
oConnection.ID = 1
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "(local)"
oConnection.ConnectionTimeout = 60
oConnection.Catalog = "EndofY"
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>"

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("DTSFlatFile")

oConnection.ConnectionProperties("Data Source") = ""
oConnection.ConnectionProperties("Mode") = 3
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.ConnectionProperties("Column Names") = "HQID,LastUpdated,Number,StoreID,ID,Name,Password,FloorLimit,ReturnLimit,CashDrawerNumber,SecurityLevel,Privileges,EmailAddress,FailedLogonAttempts,DBTimeStamp,MaxOverShortAmount,MaxOverShortPercent,OverShortLimitType,AutoID,Telephone"
oConnection.ConnectionProperties("Number of Column") = 20
oConnection.ConnectionProperties("Text Qualifier Col Mask: 0=no, 1=yes, e.g. 0101") = "00100110000010000001"
oConnection.ConnectionProperties("Max characters per delimited column") = 8000
oConnection.ConnectionProperties("Blob Col Mask: 0=no, 1=yes, e.g. 0101") = "00000000000000000000"

oConnection.Name = "Connection 2"
oConnection.ID = 2
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = ""
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

'---------------------------------------------------------------------------
' 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 Results to Step"
oStep.Description = "Copy Data from Results to Step"
oStep.ExecutionStatus = 1
oStep.TaskName = "Copied data in table "
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 Copied data in table (Copied data in table )
Call Task_Sub1( goPackage )

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

'goPackage.SaveToSQLServer "(local)", "sa", ""
goPackage.Execute
tracePackageError goPackage
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


'-----------------------------------------------------------------------------
' error reporting using step.GetExecutionErrorInfo after execution
'-----------------------------------------------------------------------------
Public Sub tracePackageError(oPackage As DTS.Package)
Dim ErrorCode As Long
Dim ErrorSource As String
Dim ErrorDescription As String
Dim ErrorHelpFile As String
Dim ErrorHelpContext As Long
Dim ErrorIDofInterfaceWithError As String
Dim i As Integer

For i = 1 To oPackage.Steps.Count
If oPackage.Steps(i).ExecutionResult = DTSStepExecResult_Failure Then
oPackage.Steps(i).GetExecutionErrorInfo ErrorCode, ErrorSource, ErrorDescription, _
ErrorHelpFile, ErrorHelpContext, ErrorIDofInterfaceWithError
MsgBox oPackage.Steps(i).Name & " failed" & vbCrLf & ErrorSource & vbCrLf & ErrorDescription
End If
Next i

End Sub

'------------- define Task_Sub1 for task Copied data in table (Copied data in table )
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")
oTask.Name = "Copied data in table "
Set oCustomTask1 = oTask.CustomTask

oCustomTask1.Name = "Copied data in table "
oCustomTask1.Description = "Copied data in table "
oCustomTask1.SourceConnectionID = 1
oCustomTask1.SourceSQLStatement = "Select * from Cashier"
oCustomTask1.DestinationConnectionID = 2
oCustomTask1.DestinationObjectName = "
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("HQID" , 1)
oColumn.Name = "HQID"
oColumn.Ordinal = 1
oColumn.Flags = 24
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("LastUpdated" , 2)
oColumn.Name = "LastUpdated"
oColumn.Ordinal = 2
oColumn.Flags = 24
oColumn.Size = 0
oColumn.DataType = 135
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("Number" , 3)
oColumn.Name = "Number"
oColumn.Ordinal = 3
oColumn.Flags = 8
oColumn.Size = 9
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("StoreID" , 4)
oColumn.Name = "StoreID"
oColumn.Ordinal = 4
oColumn.Flags = 24
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("ID" , 5)
oColumn.Name = "ID"
oColumn.Ordinal = 5
oColumn.Flags = 24
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("Name" , 6)
oColumn.Name = "Name"
oColumn.Ordinal = 6
oColumn.Flags = 8
oColumn.Size = 50
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("Password" , 7)
oColumn.Name = "Password"
oColumn.Ordinal = 7
oColumn.Flags = 8
oColumn.Size = 12
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("FloorLimit" , 8)
oColumn.Name = "FloorLimit"
oColumn.Ordinal = 8
oColumn.Flags = 24
oColumn.Size = 0
oColumn.DataType = 6
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("ReturnLimit" , 9)
oColumn.Name = "ReturnLimit"
oColumn.Ordinal = 9
oColumn.Flags = 24
oColumn.Size = 0
oColumn.DataType = 6
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("CashDrawerNumber" , 10)
oColumn.Name = "CashDrawerNumber"
oColumn.Ordinal = 10
oColumn.Flags = 24
oColumn.Size = 0
oColumn.DataType = 2
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("SecurityLevel" , 11)
oColumn.Name = "SecurityLevel"
oColumn.Ordinal = 11
oColumn.Flags = 24
oColumn.Size = 0
oColumn.DataType = 2
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("Privileges" , 12)
oColumn.Name = "Privileges"
oColumn.Ordinal = 12
oColumn.Flags = 24
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("EmailAddress" , 13)
oColumn.Name = "EmailAddress"
oColumn.Ordinal = 13
oColumn.Flags = 8
oColumn.Size = 255
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("FailedLogonAttempts" , 14)
oColumn.Name = "FailedLogonAttempts"
oColumn.Ordinal = 14
oColumn.Flags = 24
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("DBTimeStamp" , 15)
oColumn.Name = "DBTimeStamp"
oColumn.Ordinal = 15
oColumn.Flags = 592
oColumn.Size = 0
oColumn.DataType = 128
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("MaxOverShortAmount" , 16)
oColumn.Name = "MaxOverShortAmount"
oColumn.Ordinal = 16
oColumn.Flags = 24
oColumn.Size = 0
oColumn.DataType = 6
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("MaxOverShortPercent" , 17)
oColumn.Name = "MaxOverShortPercent"
oColumn.Ordinal = 17
oColumn.Flags = 24
oColumn.Size = 0
oColumn.DataType = 5
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("OverShortLimitType" , 18)
oColumn.Name = "OverShortLimitType"
oColumn.Ordinal = 18
oColumn.Flags = 24
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("AutoID" , 19)
oColumn.Name = "AutoID"
oColumn.Ordinal = 19
oColumn.Flags = 32784
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("Telephone" , 20)
oColumn.Name = "Telephone"
oColumn.Ordinal = 20
oColumn.Flags = 8
oColumn.Size = 30
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("HQID" , 1)
oColumn.Name = "HQID"
oColumn.Ordinal = 1
oColumn.Flags = 24
oColumn.Size = 12
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("LastUpdated" , 2)
oColumn.Name = "LastUpdated"
oColumn.Ordinal = 2
oColumn.Flags = 24
oColumn.Size = 25
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("Number" , 3)
oColumn.Name = "Number"
oColumn.Ordinal = 3
oColumn.Flags = 8
oColumn.Size = 9
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("StoreID" , 4)
oColumn.Name = "StoreID"
oColumn.Ordinal = 4
oColumn.Flags = 24
oColumn.Size = 12
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("ID" , 5)
oColumn.Name = "ID"
oColumn.Ordinal = 5
oColumn.Flags = 24
oColumn.Size = 12
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("Name" , 6)
oColumn.Name = "Name"
oColumn.Ordinal = 6
oColumn.Flags = 8
oColumn.Size = 50
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("Password" , 7)
oColumn.Name = "Password"
oColumn.Ordinal = 7
oColumn.Flags = 8
oColumn.Size = 12
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("FloorLimit" , 8)
oColumn.Name = "FloorLimit"
oColumn.Ordinal = 8
oColumn.Flags = 24
oColumn.Size = 21
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("ReturnLimit" , 9)
oColumn.Name = "ReturnLimit"
oColumn.Ordinal = 9
oColumn.Flags = 24
oColumn.Size = 21
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("CashDrawerNumber" , 10)
oColumn.Name = "CashDrawerNumber"
oColumn.Ordinal = 10
oColumn.Flags = 24
oColumn.Size = 7
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("SecurityLevel" , 11)
oColumn.Name = "SecurityLevel"
oColumn.Ordinal = 11
oColumn.Flags = 24
oColumn.Size = 7
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("Privileges" , 12)
oColumn.Name = "Privileges"
oColumn.Ordinal = 12
oColumn.Flags = 24
oColumn.Size = 12
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("EmailAddress" , 13)
oColumn.Name = "EmailAddress"
oColumn.Ordinal = 13
oColumn.Flags = 8
oColumn.Size = 255
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("FailedLogonAttempts" , 14)
oColumn.Name = "FailedLogonAttempts"
oColumn.Ordinal = 14
oColumn.Flags = 24
oColumn.Size = 12
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("DBTimeStamp" , 15)
oColumn.Name = "DBTimeStamp"
oColumn.Ordinal = 15
oColumn.Flags = 592
oColumn.Size = 8
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("MaxOverShortAmount" , 16)
oColumn.Name = "MaxOverShortAmount"
oColumn.Ordinal = 16
oColumn.Flags = 24
oColumn.Size = 21
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("MaxOverShortPercent" , 17)
oColumn.Name = "MaxOverShortPercent"
oColumn.Ordinal = 17
oColumn.Flags = 24
oColumn.Size = 17
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("OverShortLimitType" , 18)
oColumn.Name = "OverShortLimitType"
oColumn.Ordinal = 18
oColumn.Flags = 24
oColumn.Size = 12
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("AutoID" , 19)
oColumn.Name = "AutoID"
oColumn.Ordinal = 19
oColumn.Flags = 32784
oColumn.Size = 12
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("Telephone" , 20)
oColumn.Name = "Telephone"
oColumn.Ordinal = 20
oColumn.Flags = 8
oColumn.Size = 30
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oTransProps = oTransformation.TransformServerProperties


Set oTransProps = Nothing

oCustomTask1.Transformations.Add oTransformation
Set oTransformation = Nothing

End Sub

Lawrence





Re: DTS to VB

Adamus Turner


At first glance, oConnection.ConnectionProperties("Data Source") = "(local)" raises an eyebrow. The datasource is local to SQL Server but not local to a vb app.

Secondly, did you add the necessary library references as instructed by the MSDN link you provided

Adamus







Re: DTS to VB

Lawrence 007

I did, I added the following references:

Microsoft DTSDataPump Scripting Object Library
Microsoft DTSPackage Object Library
Microsoft DTS Custom Tasks Object Library

What do I need to change the Datasource to. The program will be run on this computer as well.

Thanks for the VERY fast reply Adamus!







Re: DTS to VB

Adamus Turner

Also, try installing or running the vb application on the server where SQL Server is installed...not on your local machine.

Remember, you can schedule DTS packages to run periodically so if all you're doing is transferring data from somewhere and dumping it into SQL Server and the information is consistent, you don't need to use a vb application unless your intention is to add additional code...but DTS gives you most/all of the funtionality you need.

Can you explain why you chose to use a vb app over scheduling DTS

Adamus






Re: DTS to VB

Adamus Turner

You need to change the datasource to either the SQL Server name or the flat file location which ever it references. Here's an article that will elaborate on this: http://www.databasejournal.com/features/mssql/article.php/1461481

Also, if you intend on adding additional functionality to the vb application, you may want to choose ADO in combination with VB and do the coding yourself.

Like most code generators, a lot of confusing code is added that doesn't make maintenance easy and also makes modifications a nightmare.

Adamus






Re: DTS to VB

Lawrence 007

Adamus,

This is something I am creating for Users that do not know SQL. They need to run certain queries when they need to, not on a schedule (unfortunately). The results also need to be saved on the location they specify. I have been working tirelessly and being very familiar with SQL and new to VB this seemed to be the best option for me. The part that I can create the DTS package and then save it to VB made it very appealing for me, but I am stuck now at the next step, because I am new to vb.

The query above is a simple one : 'Select * from Cashier'

Any help with code OR references that I can go and read would be GREATLY appreciated. Suggestions on an easier way are also welcomed.

Thanks Adamus!!






Re: DTS to VB

Adamus Turner

Lawrence,

My advice would be to avoid automated code for aforementioned reasons. Sometimes the path to least resistance is attractive but a bad idea. Here's some working code I've written for a previous employer that basically imports a .csv file to SQL. It's all written by me and should make more sense than DTS generated code and accomplishes the same goal.

As far as saving the results, I'm a little confused. If you're bulk importing only, what results does the end user need

Public Sub GetFilename()
Dim file_name As String

file_name = Dir$("G:\MA Report Automation\Sage\Engine Data\MSN" & "\*.xls", vbDirectory)
Call ImportToSQL(file_name)

End Sub


Private Sub ImportToSQL(file_name As String)
On Error GoTo CheckTab:
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H1

Set objconnection = CreateObject("ADODB.Connection")
Set objrecordset = CreateObject("ADODB.Recordset")

objconnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=G:\MA Report Automation\Sage\Engine Data\MSN\" & file_name & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"";"

objrecordset.Open "Select * FROM [MSN Report$]", _
objconnection, adOpenStatic, adLockOptimistic, adCmdText

Do Until objrecordset.EOF
Call FinalImport(objrecordset.Fields.Item("Campaign"), _
objrecordset.Fields.Item("Order Name"), _
objrecordset.Fields.Item("Imp"), _
objrecordset.Fields.Item("Clicks"), _
objrecordset.Fields.Item("CTR"), _
objrecordset.Fields.Item("Avg CPC"), _
objrecordset.Fields.Item("Total Cost"), _
objrecordset.Fields.Item("Avg Pos"))

objrecordset.MoveNext
Loop

objconnection.Close
Set objrecordset = Nothing
CheckTab:
Select Case Err.Number
Case -2147467259 'If tab is incorrectly labeled
Call MsgBox("Please check the file " & file_name & "." & vbCrLf & "-->It does not conform to the naming convention.", vbOKOnly, "Naming Convention Violation")
End Select

End Sub

Private Sub FinalImport(Campaign As String, _
AdGroup As String, _
Impressions As Long, _
Clicks As Integer, _
CTR As Double, _
AvgCPC As Double, _
Cost As Currency, _
AvgPosition As Double)

Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
Dim Rs1 As ADODB.Recordset

sConnect = "driver={sql server};server=Sheldon;Database=MktReports;UID=YourUserName;PWD=YourPassword;"

screate = "INSERT INTO Sage(Source, Campaign, AdGroup, Impressions, Clicks, CTR, AvgCPC, Cost, AvgPosition) VALUES " _
& "('MSN', '" & _
Campaign & "', '" & _
AdGroup & "', " & _
Impressions & ", " & _
Clicks & ", " & _
CTR & ", " & _
AvgCPC & ", " & _
Cost & ", " & _
AvgPosition & ")"

' Establish connection.
Set conn = New ADODB.Connection
conn.ConnectionString = sConnect
conn.Open

' Open recordset.
Set cmd = New ADODB.Command
cmd.ActiveConnection = conn
cmd.CommandText = screate
cmd.CommandType = adCmdText
Set Rs1 = cmd.Execute()

'rs.Close
'Set rs = Nothing

conn.Close
'Set conn = Nothing

End Sub

Adamus






Re: DTS to VB

Adamus Turner

Also it looks like the DTS import is creating the table. The code posted assumes the table is already created.

Sorry for the late response,

Adamus






Re: DTS to VB

Adamus Turner

Wait...you're running queries from SQL and dumping the resultset in Excel or vice versa

Adamus






Re: DTS to VB

Lawrence 007

Sorry Adamus, I am running around today.....

You are right, I am running queries from SQL and dumping it into Excel and flat file format. I think I confused you with the sample code. I need to get info out, not in.

Thanks Adamus






Re: DTS to VB

Adamus Turner

In that case, I have a very simple solution for you and one I've used many times in the past.

1. Open Excel
2. Tools | Macro | Record Macro
3. Create a new workbook
4. In the new workbook --> Data | Import External Data | Import Data
5. Import the data to the spreadsheet through the wizard
6. Format it if you need to
7. Stop the macro recorder
8. Hit Alt+F11 <--Here's your vb code (Make sure it's the correct workbook otherwise you won't see a module with code in it)

If this isn't sufficient, let me know and I'll post some raw code that will be easier to understand.

Good luck,

Adamus

 






Re: DTS to VB

Lawrence 007

Adamus,

Do I copy what is in the module and then add that to my vb project This was VERY helpful, thanks!

Lawrence






Re: DTS to VB

Adamus Turner

Yes. It is authentic vb code and should work fine. However it creates a connection string on your local machine. You'll see this when you look at the code. The directory to the connection string will be local. The solution is to find the connection string, open it in notepad, change the directory both in the connection string as well as in the vb code. Move the connection string to a shard network drive so that other people on the network using  your vb application will be able to use it.

Also, don't forget to add the Excel reference library to your project otherwise it won't recognize the Excel objects.

Hope this helps,

Adamus






Re: DTS to VB

Adamus Turner

Also another option, instead of creating a vb application, is simply to create an Excel template, as you have already done, and supply the end user with the template that populates itself.

You simply use the Workbook_Open or Workbook_Initialize event to call the first procedure. You'll find this in Alt+F11 and double click the workbook. Then use the drop down list to select the event. Inside the autogenerated sub procedure, call your first procedure. i.e. Call Module1.

If you choose this approach, remember to always create a new workbook and generate the code in the new workbook and close the template immediately. You don't want the end user to modify anything the template.

Remember, VBA or the code behind Excel is the same as VB6 code. You can create actual VB applications inside Excel.

Adamus






Re: DTS to VB

Lawrence 007

Adamus,

One more question. How can I let the user select the save location The exelspread sheet solution is working, but I still have no way for the users to select where they want to save the file.

Thanks!