I had put out a question earlier today that may need to be re-asked.

Can the osql be used in DTS?

I have to create a bunch of files, that will need the same date & time stamp
glommed onto the first part of the row.

I do this by putting the following code in the SQL I have in the
transformation:

DECLARE @curDate As DateTime
DECLARE @curTime As DateTime

SET @curDate = GetDate()
SET @curTime = GetDate()

declare @charDate as char(8)
declare @charTime as char(8)

set @chardate = convert(char(8),@curDate,112)
set @charTime = STUFF(STUFF(convert(char(8),@curTime,114),3,1,''), 5,1,'')

Once that's in there, I would pass in the two parameters @chardate &
@chartime into a stored procedure that I would call. I want that stored
procedure to then use those 2 parameters and create a header record.

Then, I'd want to call another stored procedure and have it output to a
different text file, for the next set of records, while keeping the same
@chardate & @chartime I had above in the first stored procedure to be
glommed onto the records in the 2nd table. And so on.

I need to have the same @chardate & @chartime on each of the records in
order to identify it as a "batch" within the external system.

I know that osql can use a /o command to put the output to a particular
file, but not sure if it can be done within DTS.

Ideally, I'd like to put my code for the select statement into a stored
procedure and then call it, but not sure if oSql is the way to go or not.

Any advice appreciated.

DTS can use Dynamic Properties tasks. If those aren't enough, you can use
ActiveX scripting. Also, ExecSQL tasks can take parameters. You don't need
osql.

--
Tom

---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql


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

I had put out a question earlier today that may need to be re-asked.

Can the osql be used in DTS?

I have to create a bunch of files, that will need the same date & time stamp
glommed onto the first part of the row.

I do this by putting the following code in the SQL I have in the
transformation:

DECLARE @curDate As DateTime
DECLARE @curTime As DateTime

SET @curDate = GetDate()
SET @curTime = GetDate()

declare @charDate as char(8)
declare @charTime as char(8)

set @chardate = convert(char(8),@curDate,112)
set @charTime = STUFF(STUFF(convert(char(8),@curTime,114),3,1,''), 5,1,'')

Once that's in there, I would pass in the two parameters @chardate &
@chartime into a stored procedure that I would call. I want that stored
procedure to then use those 2 parameters and create a header record.

Then, I'd want to call another stored procedure and have it output to a
different text file, for the next set of records, while keeping the same
@chardate & @chartime I had above in the first stored procedure to be
glommed onto the records in the 2nd table. And so on.

I need to have the same @chardate & @chartime on each of the records in
order to identify it as a "batch" within the external system.

I know that osql can use a /o command to put the output to a particular
file, but not sure if it can be done within DTS.

Ideally, I'd like to put my code for the select statement into a stored
procedure and then call it, but not sure if oSql is the way to go or not.

Any advice appreciated.

Don't know if I mentioned it, but I am on SQL 7.


"Tom Moreau" <tom (AT) dont (DOT) spam.me.cips.ca> wrote

No, you didn't. In that case, you can still use ActiveX Scripting to modify
the properties of other tasks within the package. You still don't need
osql.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
..
<me (AT) privacy (DOT) net> wrote

Don't know if I mentioned it, but I am on SQL 7.


"Tom Moreau" <tom (AT) dont (DOT) spam.me.cips.ca> wrote

Can you provide a link to some examples?

I have a general idea of what I want to do, but do not know if I can create
the variable I need to pass into a stored procedure and have it function
properly.


"Tom Moreau" <tom (AT) dont (DOT) spam.me.cips.ca> wrote

Here's a snippet from an article I wrote some time ago:

Function Main ()
Dim pkg, task, spid
Set pkg = DTSGlobalVariables.Parent
Set task = pkg.Tasks ("DTSTask_DTSDataPumpTask_1")
spid = DTSGlobalVariables ("spid")
task.CustomTask.SourceSQLStatement = "sp_who " _
& CStr (spid)
Set task = Nothing
Set pkg = Nothing
Main = DTSStepScriptResult_ExecuteTask
End Function
In this example, you're seeing the code for an ActiveX Script task. It is
modifying the SQL query used for a Data Pump task but the idea is the
same.You pass variables around your package through global variables. In
this case, spid would have been set by one part of the package and picked up
in this script.HTH
--
Tom

---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql


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

Can you provide a link to some examples?

I have a general idea of what I want to do, but do not know if I can create
the variable I need to pass into a stored procedure and have it function
properly.


"Tom Moreau" <tom (AT) dont (DOT) spam.me.cips.ca> wrote

Tom:

Thanks for the info.

In between when I posted and when I read your response, I started looking at
the Execute SQL Task properties, where it'll let you run some SQL code.

I have the following in it:

DECLARE @curDate As DateTime
DECLARE @curTime As DateTime

SET @curDate = GetDate()
SET @curTime = GetDate()

declare @charDate as char(8)
declare @charTime as char(8)

set @chardate = convert(char(8),@curDate,112)
set @charTime = STUFF(STUFF(convert(char(8),@curTime,114),3,1,''), 5,1,'')

exec sp_Test_Steve_SP @chardate,@charTime

but is there a way to direct the output of the Exec statement to say
\\myserver\mydirectory\file1.txt?

Yours may be the better way to do this, and I don't know if it can be done
via this task or not.

ANy ideas?

I may revisit your code if I cannot make this one work the way I want it to.

Thanks,
SC


"Tom Moreau" <tom (AT) dont (DOT) spam.me.cips.ca> wrote

Yep. Use as DTS Data Pump task and use the stored proc call in the SQL
Query portion of the Source tab of the properties.

--
Tom

---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql


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

Tom:

Thanks for the info.

In between when I posted and when I read your response, I started looking at
the Execute SQL Task properties, where it'll let you run some SQL code.

I have the following in it:

DECLARE @curDate As DateTime
DECLARE @curTime As DateTime

SET @curDate = GetDate()
SET @curTime = GetDate()

declare @charDate as char(8)
declare @charTime as char(8)

set @chardate = convert(char(8),@curDate,112)
set @charTime = STUFF(STUFF(convert(char(8),@curTime,114),3,1,''), 5,1,'')

exec sp_Test_Steve_SP @chardate,@charTime

but is there a way to direct the output of the Exec statement to say
\\myserver\mydirectory\file1.txt?

Yours may be the better way to do this, and I don't know if it can be done
via this task or not.

ANy ideas?

I may revisit your code if I cannot make this one work the way I want it to.

Thanks,
SC


"Tom Moreau" <tom (AT) dont (DOT) spam.me.cips.ca> wrote

I must be missing something.

I don't see anything about DataPumpTask in the Execute SQL Task Properties
in that thing.

I created a Data Driven Query Task in the designer. It has a SQL Query
button on the Source tab, but it maps to one file only (one of the text
files I created earlier, I set up an OLE DB source, and a text file
destiantion, and did my formatting on the transformation).

I need to do one of the following:

1. do 4 selects on a couple of tables & throw it ALL in to 1 file, or
2. do a single sql query so I can pass in my date & time (to batch them
together) and create 4 text files, which I'll combine into a single file in
another step.

Will the Data Driven Query Task throw all results into a single file?

SC



"Tom Moreau" <tom (AT) dont (DOT) spam.me.cips.ca> wrote

BTW, each one of these queries has in it the same first 18 characters (or
something like that), but from that point on, the fields (although they are
all char), differ in fields and field length. The record itself is the same
number of characters, but you might have a 50 character field in a record
generated from Query #2, where that same 50 characters may have 5 fields in
it from Query #3. Basically, it's trying to create an EDI type of output
file.

SC




"Tom Moreau" <tom (AT) dont (DOT) spam.me.cips.ca> wrote