SSIS Package T-SQL

Select A, B, C, D
From Table1
Where B NOT IN ('Tree','House', 'CAR','Truck')

I'm getting the error "The syntax for 'NOT' is incorrect,

what is so incorrect about it?

Your thoughts.

Thx
Roberto Madro R.Programmer AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
What is the relevance of SSIS here?
Is that the exact error message?

I see no syntax problem in the query you have provided.
0
Roberto Madro R.Programmer AnalystAuthor Commented:
It's the SQL code inside the OLE DB Source on a SSIS package, I always opt to go with SQL for flexibility. As for "no syntax problem" that's my problem, I don't see what's wrong with the code because when I run the query from any of any of my  SQL tools, I get no error, it's only when it's in the package.
0
PortletPaulfreelancerCommented:
Thanks, understood. Not sure how I can help further as we can both agree that the sql seen here is fine.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Vikas GargBusiness Intelligence DeveloperCommented:
Hi,

Are you using any alias name for the where clause which will not be supported in the SQL
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Maybe this is a stupid question but are you sure that the error message is really relative to that command?
I mean, how do you know that isn't another command malformed in the package?
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>what is so incorrect about it?
First things that comes to mind is..
  Your SQL task is not correctly set up to a SQL connection that where this code would execute correctly.
  B is not a char column, which would result in a data type conversion.
  Just for kicks and giggles, execute this in SSMS and verify that it is correct.

Beyond that, it could really be anything, to include code immediately before this block, as SQL errors do not always reference the exact line that causes the error, and the exact error.

>I always opt to go with SQL for flexibility.
The counter-arguments against T-SQL in an SSIS package are..
   It can't be pre-compiled
   Impact Analysis is made more difficult as it's easy to search a database for all instances of B, but not as easy to search a database and all reports / packages that consume B.
0
it_moviesCommented:
try this:

Where NOT B IN ('Tree','House', 'CAR','Truck')
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.