Solved

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

Posted on 2008-10-22
8
257 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
 
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

932 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

8 Experts available now in Live!

Get 1:1 Help Now