Execute SQL Task

Question

Execute SQL Task

Peter Newman-311337

Old Hand

Points: 394
More actions
March 31, 2006 at 8:34 am

#167545

i have an execute SQl task that returns a single field which i want to use to update a global variable. can abnybody talk me through how to do this please
April 3, 2006 at 8:00 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

omhoge SSCertifiable Points: 5416 More actions · Answer 1

I got that with a SQL Task, in the result set tab I set the Result name to the column "ID" (which is what's called in the SQL statement) and the variable to "User::typeID" which is a package int. variable. It seems to work.

Skål - jh

Peter Newman-311337 Old Hand Points: 394 More actions · Answer 2

thank you thats done the trick, now for the other direction, how to apply a glogal variable into a execute sql query task

global variable is Licence

if my sql statement is similar to this how can i use the global variable licence

Deacare @company varchar(10)

select @company = company from tablecompamy where licence = ( global variable )

omhoge SSCertifiable Points: 5416 More actions · Answer 3

I'm just begining too I *think* it goes like this. Be sure you are in task panel and not clicked into any tasks and add a variable, it should be package level scope. then in the SQL TASK expand the expressions list and look for SQLStatemnentSource source click the [...] button. in the little window that opens you will have to construct some dyno SQL like:

Select...

WHERE name =

'" + @[User::SQL_Name] + "'

AND objectID =

" + (DT_STR,5,1252)@[User::ItemtID] + "

AND contentTypeID =

" + (DT_STR,5,1252)@[User::TypeID] + "

AND latestVersion =

" + (DT_STR,5,1252)@[User::Version]

You could probably build it all into another variable, but I haven't figured that one out yet.

hth

Skål - jh

Peter Newman-311337 Old Hand Points: 394 More actions · Answer 4

This is what i have done, and for the life of me i carnt get it to work, If i substitie a value for the @glicence its fine works perfect, but trying to assing the @gLicence from a veriable just isnt happining

Execute SQl Task SQL Statement

Declare @gLicence varchar(6)

IF NOT EXISTS (SELECT *

FROM [Bossdata].[dbo].[ARUC_HEADERS]

WHERE Licence = @gLicence

AND AdviceNumber = (SELECT AdviceNumber

FROM [XML Processing].[dbo].[TempARUCHeader]))

INSERT [Bossdata].[dbo].[ARUC_HEADERS]

SELECT @gLicence, T1.ReportType, T1.AdviceNumber, T1.currentProcessingDate,

T2.UserName, T2.UserNumber,

T3.number, T3.Sortcode, T3.BankName, T3.BranchName, NULL AS 'Posted'

FROM [XML Processing].[dbo].[TempARUCHeader] AS T1 CROSS JOIN

[XML Processing].[dbo].[TempARUCServiceLicenceInformation] AS T2 CROSS JOIN

[XML Processing].[dbo].[TempARUCOriginatinAccountRecord] AS T3

i have two parameters mapped as follows:

Variable Name Direction Data Type Parameter Name

User::sLicence Input VARCHAR @gLicence

Variable is declared as datatype String and scope Package

for some reason the inset @glicence is inserting a NULL instead of the value assigned to sLicence. Ive spent hours on this and thus far come up with a blank.... help !!!