DTS fails Global Variable problem

[thendrickson]

I have a pretty simple DTS package used to import data from a tab delimited text file.

I have declared a global variable and )created a task to set the variable) which contains the path to the text file

When I set a hardcoded path as the default value, the package executes fine.

But when I remove the default and try to pass in the exact same value, the package fails (file not found).


I call the DTS package from a stored procedure.


The following is the part of the stored procedure that is failing.

declare @test varchar(255)
set @test = 'GlobalVariables("sfilename").Value'
exec @hr = sp_OASetProperty @oPkg,@test,@path


EXEC @hr = sp_OAMethod @oPKG, 'Execute'

@oPkg is the package, @path contains the full path to the file.

 

[Narizz28]

This may or may not be correct. It's posted from a VBScipt'er who dables in SQL. ;-)

Take the quotes off ("sfilename"), it's taking it literally as the string to the path instead of the variable value.

 

[thendrickson]

I tried that first thing, but I am using samples I found as a guide and this sure looks like what I found.

I set the varable @test then printed it to the screen to be certain I was in fact passing the variable in as described. It seems identical to my eyes.

Additionally I tried changing the double quotes to a pair of single quotes, but that gave a syntax error.

I went to using Bulk Insert instead which solved my immediate problem but does not help me in the long run.

I really would like to know why this failed.

 

[nigelrivett]

set @test = 'GlobalVariables("sfilename").Value'

This will set @test to that string not it's contents.


What are you trying to do? Set a global variable in a package before running it or retrieve a global variable.
If the formaer then see

http://www.nigelrivett.net/sp_oacreateLoadDTSpackage.html

exec @rc = sp_OASetProperty @objPackage,
'GlobalVariables("sfilename").value', @value

But it is easier to use dtsrun to set global variables unless you have a reason to use the ole SPs.


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[thendrickson]

Thank you Nigel

I am trying to set a global variable in a package before running it.

The variable is the path of the text file I am importing from.

This is exactly how I wrote it the first time

exec @HR = sp_OASetProperty @objPackage,
'GlobalVariables("sfilename").value', @path

Setting @test to the string was an atempt to prove to myself that I was actually passing in what I thought I was.
Either way it should have worked.

By the way, I had found this link from another post and knowing your skill level, I was using it as a guide.

Additionally, I found similar examples elsewhere.

That is why I am so puzzeled. Your examples always seem to work.

And I do have a reason for usinr the OLE sp.

Perhaps it is not the best reason, but stored procedure that runs the DTS packages will be used for many customers.

I have read several posts of problems with dtsrun and although the problems appear to be easily fixable, I chose to use OLE sp's to reduce the possiblility of a problem.

My Uncle knew Murphy and always claimed Murphy was an optimist and that "what cannot possibly go wrong will"

Currently I am using Bulk Insert instead of the DTS package. From my perspective there is not much difference customizing the stored procedure over the DTS package, except that any of engineers withthe company I am doing this for can use the wizard for creating the very simple DTS package if I can just figure out why it fails to set the Global Variable

 

[nigelrivett]

Well tht should work.
Try logging the value from the dts package so you can see what is being set.

How are you setting the filename from the global variable?

(I prefer bulk insert to dts if it is usable - tends to be simpler).

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[thendrickson]

I created a global variable.

I then set connection 1 data source value property to that variable in a task

I then edit the workflow property for this task to execute before the copy ccurs

The precedence works.

And yes, I think Bulk Insert is better. I am doing some studies now to see if I can learn to transform data more easily using Bulk Insert for future use, but I am concerned that the individuals who may need to change these packages may only be minimally qualified.