Help, Please, wine and crackers...

I have read just about every posting in this group for the past 2 days
and can't find a solution to what I think should be a simple task.

I have created a DTS Package (SQL2000) that imports a .csv file into a
TempTable (I am importing a bunch of outlook pst's into a web app but
that's another story)

I got the first ActiveX workinging where I prompt the user to enter
the filename and import the correct filename & ".csv", the in the
Connection 1, the data is imported into the SQL Table Connection 2, so
far all is well.
Thus:
Function Main()
Dim oConn, sFilename, cnOwner
cnOwner = inputbox("Who is the PST Owner")
sFilename = cnOwner
sFilename = sFilename & ".csv"
Set oConn = DTSGlobalVariables.Parent.Connections("Connection 1")
oConn.DataSource = sFilename
Set oConn = Nothing
Main = DTSTaskExecResult_Success
End Function

The kicker for me, is after the data is copied, i am trying to run a
simple SQL Select statement that creates a new TempTable with all
fields from FirstTempTable and a new Column for the Filename (This is
the ContactOwner to be used further on down the line)
Thus:
SELECT cnOwner, * --cnOwner is what I am trying to pass
INTO tbl_NewTempContact
FROM tbl_TempContact

How do I pass the Variable cnOwner

I would gladly buy the next round for who ever could help me out with
this little dilemma.

Len

Will this work for you:
Create a new global variable, then in your ActiveX script set it:
DTSGlobalVariables("newGlobalVariable").Value = cnOwner

Then set up your Execute SQL Task so the sql looks like:
SELECT ?, *
INTO tbl_NewTempContact
FROM tbl_TempContact

click the parameters button for the Execute SQL Task and specify the input
parameter as the global variable newGlobalVariable (or whatever you decide
to name it)

HTH,
Justin


"Len B" <LenBoyer98 (AT) hotmail (DOT) com> wrote

That will produce a syntax violation

You can do it like this though in an activeX script task up front of the
ExecuteSQL task

Function Main()

dim pkg
dim tsk

set pkg = DTSGlobalVariables.Parent
set tsk = pkg.Tasks("DTSTask_DTSExecuteSQLTask_1").CustomTas k
tsk.SQLStatement = "SELECT '" & DTSGlobalVariables("ExtraVal").Value & "'as
NewCol,* INTO NewAuthors FROM authors"

Main = DTSTaskExecResult_Success
End Function


We discuss this here

Global Variables and SQL statements in DTS
(http://www.sqldts.com/Default.aspx?205)



--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Justin Engelman" <GlobNews (AT) adelphia (DOT) net> wrote

Ah, yes, it does cause a Syntax error... you can trick it though if you
wanted...
How to trick it so the Execute SQL Task will work as I stated (note one
other addition, need to give the ? a column name):

In the Execute SQL Task properties window
1) change the sql statement to: "select * from tbl_TempContact where 1 =
?" -- this is a statement that it can parse and will let you set parameters
for
2) click the parameters button and and set the first parameter to your
global variable
3) change the sql statement to:
SELECT ? as colName, *
INTO tbl_NewTempContact
FROM tbl_TempContact
4) click OK and run your package

if you try to parse the sql statement in step 3 you will get a syntax error,
same with if you try to click the parameters button at that point... but if
you run the package you will see that it works fine

enjoy the hack


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Justin,

Thank you sooo much, that worked beautifully! Let me know if your
ever in the Atlanta area, and I'll be glad to buy you a round of
drinks

Allan,

I also apreciated your solution, but I am still a little clueless on
when and to define Global Variables.

Len



"Justin Engelman" <GlobNews (AT) adelphia (DOT) net> wrote