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").Custom Task
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

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 (AT) msn (DOT) com> wrote

No, I didn't know I could do it all in T-SQL... Basically, my task is to
take many different sql queries and combine them into one automated
procedure of which the "execute sql task" below is a part (it's a process
that takes an initial input file and processes it, saves results to various
tables, then creates a final completed .txt file that another company uses
to produce physical output). I assumed that a DTS Package was the easiest
route. Is it?

I'll check out the link. BTW, your site has already been extremely helpful
to me, thanks!

"Darren Green" <darren.green (AT) reply-to-newsgroup-sqldts (DOT) com> wrote

Doing it in T-SQL, I only I meant the bit about checking if a table
exists and creating a different one, with date parts in the name, etc,
the long SQL you generate could just be done in a single T-SQL relieving
you of the need to generate the statement each time. Basically you could
loose that ActX script you posted below, and just have the Exec SQL
Task.


Darren

In message <2ll315Fd49avU1 (AT) uni-berlin (DOT) de>, Sayonara <me (AT) msn (DOT) com> writes
--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

The article you specified noted an "Intermediate Level" of knowledge is
required. Maybe I haven't hit that level yet, but I can't seem to grasp how
to do what you say...

Basically, you're saying to take the loop I have currently and convert it
into a stored procedure, then somehow set the return value of that procedure
equal to the global variable?

How can I set the return value equal to said variable? Doesn't seem
possible...

Thanks!

"Darren Green" <darren.green (AT) reply-to-newsgroup-sqldts (DOT) com> wrote

In message <2llbk4Fe7l6jU1 (AT) uni-berlin (DOT) de>, Sayonara <me (AT) msn (DOT) com> writes
Add the following SQL to an Execute SQL Task, and map the Output
Parameter "TableName", using "Row Value" to your global variable.


SET NOCOUNT ON
DECLARE @TableNameBase nvarchar(128)
DECLARE @TableName nvarchar(128)
DECLARE @Suffix int
DECLARE @CreateTable nvarchar(4000)

SET @Suffix = 97
SET @TableNameBase = 'de_' + RIGHT(CONVERT(char(8), CURRENT_TIMESTAMP,
112), 4)
SET @TableName = @TableNameBase

WHILE EXISTS(SELECT 1 FROM dbo.sysobjects WHERE name = @TableName AND
type = 'U')
BEGIN
SET @TableName = @TableNameBase + CHAR(@Suffix)
SET @Suffix = @Suffix + 1
END


SET @CreateTable = '
CREATE TABLE ' + @TableName + '
(
Col1 int NOT NULL,
Col2 int NOT NULL
)'

EXEC(@CreateTable)

SELECT @TableName AS TableName

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Thank you tremendously Darren, make no mistake, I deeply appreciate you
sharing your time and experience with me.

Having said that, I suspect that while I'm not in over my head, I'm
definitely treading water... You've already done so much, but is there
anyway you could go through this and comment each section? I could just cut
n paste in all this stuff, but I'm not truly understanding *why.* I don't
want to be the atypical monkey-in-a-spaceshuttle if you know what I mean...
I want to learn the what, why's, & hows so I become a code-giver, not a
code-taker.

Thanks!

"Darren Green" <darren.green (AT) reply-to-newsgroup-sqldts (DOT) com> wrote

Firstly does it work, and do what you want?
What area don't you understand?

"Sayonara" <me (AT) msn (DOT) com> wrote

Yes, it appears to work, though I'd have to swap out the incrementing
97, 98, etc... for a series of if statements (my boss really wants his
tables in "a, b, c" format.

Basically, this is where my confusion lies.

Your comment concerning "Row Value" is confusing me (can't find any
reference in books online). How do I get the SQL variable @TableName to talk
to my DTS global variable? Thanks!

Duh.
97 is the numeric reference for "a."

I'm gettin there...