如何从DTS对象导出SQL语句?

DTS包有一个带有对象模型的API。您可以通过此命令获取SQL文本。在线文档上的书籍在某种程度上描述了这一点 Here. 您可以通过以下方式获得对象模型使用的示例: Saving the DTS package to a Visual BASIC file

如果你想节省一些工作,而且你不介意付几块钱,有一个 tool 完全记录您的DTS包。它还输出到XML,因此获取这些SQL语句应该相对容易。

我有一个 Python 2.6 脚本(易于移植到Python 2.5),该脚本将SQL从保存为Visual Basic代码的DTS包中的任务中转储。

请参阅ConcernedOfTunbridgeWells的帖子,了解如何将DTS包保存到VB文件。保存VB文件后,对其运行此函数。它将在包含包中代码的VB文件所在的位置创建一个文件夹,并转储找到的SQL代码。它假设SQL的输出进入CSV文件(参见 outExt

如果你愿意的话,请随意清理一下这段代码。

# from __future__ import with_statement  # Version 2.5 requires this.
import os, re

def dump_sql(infile, outExt=r'csv'):
    """Parse a DTS package saved as a .bas file, and dump the SQL code.

    Pull out the SQL code and the filename for each task.  This process
    depends on the way that DTS saves packages as VB modules.

    Keyword arguments:
    infile - The .bas file defining a DTS package.
    outExt - The extension (without a period) of the files exported by the
             data pumps in the DTS package. These are used to rename the
             extracted SQL scripts. If an extract file does not use this
             extension, then the whole name of the extract file is used to
             name the SQL script. (default: csv)

    The function produces a folder in the same folder that contains the
    .bas file. It's named like this: if the .bas file is "DTS package.bas",
    then the directory will be named "DTS package_SQL". The SQL scripts are
    stored in this folder.

    """
    #Declare all of the RE's used in the script here.
    basExtRE = re.compile(r'\.bas$', re.IGNORECASE)
    outExtRE = re.compile(r'\.' + outExt + r'$', re.IGNORECASE)
    startTaskRE = re.compile(r'Set oCustomTask(\d+) = oTask.CustomTask')
    startSqlRE = re.compile(
        r'oCustomTask(\d+)\.(?:Source)?SQLStatement = "(.*)"( & vbCrLf)?')
    nextSqlRE = re.compile(
        r'oCustomTask(\d+)\.(?:Source)?SQLStatement = oCustomTask\1\.'
        r'(?:Source)?SQLStatement & "(.*)"( & vbCrLf)?')
    filenameRE = re.compile(
        r'oCustomTask(\d+)\.DestinationObjectName = "(.*)"')
    descripRE = re.compile(r'oCustomTask(\d+)\.Description = "(.*)"')
    invalidCharsRE = re.compile(r'[][+/*?<>,.;:"=\\|]')

    #Read the file
    with open(infile, 'r') as f:

        #Produce the directory for the SQL scripts.
        outfolder = '%s_SQL\\' % basExtRE.sub('', infile)
        if not os.path.exists(outfolder):
            os.makedirs(outfolder)

        taskNum = -1
        outfile = ''
        sql = []

        for line in f:
            line = line.rstrip().lstrip()

            if taskNum == -1:
                #Seek the beginning of a task.
                m = startTaskRE.match(line)
                if m is not None:
                    taskNum = int(m.group(1))
            elif line == '' and outfile != '':
                #Save the SQL code to a file.
                if sql:
                    if os.path.exists(outfile):
                        os.unlink(outfile)
                    with open(outfile, 'w') as fw:
                        fw.writelines(["%s" % sqlQ for sqlQ in sql])
                    print "%2d - %s" % (taskNum, outfile)
                else:
                    print "%2d > No SQL (%s)" % (
                        taskNum, os.path.basename(outfile))
                sql = []
                outfile = ''
                taskNum = -1
            else:
                #Acquire SQL code and filename
                m = startSqlRE.match(line)
                if m:
                    #Start assembling the SQL query.
                    tnum, sqlQ, lf = m.groups()
                    assert int(tnum) == taskNum
                    sql = [sqlQ.replace('""', '"')
                           + ('\n' if lf is not None else '')]
                    continue
                m = nextSqlRE.match(line)
                if m:
                    #Continue assembling the SQL query
                    tnum, sqlQ, lf = m.groups()
                    assert int(tnum) == taskNum
                    sql.append(sqlQ.replace('""', '"')
                               + ('\n' if lf is not None else ''))
                    continue
                m = descripRE.match(line)
                if m:
                    # Get a SQL output filename from the task's
                    # description.  This always appears near the top of the
                    # task's definition.
                    tnum, outfile = m.groups()
                    assert int(tnum) == taskNum
                    outfile = invalidCharsRE.sub('_', outfile)
                    outfile = "%s%s.sql" % (outfolder, outfile)
                    continue
                m = filenameRE.match(line)
                if m:
                    # Get a SQL output filename from the task's output
                    # filename.  This always appears near the bottom of the
                    # task's definition, so we overwrite the description if
                    # one was found earlier.
                    tnum, outfile = m.groups()
                    assert int(tnum) == taskNum
                    outfile = os.path.basename(outfile)
                    outfile = outExtRE.sub('', outfile)
                    outfile = "%s%s.sql" % (outfolder, outfile)
                    continue
    print 'Done.'