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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
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
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
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?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply