Solved

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

Posted on 2008-10-22
8
261 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

688 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