StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POhow to pass parameters to an ado.net source in ssis?
text
Body
copied!<p>This is the original query, which works fine using <code>ado.net</code> source and using the .net providers\odbc data provider specified inside the source.</p> <pre><code>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' </code></pre> <p>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.</p> <pre><code>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]' </code></pre> <p><strong>Update</strong></p> <p>Hi,</p> <p>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:</p> <pre><code>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] </code></pre> <p>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.</p> <pre><code>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 </code></pre> <p><strong>***Update2***</strong></p> <h2>TITLE: Microsoft Visual Studio</h2> <p>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</p> <hr> <p>ADDITIONAL INFORMATION:</p> <p>Pipeline component has returned HRESULT error code 0xC02020FF from a method call. (Microsoft.SqlServer.DTSPipelineWrap)</p> <hr> <p>BUTTONS:</p> <h2>OK</h2>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload