October 23, 2011 at 1:52 am
i am looking for solution for the scenario.
scenario is:
i have some 50 oracle db tables.
i have to load the data from oracle to sql server tables through ssis.
the loading must be haappened incrementally for this i have to take last_updated_date from my destination table
and have to pass to OLEDB source for filter data .
the data must be filtered at source it self as the oracle tables have lakhs of records, but wana bring only records from last_updat_date.
i have set Run64bitrun as false in ssis solution properties
always usedefaultcode page=true at OLEDB source
i tried fallowing methods but all are failed.
1)excuteSQL task---taken last_updated_date from query and result set is passed as single row to user:Udate_key
execute sql task---->dataflow(OLEDBsource-->OLEDB Destination)
when i tried the fallowing select * from sourcetable where last_updated_date>? " the oledb command throws fallowing error
------------
Provider cannot derive parameter information and SetParameterInfo has not been called. (Microsoft OLE DB Provider for Oracle)
2)when i tried with taking another package level variable USER::SqlQuery as evaluate asexpression =true
and entered "SELECT * FROM TRNG.AP_INVOICES_ALL WHERE LAST_UPDATE_DATE >" +@[User::UdateKey]
the expression got validated
but again oledb source with "sql command from variable" option throws error
----
Error at Data Flow Task [OLE DB Source [1]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E14 Description: "ORA-00936: missing expression
".
3)
execute sql task------->script task---data floe task(oledb source----oledb destination)
by taking user::udatekey as readonly variable and user::sqlquery as read and write variable for script task to dynamically assigning the value to variable
Dts.Variables("SqlQuery").Value = "SELECT * FROM AP_INVOICES_ALL WHERE LAST_UPDATE_DATE >" + Dts.Variables("Updatekey").Value
Dts.TaskResult = ScriptResults.Success
it also throwing error.
please guide me in this regard. with appropriate solution .......
if possible send me dtailed steps
October 23, 2011 at 2:43 am
duplicate post. no replies please.
Original post: http://www.sqlservercentral.com/Forums/Topic1194914-363-1.aspx
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply