Solved

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

Posted on 2008-10-22
8
256 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
Comment Utility
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
Comment Utility
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
Comment Utility
No.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 160 total points
Comment Utility
There is no way aroung having to use the DTS runtime, other than rewriting your code from scratch.  Period.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 180 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks!!!
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

772 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now