Re: Can you use osql in DTS?

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

  • Next message: Lontae Jones: "Restoring a .BAK file" 
    Date: Fri, 14 May 2004 16:51:02 -0400

    This sounds simple enough to do in a single package. You may be onto
    something with respect to populating a table and then using just one Data
    Pump to extract to the one file, using the transformation I showed you to
    "glom on the date & time" to the front of each record.

    So, it looks like you'll have a package like so:

    1.) ActiveX Script task to build the date and time strings to populate the
    global variables.
    2.) Data Pump task that executes a proc that populates a table and then
    selects the rows from that table. (It is possible that this proc calls 3
    other procs that each populate this table in stages.) This Data Pump uses a
    transformation to add on the date and time bits to each output record.

    It's possible that you may need a final task in your package to zip the file
    or copy it to a share or whatever.

    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:%23VYbGOfOEHA.680@TK2MSFTNGP11.phx.gbl...
Let me make sure I understand.
In DTS Design, put in Execute Package Task, and then have 3 more DTS' that
I'd call from it, using the date routine to put the date & time on
everything.  Either that, or do all of my sql in a single pass, and then use
the execute package task to call it, and glom on the date & time where I've
created the inner package global variables.
Instead of throwing each of these out into a text file to begin with, I
guess I would put them into a table and then use the code below to copy the
stuff out of the table and into a text file.
SC
"Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
news:O3sIqQeOEHA.3596@tk2msftngp13.phx.gbl...
> You don't need to have the stored proc produce those dates and times,
since
> they are already known.  Let's say you have a package that extracts the
> category names from Northwind.  Here is your source query:
>
> select
>  CategoryName
> from
>  Categories
>
> Now, you want to have that date and time stuff added to each row.  In your
> Data Pump Transformation, just add that to the first column of the query:
>
>
> '  Copy each source column to the destination column
> Function Main()
>  DTSDestination("CategoryName") = DTSGlobalVariables("TheDate").Value &
> vbTab & _
>   DTSGlobalVariables("TheTime").Value & vbTab & DTSSource("CategoryName")
>  Main = DTSTransformStat_OK
> End Function
>
>
> Note the use of global variables.  These would have been populated in an
> earlier task within the same package.
>
> 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:eEBEJDbOEHA.3096@TK2MSFTNGP09.phx.gbl...
> <<Looking at what you have here, I'd construct the charDate and charTime
>  values simply via VB Script inside an ActiveX Script task and then store
>  them as global variables in the package.>>
>
> I thought about doing this but wasn't sure how I'd reference them inside
the
> stored procedure I wanted to run.
>
> << Next, I'd modify the procs not to output the charDate and charTime.>>
>
> I have to have this.  The output (modified more than what my sample was),
is
> going out of this into an SAP application, from which EDI orders will be
> generated.  I have to have the date & time on these in order for the
remote
> system to identify them as being from the same batch.  These have to be on
> each of the records I create.
>
> <<After that, I'd make Data Pump tasks that use the procs as data sources
> and then add the charDate and charTime through the transformation inside
the
> Data Pump.>>
>
> This is what I did not know how to do.  If you know of a way to do this,
I'd
> appreciate any advice.  I know how to do some things in DTS, but none of
> them are very sophisticated.
>
> <<After all of those Data Pumps have completed successfully, you can do a
> Create Process task to use the OS copy  command to concatenate the
files.>>
>
> Believe it or not, I already have this working.  I was using the following
> script, that seems to concatenate them fine.  I have a couple stored
> procedures outputting test records (like the ones in my example) and am
> concatenating them together with the following:
>
> '**********************************************************************
> '  Visual Basic ActiveX Script
> '************************************************************************
>
> 'Script combines two files
>
> Function Main()
>
> Dim obj, file1, file2, file3, file4, file5, filesys, newfilename, newfile
>
> Set filesys = CreateObject("Scripting.FileSystemObject")
>
> newfilenm = "\\myserver\dir1\file9.txt"
> file1  =  "\\myserver\dir1\test1.txt"
> file2  =  "\\myserver\dir1\test2.txt"
> file3  = "\\myserver\dir1\test3.txt"
> file4  = "\\myserver\dir1\test4.txt"
> file5  = "\\myserver\dir1\test6.txt"
> file6 =  "\\myserver\dir1\test7.txt"
>
> filesys.CopyFile file1, newfilenm
>
> '8 = Append
> Set newfile = filesys.OpenTextFile(newfilenm, 8)
>
> '1 = Reading
> Set obj = filesys.OpenTextFile(file2, 1)
> newfile.Write obj.readall
>
> '1 = Reading
> Set obj = filesys.OpenTextFile(file3, 1)
> newfile.Write obj.readall
>
> '1 = Reading
> Set obj = filesys.OpenTextFile(file4, 1)
> newfile.Write obj.readall
>
> '1 = Reading
> Set obj = filesys.OpenTextFile(file5, 1)
> newfile.Write obj.readall
>
> '1 = Reading
> Set obj = filesys.OpenTextFile(file6, 1)
> newfile.Write obj.readall
>
>
> newfile.close
> obj.close
>
>  Main = DTSTaskExecResult_Success
> End Function
>
>
>
>
>
> >
> > -- 
> >    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:%23miRPwSOEHA.640@TK2MSFTNGP12.phx.gbl...
> > This is the output (test) from the stored procedure:
> >
> > charDate charTime username
> > -------- -------- ------------------------------ 
> > 20040513 164202   BUBBA
> > 20040513 164202   LEE
> > 20040513 164202   JIM
> >
> > (3 row(s) affected)
> >
> > when run in Query Analyzer.
> >
> > I also need to write a second query, that'll put the same charDate &
> > charTime on each row of the output, and write it to the same text file
(or
> a
> > new one).  However, I need to carry forward the parameters charDate &
> > charTime into each of the subsequent queries.  I'm passing them into the
> > stored procedure like this:
> >
> > exec sp_Test_Steve_SP @charDate, @charTime
> >
> > and I need the output of this stored procedure to go either into
file1.txt
> > or to have it go into dumpfile.txt (which would contain all of the
records
> > from each of the queries I would run after this one).
> >
> > If my next stored procedure
> > exec sp_Test_George_SP @chardate, @charTime
> >
> >  ran, I'd need for it to have the same values that went into the first
sp,
> > as well as writing to either file2.txt or appended to dumpfile.txt.
> >
> > At some point, I'd either send dumpfile.txt (which would have the
results
> of
> > all queries into one single file), or I would run an activex job to
> combine
> > file1.txt+file2.txt etc etc and "make" dumpfile.txt.
> >
> > From what I can tell, you can make as many output text files as you
want,
> > but I cannot then pull in the variables I need to be constant (@charDate
&
> > @charTime) throughout the run of this DTS job.
> >
> > I've got a working version of it where each of the files gets created,
but
> > it's a datasource, and a text file output, with the transformation
> pointing
> > to each individual output file.  However, they all have a different
value
> > for @charDate & @charTime on them.
> >
> > SC
> >
> >
> >
> >
> >
> > "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
> > news:uxOZmkSOEHA.2480@tk2msftngp13.phx.gbl...
> > > Maybe I'm missing something.  Does sp_Test_Steve_SP produce a row set?
> If
> > > so, you'd want a Data Pump to output the results to a file.  An
ExecSQL
> > task
> > > is not designed for that.
> > >
> > > -- 
> > > 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:OEJLpaSOEHA.3264@tk2msftngp13.phx.gbl...
> > > 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@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: Lontae Jones: "Restoring a .BAK file"