Saving all DTS packages to VB files
-
OK here we go...
I'm coming into an environment that has hundreds of DTS packages and the SQL Server Connections within the packages are using "sa". One of the big ticket items right now is changing the "sa" password on all the servers... which means all the SQL Server Connections within the DTS packages need to have the new password in them.
I've done TONNES of investigating and I'm come to the conclusion that there is no "easy" way to do this, so the solution I'm working towards involves exporting all the DTS packages to VB (*.bas) files and then doing the ever popular "search and replace".
I've found tools/scripts to perform exports to *.dts package but I've yet to find something where I can export all DTS packages on a server to *.bas files.
Please help!!!
Thanks in advance
CK
-
Ewwwwwwwwwwwwwww
I just read/learned that once a DTS package is saved to a *.bas file it cannot be viewed again using the DTS designer
OK I'm back at square 1. Other then manually editting the packages (SQL Server Connections) Does anyone have any ideas??
Thanks
CK
-
Maybe you could export all the dts to vbs, then flag which dts use the sa account then manually change them on the server... I hope that there's a better way but I don't know it...
-
well I spot checked 10 or so on each server and I think I can say with a decent comfort level that ALL the dts packages are using the "sa" user
Fun stuff.... LOL thanks for the reply and hopefully there is a guru supreme out there that'll save the day for me.
Thanks
CK
-
See this previous topic:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=19&messageid=170826#bm170869
I am not too sure that the sa password will show (unencrypted) in the package when exported to vb.
I was under the impression that if you changed the sa password, you don't need to change the packages themselves. Have you checked to make sure this is the case?
------------------------------
The Users are always right - when I'm not wrong! -
There is a line in the VB code that you can uncomment to rave the package back to SQL Server.
--------------------
Colt 45 - the original point and click interface
-
i use this to modify multiple dts package connections. you need to modify the servername and some queries, but this will do what you want. you'll need to look up the connection object to find the properties to modify, but i've switched back and forth between all kinds of different connections and as you can see i even accounted for flat file imports because sometimes i need to change the path in a bunch of dts packages.
use at your own risk of course, you can always remove the save to server and leave the save to file backup line in and then just look at the .dts files to test.
=)
'strict
Option Explicit
'dim vars
Dim strServerName,strConnectionString,arrPackageNames
Dim strPackageName,strSelectDTSPackagesQuery,strFilePath
'set vars
strServerName = "(local)" 'inputbox("input source server name")
strConnectionString = _
"Provider=SQLOLEDB;Integrated Security=SSPI;" & _
"Persist Security Info=False;Initial Catalog=MSDB;" & _
"Data Source=" & strServerName
strSelectDTSPackagesQuery = _
"SELECT distinct name FROM msdb..sysdtspackages WHERE name LIKE '%whatever%'"
'get dts packages from the sql server
With CreateObject("ADODB.Connection")
.Open strConnectionString
arrPackageNames = Split(.Execute(strSelectDTSPackagesQuery).GetString,vbcr)
.Close
End With
'for each package
For Each strPackageName In arrPackageNames
'ensure it has a name
If NOT Len(strPackageName) = 0 Then
'set current vars
strFilePath = Replace(WScript.ScriptFullName, WScript.ScriptName,"") & year(now) & "-" & month(now) & "-" & day(now) & "_" & strPackageName & ".dts"
wscript.echo "Exporting " & strPackageName & " to " & strFilePath
'load and export the dts package
With CreateObject("DTS.Package")
'load the dts package
.LoadFromSQLServer strServerName,,,256,,,,strPackageName
'make a backup
.SaveToStorageFileAs .Name,strFilePath,"",""
'fix connections
FixConnections(.Connections)
'save back to server
.SaveToSQLServer strServerName,,,256,,,,"___" & .Name
End With
End If
Next
Sub FixConnections(connections)
dim connection
for each connection in connections
with connection
select case .ProviderID
case "SQLOLEDB"
.DataSource = "(LOCAL)"
.Catalog = "whatever"
case "DTSFlatFile"
.DataSource = "c:\newpath\" & right(.DataSource,len(.DataSource)-instrrev(.DataSource,"\"))
end select
end with
next
End Sub
-
>> There is a line in the VB code that you can >> uncomment to rave the package back to >> SQL Server.
I'm actually curious about this part of the chat ... I've saved about 300 DTS packages as ".bas" to load the code to tables and parse through as a project to remove hard coded directory paths. That worked beautifully.
However to touch each package (AGAIN!) and modify the hard coding has piqued my interest in finding another method. Can I adapt something in the ".bas" scripts to recreate the DTS packages ... and what?
Thanks!
-
I did about 120 packages at one go.
All I did was put all the seperate .bas files into a Visual Basic Project and performed a find & replace across all files in the project.
There are also text editors, like Ultraedit, that allow you to perform Find & Replace across all files in a directory.
--------------------
Colt 45 - the original point and click interface -
I was misunderstanding, I guess.
I was thinking you had meant that you could rebuild the DTS packages from the .bas output files. That would be a great shortcut to fixing my hard coded directory paths, as I've identified the spots to change in the .bas files and could simply do the find/replace there, then rebuild the DTS packages from those adapted files.
I've attempted to, but cannot, find anywhere explaining further use for the .bas output format beyond a VB use. Or in my case a text search capability.
Thanks!
-
No there wasn't any misunderstanding. In the .bas file there is a line that reads,
goPackage.Execute
just above this line is a line that is commented out. It'll be something like,
goPackage.SaveToSQLServer "local", etc...
If you comment out the Execute line and change the SaveTo... line to connect to your server, then when you run the file in VB the package is saved back to the server.
For my 120 packages, I renamed the sub Main() in each of the files to the name of the package and wrote a new sub Main() to call each one in turn.
--------------------
Colt 45 - the original point and click interface -
Ok - wow! I see what you are referring to now.
Excellent idea to build one sub Main() as a routine to rebuild all.
I will try this. Thank you!
-
Hi,
I am going through the same situation. I already had all my packages in .bas files. from SQL 2000.
I just made the following adjustments and re-loaded my packages to SQL 2000.
Hope this helps. This is a copy of the documentation I wrote.
----------------------------------------------------------
Topic: Loading Visual Basic .bas files into SQL Server 2000 as DTS packages.
Problem: SQL 2000 DTS package that is exported as a VB .bas file cannot be re-loaded into SQL server 2000 as DTS package.
Solution:
1.Open the .bas file with VB 6.0
2.Find the following two lines…
'goPackage.SaveToSQLServer "(local)", "sa", ""
goPackage.Execute
…and change them to:
goPackage.SaveToSQLServer "server name", "login", "password"
'goPackage.Execute
3.Go to Project – References and add a reference to: Microsoft DTSPackage Object Library
4.Save .bas file (optional)
5.Execute module
6.The package should now be available in SQL server 2000
-
I just had to perform this same task a few days ago on a server with 200 packages. I've attached the script which assumes you are using Windows authentication with an account in the sysadmin fixed server role.
Double check that the OLE Automation sprocs are installed on the (SQL2000) server by running this script. You should get 7 records.
SELECT * FROM master.dbo.sysobjectsWHERE name LIKE '%sp_OA%'
AND xtype = 'X'
The attached script simply loops through all of the packages, checks for connections which use a certain DSN (so you only update the one's you need to), sets the new password and lastly saves the package. It will print a success (or failure) message indicating which connection (by name) was updated.
To be clear, the script does NOT update a password on the package itself, just the connections within it.
-
How do you "BULK" save the DTS packages to Visual Basic File (with extension".BAS" )? I have SQL 2005 servers with hundreds of DTs packages and I know I can open a dts package and save it to .bas file. But how will I do that for all the dts packages on a SQL Instance at the same time? Does any one have a code or tool for that? I have a Data Center Migration coming in weeks and most of the servers are being renamed at that time. Please help....
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply