Re: Setting sql result to global variable?
From: Darren Green (darren.green_at_reply-to-newsgroup-sqldts.com)
Date: 07/14/04
- Next message: Sayonara: "Re: Setting sql result to global variable?"
- Previous message: Sayonara: "Setting sql result to global variable?"
- In reply to: Sayonara: "Setting sql result to global variable?"
- Next in thread: Sayonara: "Re: Setting sql result to global variable?"
- Reply: Sayonara: "Re: Setting sql result to global variable?"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 14 Jul 2004 16:35:17 +0100
Hmm, you do know that you could do all of that in T-SQL? Makes my eyes hurt
trying to look at it! Yes I know it works....
To get a value out of a task, then you need to use a resultset, so just use
SELECT 'TableNameXX' AS TableName as the last statement or thereabouts.
I suspect the design-time validation will fail, so use the workaround, of
some dummy SQL to set-up the parameter mapping, then the real SQL will get
stuffed in at run-time by your script task. The technique is described in
the "Input and Output Parameters" section of this article.
I also suggest you use SET NOCOUNT ON at the top of your script, also
described in the article.
Global Variables and Stored Procedure Parameters
(http://www.sqldts.com/default.aspx?234)
-- Darren Green http://www.sqldts.com "Sayonara" <me@msn.com> wrote in message news:2ll0mpFe2sacU1@uni-berlin.de... > Hey all, > > .The below code is included in the "Use ActiveX Script" area of an "Execute > SQL Task" in DTS. I know it's a sloppy loop, but I'm a newbie, and it works, > so... Anyways, the sqlstatement checks if a table exists, if it does, it > appends an "a" to the tablename and creates a new table, if "a" table exists > as well, then it creates a "b" table and so on... > > What I'm trying to do is set a dts global variable equal to whatever table > is eventually produced. Any tips or links are greatly appreciated! Thanks! > > > Function Main() > > Dim sqlstatement, objPkg, ExecSQL > > sqlstatement = "IF object_id('dbo.de_"& dtname() &"') IS NULL CREATE TABLE > [de_"& dtname() &"] (tables,etc...) ON [PRIMARY] ELSE IF > object_id('dbo.de_"& dtname() &"a') IS NULL CREATE TABLE [de_"& dtname() > &"a] (tables,etc...) ON [PRIMARY] ELSE IF object_id('dbo.de_"& dtname() > &"b') IS NULL CREATE TABLE [de_"& dtname() &"b] (tables,etc...) ON [PRIMARY] > ELSE IF object_id('dbo.de_"& dtname() &"c') IS NULL CREATE TABLE [de_"& > dtname() &"c] (tables,etc...) ON [PRIMARY] ELSE IF object_id('dbo.de_"& > dtname() &"d') IS NULL CREATE TABLE [de_"& dtname() &"d] (tables,etc...) ON > [PRIMARY] ELSE IF object_id('dbo.de_"& dtname() &"e') IS NULL CREATE TABLE > [de_"& dtname() &"e] (tables,etc...) ON [PRIMARY] ELSE IF > object_id('dbo.de_"& dtname() &"f') IS NULL CREATE TABLE [de_"& dtname() > &"f] (tables,etc...) ON [PRIMARY] ELSE IF object_id('dbo.de_"& dtname() > &"g') IS NULL CREATE TABLE [de_"& dtname() &"g] (tables,etc...) ON > [PRIMARY]" > > > ' Whatever table is eventually created above is what I wish to set the > global variable Raw_Table to > > DTSGlobalVariables("Raw_Table").Value = > > > ' Getting sql script to execute > > Set objPkg = DTSGlobalVariables.Parent > Set ExecSQL = objPkg.Tasks("DTSTask_DTSExecuteSQLTask_1").CustomTask > ExecSQL.SQLStatement = sqlstatement > > ' Reset object variables > > Set ExecSQL = Nothing > Set objPkg = Nothing > > Main = DTSTaskExecResult_Success > > End Function > > > ' Creates date-based name for table, ex., "de_0101" > Function dtname() > Dim d > d = date() > d = Right("0" & Month(d), 2) & Right("0" & Day(d), 2) > dtname = d > End Function > >
- Next message: Sayonara: "Re: Setting sql result to global variable?"
- Previous message: Sayonara: "Setting sql result to global variable?"
- In reply to: Sayonara: "Setting sql result to global variable?"
- Next in thread: Sayonara: "Re: Setting sql result to global variable?"
- Reply: Sayonara: "Re: Setting sql result to global variable?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
