Option Explicit Public goPackageOld As New DTS.Package Public goPackage As DTS.Package2 Private Sub Main() Set goPackage = goPackageOld goPackage.Name = "New Package" goPackage.Description = "DTS package description" 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") = "SIMS" oConnection.ConnectionProperties("Data Source") = "127.0.0.1\SIMS2005" oConnection.ConnectionProperties("Application Name") = "DTS Import/Export Wizard" oConnection.Name = "Connection 1" oConnection.ID = 1 oConnection.Reusable = True oConnection.ConnectImmediate = False oConnection.DataSource = "127.0.0.1\SIMS2005" oConnection.ConnectionTimeout = 60 oConnection.Catalog = "SIMS" oConnection.UseTrustedConnection = False oConnection.UseDSL = False 'If you have a password for this connection, please uncomment and add your password below. 'oConnection.Password = "" 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") = "c:\simsexports\year3.csv" 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("First Row Column Name") = False oConnection.ConnectionProperties("Column Names") = "Surname,Forename,RegGroup" oConnection.ConnectionProperties("Number of Column") = 3 oConnection.ConnectionProperties("Text Qualifier Col Mask: 0=no, 1=yes, e.g. 0101") = "111" oConnection.ConnectionProperties("Max characters per delimited column") = 8000 oConnection.ConnectionProperties("Blob Col Mask: 0=no, 1=yes, e.g. 0101") = "000" oConnection.Name = "Connection 2" oConnection.ID = 2 oConnection.Reusable = True oConnection.ConnectImmediate = False oConnection.DataSource = "c:\simsexports\year3.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 = "" 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 c:\simsexports\year3.csv Step" oStep.Description = "Copy Data from Results to c:\simsexports\year3.csv Step" oStep.ExecutionStatus = 1 oStep.TaskName = "Copied data in table c:\simsexports\year3.csv" 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 c:\simsexports\year3.csv (Copied data in table c:\simsexports\year3.csv) 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 c:\simsexports\year3.csv (Copied data in table c:\simsexports\year3.csv) 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 c:\simsexports\year3.csv" Set oCustomTask1 = oTask.CustomTask 'bah hate vb6!!!!!!ARRRGHZ! ' Dim d1 As DateTime = DateTime.Now Dim DateNow As String DateNow = Format(Now(), "mmm d yyyy h:mm:ss:999AM/PM") ' MsgBox (DateNow) ' what is the format we need. ' Apr 8 2007 2:57:26:843AM' oCustomTask1.Name = "Copied data in table c:\simsexports\year3.csv" oCustomTask1.Description = "Copied data in table c:\simsexports\year3.csv" oCustomTask1.SourceConnectionID = 1 oCustomTask1.SourceSQLStatement = "exec sims.sta_pix_StudentBrowse_GetStudents @surname = '%', @forename = '%', @year = ' 3', @reg = '%', @house = '%', @tier = '%', @effective_date = '" & DateNow & "', @roll_mode = 'Current', @show_photo = 'T', @use_dm = 'F'" oCustomTask1.DestinationConnectionID = 2 oCustomTask1.DestinationObjectName = "c:\simsexports\year3.csv" 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("Surname", 2) oColumn.Name = "Surname" oColumn.Ordinal = 2 oColumn.Flags = 32776 oColumn.Size = 60 oColumn.DataType = 129 oColumn.Precision = 0 oColumn.NumericScale = 0 oColumn.Nullable = False oTransformation.SourceColumns.Add oColumn Set oColumn = Nothing Set oColumn = oTransformation.SourceColumns.New("Forename", 3) oColumn.Name = "Forename" oColumn.Ordinal = 3 oColumn.Flags = 32872 oColumn.Size = 60 oColumn.DataType = 129 oColumn.Precision = 0 oColumn.NumericScale = 0 oColumn.Nullable = True oTransformation.SourceColumns.Add oColumn Set oColumn = Nothing Set oColumn = oTransformation.SourceColumns.New("RegGroup", 7) oColumn.Name = "RegGroup" oColumn.Ordinal = 7 oColumn.Flags = 104 oColumn.Size = 12 oColumn.DataType = 129 oColumn.Precision = 0 oColumn.NumericScale = 0 oColumn.Nullable = True oTransformation.SourceColumns.Add oColumn Set oColumn = Nothing Set oColumn = oTransformation.DestinationColumns.New("Surname", 2) oColumn.Name = "Surname" oColumn.Ordinal = 2 oColumn.Flags = 32776 oColumn.Size = 60 oColumn.DataType = 129 oColumn.Precision = 0 oColumn.NumericScale = 0 oColumn.Nullable = False oTransformation.DestinationColumns.Add oColumn Set oColumn = Nothing Set oColumn = oTransformation.DestinationColumns.New("Forename", 3) oColumn.Name = "Forename" oColumn.Ordinal = 3 oColumn.Flags = 32872 oColumn.Size = 60 oColumn.DataType = 129 oColumn.Precision = 0 oColumn.NumericScale = 0 oColumn.Nullable = True oTransformation.DestinationColumns.Add oColumn Set oColumn = Nothing Set oColumn = oTransformation.DestinationColumns.New("RegGroup", 7) oColumn.Name = "RegGroup" oColumn.Ordinal = 7 oColumn.Flags = 104 oColumn.Size = 12 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