Creating DTS using OSQL
-
We have situtation where there is no client tools for sql server.
WE need to create DTS package. It is possible to create DTS package using osql?
Thanks
-
I don't think you're going to be able to create a DTS package with just OSQL. You can get a Developer copy of SQL Server for $50 or at least you could at one point. You can probably mimic an awful lot of DTS functionality with OSQL commands, but it won't necessarily be easy to work with.
If you can still download an evaluation copy of SQL Server, that may have the tools included. I don't know what your scenario is so can't advise much beyond that.
Perhaps you can include some details on what you need to do and we can try to help some more.
-
Here is a package that took 10 seconds to create using the wizard; I then converted it to VB, which is one way for you to accomplish what you want. Anyway, it's not for the faint of heart. The $50 suggestion looks very attractive.
'****************************************************************
'Microsoft SQL Server 2000
'Visual Basic file generated for DTS Package
'File Name: P:\SQL\DTS\CopyLog.bas
'Package Name: CopyLog
'Package Description: CopyLog
'Generated Date: 4/13/2006
'Generated Time: 8:52:53 AM
'****************************************************************
Option Explicit
Public goPackageOld As New DTS.Package
Public goPackage As DTS.Package2
Private Sub Main()
set goPackage = goPackageOld
goPackage.Name = "CopyLog"
goPackage.Description = "CopyLog"
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
'---------------------------------------------------------------------------
' 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("Persist Security Info") = True
oConnection.ConnectionProperties("User ID") = "curbina"
oConnection.ConnectionProperties("Initial Catalog") = "cmdtestdb"
oConnection.ConnectionProperties("Data Source") = "PLASQLDEV03"
oConnection.ConnectionProperties("Application Name") = "DTS Import/Export Wizard"
oConnection.Name = "Connection 1"
oConnection.ID = 1
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "PLASQLDEV03"
oConnection.UserID = "curbina"
oConnection.ConnectionTimeout = 60
oConnection.Catalog = "cmdtestdb"
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
'------------- 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("Data Source") = "(local)"
oConnection.ConnectionProperties("Application Name") = "DTS Import/Export Wizard"
oConnection.Name = "Connection 2"
oConnection.ID = 2
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "(local)"
oConnection.ConnectionTimeout = 60
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
'---------------------------------------------------------------------------
' 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 = "Create Table [master].[dbo].[TransmissionLog] Step"
oStep.Description = "Create Table [master].[dbo].[TransmissionLog] Step"
oStep.ExecutionStatus = 1
oStep.TaskName = "Create Table [master].[dbo].[TransmissionLog] Task"
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 = "Copy Data from TransmissionLog to [master].[dbo].[TransmissionLog] Step"
oStep.Description = "Copy Data from TransmissionLog to [master].[dbo].[TransmissionLog] Step"
oStep.ExecutionStatus = 1
oStep.TaskName = "Copy Data from TransmissionLog to [master].[dbo].[TransmissionLog] Task"
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("Copy Data from TransmissionLog to [master].[dbo].[TransmissionLog] Step")
Set oPrecConstraint = oStep.PrecedenceConstraints.New("Create Table [master].[dbo].[TransmissionLog] Step")
oPrecConstraint.StepName = "Create Table [master].[dbo].[TransmissionLog] Step"
oPrecConstraint.PrecedenceBasis = 0
oPrecConstraint.Value = 4
oStep.precedenceConstraints.Add oPrecConstraint
Set oPrecConstraint = Nothing
'---------------------------------------------------------------------------
' create package tasks information
'---------------------------------------------------------------------------
'------------- call Task_Sub1 for task Create Table [master].[dbo].[TransmissionLog] Task (Create Table [master].[dbo].[TransmissionLog] Task)
Call Task_Sub1( goPackage 
'------------- call Task_Sub2 for task Copy Data from TransmissionLog to [master].[dbo].[TransmissionLog] Task (Copy Data from TransmissionLog to [master].[dbo].[TransmissionLog] Task)
Call Task_Sub2( goPackage 
'---------------------------------------------------------------------------
' Save or execute package
'---------------------------------------------------------------------------
'goPackage.SaveToSQLServer "(local)", "sa", ""
goPackage.Execute
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
'------------- define Task_Sub1 for task Create Table [master].[dbo].[TransmissionLog] Task (Create Table [master].[dbo].[TransmissionLog] Task)
Public Sub Task_Sub1(ByVal goPackage As Object)
Dim oTask As DTS.Task
Dim oLookup As DTS.Lookup
Dim oCustomTask1 As DTS.ExecuteSQLTask2
Set oTask = goPackage.Tasks.New("DTSExecuteSQLTask")
Set oCustomTask1 = oTask.CustomTask
oCustomTask1.Name = "Create Table [master].[dbo].[TransmissionLog] Task"
oCustomTask1.Description = "Create Table [master].[dbo].[TransmissionLog] Task"
oCustomTask1.SQLStatement = "CREATE TABLE [master].[dbo].[TransmissionLog] (" & vbCrLf
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "[LogID] int NOT NULL, " & vbCrLf
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "[LogTime] datetime NULL, " & vbCrLf
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "[TransmissionID] int NULL, " & vbCrLf
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "[ApplicationType] varchar (20) NULL, " & vbCrLf
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "[Application] varchar (50) NULL, " & vbCrLf
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "[DataCount] int NULL, " & vbCrLf
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "[LogMessage] varchar (1024) NULL" & vbCrLf
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & ")"
oCustomTask1.ConnectionID = 2
oCustomTask1.CommandTimeout = 0
oCustomTask1.OutputAsRecordset = False
goPackage.Tasks.Add oTask
Set oCustomTask1 = Nothing
Set oTask = Nothing
End Sub
'------------- define Task_Sub2 for task Copy Data from TransmissionLog to [master].[dbo].[TransmissionLog] Task (Copy Data from TransmissionLog to [master].[dbo].[TransmissionLog] Task)
Public Sub Task_Sub2(ByVal goPackage As Object)
Dim oTask As DTS.Task
Dim oLookup As DTS.Lookup
Dim oCustomTask2 As DTS.DataPumpTask2
Set oTask = goPackage.Tasks.New("DTSDataPumpTask")
Set oCustomTask2 = oTask.CustomTask
oCustomTask2.Name = "Copy Data from TransmissionLog to [master].[dbo].[TransmissionLog] Task"
oCustomTask2.Description = "Copy Data from TransmissionLog to [master].[dbo].[TransmissionLog] Task"
oCustomTask2.SourceConnectionID = 1
oCustomTask2.SourceSQLStatement = "select [LogID],[LogTime],[TransmissionID],[ApplicationType],[Application],[DataCount],[LogMessage] from [cmdtestdb].[dbo].[TransmissionLog]"
oCustomTask2.DestinationConnectionID = 2
oCustomTask2.DestinationObjectName = "[master].[dbo].[TransmissionLog]"
oCustomTask2.ProgressRowCount = 1000
oCustomTask2.MaximumErrorCount = 0
oCustomTask2.FetchBufferSize = 1
oCustomTask2.UseFastLoad = True
oCustomTask2.InsertCommitSize = 0
oCustomTask2.ExceptionFileColumnDelimiter = "|"
oCustomTask2.ExceptionFileRowDelimiter = vbCrLf
oCustomTask2.AllowIdentityInserts = False
oCustomTask2.FirstRow = 0
oCustomTask2.LastRow = 0
oCustomTask2.FastLoadOptions = 2
oCustomTask2.ExceptionFileOptions = 1
oCustomTask2.DataPumpOptions = 0
Call oCustomTask2_Trans_Sub1( oCustomTask2 
goPackage.Tasks.Add oTask
Set oCustomTask2 = Nothing
Set oTask = Nothing
End Sub
Public Sub oCustomTask2_Trans_Sub1(ByVal oCustomTask2 As Object)
Dim oTransformation As DTS.Transformation2
Dim oTransProps as DTS.Properties
Dim oColumn As DTS.Column
Set oTransformation = oCustomTask2.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("LogID" , 1)
oColumn.Name = "LogID"
oColumn.Ordinal = 1
oColumn.Flags = 16
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("LogTime" , 2)
oColumn.Name = "LogTime"
oColumn.Ordinal = 2
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 135
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("TransmissionID" , 3)
oColumn.Name = "TransmissionID"
oColumn.Ordinal = 3
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("ApplicationType" , 4)
oColumn.Name = "ApplicationType"
oColumn.Ordinal = 4
oColumn.Flags = 104
oColumn.Size = 20
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("Application" , 5)
oColumn.Name = "Application"
oColumn.Ordinal = 5
oColumn.Flags = 104
oColumn.Size = 50
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("DataCount" , 6)
oColumn.Name = "DataCount"
oColumn.Ordinal = 6
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.SourceColumns.New("LogMessage" , 7)
oColumn.Name = "LogMessage"
oColumn.Ordinal = 7
oColumn.Flags = 104
oColumn.Size = 1024
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("LogID" , 1)
oColumn.Name = "LogID"
oColumn.Ordinal = 1
oColumn.Flags = 16
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("LogTime" , 2)
oColumn.Name = "LogTime"
oColumn.Ordinal = 2
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 135
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("TransmissionID" , 3)
oColumn.Name = "TransmissionID"
oColumn.Ordinal = 3
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("ApplicationType" , 4)
oColumn.Name = "ApplicationType"
oColumn.Ordinal = 4
oColumn.Flags = 104
oColumn.Size = 20
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("Application" , 5)
oColumn.Name = "Application"
oColumn.Ordinal = 5
oColumn.Flags = 104
oColumn.Size = 50
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("DataCount" , 6)
oColumn.Name = "DataCount"
oColumn.Ordinal = 6
oColumn.Flags = 120
oColumn.Size = 0
oColumn.DataType = 3
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("LogMessage" , 7)
oColumn.Name = "LogMessage"
oColumn.Ordinal = 7
oColumn.Flags = 104
oColumn.Size = 1024
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
oCustomTask2.Transformations.Add oTransformation
Set oTransformation = Nothing
End Sub
-
Without SQLs Client tools installed at the Client, the dts COM+ objects would not be available to the client machine compiling the VB source. This solution will not work.
If you are really hell-bent on using osql to create DTS packages... you would need to use the sp_OA (OLE Automation) methods to instanciate the DTS package object and any task objects on the server within a script prior to calling the Execute method. In order to get this to work to create a package and execute it... you would need to have a great understanding of the DTS object model and build a client side tool to generate the SQL scripts to apply your logic via sp_OA automation.
So to answer your question, "can it be done..." the answer is "Yes"; but this is also the same answer I would give for the question, "can you make soup with just a rock and a pot of water..."
It could be done... but is it really worth your time and effort?
-Mike Gercevich
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply