Solved

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

Posted on 2008-10-22
8
259 Views
Last Modified: 2013-12-17
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
Comment
Question by:niceguy971
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 4

Accepted Solution

by:
randy_knight earned 160 total points
ID: 22782881
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
 

Author Comment

by:niceguy971
ID: 22782911
Any other way ???  Is it possibe to write the code that loops thru each package and "reads" the content of the package???     Thanks
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 160 total points
ID: 22783172
No.
0
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 160 total points
ID: 22783175
There is no way aroung having to use the DTS runtime, other than rewriting your code from scratch.  Period.
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 180 total points
ID: 22787688
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
 

Author Comment

by:niceguy971
ID: 22847434
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
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 180 total points
ID: 22847615
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
 

Author Closing Comment

by:niceguy971
ID: 31509044
Thanks!!!
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question