SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Import/Export (DTS) and Replication (2000)
 How to change DTS connections programmatically?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

PiggyZhou
Starting Member

6 Posts

Posted - 08/18/2008 :  09:31:21  Show Profile  Reply with Quote
Hi, all experts here,
We are moving all DTS packages from one server to another server and thus I need to update all connections, instead of going through all packages manually, is there anyway I can change them programmatically? Say for a connection to Server1 I need to change all connections associated with Server1 to Server2.
Hope it is clear for your help.
I am looking forward to hearing from you for your advices and help.
Best regards,
Yours sincerely,
Piggy

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 08/18/2008 :  12:59:51  Show Profile  Reply with Quote
Check this out:

http://www.dbazine.com/sql/sql-articles/larsen8
Go to Top of Page

PiggyZhou
Starting Member

6 Posts

Posted - 08/18/2008 :  14:48:27  Show Profile  Reply with Quote

Hi,
Thanks for the suggestion post. But the sp_OA stored procedures only works for SQL Server 2000. In my case, the DTS packages I am moving to a SQL Server 2005 instance and run them all from there. But the sp_OA stored procedures only supportive on SQL Server 2000 which is used in the suggested post.
Any other way I can get this done? Thanks a lot and I am looking forward to hearing from you.
Best regards,
Yours sincerely,
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 08/18/2008 :  15:40:16  Show Profile  Reply with Quote
http://decipherinfosys.wordpress.com/2008/08/15/ssis-creating-package-configurations/
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 08/18/2008 :  16:48:05  Show Profile  Reply with Quote
If you are moving them to 2005, then you should really be upgrading them to SSIS packages.

Boycott Beijing Olympics 2008
Go to Top of Page

PiggyZhou
Starting Member

6 Posts

Posted - 08/19/2008 :  03:47:07  Show Profile  Reply with Quote
Hi,
Thanks for the suggestion.
But what I am trying to do is to keep the DTS packages intact running on the SQL Server 2005 instance. Thus I am not migrating to SSIS packages at the moment due to time consideration as we have too hundreds of DTS packages to migrate.
I only want to update the DTS packages connections with minimal effort without going through all hundreds of them manunally.
Hope any of you experts here can give me some good ideas if you have done so before.
Thanks a lot in advance.
Best regards,
Yours sincerely,
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 08/19/2008 :  10:05:13  Show Profile  Reply with Quote
quote:
Originally posted by PiggyZhou


Hi,
Thanks for the suggestion post. But the sp_OA stored procedures only works for SQL Server 2000. In my case, the DTS packages I am moving to a SQL Server 2005 instance and run them all from there. But the sp_OA stored procedures only supportive on SQL Server 2000 which is used in the suggested post.
Any other way I can get this done? Thanks a lot and I am looking forward to hearing from you.
Best regards,
Yours sincerely,



You have to enable Ole Automation in SQL server surface area config.
Go to Top of Page

PiggyZhou
Starting Member

6 Posts

Posted - 08/19/2008 :  10:55:47  Show Profile  Reply with Quote
Hi,
Thanks a lot for your help.
I know have run the script against the Master database on the SQL Server 2005 instance and successfully installed these system extended stored procedures. But after I run the script from the suggested post, it returns output of 'class not registered'? What else do I need to sort out to successfully run the script from the post to update my connections?
Thanks a lot in advance and I am looking forward to hearing from you for your further advice and help. I have been stuck in this problem for over 2 days now. Please help.
Best regards,
Yours sincerely,
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 08/19/2008 :  11:53:46  Show Profile  Reply with Quote
quote:
Originally posted by PiggyZhou

I only want to update the DTS packages connections with minimal effort
Unfortunately, the architecture you have chosen does not have a "minimal effort" solution.
I hope you will fix this, and move to SSIS/StagingTable architecture very soon.

Boycott Beijing Olympics 2008
Go to Top of Page

PiggyZhou
Starting Member

6 Posts

Posted - 08/20/2008 :  08:03:45  Show Profile  Reply with Quote
Hi, all here,
Anyone has any idea? It's really interesting that no body ever encountered this situation? Cos before the release of SQL Server 2005 there must be scenario where DTS packages were in place? Anyone successfully update the connection string on SQL Server 2005 instance programatically successfully?
The thing is when I tried to use the sp_OA extended stored procedures it gives me the message that 'Class not registered, source of ODSOLE Extended stored procedure', but while I tried to register the 'ODSOLE70.DLL' which the sp_OA stored procedures were created it again said 'the DLL file was loaded, but the Dll Register Entry Point was not found', therefore I am not able to register the ODSOLE70.DLL class? Anyone has any idea of this?
Therefore I am trying to write up a small application in VB.net with Script task in SSIS to update these connections.
Thanks a lot and I am looking forward to hearing from you.
Best regards,
Yours sincerely,


Go to Top of Page

PiggyZhou
Starting Member

6 Posts

Posted - 08/21/2008 :  11:20:26  Show Profile  Reply with Quote
The solution is as follows:
By using DTS Package library, we can change the connections (including its data source etc.) and save it back to either the SQL Server instance including SQL Server 2005 instance and in this case the DTS packages will be uploaded to the Legacy folder of the SQL Server 2005 instance. (Sure if you want to save the DTS Packages to any file system then it is OK too. Just call a different function of DTS Library)

The code is pretty simple as follows:
Imports DTS
Imports System
Imports System.Data.SqlClient



Module Module1
Dim Opkg As DTS.Package = New DTS.Package()
Dim PkgName As String
Dim ServerName As String
Dim ConnectionString As String
Dim Con As Connections
Dim NumOfCon As Integer
Dim Str As String = ""
Dim myConnection As SqlConnection
Dim myCommand1 As SqlCommand
Dim myCommand2 As SqlCommand

Dim dr As SqlDataReader






Sub Main()

'Establish ODBC connections with SQL Server Instance
myConnection = New SqlConnection("Server = MISQLD01; uid = username; pwd = password; database =msdb")

Try
'Opening the connection
myConnection.Open()

'Executing the command and assigning it to my connection
myCommand1 = New SqlCommand("SELECT name FROM sysdtspackages", myConnection)
'myCommand2 = New SqlCommand("SELECT COUNT(*) FROM sysdtspackages", myConnection)

'Read from the datareader
dr = myCommand1.ExecuteReader()
While dr.Read()
'MsgBox(dr(0).ToString())

If (dr(0).ToString()).Equals("PACKAGENAME") Then
MsgBox(dr(0).ToString())


'Load DTS package from SQL Server
Opkg.LoadFromSQLServer("MISQLD01", "username", "password", DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, "", "", "", dr(0).ToString())

' Get the loaded DTS package name
PkgName = Opkg.Name()

'Get the number of connections within the loaded DTS package


'Get the connection string of each connections of the DTS package
For NumOfCon = 1 To Opkg.Connections.Count
If (Opkg.Connections.Item(NumOfCon).DataSource = "OLDSERVERNAME") Then
Opkg.Connections.Item(NumOfCon).DataSource = "NEWSERVERNAME"
End If
'MsgBox(Opkg.Connections.Item(NumOfCon).DataSource)
Next
'MsgBox(Opkg.Connections.Item(NumOfCon).DataSource)
'Opkg.Connections.Item(1).DataSource = "NEWSERVERNAME"
'MsgBox(Opkg.Connections.Item(1).DataSource)
Opkg.SaveToSQLServer("MISQLP01", "username", "password", DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, "", "", "", dr(0).ToString())

End If
End While
dr.Close()
myConnection.Close()

Catch ex As Exception

End Try

End Sub





End Module

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000