Solved

SSIS Expression problem

Posted on 2011-09-26
6
682 Views
Last Modified: 2012-05-12
Hi
I am battling to pass an expression in a variable
in SSIS driving me nuts
i have a simple select statement and one of the fields is s numeric (10.0)
I have a variable in SSIS which i want to use as part of the WHERE clause on this numeric field but i keep getting the following error

>>AND TCMSourceSystemID =" + @[User::TCMSourceSystemID]  + "

"Data Types DT_WSTR and DT_R8" are incompatible for binary operator +

thanks


"
SELECT TCMSourceSystemID, SourceCustID  FROM " + @[User::TargetSchema] + ".TCMStdCustomerMapSourceMULTI
WHERE TCMBusinessDate = '" + @[User::TCMBusinessDate]  + "' AND TCMSourceSystemID =" + @[User::TCMSourceSystemID]  + "
"
0
Comment
Question by:CraigLazar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 5

Expert Comment

by:DerZauberer
ID: 36598188
Not sure what's the 100% correct syntax, but i think you should convert your number to a varchar type or something like that ...

... + cast(@[User::TCMSourceSystemID] as nvarchar(50)) ...
0
 
LVL 4

Author Comment

by:CraigLazar
ID: 36598633
Hi,
i cannot as it is numeric in the database, so not sure why i am getting this error
0
 
LVL 5

Expert Comment

by:DerZauberer
ID: 36599266
You are using string concatenation to create your sql-statement, so everything has to be text to be concatenated with the rest of the statement. You can and should convert!
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 21

Accepted Solution

by:
Alpesh Patel earned 250 total points
ID: 36707753
SELECT TCMSourceSystemID, SourceCustID  FROM " + @[User::TargetSchema] + ".TCMStdCustomerMapSourceMULTI
WHERE TCMBusinessDate = '" + @[User::TCMBusinessDate]  + "' AND TCMSourceSystemID = (DT_WSTR,20) " +  @[User::TCMSourceSystemID]  + "
0
 
LVL 4

Author Comment

by:CraigLazar
ID: 36712552
Thanks will give it a try
0
 
LVL 4

Author Closing Comment

by:CraigLazar
ID: 36714633
thanks got it going
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question