Re: Can you use osql in DTS?
me_at_privacy.net
Date: 05/13/04
- Next message: Scott: "passing calculated parameter to a stored procedure"
- Previous message: me_at_privacy.net: "Re: Can you use osql in DTS?"
- In reply to: Tom Moreau: "Re: Can you use osql in DTS?"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 13 May 2004 16:16:27 -0400
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@dont.spam.me.cips.ca> wrote in message
news:%23pMEpyQOEHA.2780@TK2MSFTNGP09.phx.gbl...
> 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@privacy.net> wrote in message
> news:uFQ$SqQOEHA.3884@TK2MSFTNGP12.phx.gbl...
> 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@dont.spam.me.cips.ca> wrote in message
> news:edBl7JQOEHA.2780@TK2MSFTNGP09.phx.gbl...
> > 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@privacy.net> wrote in message
> > news:OMb0O3POEHA.1160@TK2MSFTNGP09.phx.gbl...
> > 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@dont.spam.me.cips.ca> wrote in message
> > news:etB1ruGOEHA.3752@TK2MSFTNGP12.phx.gbl...
> > > 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@privacy.net> wrote in message
> > > news:ukcBuNGOEHA.1456@TK2MSFTNGP09.phx.gbl...
> > > Don't know if I mentioned it, but I am on SQL 7.
> > >
> > >
> > > "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
> > > news:ekMmf$FOEHA.1160@TK2MSFTNGP09.phx.gbl...
> > > > 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.
> > > >
> > > >
> > >
> > >
> >
> >
>
>
- Next message: Scott: "passing calculated parameter to a stored procedure"
- Previous message: me_at_privacy.net: "Re: Can you use osql in DTS?"
- In reply to: Tom Moreau: "Re: Can you use osql in DTS?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
