''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'
Purpose: * Excersize DTSPackage
Object library
'
'
How To Change the HRIS$ PPD Format Here:
'
-----------------------------------------
'
1) Uncomment the line that contains "goPackage.SaveToSQLServe
strServer"
located at the
bottom of this module.
2) Run the package
(despite any errors it may raise).
3) Open
the package using
Enterprise Manager DTS Designer.
4) Let DTS parse the new
text file format and create
a staging table
and transformation spec for it.
5) Test the
package in DTS Designer
6) Save the package
as a Visual Basic File.
7) Paste the resulting .bas file
in place of the
code here
(including all
Task subs).
8) Change
all:
oConnection.ConnectionProperties("Data
Source") to: strServer
oConnection.Catalog
to: strDatabase
oConnection.ConnectionProperties("Initial
Catalog") to: strDatabase
oCustomTask1.DestinationObjectName to:
"[" & strDatabase & "].[dbo].[tblStgPPD]"
oConnection.UseTrustedConnection
to: IIf(Len(strPassword), False,
True)
UserID
to: strUsername
Password to:
strPassword
9) Edit sproc_UpdatePPD
to accomodate
the new HRIS$ PPD format.
10) Rebuilt a new Analyst Tools installation.
'
' Contact
www.morningglorytech.com for more info.
'
'
History:
'
'
When
Who
What
---- ---
----
'
'
17SEP07 J. Martin
v3.5
www.morningglorytech.com * Modified from DTS Designers code generator.'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Sub CallDTS(sPPDfile As String)
Set goPackage
= goPackageOld
goPackage.Name
= "dtsLoadLaborPPD"
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
'---------------------------------------------------------------------------
'
begin to write package global variables information
---------------------------------------------------------------------------
Dim oGlobal As
DTS.GlobalVariable
Set oGlobal
= goPackage.GlobalVariables.New("PPDFilePath")
oGlobal.Value
= sPPDfile
goPackage.GlobalVariables.Add oGlobal
Set oGlobal
= Nothing
'---------------------------------------------------------------------------
'
create package connection information
---------------------------------------------------------------------------
Dim oConnection As
DTS.Connection2
------------- a new connection defined below.
Set oConnection
= goPackage.Connections.New("DTSFlatFile")
oConnection.ConnectionProperties("Data
Source") = goPackage.GlobalVariables.Item("PPDFilePath")
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") = True
oConnection.ConnectionProperties("Number
of Column") = 0
oConnection.ConnectionProperties("Max
characters per delimited column") = 255
oConnection.Name
= "Text File (Source)"
oConnection.ID = 1
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = goPackage.GlobalVariables.Item("PPDFilePath")
oConnection.ConnectionTimeout = 60
oConnection.UseTrustedConnection = IIf(Len(strPassword), False, True)
oConnection.UseDSL = False
goPackage.Connections.Add
oConnection
Set oConnection
= Nothing
------------- a new connection defined below.
Set oConnection
= goPackage.Connections.New("SQLOLEDB")
oConnection.ConnectionProperties("Persist
Security Info") = True
oConnection.ConnectionProperties("User
ID") = strUsername
oConnection.ConnectionProperties("Initial
Catalog") = strDatabase
oConnection.ConnectionProperties("Data
Source") = strServer
oConnection.ConnectionProperties("Application
Name") = "DTS Designer"
oConnection.Name
= "Microsoft OLE DB Provider for SQL Server"
oConnection.ID = 2
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = strServer
oConnection.UserID = strUsername
oConnection.Password
= strPassword
oConnection.ConnectionTimeout = 60
oConnection.Catalog = strDatabase
oConnection.UseTrustedConnection = IIf(Len(strPassword), False, True)
oConnection.UseDSL = False
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
= "DTSStep_DTSDataPumpTask_1"
oStep.Description
= "Transform Data Task: undefined"
oStep.ExecutionStatus = 1
oStep.TaskName =
"DTSTask_DTSDataPumpTask_1"
oStep.CommitSuccess = False
oStep.RollbackFailure = False
oStep.ScriptLanguage = "VBScript"
oStep.AddGlobalVariables = True
oStep.RelativePriority = 3
oStep.CloseConnection = True
oStep.ExecuteInMainThread = False
oStep.IsPackageDSORowset = False
oStep.JoinTransactionIfPresent = False
oStep.DisableStep = False
oStep.FailPackageOnError = False
goPackage.Steps.Add
oStep
Set oStep
= Nothing
------------- a new step defined below
Set oStep
= goPackage.Steps.New
oStep.Name
= "DTSStep_DTSDynamicPropertiesTask_1"
oStep.Description
= "Set PPDFilePath"
oStep.ExecutionStatus = 4
oStep.TaskName =
"DTSTask_DTSDynamicPropertiesTask_1"
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
------------- a new step defined below
Set oStep
= goPackage.Steps.New
oStep.Name
= "DTSStep_DTSExecuteSQLTask_1"
oStep.Description
= "EXEC sproc_UpdatePPD"
oStep.ExecutionStatus = 1
oStep.TaskName =
"DTSTask_DTSExecuteSQLTask_1"
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
------------- a precedence constraint for steps defined below
Set oStep
= goPackage.Steps("DTSStep_DTSDataPumpTask_1")
Set oPrecConstraint
= oStep.PrecedenceConstraints.New("DTSStep_DTSDynamicPropertiesTask_1")
oPrecConstraint.StepName =
"DTSStep_DTSDynamicPropertiesTask_1"
oPrecConstraint.PrecedenceBasis = 1
oPrecConstraint.Value
= 0
oStep.PrecedenceConstraints.Add oPrecConstraint
Set oPrecConstraint
= Nothing
------------- a precedence constraint for steps defined
below
Set oStep
= goPackage.Steps("DTSStep_DTSExecuteSQLTask_1")
Set oPrecConstraint
= oStep.PrecedenceConstraints.New("DTSStep_DTSDataPumpTask_1")
oPrecConstraint.StepName =
"DTSStep_DTSDataPumpTask_1"
oPrecConstraint.PrecedenceBasis = 1
oPrecConstraint.Value
= 0
oStep.PrecedenceConstraints.Add oPrecConstraint
Set oPrecConstraint
= Nothing
'---------------------------------------------------------------------------
create
package tasks information
---------------------------------------------------------------------------
'-------------
call Task_Sub1 for task DTSTask_DTSDataPumpTask_1 (Transform Data Task:
undefined)
Call Task_Sub1(goPackage)
------------- call Task_Sub2 for task
DTSTask_DTSDynamicPropertiesTask_1 (Set PPDFilePath)
Call Task_Sub2(goPackage)
------------- call Task_Sub3 for task
DTSTask_DTSExecuteSQLTask_1 (EXEC sproc_UpdatePPD)
Call Task_Sub3(goPackage)
'---------------------------------------------------------------------------
Save or execute package
---------------------------------------------------------------------------
'goPackage.SaveToSQLServe
'Comment
out this line to save the package name. Be sure the
'server
is using Trusted Windows Logins (or else supply a
'username
and Password seperated by columns
'
'goPackage.SaveToSQLServer strServer
goPackage.Execute
tracePackageError goPackage
goPackage.UnInitialize
'to
save a package instead of executing it, comment out the executing package lines
above and uncomment the saving package line
Set goPackage
= Nothing
Set goPackageOld
= Nothing
End Sub