September 12, 2007 at 10:36 am
We want to automate the conversion of all the packages on a server to .BAS files so that we can extract table, column and stored proc dependencies. There is no need to put them back, we just need a read-only version. We've Googled and reviewed the topics below and still nothing. Phil Cart may know how to do it but we don't know how to contact him.
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=179518
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=19&messageid=191996
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=19&messageid=321738#bm321993
September 13, 2007 at 4:27 am
I thought this would be easy - just replace the SaveToStorageFile method with the method to save to VB. Except there is no method to save to VB.
I found a thread in the Aussie SQL UG forum that had input from Microsoft on this issue. The official answer is that because a save to VB can be done from the GUI, there is no need to be able to do this in program code. (I have seen a similar response about why the SaveToStorageFile method looses the DTS Designer layout and annotations - "it can be done in the GUI so why make it possible programatically?"). Ain't life grand!
The most helpful response I found was to use a screen automation tool and create a macro that drives the GUI. Otherwise you have to create each VB file manually.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
September 14, 2007 at 8:16 am
Hi Carlos,
IIRC we talked about this a few years back. I figured out two ways that you can get what you need.
The first way, as EdVassie suggests, is to control the GUI from an external program. You could use a GUI macro tool, or write some code that pokes around at the windows yourself. I have no input on how to do this, except to say that I would probably enjoy figuring it out.
The other way is to write code that will create an empty DTS package, and then use that object's "load from SQL Server" method to load the object from the serialized binary storage format that is used to store COM objects in a binary file. Once you have the object loaded up, you can use your own code to locate and enumerate all of the subcomponents of the package object and find the data that you are looking for.
That's what the EM GUI does when you export to VB, but it takes the process one step further: It generates the code in VB that will re-create the package object. There is no such thing as a VB version of a DTS package, so there is no way to convert it. What there is, is a way, through code, to programmatically create the package, and that is what the EM GUI generates. Unfortunately, this functionality is not a public method that you can call from anywhere. (That I know of)
If you open the "disconnected edit" dialog in the DTS designer, you will see essentially what the package looks like to the program that you'd have to write to reverse-engineer the package.
So you're kinda stuck writing code, or manually performing the export for each package.
September 17, 2007 at 9:27 am
Jeff,
I think your suggestion is good and I will go in that direction to finally resolve this multi-year challenge. But for those who who would like to know what Jeff is referring to, there is one additional post to be presented:
http://www.sqlservercentral.com/forums/printtopic.aspx?forumid=8&messageid=274907
In it there's a means to create a directory of .bas files that can be searched. However, I haven't tried to restore them but I've found additional resources from Nigel Rivett's site:
http://www.nigelrivett.net/ScriptDTSProperties.html
Another resource I remember is Darren Gordon:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=181665
WHo says that SQL Server Central is not the best resource on the Internet for SQL DBAs and Developers?!!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply