Hi:

I've covered a number of newsgroups and web sites today, but still cannot
seem to find out why my DTS package (which works fine via SQL) fails to work
correctly when I execute it from a C# program.
Here is the code I use:


object pVarPersistStgOfHost = null;

string val;


try

{

//pkg.LoadFromSQLServer("(Local)", "sa","",0, "", "","", "authorsTest",ref
pVarPersistStgOfHost);

pkg.LoadFromSQLServer("(local)",
"sa","",DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag _Default, "", "","",
"authorsTest",ref pVarPersistStgOfHost);

val=pkg.GlobalVariables.Item("city").Value.ToStrin g();

label1.Text=val;

pkg.GlobalVariables.Remove("city");

DTS.GlobalVariable gv=pkg.GlobalVariables.New("city");

gv.Value="Oakland";

//pkg.GlobalVariables.Item("city").Value="Oakland";

pkg.GlobalVariables.Add(gv);

label1.Text+=" *** "+pkg.GlobalVariables.Item("city").Value.ToString( );

pkg.Execute();

pkg.UnInitialize();

label1.Text+=" **** Success !!";

}

catch (Exception ex)

{

label1.Text=ex.ToString();

}



The label always displays the correct values too, but the spreadsheet
(except for the column headers, is empty....no data returned)



Thanks in advance for any help



Ken

Have a look at these couple of articles/sites


DTS Global Variables and Visual Basic .Net
(http://www.sqldts.com/default.aspx?265)

Converting a DTS Package from Visual Basic 6.0 to Visual Basic .Net
(http://www.sqldts.com/default.aspx?264)

http://www.sqldev.net/dts/DotNETCookBook.htm

--
--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Keng" <nospam (AT) nospam (DOT) com> wrote

I've been to those sites.....tried the sample code......doesn't work
on three different computers.

Thanks for the reply..




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

Errors ?


"KenG" <keng (AT) themailroom (DOT) com> wrote

In article <3d7721e9.0311030806.517bae16 (AT) posting (DOT) google.com>, KenG
<keng (AT) themailroom (DOT) com> writes
How do you know if the package works or not?

You don't, all you know is that you loaded a package and you executed
it,, not the result of that execution at either a package or individual
task or step level.

Have a look at each step object and check the ExecutionResult and
preferably call the GetExecutionErrorInfo method for the full error
message.


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

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

Ken,

I faced the same problem you did, and found an answer at a website I
now plan to frequent:
http://www.c-sharpcorner.com/Code/2002/Sept/DTSnCS.asp


At any rate, it boils down to using the "let_value" method off the
GlobalVariables.Item object. So to set a global variable's value you'd
do the following:

objDTSPkg.GlobalVariables.Item("MyGlobalVariable") .let_value("NewValue");


What I also found (I'm not sure why this is so) is that when you use
this method,there is no need to remove and re-add the global variable
from the package. So I just did a

objDTSPkg.LoadFromSQLServer

and then used the let_value method directly.

Hope this helps.

Prem Isaac
creationmatters (AT) yahoo (DOT) com

"Keng" <nospam (AT) nospam (DOT) com> wrote