Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 265
  • Last Modified:

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
0
niceguy971
Asked:
niceguy971
  • 3
  • 2
  • 2
  • +1
5 Solutions
 
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
 
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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
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

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now