Multiple-step OLE DB operation generated error with parameter mapping
Hi All, I have an issue that's been bothering me for a couple days. I've defined a variable that I'm attempting to pass a parameter in an Execute SQL Task. I've found that I only need to define the mapping to receive the error. Other posts suggest the issue is data type related. I haven't found a discrepancy in the type mapping and am hoping someone may be able to point me in the correct direction. Variable declaration: =================== Name: ProcDt Scope: <Local> Data Type: Int32 Value: 2010098 Parameter Mapping: ================= Variable Name: User::ProcDt Direction: Input Data Type: Long Parameter Name: 1 Parameter Size = -1 The task error is: ================== Error: 0xC002F210 at Load Staging Table, Execute SQL Task: Executing the query "Insert into clro_rpt.dbo.LSRP_Prelim_newrecs Select *, ? from openquery(bacardi,'select right(''000000000000000000''||ltrim(rtrim(a.accno)),18) as acctid, a.locat_cd as location, a.cltrl_map_cd, a.cltl_cd, a.decsn_dt as dateattr, a.cyc_pdue_qt as cpd, a.actl_dy_pdue_qt as dpd, a.seg_id as cpc, a.decsn_id as decsnid, a.chlng_rpt_grp_cd as rptgrp, a.acc_chn_cd as type, a.cci_in as aci_id, a.lgl_stat_cd as legal_stat, a.acc_bl_am as EBALANCE, a.tst_grp_id as testgrp, a.bhvr_scr_cd as bscore, a.hld_dy_qt, a.rfsh_crb_scr_no as FICO2, a.spec_cd, a.proc_ccyymm_dt, cast(null as smallint) as cycle_id, right(''000000000000000000''||ltrim(rtrim(a.accno)),18) as acctnr from cfg.clct_rsk_hs a where substr(a.chlng_rpt_grp_cd,1,2) in (''1C'',''2C'',''3C'',''4C'') and a.proc_ccyymm_dt >= ?') " failed with the following error: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Task failed: Load Staging Table Kind regards, OrlanzoOrlanzo
November 5th, 2010 12:19pm

Not sure you can use the ? in the SELECT portion of the script, only in the WHERE portion. You have the ? listed twice. Are you mapping two parameters, or only one? FYI: Each "?" in the statement needs its own parameter mapping. Next possibility (and I'm leaning toward this one): You are using OPENQUERY. Why? Why not crate a Connection Manager to that resource directly and connect the Execute SQL Task to it and ditch the OPENQUERY? It is possible that the OPENQUERY statement does not take to having parameters passed INSIDE it. If you are stuck on the OPENQUERY syntax instead of creating a new CM, then try building your SQL statement into a variable and use either an Expression or a Script Task to get the Parameter variable mapped accordingly.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
Free Windows Admin Tool Kit Click here and download it now
November 5th, 2010 12:38pm

Next suggestion: Throw the whole statement into a stored procedure and pass the parameter to it. " EXEC dbo.MyProc ? " Logic that is this complex is hard to debug inside the SQL command box in SSIS and poersonally, stuff like this I move over to the database. I reserve my Execute SQL tasks for a) executing stored procedures, or b) doing simple operations like TRUNCATE or COUNT(*), or DELETEs. Hope this helps.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
November 5th, 2010 12:41pm

Hi Are you declaring more than one parameter? You have used one in the SELECT *,? you then use another in a.proc_ccyymm_dt >= ? Sethhttp://lqqsql.wordpress.com
Free Windows Admin Tool Kit Click here and download it now
November 5th, 2010 12:42pm

Yes, I'm actually mapping multiple parameters. Only the variable mentioned here is presenting an issue. I had previously hard coded the value in the OPENQUERY statement for testing. I still received an error. I'm going to throw everything in a variable expression. That may allow me to work around the issue. If not, I'll call it from a stored procedures. Either way - I'll report my findings Regards, OrlanzoOrlanzo
November 5th, 2010 1:56pm

Seth, I'm supplying multiple parameters. The first works as expected. The second, embedded within the OpenQuery statement, is failing. Although, the failure seems to be with the variable definition rather than the ? in the statement. For example, if I hard code a value for proc_ccyymm_dt, the error is received. If I delete the parameter mapping and hard code the value - no error.Orlanzo
Free Windows Admin Tool Kit Click here and download it now
November 5th, 2010 1:59pm

what is your connection manager? is it oledb?http://www.rad.pasfu.com
November 5th, 2010 2:00pm

Reza, Yes, its an OLE DB connection for SQL Server. Orlanzo
Free Windows Admin Tool Kit Click here and download it now
November 5th, 2010 3:18pm

What is the server "bacardi" ? Is it SQL, Oracle? DB2? Can you create an OLE DB Connection Manager for it and ditch the OPENQUERY syntax?Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
November 5th, 2010 3:24pm

when you work with OLEDB connection manager in execute sql task, your parameters should be question mark ( ? ) and you should map them in parameter mappings tab with parameter name index start from 0 for example if your sql statement is: select f1,f2 from mytable where id=? and f3=? then you should map parameters with parameter names 0 and 1http://www.rad.pasfu.com
Free Windows Admin Tool Kit Click here and download it now
November 5th, 2010 3:27pm

when you work with OLEDB connection manager in execute sql task, your parameters should be question mark ( ? ) and you should map them in parameter mappings tab with parameter name index start from 0 for example if your sql statement is: select f1,f2 from mytable where id=? and f3=? then you should map parameters with parameter names 0 and 1 http://www.rad.pasfu.com The parameters are mapped just as you've specified. Parameter (0) refers to another variable LoadDt which is behaving as expected.Orlanzo
November 5th, 2010 3:34pm

What is the server "bacardi" ? Is it SQL, Oracle? DB2? Can you create an OLE DB Connection Manager for it and ditch the OPENQUERY syntax? Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate. Todd, Bacardi refers to a DB2 server. I can't use a connection manager because its access via a Windows Authenticated account. My security context doesn't allow direct access hence the need to use the linked server via the OPENQUERY statement.Orlanzo
Free Windows Admin Tool Kit Click here and download it now
November 5th, 2010 3:38pm

Bacardi refers to a DB2 server. I can't use a connection manager because its access via a Windows Authenticated account. My security context doesn't allow direct access hence the need to use the linked server via the OPENQUERY statement. DB2 can use Windows Integrated Security? Really? That's news to me and I wish I knew that years ago. I'm not a DB2 guy so I just accepted what they told me, and they always told me that you would only access DB2 with username and password. I don't suppose there is any way for them to grant your account the same access as what is being gained by the Windows account specified in the linked server setup? (Yeah, I'm probably dreaming, aren't I?) So you're down to storing the whole SQL statement in a Variable and executing that variable in the Execute SQL Task.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
November 5th, 2010 3:51pm

Bacardi refers to a DB2 server. I can't use a connection manager because its access via a Windows Authenticated account. My security context doesn't allow direct access hence the need to use the linked server via the OPENQUERY statement. DB2 can use Windows Integrated Security? Really? That's news to me and I wish I knew that years ago. I'm not a DB2 guy so I just accepted what they told me, and they always told me that you would only access DB2 with username and password. I don't suppose there is any way for them to grant your account the same access as what is being gained by the Windows account specified in the linked server setup? (Yeah, I'm probably dreaming, aren't I?) So you're down to storing the whole SQL statement in a Variable and executing that variable in the Execute SQL Task. Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate. Just double checked - it isn't a Windows Authenticated account. The linked server was setup with an actual user account. It isn't a system or process account that I can utilize within a connection manager. So... yes, I'm down to using a variable expression. I'm really curious as to why the parameter mapping isn't working as expected. Orlanzo
Free Windows Admin Tool Kit Click here and download it now
November 5th, 2010 4:03pm

I'm really curious as to why the parameter mapping isn't working as expected. And I'm real curious as to why my 1965 diesel Volkswagon bus can't get up to 120 mph unless its going downhill in a hurricane. Answer: Because the designers never intended it to work that way, that's why. :-) Best of luckTodd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
November 5th, 2010 4:14pm

Hey Im new to the reporting services area, and have a similar problem, but I cant get any of the variables to work as parametter. my error is: Error: 0xC002F210 at 068 CREATE, Execute SQL Task: Executing the query " TRUNCATE TABLE Reports" failed with the following error: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Task failed: 068 CREATE SSIS package "st2.dtsx" finished: Success. Could you please help me? thank you
Free Windows Admin Tool Kit Click here and download it now
July 18th, 2012 1:36pm

What is the SQL Statement you are trying to execute inside the Execte SQL Task? If it is anything with "TRUNCATE TABLE..." then know that requires either db_owner or dll_admin role priveledges on the database.Todd C - MSCTS SQL Server 2005, MCITP BI Dev 2008 Please mark posts as answered where appropriate.
July 18th, 2012 2:40pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics