how to pass parameters to execute SQL tasks? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2005 Forums

SSIS and Import/Export (2005)

how to pass parameters to execute SQL tasks?

Author

Topic

Dave_007
Starting Member

15 Posts

Posted - 2008-06-11 : 02:42:19

Hi,
I have a table with queries. I need to execute those queries and pass results into a variable. Then use that variable/result to execute other queries to make business decisions.
EXAMPLE:
TASK#1
TABLE-A :has queries below
select count(*) from employee
select count(* ) from mangers

I want to execute those queries and store results in @counts. How I execute all queries in table A and pass that to a variable?

TASK#2
Then I have another SQL task(may be SQL task) which use the value @counts make some decisions
If @count > 1 then pass
If @count <1 then fail

How can I do that?

I am still new to SSIS and not very familiar with variables. Any advice would be appreciated.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2008-06-11 : 02:48:43

1


Declare @Count1 Int
Set @Count1 = (Select Count(1) From Employees)
Or 
Select @Count = Count(1) From Employees

Above both queries will do the same, but its prefered to used the SET based query.

I guess logic for the second task you have mentioned it should work fine ...

Chirag

http://www.chirikworld.com

Dave_007
Starting Member

15 Posts

Posted - 2008-06-11 : 02:58:37

so if Table A structure is as below
TAble A
ID query
1 select count(*) from employee
2 select count(* ) from mangers

following will work in SSIS SQL task?

Declare @Count1 Int
Set @Count1 = (Select query From TableA)
Or
Select @Count = query From TableA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-11 : 03:44:50

quote:
Originally posted by Dave_007

so if Table A structure is as below
TAble A
ID query
1 select count(*) from employee
2 select count(* ) from mangers

following will work in SSIS SQL task?

Declare @Count1 Int
Set @Count1 = (Select query From TableA)
Or
Select @Count = query From TableA

Are you trying to assign value to variable created in ssis? if yes, you can use an Execute SQL task and map the variable to get result.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2008-06-11 : 03:56:32

Not sure what are you trying to do, if the queries are saved in the database and it would eventually get much complicated for you to fetch the records from it and its not also a good design too.

Here is the sample of what you would have to do ..


Your table Structure should be 
ID          Query
-----------------------------------------------------------
1           Select @Count = Count(1) From Company
2           Select @Count = Count(1) From TradingRequest


Declare @Query NVarChar (800),
		@Count Int ,
		@Count1 Int

Select @Query = Query From @t where Id = 1 

--[B] 
Execute sp_executesql @Query , N'@Count int output', @Count output

Select @Count

-- Similar for the query with the ID2 and then you require to compare it .. or else you have to run through a while statement..

Chirag

http://www.chirikworld.com

Dave_007
Starting Member

15 Posts

Posted - 2008-06-12 : 02:33:16

HI
thanks for info!!!!
I have another addition to this. My queries are from different servers and dbs. For an example query ID 1 is from server A, database 1. Query ID 2 is from server B, database 2. How can I change this in run time in SSIS when i run the package? I capture the server name and the database name in the table.
thanks again!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-12 : 03:08:21

quote:
Originally posted by Dave_007

HI
thanks for info!!!!
I have another addition to this. My queries are from different servers and dbs. For an example query ID 1 is from server A, database 1. Query ID 2 is from server B, database 2. How can I change this in run time in SSIS when i run the package? I capture the server name and the database name in the table.
thanks again!!

Do you mean mapping of server databases while you migrate package across environments. Then you need to look into books online about package configuration. you've different ways of doing it. Using xml file,environment variable,..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources