?
Solved

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

Posted on 2008-10-22
8
Medium Priority
?
263 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 640 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 640 total points
ID: 22783172
No.
0
CHALLENGE LAB: Troubleshooting Connectivity Issues

Goal: Fix the connectivity issue in the lab's AWS environment so that you can SSH into the provided EC2 instance.  

 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 640 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 720 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 720 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

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

765 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