July 6, 2009 at 6:13 am
Hi 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?
Regards
Senthil
July 6, 2009 at 6:21 am
Use an Expression to set the SQL ... That will decode your variable for you.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 6, 2009 at 6:28 am
Phil Parkin (7/6/2009)
Use an Expression to set the SQL ... That will decode your variable for you.
Can you please guide how can i set the expression to SQL?
July 6, 2009 at 6:36 am
OK, not quite correct for an OLEDB source.
First you need to assign the whole 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
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 6, 2009 at 6:52 am
Phil Parkin (7/6/2009)
OK, not quite correct for an OLEDB source.First you need to assign the whole 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
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?
Thanks
Senthil
July 6, 2009 at 7:04 am
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 6, 2009 at 7:24 am
Phil Parkin (7/6/2009)
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.
Hi Phil,
I tried to create the script task and now i am getting the error
An OLE DB
error has occurred. Error code: 0x80040E0C.
An OLE Database record is available. Source: "Microsoft OLE DB Provider for
Oracle" Hresult: 0x80040E0C
Description: "Command text was not set for the command object.".
please help me
Regards
Senthil
July 6, 2009 at 9:56 pm
I have not seen this error before.
Please post the code from your Script task.
How is Oracle involved in your package?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply