x

ssis variables

hello,

I need a solution to an ssis package. i need to know how to pass a value to a SSIS package variable.

the value comes from a sql task that runs a aql scripts that returns an integer value.

i need to then pass this value to the already created variable . from then i use this variable as part of a constraint on a connector with "expression and constraint"

i.e. @totalcount > 0 action -> (move on to next task0) @totalcount < 0 action -> (send out an email)

more ▼

asked Apr 19, 2012 at 10:20 AM in Default

Pinkett gravatar image

Pinkett
30 11 14 15

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

To pass the results of a SQL script to a variable you need to:

  1. Create the variable.
  2. In the SQL task under Result Set set the Result Name to be the name of the field returned from the SQL script then under Variable Name select the created variable.
Your variable is now available for use wherever you need it.
more ▼

answered Apr 19, 2012 at 10:47 AM

Mrs_Fatherjack gravatar image

Mrs_Fatherjack
4.7k 60 62 66

@Lerato Mankwe : Annette Allen correctly highlighted one of the ways to do that. But one important point is missing. Since you are returning only one value, and wanted to assign it to a variable, you should set the ResultSet to Single row on the General page. Otherwise, it will return error.

Also, the data type for the variable should be compatible with the data type of the column that the result set return.
Apr 19, 2012 at 11:06 AM robbin
(comments are locked)
10|1200 characters needed characters left

After a quick search on google I came up with the following article that seems to do what you want:

[Pass dynamic parameter values to SQL Server Integration Services][1]

[1]: http://www.mssqltips.com/sqlservertip/1395/pass-dynamic-parameter-values-to-sql-server-integration-services/
more ▼

answered Apr 19, 2012 at 10:43 AM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

Unfortunately, the link is not related to the OP's requirement.
Apr 19, 2012 at 11:08 AM robbin
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x939

asked: Apr 19, 2012 at 10:20 AM

Seen: 587 times

Last Updated: Apr 19, 2012 at 11:09 AM