Re: Can you use osql in DTS?

From: Tom Moreau (tom_at_dont.spam.me.cips.ca)
Date: 05/12/04 

Date: Wed, 12 May 2004 16:29:42 -0400

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@privacy.net> wrote in message
news:euEFc8FOEHA.2728@TK2MSFTNGP12.phx.gbl...
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.

Relevant Pages

  • Re: Can you use osql in DTS?
    ... This is the output from the stored procedure: ... charDate charTime username ... I also need to write a second query, that'll put the same charDate & ... > Columnist, SQL Server Professional ...
    (microsoft.public.sqlserver.dts)
  • Re: Can you use osql in DTS?
    ... This is the output from the stored procedure: ... charDate charTime username ... I also need to write a second query, that'll put the same charDate & ... > Columnist, SQL Server Professional ...
    (microsoft.public.sqlserver.programming)
  • Can you use osql in DTS?
    ... Can the osql be used in DTS? ... DECLARE @curDate As DateTime ... declare @charTime as char ... @chartime into a stored procedure that I would call. ...
    (microsoft.public.sqlserver.dts)
  • Can you use osql in DTS?
    ... Can the osql be used in DTS? ... DECLARE @curDate As DateTime ... declare @charTime as char ... @chartime into a stored procedure that I would call. ...
    (microsoft.public.sqlserver.programming)
  • Re: Can you use osql in DTS?
    ... Can the osql be used in DTS? ... declare @charDate as char ... declare @charTime as char ... @chartime into a stored procedure that I would call. ...
    (microsoft.public.sqlserver.programming)
Loading