variable value

Question

variable value

peacesells

SSCrazy

Points: 2882
More actions
May 11, 2012 at 12:08 am

#255807

How do i pass variable value from a table in ssis?

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

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

RSuman SSC Enthusiast Points: 121 More actions · Answer 1

You can use a EXEC SQL Task, fetch the column value from a table and pass it to a parameter. Attached images might help you.

Here, I am fetching the row count into a variable. Query will be like this.

SELECT ? = COUNT(*) FROM MDM_DB.team_centre_schema.ctrl_cost_types

Configure the output parameter in 'Parameter Mapping' tab of EXEC SQL Task and give its postion as 0 as the first '?' returns the value into corresponding variable.

peacesells SSCrazy Points: 2882 More actions · Answer 2

Raja Suman (5/11/2012)
You can use a EXEC SQL Task, fetch the column value from a table and pass it to a parameter. Attached images might help you.
Here, I am fetching the row count into a variable. Query will be like this.
SELECT ? = COUNT(*) FROM MDM_DB.team_centre_schema.ctrl_cost_types
Configure the output parameter in 'Parameter Mapping' tab of EXEC SQL Task and give its postion as 0 as the first '?' returns the value into corresponding variable.

Ok this should work fine in Execute SQL Task. However I have to pass values to parameter in ADO.NET source from a table? how would i do that?

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

Same process, you need to GET the value and then you can USE the value.

You can then likely pass the value in as a parameter to the DFT source.

CEWII

peacesells SSCrazy Points: 2882 More actions · Answer 4

(' select ?= replace((cast(convert(date,min(lastrundate))as varchar)),'-','') from TableA

I can't figure out what is wrong with this code? I am using above code in Excute Sql Task to get the minimum date from a table-convert it to string-remove the hyphens and populate that value to string variable in package and use that value for parameter in ado.net source downstream in DFT but i can't get this Execute SQL task to work? What possibly cud be wrong here.

The Error Reads:

[Execute SQL Task] Error: Executing the query "select ?= replace((cast(convert(date,min(lastrunda..." failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

i tried with different result sets settings-not helping.

RSuman SSC Enthusiast Points: 121 More actions · Answer 5

Your query is working fine.

select ? = replace((cast(convert(date,min(date_column))as varchar)),'-','') from schema.table

You may need to check some settings in your EXEC SQL Task.

Declare a package variable of string type

Keep 'Result Set' to 'None'

In 'Parameter Mapping' tab, select that variable, and use following settings.

Direction as Output

Data type as varchar

Parameter Name as 0

Run your task and it should load to variable.