StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You can use the following steps:</p> <ol> <li>Use your working query into the ADO.NET source.[You could not map the parameters for the ADO.NET source]</li> <li>Select your <code>Dataflow</code> and click <code>Properties</code>.</li> <li>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 <code>SqlCommand</code> for your Data Source and using the Expression builder prepare your query using SSIS variables (datetime variable in your case).</li> </ol> <p><img src="https://i.stack.imgur.com/iRXZp.png" alt="ADO.NET Query preparation"></p> <p>Try this in your expression builder -- note that your variable must be a string, if it's not, you use <code>(DT_WSTR,30)</code> to cast it. Since you're using dates, your SQL will need to handle a string-formatted date, i.e. use <code>to_date()</code> in Oracle.</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 <= '"+ (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]+"'" </code></pre> <p>Hope this helps!</p>

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