Re: Can you use osql in DTS?
From: Tom Moreau (tom_at_dont.spam.me.cips.ca)
Date: 05/13/04
- Next message: Michael: "Data Driven Query deadlock"
- Previous message: Allan Mitchell: "Re: Identity key"
- In reply to: me_at_privacy.net: "Re: Can you use osql in DTS?"
- Next in thread: me_at_privacy.net: "Re: Can you use osql in DTS?"
- Reply: me_at_privacy.net: "Re: Can you use osql in DTS?"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 13 May 2004 11:53:21 -0400
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: Michael: "Data Driven Query deadlock"
- Previous message: Allan Mitchell: "Re: Identity key"
- In reply to: me_at_privacy.net: "Re: Can you use osql in DTS?"
- Next in thread: me_at_privacy.net: "Re: Can you use osql in DTS?"
- Reply: me_at_privacy.net: "Re: Can you use osql in DTS?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
