SQL Server 2000 - the content (code) of DTS package

I have SQL Server 2000 on my computer (the name of the instance is SVR2000).  I have 3 DTS packages- MyPack1,  MyPack2, MyPack3. in SVR2000.

1) Is it possible to create a code (C# OR VB for example) that returns the  content (code)  of DTS packages - in this case the code in  MyPack1,  MyPack2, MyPack3 ?    The content (code)  --I mean all Select , Insert,  Update  statements in   MyPack1;   all Select , Insert,  Update  statements in MyPack2  ; all Select , Insert,  Update  statements in MyPack3.  Thanks
niceguy971Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

randy_knightCommented:
Kind of.  You can save your packages as  Visual Basic File which will give you VB6 code to create the package.  You would then need to parse that code to pull out the statements you want.

Just open the pakcage and go to Package -- > Save As.  Then choose Visual Basic file in the Location drop down.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
niceguy971Author Commented:
Any other way ???  Is it possibe to write the code that loops thru each package and "reads" the content of the package???     Thanks
0
Anthony PerkinsCommented:
No.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Anthony PerkinsCommented:
There is no way aroung having to use the DTS runtime, other than rewriting your code from scratch.  Period.
0
Mark WillsTopic AdvisorCommented:
If you can code, then there is the DTS API, haven't used it before, but seems to be able to load from repository or from file... Here is an example from MS...
The following code example shows a function loading a package in one format and saving it in another:
 
Enum eDTSPkgFormat
    REPOSITORY
    SQL_SERVER
    STORAGE_FILE
End Enum
 
Public Function blnCopyDTSPackage( _
    ByVal strReposServerName As String, ByVal strReposDBName As String, _
    ByVal strReposUserName As String, ByVal strReposPassword As String, _
    ByVal blnReposNTAuth As Boolean, ByVal strSQLServerName As String, _
    ByVal strSQLSvUserName As String, ByVal strSQLSvPassword As String, _
    ByVal blnSQLSvNTAuth As Boolean, ByVal strPackageID As String, _
    ByVal strPackageVerID As String, ByVal strPackageName As String, _
    ByVal strPkgOwnerPwd As String, ByVal strPkgUserPwd As String, _
    ByVal strPkgUNCPath As String, ByVal dpfPkgSource As eDTSPkgFormat, _
    ByVal dpfPkgDestination As eDTSPkgFormat) As Boolean
'Copy the DTS package source to the destination format.
Dim objPackage      As DTS.Package2
Dim rsfFlags        As DTS.DTSRepositoryStorageFlags
Dim ssfFlags        As DTS.DTSSQLServerStorageFlags
Dim strPhase        As String       'load/save phase for error msg
 
On Error GoTo ErrorHandler
 
'Copying the source to the destination in the same format is not supported.
If dpfPkgSource = dpfPkgDestination Then
    MsgBox "Same format for source and destination not supported", _
            vbExclamation
    Exit Function
End If
 
'Create the package object and calculate the storage flags.
Set objPackage = New DTS.Package
rsfFlags = IIf(blnReposNTAuth, DTSReposFlag_UseTrustedConnection, _
                               DTSReposFlag_Default)
ssfFlags = IIf(blnSQLSvNTAuth, DTSSQLStgFlag_UseTrustedConnection, _
                                DTSSQLStgFlag_Default)
                                
'Load the package from the specified storage type.
strPhase = "loading"
Select Case dpfPkgSource
    Case REPOSITORY
        objPackage.LoadFromRepository _
            strReposServerName, strReposDBName, strReposUserName, _
            strReposPassword, strPackageID, strPackageVerID, _
            strPackageName, rsfFlags
 
    Case SQL_SERVER
        objPackage.LoadFromSQLServer _
            strSQLServerName, strSQLSvUserName, strSQLSvPassword, _
            ssfFlags, strPkgOwnerPwd, strPackageID, _
            strPackageVerID, strPackageName
    
    Case STORAGE_FILE
        objPackage.LoadFromStorageFile _
            strPkgUNCPath, strPkgOwnerPwd, strPackageID, _
            strPackageVerID, strPackageName
End Select
 
'Save the package to the specified storage type.
strPhase = "saving"
Select Case dpfPkgDestination
    Case REPOSITORY
        objPackage.SaveToRepository _
            strReposServerName, strReposDBName, strReposUserName, _
            strReposPassword, rsfFlags
            
    Case SQL_SERVER
        objPackage.SaveToSQLServer _
            strSQLServerName, strSQLSvUserName, strSQLSvPassword, _
            ssfFlags, strPkgOwnerPwd, strPkgUserPwd
        
    Case STORAGE_FILE
        objPackage.SaveToStorageFile _
            strPkgUNCPath, strPkgOwnerPwd, strPkgUserPwd
End Select
 
blnCopyDTSPackage = True
Exit Function
 
ErrorHandler:
MsgBox "Error " & strPhase & " DTS package: 0x" & Hex(Err.Number) & _
        vbCrLf & Err.Description, vbExclamation
Exit Function
End Function

Open in new window

0
niceguy971Author Commented:
Thanks Mark! So you "...haven't used it before" ...Do you mind to give me a website where you found it?
MS website?? Thanks
0
Mark WillsTopic AdvisorCommented:
Sure,   MSDN : http://msdn.microsoft.com/en-us/library/aa176245(SQL.80).aspx?ppud=4   and no, haven't used it before, but have been tempted to... Almost used it for migrating some packages.
0
niceguy971Author Commented:
Thanks!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.