declaring variables in a DTS package

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

declaring variables in a DTS package

Author

Topic

lauramayer
Posting Yak Master

152 Posts

Posted - 2004-04-30 : 07:48:07

Good morning,

I have a package that passes 2 global variables. strSource and strTrailerID

I wrote it like this:

exec dbo.PS_FailedUpLoad_Check dtsglobalvariable.strSource, dtsglobalvariable.strtrailerID

I get the error:

incorrect syntax near "."

Can anyone tell me what it should look like?

Thanks

Laura

cas_o
Posting Yak Master

154 Posts

Posted - 2004-04-30 : 11:13:10

is this statement inside your package? can we see the DDL for the sp?

of the full stops in your statement, my gut instinct is to drop the dbo. in front of the sp name.

;-]... Quack Waddle

lauramayer
Posting Yak Master

152 Posts

Posted - 2004-04-30 : 11:26:19

no its not. It's a long and complicated one. I can put the first step though....

ALTER PROCEDURE [dbo].[PS_FailedUpLoad_DataCheck]
@strTrailerID char(20),
@strSource char(20)
AS

declare @bErr int
select @bErr=0

startStep1:
--finds 3rd party exceptions
begin tran Step1
BEGIN
Insert into tblFailedUpLoad (Source,Location,TrailerID, TrailerType,Classification,Contents, Comments,UpLoadDate,UpLoadTime,FailedCode )
SELECT tblM5UpLoad.Source, tblM5UpLoad.Location, tblM5UpLoad.TrailerID, tblM5UpLoad.TrailerType, tblM5UpLoad.Classification, tblM5UpLoad.Contents, tblM5UpLoad.Comments, convert(varchar, tblM5UpLoad.timestamp, 101)as UpLoadDate, LEFT (CONVERT(char(19), tblM5UpLoad.timestamp,108),5) as UpLoadTime, 'May be a C&S Trailer' as FailedCode
FROM tblM5UpLoad LEFT JOIN tblTrailerInfo
ON tblM5UpLoad.TrailerID = tblTrailerInfo.TrailerID WHERE tblM5UpLoad.TrailerType ='3' AND tblTrailerInfo.TrailerID Is Not Null AND tblM5UpLoad.TrailerID=@strTrailerID AND tblM5UpLoad.Source=@strSource

IF(@@ERROR<>0)
GOTO errStep1

BEGIN
delete tblM5UpLoad FROM tblM5UpLoad LEFT JOIN tblTrailerInfo ON tblM5UpLoad.TrailerID = tblTrailerInfo.TrailerID WHERE tblM5UpLoad.TrailerType ='3' AND tblTrailerInfo.TrailerID Is Not Null
AND tblM5UpLoad.TrailerID=@strTrailerID AND tblM5UpLoad.Source=@strSource
END
END

IF(@@ERROR=0) goto exitStep1

errStep1:

Rollback tran Step1
Goto exitStored

exitStep1:
Commit tran Step1
select @bErr=0

--Return 1
startStep2:

cas_o
Posting Yak Master

154 Posts

Posted - 2004-04-30 : 12:06:57

Ok so we have established dbo is the owner of the sp. thats good.

quote:
no its not

no it's not in the DTS package? global variables are only global to the steps in the package.

Looking at BOL the examples are:

DTSGlobalVariables("strSource")

try that instead of DTSGlobalVariables.strSource

;-]... Quack Waddle

lauramayer
Posting Yak Master

152 Posts

Posted - 2004-04-30 : 12:35:57

by statement do you mean the proc? Then no, the global variables are set though. Could it be the order of the steps? I have a text file ---> to a table then on completion it runs the execute sql task. Could that be my problem?

Laura

cas_o
Posting Yak Master

154 Posts

Posted - 2004-04-30 : 15:08:20

No I think it's syntax. the error says incorrect syntax. I looked in BOL for you under global variables and the syntax looks different there as opposed to your syntax.

Look at my previous post.

so instead of:
exec dbo.PS_FailedUpLoad_Check dtsglobalvariable.strSource, dtsglobalvariable.strtrailerID

do:
exec dbo.PS_FailedUpLoad_Check dtsglobalvariable("strSource"), dtsglobalvariable("strtrailerID")

;-]... Quack Waddle

lauramayer
Posting Yak Master

152 Posts

Posted - 2004-05-01 : 07:53:52

Thanks for all the help. This is the first time I've tried to do something like this so it's very confusing.

Thanks again

Laura

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources