I have some sql code that I'm working on that I need to create a text file
as it's output. I'm creating a text file that's more or less just a flat
file, that has 01, 02 and 05 level records in it.

The 01 level is a header, with a dollar amount in a field on it. Haven't
figured out how to do this one yet.

The next records are a soldto and a shipto records. For each row on my
source table, I'll create 2 02 records, one with soldto info on it, one with
shipto info on it.

Next are the 05 records, with items on it. I can create 1+ of these.

The sql as it is now (in query analyzer) is a bunch of select & formatting
statements to get the info out on the record. I have a variable where I set
the time when it loads, in order to pass the same date & time stamp to each
of the records (because the 02's are created in a different place than the
05's, they could have a different timestamp). The variable is @curtime and
it's datetime and set to getdate(). I then format it as I need to.

The 05 records are built from a second table, where I do similar formatting
on the record, and then do an inner join on an orderid field that's in the
main table.

The fields are like this:

date, time, orderid, reclevel.... misc data....

So when if I need to sort it, the orderid would force the 01's, 02's and
05's for customer X together, as apposed to putting customer Y's data
intermingled with it (the orderno has a timestamp & other info in it).

Question is - since this is really 3+ queries, how can I do this in DTS? I
need to pass on the same date & time variable into each of the 3 selects,
but DTS will only process the first select statement.

Any help appreciated.

Thanks,

SC

You could maybe do this by pumping to 3 text files then using the dos COPY
command amalgamate

COPY A.txt+B.txt+C.txt D.txt



--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
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


<me (AT) privacy (DOT) net> wrote

Allan:

How do you run a DOS command from within DTS?

SC
"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

look at the ExecuteProcess task

How to manipulate the Execute Process task.
(http://www.sqldts.com/default.aspx?251)

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
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


<me (AT) privacy (DOT) net> wrote

I've got the DOS stuff ready to go, where it'll work like it is supposed to,
but when I try to run this, I get an error.

I make a few passes at the data to create the proper 01, 02 and 05 records
in this same DTS job, then I drug the ActiveX Script Task from the Task
section of the DTS menu over into the designer, and put the code from the
page you had referred me to into the window in the package.

I think the line it is having a problem with is this one:

set cus = pkg.Tasks("DTSTask_DTSCreateProcessTask_1").Custom task

When I attempt to execute the package, the error message is a Package Error.
The box says, "Microsoft Data Transformation Services (DTS) Package Task
'DTSTask_DTSCreateProcessTask_1' was not found.

I'm running this on a box with SQL7 on it.

Is this compatible with SQL7?

Here is the entire code:

Function Main()

dim pkg
dim cus
dim strCmdLine

set pkg = DTSGlobalVariables.Parent

set cus = pkg.Tasks("DTSTask_DTSCreateProcessTask_1").Custom task

'Build the commandline string

strCmdLine = "copy test1.txt+test2.txt+test3.txt+test4.txt+test6.txt
test5.txt /y"
' & _ DTSGlobalVariables("gv_strTextFilename").Value


'assign it to the ProcessCommandline property

cus.ProcessCommandLine = strCmdLine

'Assign our success return code

cus.SuccessReturnCode = DTSGlobalVariables("gv_i_SuccessCode").Value

'How long do we want to wait for the process to finish ?

cus.Timeout = DTSGlobalVariables("gv_i_Timeout").Value

'If we reach our timeout value should we terminate the process ?

cus.TerminateProcessAfterTimeout = _
DTSGlobalVariables("gv_b_TermAfterTimeout").Value


'Clean up

set pkg = nothing

Main = DTSTaskExecResult_Success
End Function


A second problem I am having is passing the variable curtime into each of
the passes on the data. It's set to be a particular date & time set when
the process starts, to ensure that the date & time for each of the passes is
set to the same date & time.

Thanks,

SC


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

In 7 they were called CreateProcess tasks

If you right click on the task | Workflow | Workflow Properties

It will tell you the name of the task.



--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
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


<me (AT) privacy (DOT) net> wrote

Hi Allan & SC -
I'm having the same problem below(Microsoft Data Transformation Services (DTS) Package Task 'DTSStep_DTSExecuteSQLTask_10' was not found) even though I looked up the right name by going to Workflow properties and then options.

I'm doiong this on a SQL7 box.
Do you know what else I can do to resolve this error?
Appreciate any help!

Thanks
Jessica

"Allan Mitchell" wrote:

See my reply to your other message for the same problem (Error referencing
'DTSStep_DTSExecuteSQLTask_10' Task)

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
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


"uzoma" <uzoma (AT) discussions (DOT) microsoft.com> wrote

I looked up the right name by going to Workflow properties and then options.