looking for a less specific DTS-VB module

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

looking for a less specific DTS-VB module

Author

Topic

lalbatros
Yak Posting Veteran

69 Posts

Posted - 2008-01-08 : 10:48:18

I created several times a VB module for a DTS package.
It is nice that such a module can indeed be created.

My task is extremely simple: bulk copy of similar tables from one database to another.
(tables from a pre-defined list of tables).
However, till now, I have re-created the DTS-VB package everytime I changed the structure of these tables a little bit or when some new tables had to be included.
This looks like a useless work, also a bit time consuming.

Would there be some general purpose VB module available for performing such generic task?
Such a code would avoid me useless editing the DTS package, and would be much more reliable too.

Thanks for any suggestion (or possibly some code ...)

nr
SQLTeam MVY

12543 Posts

Posted - 2008-01-09 : 03:19:14

I have a dts package which creates a dts transfer package from the table structure - actually it takes a prebuilt dts package with a single column transfer - deltes that column and then adds each column in the table. It uses an SP as the source but you could change that to a select query.
Here's the script - should be fairly straightforward (I do the same thing for creating SSIS packages but thta's a bit more fun and easier).

'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
option explicit
Function Main()
dim objPackage
dim objTask
dim objCustomtask
dim objColumn
dim objTransformation

dim objCN
dim objCMD
dim objRS
dim strConnectionString
dim objTransPropertiess
dim ordinalposition

set objPackage = createobject("DTS.Package2")
objPackage.LoadFromStorageFile DTSGlobalVariables("DTSFileLocation").Value & DTSGlobalVariables("TemplatePackageName").Value, ""

'objPackage.LoadFromSQLServer DTSGlobalVariables("ServerName").Value, , , 256, , , , DTSGlobalVariables("TemplatePackageName").Value

set objTask = objPackage.tasks("PopulateTable")
set objCustomtask = objTask.CustomTask
set objTransformation = objCustomtask.Transformations(1)

objTransformation.SourceColumns.remove(1)
objTransformation.DestinationColumns.remove(1)

objCustomtask.DestinationObjectName = replace(DTSGlobalVariables("TableName").Value, "_Initial", "")

'strConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;"
'strConnectionString = strConnectionString & "Initial Catalog=" & DTSGlobalVariables("DatabaseName").Value
'strConnectionString = strConnectionString & ";Packet Size=4096;"
'strConnectionString = strConnectionString & "DataSource=""" & DTSGlobalVariables("ServerName").Value & """"

strConnectionString = "Driver={SQL Server}; Server=" & DTSGlobalVariables("ServerName").Value & "; Database=" & DTSGlobalVariables("DatabaseName").Value & "; UID=; PWD="
set objCN = CreateObject("ADODB.Connection")
set objCMD = CreateObject("ADODB.Command")
objCN.open strConnectionString
objCMD.ActiveConnection = objCN
if DTSGlobalVariables("Type").Value = "PresentationInsert" then
objCMD.CommandText = "select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = '" & replace(replace(DTSGlobalVariables("TableName").Value, "_Initial", ""),"_stg","") & "' order by ORDINAL_POSITION"
else
objCMD.CommandText = "select * from INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME <> 'stg_id' and TABLE_NAME = '" & replace(DTSGlobalVariables("TableName").Value, "_Initial", "") & "' order by ORDINAL_POSITION"
end if
objCMD.CommandType = 1 'adCmdText
Set objRS = objCMD.Execute

ordinalposition = 1
do while not objRS.eof
set objColumn = objTransformation.SourceColumns.new(objRS("COLUMN_NAME") , 1)
objColumn.name = objRS("COLUMN_NAME")
objColumn.ordinal = ordinalposition
objColumn.precision = 0
objColumn.NumericScale = 0
if objRS("DATA_TYPE") = "int" then
objColumn.flags = 120
objColumn.size = 0
objColumn.datatype = 3
elseif objRS("DATA_TYPE") = "varchar" then
objColumn.flags = 104
objColumn.size = objRS("CHARACTER_MAXIMUM_LENGTH")
objColumn.datatype = 129
elseif objRS("DATA_TYPE") = "char" then
objColumn.flags = 120
objColumn.size = objRS("CHARACTER_MAXIMUM_LENGTH")
objColumn.datatype = 129
elseif objRS("DATA_TYPE") = "datetime" then
objColumn.flags = 16
objColumn.size = 0
objColumn.datatype = 135
elseif objRS("DATA_TYPE") = "money" then
objColumn.flags = 120
objColumn.size = 0
objColumn.datatype = 6
elseif objRS("DATA_TYPE") = "bit" then
objColumn.flags = 120
objColumn.size = 0
objColumn.datatype = 11
elseif objRS("DATA_TYPE") = "smalldatetime" then
objColumn.flags = 120
objColumn.size = 0
objColumn.datatype = 135
elseif objRS("DATA_TYPE") = "smallint" then
objColumn.flags = 120
objColumn.size = 0
objColumn.datatype = 2
elseif objRS("DATA_TYPE") = "tinyint" then
objColumn.flags = 120
objColumn.size = 0
objColumn.datatype = 17
elseif objRS("DATA_TYPE") = "numeric" then
objColumn.flags = 120
objColumn.size = 0
objColumn.datatype = 131
objColumn.precision = objRS("NUMERIC_PRECISION")
objColumn.NumericScale = objRS("NUMERIC_SCALE")
else
'msgbox objRS("COLUMN_NAME") & ": " & objRS("DATA_TYPE")
Main = DTSTaskExecResult_Failure
exit function
end if
objColumn.Nullable = 1

' Set objTransPropertiess = objTransformation.TransformServerProperties
' Set objTransPropertiess = Nothing

objTransformation.SourceColumns.Add objColumn
set objColumn = nothing

set objColumn = objTransformation.DestinationColumns.new(objRS("COLUMN_NAME") , 1)
objColumn.name = objRS("COLUMN_NAME")
objColumn.ordinal = ordinalposition
objColumn.precision = 0
objColumn.NumericScale = 0
if objRS("DATA_TYPE") = "int" then
objColumn.flags = 120
objColumn.size = 0
objColumn.datatype = 3
elseif objRS("DATA_TYPE") = "varchar" then
objColumn.flags = 104
objColumn.size = objRS("CHARACTER_MAXIMUM_LENGTH")
objColumn.datatype = 129
elseif objRS("DATA_TYPE") = "datetime" then
objColumn.flags = 16
objColumn.size = 0
objColumn.datatype = 135
elseif objRS("DATA_TYPE") = "money" then
objColumn.flags = 120
objColumn.size = 0
objColumn.datatype = 6
elseif objRS("DATA_TYPE") = "bit" then
objColumn.flags = 120
objColumn.size = 0
objColumn.datatype = 11
elseif objRS("DATA_TYPE") = "smalldatetime" then
objColumn.flags = 120
objColumn.size = 0
objColumn.datatype = 135
elseif objRS("DATA_TYPE") = "smallint" then
objColumn.flags = 120
objColumn.size = 0
objColumn.datatype = 2
elseif objRS("DATA_TYPE") = "tinyint" then
objColumn.flags = 120
objColumn.size = 0
objColumn.datatype = 17
elseif objRS("DATA_TYPE") = "numeric" then
objColumn.flags = 120
objColumn.size = 0
objColumn.datatype = 131
objColumn.precision = objRS("NUMERIC_PRECISION")
objColumn.NumericScale = objRS("NUMERIC_SCALE")
end if
objColumn.Nullable = 1

' Set objTransPropertiess = objTransformation.TransformServerProperties
' Set objTransPropertiess = Nothing

objTransformation.DestinationColumns.Add objColumn
set objColumn = nothing

objRS.movenext
ordinalposition = ordinalposition + 1
loop

set objRS = nothing
set objCMD = nothing
set objCN = nothing

if DTSGlobalVariables("Type").Value = "PresentationInsert" then
objCustomtask.SourceSQLStatement = "exec s_Extract_" & replace(DTSGlobalVariables("TableName").Value, "_stg","")
else
objCustomtask.SourceSQLStatement = "exec s_Extract_" & DTSGlobalVariables("TableName").Value
end if

objPackage.SaveToStorageFileAs replace(DTSGlobalVariables("DTSFileName").Value, ".dts",""), DTSGlobalVariables("DTSFileLocation").Value & DTSGlobalVariables("DTSFileName").Value

Main = DTSTaskExecResult_Success
End Function

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources