ssis variables


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

avatar image

30 13 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

avatar image

5.3k 66 69 77

@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

more ▼

answered Apr 19, 2012 at 10:43 AM

avatar image

26.2k 18 38 48

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.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Apr 19, 2012 at 10:20 AM

Seen: 746 times

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

Copyright 2018 Redgate Software. Privacy Policy