SSIS: Adding a int variable to SQL Query via Expression

Question

I have added a variable in SSIS and I am trying to add tha variable in my expression query. But it says Expression cannot be evaluated Below is my expression query. Please help if you know where I am making mistakes.

SQL Query

SELECT c.CustomerName, o.OrderID from  Customers c
INNER JOIN Orders o ON c.CustomerID=o.CustomerID
Inner Join OrderDetails od ON od.OrderId = o.OrderID
Inner Join Products p on p.ProductID = od.ProductID 
where ISNULL(c.IsResult,0) = 0 and o.CompanyID = @CompanyID

Expression Format

"SELECT c.CustomerName, o.OrderID from  Customers c
    INNER JOIN Orders o ON c.CustomerID=o.CustomerID
    Inner Join OrderDetails od ON od.OrderId = o.OrderID
    Inner Join Products p on p.ProductID = od.ProductID 
    where ISNULL(c.IsResult,0) = 0 and o.CompanyID ="+ @[User::intCompanyID]

Let me know if I am doing it right.

Inus C · Accepted Answer · 2016-01-22 07:21:48Z

1

I found that casting the numeric values to String or Unicode works. so maybe try

ISNULL(c.IsResult,0) = 0 and o.CompanyID ="+ (DT_WSTR,20) @[User::intCompanyID]

answered Jan 22, 2016 at 7:21

Inus C

1,5511 gold badge16 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

vmachan · Accepted Answer · 2016-01-21 21:03:02Z

0

Have you tried the following

"SELECT c.CustomerName, o.OrderID from  Customers c
INNER JOIN Orders o ON c.CustomerID=o.CustomerID
Inner Join OrderDetails od ON od.OrderId = o.OrderID
Inner Join Products p on p.ProductID = od.ProductID 
where ISNULL(c.IsResult,0) = 0 and o.CompanyID = ?"

NOTE: The image is from a different example

Click on the "Parameters" button and you can then selected your parameter from there.

Hope this helps.

answered Jan 21, 2016 at 21:03

vmachan

1,6821 gold badge10 silver badges10 bronze badges

Collectives™ on Stack Overflow

SSIS: Adding a int variable to SQL Query via Expression

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related