how to pass parameters to an ado.net source in ssis?

Question

This is the original query, which works fine using ado.net source and using the .net providers\odbc data provider specified inside the source.

SELECT a.FA_CLNT_ID, a.FA_ACCT_NM, a.ACCT_E_DT, a.POL_PER_CURR_DT, a.POL_PER_NEXT_DT, a.FA_ACCT_NUM, a.GRP_SALES_OFFC_CD, a.ACCT_C_DT   
FROM  gyv2M.DDM_ACCT_STRUC a   
INNER JOIN  
(SELECT max(DDM_ACCT_STRUC_TP) as   DDM_ACCT_STRUC_TP, FA_CLNT_ID 
 FROM gyv2M.DDM_ACCT_STRUC  
 WHERE FA_DM_ROW_DT <= '6/30/2011' AND DM_ROW_E_DT <= '6/30/2011'
 GROUP BY  FA_CLNT_ID) b 
 ON a.DDM_ACCT_STRUC_TP = b.DDM_ACCT_STRUC_TP AND a.FA_CLNT_ID = b.FA_CLNT_ID AND FA_DM_ROW_DT <= '6/30/2011' AND a.DM_ROW_E_DT <= '6/30/2011'

but I need to make it automated for that DATE getting a value from a table in sql server. I created a variable to store that date value, but i want to use that variable as parameter to pass in to the above query. Unfortunately I didn't find the data access mode : sql command from a variable in the ado.net source. I seached some other sites and found that we can use some expression using expression builder. rt click on preperties ..> expressions ..> etc.. evaluated the expressions successfully. the expresion looks like below but I got struck there. What is next ? How do I pass the evaluated expression as a source command in ado.net source ? Please help.

SELECT a.FA_CLNT_ID, a.FA_ACCT_NM, a.ACCT_E_DT, a.POL_PER_CURR_DT, a.POL_PER_NEXT_DT, a.FA_ACCT_NUM, a.GRP_SALES_OFFC_CD, a.ACCT_C_DT   
FROM  gyv2M.DDM_ACCT_STRUC a   
INNER JOIN  
(SELECT max(DDM_ACCT_STRUC_TP) as   DDM_ACCT_STRUC_TP, FA_CLNT_ID 
 FROM gyv2M.DDM_ACCT_STRUC  
 WHERE FA_DM_ROW_DT <=  '@[User::RepDate]'  AND DM_ROW_E_DT <=  '@[User::RepDate]'
 GROUP BY  FA_CLNT_ID) b 
 ON a.DDM_ACCT_STRUC_TP = b.DDM_ACCT_STRUC_TP AND a.FA_CLNT_ID = b.FA_CLNT_ID AND FA_DM_ROW_DT <=  '@[User::RepDate]'  AND a.DM_ROW_E_DT <=  '@[User::RepDate]'

Update

Hi,

Thank you very much for your response. As you specified i creeated a user variable user:RepDate with datetime and an execute sql task that assigns datetime to that variable RepDate. And also Yes, I am able to evaluate the expression and i was able to see the query builded in the ado.net souce sql command mode. here is the query:

SELECT a.FA_CLNT_ID, a.FA_ACCT_NM, a.ACCT_E_DT, a.POL_PER_CURR_DT, a.POL_PER_NEXT_DT, a.FA_ACCT_NUM, a.GRP_SALES_OFFC_CD, a.ACCT_C_DT   
FROM  gyv2M.DDM_ACCT_STRUC a   
INNER JOIN  
(SELECT max(DDM_ACCT_STRUC_TP) as   DDM_ACCT_STRUC_TP, FA_CLNT_ID 
 FROM gyv2M.DDM_ACCT_STRUC  
 WHERE FA_DM_ROW_DT <= @[User::RepDate]  AND DM_ROW_E_DT <=  @[User::RepDate] 
 GROUP BY  FA_CLNT_ID) b 
 ON a.DDM_ACCT_STRUC_TP = b.DDM_ACCT_STRUC_TP AND a.FA_CLNT_ID = b.FA_CLNT_ID AND FA_DM_ROW_DT <=  @[User::RepDate] AND a.DM_ROW_E_DT <=  @[User::RepDate]

But I am receiving the following error given below. I think it's because the inside query is expecting date format 'mm/dd/yyyy' as I specified in the original query..but we are providing datetime from the user variable. Could you tell me how to convert the datetime format of user variable to the specified format using the inbuilt functions. I tried DT_DAte but no luck.

Error at Data Flow Task 4 [ADO NET Source [1]]: The component "ADO NET Source" (1) was unable to retrieve column information for the SQL command. The following error occurred: ERROR [42601] [IBM][CLI Driver][DB2] SQL0104N  An unexpected token "[" was found following "".  Expected tokens may include:  "CONCAT || / MICROSECONDS MICROSECOND SECONDS SECOND MINUTES".  SQLSTATE=42601
ADDITIONAL INFORMATION:

Pipeline component has returned HRESULT error code 0xC02020FF from a method call. (Microsoft.SqlServer.DTSPipelineWrap)
BUTTONS:
OK

***Update2***

TITLE: Microsoft Visual Studio

Error at Data Flow Task [ADO NET Source [1]]: The component "ADO NET Source" (1) was unable to retrieve column information for the SQL command. The following error occurred: ERROR [22007] [IBM][CLI Driver][DB2] SQL0180N The syntax of the string representation of a datetime value is incorrect. SQLSTATE=22007

---

ADDITIONAL INFORMATION:

Pipeline component has returned HRESULT error code 0xC02020FF from a method call. (Microsoft.SqlServer.DTSPipelineWrap)

---

BUTTONS:

OK

Answer 1

You can use the following steps:

1. Use your working query into the ADO.NET source.[You could not map the parameters for the ADO.NET source]
2. Select your Dataflow and click Properties.
3. In the properties pane, look for the property named "Expressions". Expand it and click the "..." icon to open the Property Expressions Editor (screenshot below). Select the property SqlCommand for your Data Source and using the Expression builder prepare your query using SSIS variables (datetime variable in your case).

Try this in your expression builder -- note that your variable must be a string, if it's not, you use (DT_WSTR,30) to cast it. Since you're using dates, your SQL will need to handle a string-formatted date, i.e. use to_date() in Oracle.

 "SELECT a.FA_CLNT_ID, a.FA_ACCT_NM, a.ACCT_E_DT, a.POL_PER_CURR_DT, a.POL_PER_NEXT_DT, a.FA_ACCT_NUM, a.GRP_SALES_OFFC_CD, a.ACCT_C_DT   
FROM  gyv2M.DDM_ACCT_STRUC a   
INNER JOIN  
(SELECT max(DDM_ACCT_STRUC_TP) as   DDM_ACCT_STRUC_TP, FA_CLNT_ID 
 FROM gyv2M.DDM_ACCT_STRUC 
WHERE FA_DM_ROW_DT <= '"+ (DT_WSTR,30)@[User::RepDate] +"'  AND DM_ROW_E_DT <= '"+ (DT_WSTR,30)@[User::RepDate] +"' 
 GROUP BY  FA_CLNT_ID) b 
 ON a.DDM_ACCT_STRUC_TP = b.DDM_ACCT_STRUC_TP AND a.FA_CLNT_ID = b.FA_CLNT_ID AND FA_DM_ROW_DT <= '" +(DT_WSTR,30)@[User::RepDate] +"' AND a.DM_ROW_E_DT <='"+(DT_WSTR,30)@[User::RepDate]+"'"

Hope this helps!