May 11, 2012 at 12:08 am
How do i pass variable value from a table in ssis?
May 11, 2012 at 2:32 am
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.
May 11, 2012 at 11:24 am
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?
May 11, 2012 at 12:48 pm
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
May 11, 2012 at 6:28 pm
(' 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.
May 14, 2012 at 12:46 am
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply