May 18, 2009 at 7:45 pm
Hi there! Newbie here.
We have migrated from SQL 2000 to SQL 2005. However, DTS in SQL 2005 has really changed and I'm having trouble converting my codes to one which is compatible with SQL 2005 DTS.
Please help. Thank you.
Private Function F_bln_Import_From_Major_Sources() As Boolean
Dim obj_Package As DTS.Package2
Dim obj_Connect As DTS.Connection2
Dim obj_Step As DTS.Step
Dim obj_Task As DTS.Task
Dim obj_CustTask As DTS.DataPumpTask
Dim obj_Transform As DTS.Transformation
Dim obj_Column As DTS.Column
Dim bln_Ret As Boolean
Dim str_Extended As String
On Error GoTo Err_Handler
Set obj_Package = New DTS.Package
obj_Package.FailOnError = True
'=======================================================================
'establish connection to source server
'=======================================================================
Set obj_Connect = obj_Package.Connections.New("SQLOLEDB.1")
With obj_Connect
.ID = 1
.DataSource = g_str_WorkingDBServerName 'Destination Server
.UserID = g_str_WorkingDBUserName 'Destination Database
.Password = g_str_WorkingDBPassword 'Destination User Password
End With
obj_Package.Connections.Add obj_Connect
'=======================================================================
'Establish a connection to any data source
'=======================================================================
Select Case m_int_Source_Type
Case ENUM_DBF_EXCEL
Set obj_Connect = obj_Package.Connections.New("Microsoft.Jet.OLEDB.4.0")
With obj_Connect
.ID = 2
.DataSource = CommonDialog1.Filename
.ConnectionProperties("Extended Properties").Value = "Excel 8.0;HDR=YES;"
End With
obj_Package.Connections.Add obj_Connect
Case ENUM_DBF_ACCESS
Set obj_Connect = obj_Package.Connections.New("Microsoft.Jet.OLEDB.4.0")
With obj_Connect
.ID = 2
.DataSource = CommonDialog1.Filename
End With
obj_Package.Connections.Add obj_Connect
Case ENUM_DBF_DBASE_III, ENUM_DBF_DBASE_IV, ENUM_DBF_DBASE_V
str_Extended = "DSN=dBASE Files;DBQ=@@@;DefaultDir=@@@;DriverId=533;MaxBufferSize=2048;PageTimeout=5;"
str_Extended = Replace(str_Extended, "@@@", Replace(CommonDialog1.Filename, CommonDialog1.FileTitle, ""))
Set obj_Connect = obj_Package.Connections.New("MSDASQL.1")
With obj_Connect
.ID = 2
.Catalog = CommonDialog1.Filename
.DataSource = "dBASE Files"
.ConnectionProperties("Extended Properties") = str_Extended
End With
obj_Package.Connections.Add obj_Connect
End Select
'=======================================================================
'create step and task, link step to task
'=======================================================================
Set obj_Step = obj_Package.Steps.New
obj_Step.Name = "LowerCaseStep"
Set obj_Task = obj_Package.Tasks.New("DTSDataPumpTask")
Set obj_CustTask = obj_Task.CustomTask
obj_CustTask.Name = "LowerCaseTask"
obj_Step.TaskName = obj_CustTask.Name
obj_Step.ExecuteInMainThread = True
obj_Package.Steps.Add obj_Step
'=======================================================================
'link task to connections
'=======================================================================
With obj_CustTask
.SourceConnectionID = 2
Select Case m_int_Source_Type
Case ENUM_DBF_ACCESS, ENUM_DBF_EXCEL
.SourceObjectName = txtTableName.Text
Case ENUM_DBF_DBASE_III, ENUM_DBF_DBASE_IV, ENUM_DBF_DBASE_V
.SourceObjectName = CommonDialog1.Filename
End Select
.DestinationConnectionID = 1
.DestinationObjectName = "[AUDIT_DATA].[dbo].[" & txtTableName & "]"
End With
'=======================================================================
'create custom transform, link to source and dest columns
'=======================================================================
Set obj_Transform = obj_CustTask.Transformations. _
New("DTSPump.DataPumpTransformCopy")
With obj_Transform
.Name = "TransformCopyOnly"
.TransformFlags = 63 'DTSTransformFlag_Default
End With
'=======================================================================
'link transform to task, task to package, run package
'=======================================================================
obj_CustTask.Transformations.Add obj_Transform
obj_Package.Tasks.Add obj_Task
obj_Package.Execute
bln_Ret = True
Set obj_Package = Nothing
Set obj_Connect = Nothing
Set obj_Step = Nothing
Set obj_Task = Nothing
Set obj_CustTask = Nothing
Set obj_Transform = Nothing
Set obj_Column = Nothing
Exit_Proc:
F_bln_Import_From_Major_Sources = bln_Ret
Exit Function
End Function
May 19, 2009 at 12:52 am
Hello,
I just wanted to check how you are performing the migration e.g. are you using the Migration Wizard and then manually “fixing” the parts that are not converted? Is the goal to convert your DTS package totally to SSIS?
Also, is the Excel Import package a complex process with many steps? It is only that you could perform a one off Import using the Data Import Wizard and then save the operation as an SSIS Package. This could form the working basis of a solution, and may save you a lot of hand coding. (You could also cut and paste it into a larger SSIS Package if necessary).
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply