Sql command not taking the packagevariable value

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » Sql command not taking the packagevariable...

Sql command not taking the packagevariable value

Rate Topic

Display Mode

Topic Options

Author

Message

senthilkumar.m-555627

Posted Monday, July 06, 2009 6:13 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, July 09, 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

Phil Parkin

Posted Monday, July 06, 2009 6:21 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:10 PM
Points: 4,319, Visits: 9,658

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:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #747649

senthilkumar.m-555627

Posted Monday, July 06, 2009 6:28 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, July 09, 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

Phil Parkin

Posted Monday, July 06, 2009 6:36 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:10 PM
Points: 4,319, Visits: 9,658

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:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #747658

senthilkumar.m-555627

Posted Monday, July 06, 2009 6:51 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, July 09, 2009 2:29 AM
Points: 11, Visits: 84

Phil Parkin (7/6/2009)

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

Phil Parkin

Posted Monday, July 06, 2009 7:04 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:10 PM
Points: 4,319, Visits: 9,658

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:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #747679

senthilkumar.m-555627

Posted Monday, July 06, 2009 7:24 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, July 09, 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

Phil Parkin

Posted Monday, July 06, 2009 9:56 PM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:10 PM
Points: 4,319, Visits: 9,658

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:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #748284

« Prev Topic | Next Topic »

Permissions