I'm running SQL Server 2000 and I need to export the SQL Statement from all the DTS objects so that they can be parsed and put into a wiki documentation if needed.
Is there a way to do that?
maybe dumping each DTS object out into a text file with the object name as the file name with the name of the process and the date it was extracted as a file header.
thanks.
There is an API with an object model for the DTS packages. You can get the SQL text through this. The Books on Line docs describe this to some extent Here. You can get examples of the object model usage by Saving the DTS package to a Visual BASIC file and looking at what the VB file does to the object model.
If you want to save some work and you don't mind paying a few bucks there's a tool that fully documents your DTS packages. It outputs to XML too, so it should be relatively easy to get those SQL statements.
I have a Python 2.6 script (easily portable to Python 2.5) that dumps the SQL from the tasks in a DTS package that has been saved as Visual Basic code.
Refer to ConcernedOfTunbridgeW's post to find out how to save the DTS package to a VB file. After you save a VB file, run this function on it. It will create a folder in the same location as the VB file which contains the code from the package, and it will dump the SQL code that it finds. It assumes the output of the SQL goes into CSV files (see the outExt parameter) or comes from an "Execute SQL Task" task, and names the SQL queries after the output file or SQL task. If your package doesn't do anything else, this is an useful solution.
Feel free to clean this code up if you're so inclined.
# 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.'