InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 05:27:33 GMT20http://www.sqlservercentral.com/Forums/Topic747645-148-1.aspxI have not seen this error before.Please post the code from your Script task.How is Oracle involved in your package?Mon, 06 Jul 2009 21:56:05 GMTPhil Parkinhttp://www.sqlservercentral.com/Forums/Topic747645-148-1.aspx[quote][b]Phil Parkin (7/6/2009)[/b][hr]You probably need to use a Script task to assign the text and access the contents of the variable - otherwise you'll just get the literal text in there.[/quote]Hi Phil,I tried to create the script task and now i am getting the errorAn OLE DBerror has occurred. Error code: 0x80040E0C.An OLE Database record is available. Source: "Microsoft OLE DB Provider forOracle" Hresult: 0x80040E0CDescription: "Command text was not set for the command object.".please help meRegardsSenthilMon, 06 Jul 2009 07:24:15 GMTsenthilkumar.m-555627http://www.sqlservercentral.com/Forums/Topic747645-148-1.aspxYou probably need to use a Script task to assign the text and access the contents of the variable - otherwise you'll just get the literal text in there.Mon, 06 Jul 2009 07:04:21 GMTPhil Parkinhttp://www.sqlservercentral.com/Forums/Topic747645-148-1.aspx[quote][b]Phil Parkin (7/6/2009)[/b][hr]OK, not quite correct for an OLEDB source.First you need to assign the [b]whole [/b]SQL string to a variable, then you need to change the Data Access Mode on your OLEDB source to be SQL Command from variable - the rest is obvious, I think.Phil[/quote]Hi Phil,I have assigned this query text into one variable and i have used sql command from variable but still it is not taking that variable value. however when i am displaying the effectivedate variable value in message box it is showing properly. task is executed successfully but i am not getting the result what i have expected. Can you find out what may the other cause for that?ThanksSenthilMon, 06 Jul 2009 06:51:59 GMTsenthilkumar.m-555627http://www.sqlservercentral.com/Forums/Topic747645-148-1.aspxOK, not quite correct for an OLEDB source.First you need to assign the [b]whole [/b]SQL string to a variable, then you need to change the Data Access Mode on your OLEDB source to be SQL Command from variable - the rest is obvious, I think.PhilMon, 06 Jul 2009 06:36:25 GMTPhil Parkinhttp://www.sqlservercentral.com/Forums/Topic747645-148-1.aspx[quote][b]Phil Parkin (7/6/2009)[/b][hr]Use an Expression to set the SQL ... That will decode your variable for you.[/quote]Can you please guide how can i set the expression to SQL?Mon, 06 Jul 2009 06:28:07 GMTsenthilkumar.m-555627http://www.sqlservercentral.com/Forums/Topic747645-148-1.aspxUse an Expression to set the SQL ... That will decode your variable for you.Mon, 06 Jul 2009 06:21:06 GMTPhil Parkinhttp://www.sqlservercentral.com/Forums/Topic747645-148-1.aspxHi Friends,I have created one script task to get the effectivedate value and assigned to the package variable @[User::Effectivedate] and using data flow task,OLEDB connection and SQL Command option i am trying to execute the below query . the actual result (2009-02-07') is not passing to the variable @[User::Effectivedate] hence the below query is not giving any result. SELECT POSITION.SECURITY_ALIAS,POSITION1.ORIG_FACE FROM (SELECT LLP.SECURITY_ALIAS AS SECURITY_ALIAS FROM HOLDING.DBO.POSITION P, HOLDING.DBO.POSITION_DETAIL PD, HOLDING.DBO.LOT_LEVEL_POSITION LLP WHERE P.POSITION_ID = PD.POSITION_ID AND P.POSITION_ID = LLP.POSITION_ID AND PD.security_alias = LLP.security_alias AND P.SRC_INTFC_INST =(SELECT INSTANCE FROM PACE_MASTER.DBO.INTERFACES WHERE SHORT_DESC='DD') AND CONVERT(VARCHAR(8),P.EFFECTIVE_DATE,112) ='"+ @[User::Effectivedate] + "' AND (LLP.department_acquisition_date IS NULL OR CONVERT(nvarchar(8),LLP.department_acquisition_date,112) >= '"+ @[User::Effectivedate] + "') ) POSITION LEFT JOIN ( SELECT LLP.SECURITY_ALIAS AS SECURITY_ALIAS, LLP.ORIG_FACE FROM HOLDING.DBO.POSITION P, HOLDING.DBO.POSITION_DETAIL PD, HOLDING.DBO.LOT_LEVEL_POSITION LLP WHERE P.POSITION_ID = PD.POSITION_ID AND P.POSITION_ID = LLP.POSITION_ID AND PD.security_alias = LLP.security_alias AND P.SRC_INTFC_INST =(SELECT INSTANCE FROM PACE_MASTER.DBO.INTERFACES WHERE SHORT_DESC='BLACKROCK') AND CONVERT(VARCHAR(8),P.EFFECTIVE_DATE,112) ='"+ @[User::Effectivedate] + "')POSITION1 ON POSITION1.SECURITY_ALIAS = POSITION.SECURITY_ALIAS"Can someone guide me how to create resolve the problem?RegardsSenthilMon, 06 Jul 2009 06:13:54 GMTsenthilkumar.m-555627