Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Sql command not taking the packagevariable value Expand / Collapse
Author
Message
Posted Monday, July 6, 2009 6:13 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 9, 2009 2:29 AM
Points: 11, Visits: 84
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
Post #747645
Posted Monday, July 6, 2009 6:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:25 PM
Points: 5,174, Visits: 12,028
Use an Expression to set the SQL ... That will decode your variable for you.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #747649
Posted Monday, July 6, 2009 6:28 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 9, 2009 2:29 AM
Points: 11, Visits: 84
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?
Post #747654
Posted Monday, July 6, 2009 6:36 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:25 PM
Points: 5,174, Visits: 12,028
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



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #747658
Posted Monday, July 6, 2009 6:51 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 9, 2009 2:29 AM
Points: 11, Visits: 84
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
Post #747667
Posted Monday, July 6, 2009 7:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:25 PM
Points: 5,174, Visits: 12,028
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.




Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #747679
Posted Monday, July 6, 2009 7:24 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 9, 2009 2:29 AM
Points: 11, Visits: 84
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
Post #747704
Posted Monday, July 6, 2009 9:56 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:25 PM
Points: 5,174, Visits: 12,028
I have not seen this error before.

Please post the code from your Script task.

How is Oracle involved in your package?



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #748284
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse