SSIS Expressions

Question

Post reply

SSIS Expressions

colaforever

Say Hey Kid

Points: 699
More actions
January 14, 2008 at 8:42 am

#121774

I am connecting an oracle database table which is broken down into partitions and I am trying to use an expression to dynamically get these partitions, however I get the following error message:-
TITLE: Expression Builder
------------------------------
Expression cannot be evaluated.
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.DataTransformationServices.Controls.TaskUIFramework.TaskUIFrameworkSR&EvtID=FailToEvaluateExpression&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
Attempt to parse the expression ""SELECT * FROM schema.table
PARTITION(name_rest01" + right((DT_WSTR,20)month(dateadd("mm",-1,getdate())),2) +
right((DT_WSTR,50)year(dateadd("mm",-1,getdate())),2))" failed. The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a required element such as a parenthesis.
Here is the query:-
"SELECT * FROM schema.table
PARTITION(name_rest01" + right((DT_WSTR,20)month(dateadd("mm",-1,getdate())),2) +
right((DT_WSTR,50)year(dateadd("mm",-1,getdate())),2))
your aid would be highly appreciated

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Toby White SSCertifiable Points: 6187 More actions · Answer 1

"SELECT * FROM schema.table

PARTITION(name_rest01" + right((DT_WSTR,20)month(dateadd("mm",-1,getdate())),2) +

right((DT_WSTR,50)Year(dateadd("mm",-1,getdate())),2)

Should work for your expression. I think the only thing wrong with your expression is that it has an extra parenthesis at the end.

I hope this helps

colaforever Say Hey Kid Points: 699 More actions · Answer 2

Thanks for the quick response!

There is a problem with the result since it will evaluate to

SELECT * FROM schema_name.table_name

PARTITION name_rest011207

Now this query is meant to work with an oracle database and this syntax is not correct. The correct syntax to work with oracle partitions is:-

select * from schema_name.table_name partition(partition_name)

I can't make do with the parenthesis!

colaforever Say Hey Kid Points: 699 More actions · Answer 3

DONE IT!

here is the syntax:-

"SELECT * FROM schema_name.table_name

PARTITION (name_rest01" + right((DT_WSTR,20)month(dateadd("mm",-1,getdate())),2) +

right((DT_WSTR,50)Year(dateadd("mm",-1,getdate())),2) +")"

and here is the result:-

SELECT * FROM schema_name.table_name

PARTITION ( name_rest011207)

Toby White SSCertifiable Points: 6187 More actions · Answer 4

Good job - glad you figured it out.

P.S. I don't envy you having to work with Oracle

colaforever Say Hey Kid Points: 699 More actions · Answer 5

Thanks!

Oracle is stable and thats it, apart from that its not worth the hype!!!!

BTW - I think that Microsoft have hit the jack pot with there strategy of making their tools available to all and work with any given source/destination. Basically expressions make it possible to write SQL for any given implementation which is an amazing capability.

Cheers!