DTS PACKAGE WORKS ONLY IN MY LOCAL SERVER
-
Hi All,
This is the problem I thought I have fixed yesterday. What I am trying to do is creating the dts package so that I can populate tables and other objects from one database to another.
When I did this in my local sql server 7 which is on NT works fine. Now I am tring to create the dts pacakge on the main server and it does transfer the tables that I selected but not the data nor the constraints and the dependancies.
What do you think I'm doing wrong hear?
These are the steps I took in the enterprise manager.
-right click, export data
-picked the source server and db
-picked the target server and db
-copy object and data between sql server db's
(third option)
-unclick copy all objects
-selected the needed objects
-ok
-save dts package
-finish
Please can someone get back to me? Thank you!
-
Try changing the default options (deselect "use default options" click on options) and removing the check in "copy database users and database roles" option. Also deselect any of the other options that are not necessary. I have found this to make a difference in the past. Hope this helps.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
-
THANK YOU SO MUCH DAVID. IT WORKS NOW! I WASTED MY WHOLE AFTERNOON ON THE THINGS!
-
My pleasure and I too have been there. Glad to help.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
-
Hi David,
I just have to ask you a question regarding these packages. Write now I'm using the GUI steps to create the package. How can I programatically do the same thing. I know we can use the dtsrun.exe within a procedure to run my package. But I need to write a procedure that will create the package. Have you done something similar? Any sort of direction will help.
Thank you!
-
I'm afraid I will have to call in the likes of Andy Warren for this one. More his style. I am in the process of learning how to use ADO but, not there yet. Sorry
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
-
It be complex, but you can do it.
Here's a start:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtsprog/dtspapps_2e5f.asp
This gets you setup in VB. From there, you could have to create each part of the package, connections, tasks, etc. The best way is to create your package, save as VB, and open in VB.
To convert this to VB script isn't that hard, just a few syntactical items, replace NEW with createobject, etc. Don't have a good procedure here myself, I actually fumble through it each time.
andy?
Steve Jones
-
Definitely create and test in DTS, then save as VB. Essentially you're using it as a code generator (which is a great idea, similar to recording macro's in Word/Excel that generate VBA code you can modify). From there I typically load the whole thing into VB - all you have to do is add a reference to the MS SQL DTS library. At that point it will run. Its a lot of code, but the flow is not hard to follow. The code is more verbose than if you did it yourself since DTS dumps all the properties and not just the ones you set - not a big deal.
I usually compile as exe or a dll depending on what I'm working on. Exe's are less hassle for doing upgrades - just deploy a new exe. DLL's tend to be a better solution, but you have to unregister/re-register the dll's. If you're running them under the agent you normally have to stop/start the agent to get it to dump the old instance of the dll. You also have to either go to the server or terminal service in to it to do the unregister/re-register.
So why use dll? One reason is that it's easier to re-use. I want to re-use everything! I'll take a DTS package (or whatever else) and bring it down to a few key properties and an 'execute' method. Then my ActiveX script job looks like this:
dim oTest
set oTest=CreateObject ("Whatever.Test")
oTest.param1="TableA"
otest.param2="TableB"
otest.execute
set oTest=nothing
Probably off track now...
Anyway. If you save the code as VB and then compile, or convert to VBS (no, I don't have a process - but maybe we'll build one!) you can run it anytime, you don't even have to ever save the package on the server.
Maybe some of that helps?
Andy
-
HI All,
The steps I took to save the dts pacakage to visual basic is:
-right click on dts package
-(location) Visual Basic File
-(file name)my_pkg.vbp
Now I don't understand the part where I have to add a reference to the MS SQL DTS Library.
Can someone guide me on that please?
Thank you!
-
IT SEEMS TO WORK. THANK YOU ALL!
-
-
Hi All,
I cannot seem to use the DTS Package to export procedures from the source database to the target database. Can someone tell me why I cannot import procedures?
Thank you!
-
What error are you receiving?
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley -
HI,
When I tried creating a package with sql server the error I'm getting is something like this:
[microsoft][odbc sql server driver][sql server] line2: incorrect syntax near '-'
Now I don't understand this because the now non of the objects were imported from the source database. The package contains 2 tables, one procedure and one view.
Any thoughts on this please?
-
Hi All,
This is the package that I oppend in Visual Basic 6.0.
Option Explicit
Public goPackageOld As New DTS.Package
Public goPackage As DTS.Package2
Private Sub Main()
Set goPackage = goPackageOld
goPackage.Name = "point_conn_pkg"
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
'---------------------------------------------------------------------------
' 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 SQL Server Objects"
oStep.Description = "Copy SQL Server Objects"
oStep.ExecutionStatus = 1
oStep.TaskName = "Copy SQL Server Objects"
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 Copy SQL Server Objects (Copy SQL Server Objects)
Call Task_Sub1(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 Copy SQL Server Objects (Copy SQL Server Objects)
Public Sub Task_Sub1(ByVal goPackage As Object)
Dim oTask As DTS.Task
Dim oLookup As DTS.Lookup
Dim oCustomTask1 As DTS.TransferObjectsTask2
Set oTask = goPackage.Tasks.New("DTSTransferObjectsTask")
Set oCustomTask1 = oTask.CustomTask
oCustomTask1.Name = "Copy SQL Server Objects"
oCustomTask1.Description = "Copy SQL Server Objects"
oCustomTask1.SourceServer = "MRSPOWER1"
oCustomTask1.SourceUseTrustedConnection = True
oCustomTask1.SourceDatabase = "PIPENET"
oCustomTask1.DestinationServer = "mrspower1"
oCustomTask1.DestinationUseTrustedConnection = True
oCustomTask1.DestinationDatabase = "migration_eisat"
oCustomTask1.ScriptFileDirectory = "C:\Program Files\Microsoft SQL Server\80\Tools"
oCustomTask1.CopyAllObjects = False
oCustomTask1.IncludeDependencies = True
oCustomTask1.IncludeLogins = False
oCustomTask1.IncludeUsers = False
oCustomTask1.DropDestinationObjectsFirst = True
oCustomTask1.CopySchema = True
oCustomTask1.CopyData = 1
oCustomTask1.ScriptOption = -2146995971
oCustomTask1.ScriptOptionEx = 17305616
oCustomTask1.SourceTranslateChar = True
oCustomTask1.DestTranslateChar = True
oCustomTask1.DestUseTransaction = False
oCustomTask1.UseCollation = False
oCustomTask1.AddObjectForTransfer "control_point", "dbo", 8
oCustomTask1.AddObjectForTransfer "point_connection", "dbo", 8
oCustomTask1.AddObjectForTransfer "CONTROL_SEGMENT", "dbo", 4
oCustomTask1.AddObjectForTransfer "sp_isat_rebuild_point_connections", "dbo", 16
goPackage.Tasks.Add oTask
Set oCustomTask1 = Nothing
Set oTask = Nothing
End Sub
When I execute this package I'm getting an error: something like user-defined type not defined.
Please help!!!
Thank you!
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply