How to change DTS connections programmatically? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Import/Export (DTS) and Replication (2000)

How to change DTS connections programmatically?

Author

Topic

PiggyZhou
Starting Member

6 Posts

Posted - 2008-08-18 : 09:31:21

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
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-18 : 12:59:51

Check this out:

http://www.dbazine.com/sql/sql-articles/larsen8

PiggyZhou
Starting Member

6 Posts

Posted - 2008-08-18 : 14:48:27

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,

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-18 : 15:40:16

http://decipherinfosys.wordpress.com/2008/08/15/ssis-creating-package-configurations/

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-08-18 : 16:48:05

If you are moving them to 2005, then you should really be upgrading them to SSIS packages.

Boycott Beijing Olympics 2008

PiggyZhou
Starting Member

6 Posts

Posted - 2008-08-19 : 03:47:07

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,

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-19 : 10:05:13

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.

PiggyZhou
Starting Member

6 Posts

Posted - 2008-08-19 : 10:55:47

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,

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-08-19 : 11:53:46

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

PiggyZhou
Starting Member

6 Posts

Posted - 2008-08-20 : 08:03:45

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,

PiggyZhou
Starting Member

6 Posts

Posted - 2008-08-21 : 11:20:26

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources