I have a process where I need to create a fixed length text file with
a header section, details section, and a trailer section. I created a
DTS package that created the individual sections, then from a job
created for the DTS concatenated the three files together and ftp'd it
to a mainframe. This all worked great until they wanted the detail
section grouped and a sequence number put on one of the fields. The
only way I could figure out how to do it was by dumping the data into
a temporary table then use a cursor to go through the records one at a
time comparing my identifier and adding a sequence number if it was
the indentifier was the same. This works great in Query Analyzer, but
then when I move the detail into the package it complains about the
temp table. How do I resolve this? Can I not create and use a
temporary table in a DTS package?

Why does the table need to be temporary?
If the process is going to be repeated it may serve you to create a working
table and clearing it after each package run.


OK the temp table

Here is what I do

ExecuteSQL task to create the temp(#) table
DataPump task that uses that same connection as the source linked to the
ExecuteSQL task ON SUCCESS
The SourceSQLStatement of the datapump exposes the same metadata as my #
table

so

If my table is

CREATE TABLE #A(ColA int)


My SourceSQLStatement would be

SELECT 'A' as A

I then use either a Dynamic Properties task or a piece of VBScript to change
the SourceSQLStatement to the real value i.e.

SELECT a FROM #A

I do this right at the start of the package and join it onto the ExecuteSQL
task.

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Ken Grimmett" <ken_grimmett (AT) yahoo (DOT) com> wrote