April 24, 2006 at 9:32 am
Does anyone know how to automate the conversion of all our DTS packages to the .BAS format? The closest article we have found was Andy Warren's: http://www.sqlservercentral.com/columnists/awarren/copyingdtspackagestoadifferentserver.asp
We don't really need to copy them to a different server, just SAVE AS to a Visual Basic File. Our goal is to then process the .BAS files in order to find dependencies, like you get with sp_depends for stored procs. We're building a data dictionary and we need to know the tables, columns, stored procs, other packages, etc., that our large number of DTS packages use.
April 24, 2006 at 4:02 pm
Can you try something like this. It works for me when I saved the file as .dts, I don't know it will work for .bas or not.:
DECLARE @TARGETDIR varchar(1000)
SET @TARGETDIR = 'd:\backup\sqlbackup\dtspackage\'
SELECT distinct
'exec master..xp_cmdshell '+'DTSRUN.EXE /S '
+ CONVERT(varchar(200), SERVERPROPERTY('servername'))
+ ' /E '
+ ' /N '
+ '"' + name + '"'
+ ' /F '
+ '"' + @TARGETDIR + name + '.bas"'
+ ' /!X'
FROM msdb.dbo.sysdtspackages P
After I got the list from the above statement, I created a job like this:
exec master..xp_cmdshell 'DTSRUN.EXE /S DMISQL05 /E /N "Acor_LinkShare_Inventory" /F "\d:\\sql05backup\dtspackage\Acor_LinkShare_Inventory.dts" /!X'
Hope this help.
Minh Vu
April 25, 2006 at 8:54 am
I posted a reply, but I don't see it.
April 25, 2006 at 9:08 am
Thanks, Minh; I've been working on this. I ran the following in QA:
DECLARE @TARGETDIR varchar(1000)
SET @TARGETDIR = '\\Plads15\Users\Curbina1\SQL\DTS\KONA\'
SELECT distinct
'exec master..xp_cmdshell '+'DTSRUN.EXE /S '
+ CONVERT(varchar(200), SERVERPROPERTY('servername'))
+ ' /E '
+ ' /N '
+ '"' + name + '"'
+ ' /F '
+ '"' + @TARGETDIR + name + '.bas"'
+ ' /!X'
FROM msdb.dbo.sysdtspackages P
It worked fine and the first two lines of the results follow:
exec master..xp_cmdshell DTSRUN.EXE /S FTWCMDTECHSQL02 /E /N "BuilderPipeline" /F "\\Plads15\Users\Curbina1\SQL\DTS\KONA\BuilderPipeline.bas" /!X
exec master..xp_cmdshell DTSRUN.EXE /S FTWCMDTECHSQL02 /E /N "CMDBRANCHLIST" /F "\\Plads15\Users\Curbina1\SQL\DTS\KONA\CMDBRANCHLIST.bas" /!X
Then I grabbed all the results and put them into \\Plads15\Users\Curbina1\SQL\DTS\KONAPackageList.dts
Then I created a single-step SQL job of type T-SQL; the command follows:
exec master..xp_cmdshell 'DTSRUN.EXE /S FTWCMDTECHSQL02 /E /N "KONAPackageList" /F "\\Plads15\Users\Curbina1\SQL\DTS\KONAPackageList.dts" /!X'
The only question that I have is the package name; you had /N "Acor_LinkShare_Inventory", so I assumed that it was the name of the list minus ".DTS", so I did the same thing. Of course, this may be wrong, so please let me know what is the right nomenclature. At any rate, I ran the job and there were no errors but it ran in 0:00 seconds, which made me suspicious. I looked at the \\Plads15\Users\Curbina1\SQL\DTS\KONA\ directory and it was empty, so I'm assuming that it did not really run. Thank you for your help so far; further help will be appreciated.
April 25, 2006 at 11:06 am
Carlos,
If you are able to get the /F SomePackageName.bas and /!X options working, you will create a COM-Object structured file with a .bas extension. This is probably not what you want.
I think that you will need to write code to generate the scripts. I've had a look through the object model and there is not a "Save to VB" method call for the DTS Package object. There is a Save As method, but it is the one that creates the COM-Object file.
Essentially, you'll have to write something that creates a package object, loads it from the SQL server (Using the LoadFromSQLServer() method), and then walks the object hierarchy, creating the script on the fly. This is most likely how the process works inside EM. The script that EM creates is actually just a sequence of object manipulations that will eventually create the COM object that is the DTS package.
I can't find any exported functionality that would allow you to "call" the script generation directly, but there could be something I missed. Another possibility would be to write some code that handles the process by hijacking the windows in EM, if that's even possible.
Based on what you are trying to accomplish, it would seem to me that the first method would allow you to find the exact information that you need, without having to go back and parse a bunch of VB files. So, it might actually be better in the long run.
hth
jg
April 25, 2006 at 3:09 pm
Thanks for your input, Jeff. I will begin working on this and will post a reply when I get it working. It might be a few days, but it's worth it.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply