x

How is user variable added to Oracle sql expression used in SSIS variable

HI, I am connecting to an Oracle DB using SSIS. I have set the access mode to SQL from Variable. I created the variable "SQLSource". When all values are hardcoded there is no problem getting a result set. However I need to pass a variable to the sql statement to change the number of days in which data is collected. This variable is set in the config file. I have confirmed that the NumOfDays variable is passed correctly from the config file, but when I substitute the variable name for the value in the SQLSource, I get an Oracle missing Expresion error. Here are the evaluated expressions I have tried. The first gives me a conversion error so I tried the second and I get the missing expresion error.

 > "Select ASSET,BOOK,COMPANY,SEQ_NUMBER,LEASE_COMPANY,LEASE,ASSET_GROUP,Trans_Date,PROCESS_DATE,REFERENCE,DESCRIPTION,TRANS_STATUS,ACCT_GRP,ASSET_TYPE,SUB_TYPE,ACCT_TYPE,ACCT_UNIT,ACCOUNT,SUB_ACCT,ASSET_PROCESS,ATN_OBJ_ID,ACTIVITY,ACCT_CATEGORY,TRAN_ND,TRANS_AMOUNT,PROCESS_ASSET,PROCESS_SEQ,GLT_OBJ_ID,CREATION_DATE,CREATION_TIME,CREATOR_ID,AMTSET4_SS_SW,AMTSET8_SS_SW,AMTSET9_SS_SW
 > From Lawson.AMTrans where
 > to_char(creation_date,'yyyymmdd') >=
 > to_char((current_date -
 > @[User::NumOfDays]),'yyyymmdd') and
 > to_char(creation_date,'yyyymmdd') <
 > to_char((current_date),'yyyymmdd')"

  

 > "Select ASSET,BOOK,COMPANY,SEQ_NUMBER,LEASE_COMPANY,LEASE,ASSET_GROUP,Trans_Date,PROCESS_DATE,REFERENCE,DESCRIPTION,TRANS_STATUS,ACCT_GRP,ASSET_TYPE,SUB_TYPE,ACCT_TYPE,ACCT_UNIT,ACCOUNT,SUB_ACCT,ASSET_PROCESS,ATN_OBJ_ID,ACTIVITY,ACCT_CATEGORY,TRAN_ND,TRANS_AMOUNT,PROCESS_ASSET,PROCESS_SEQ,GLT_OBJ_ID,CREATION_DATE,CREATION_TIME,CREATOR_ID,AMTSET4_SS_SW,AMTSET8_SS_SW,AMTSET9_SS_SW
     > From Lawson.AMTrans where
     > to_char(creation_date,'yyyymmdd') >=
     > to_char((current_date - " 
     > +  Convert(varchar,@[User::NumOfDays]) + " ),'yyyymmdd') and to_char(creation_date,'yyyymmdd') <
     > to_char((current_date),'yyyymmdd')"



Suggestions anyone? Thanks!

more ▼

asked Oct 22, 2012 at 04:43 PM in Default

avatar image

paws27284
433 34 41 43

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

The second approach should work, you only have wront conversion expression for the @[User::NumOfDays] variable.

The expression should look like `(DT_WSTR, 10) @[User::NumOfDays]`.

So the final result should look like:

 "Select ASSET,BOOK,COMPANY,SEQ_NUMBER,LEASE_COMPANY,LEASE,ASSET_GROUP,Trans_Date,PROCESS_DATE,REFERENCE,DESCRIPTION,TRANS_STATUS,ACCT_GRP,ASSET_TYPE,SUB_TYPE,ACCT_TYPE,ACCT_UNIT,ACCOUNT,SUB_ACCT,ASSET_PROCESS,ATN_OBJ_ID,ACTIVITY,ACCT_CATEGORY,TRAN_ND,TRANS_AMOUNT,PROCESS_ASSET,PROCESS_SEQ,GLT_OBJ_ID,CREATION_DATE,CREATION_TIME,CREATOR_ID,AMTSET4_SS_SW,AMTSET8_SS_SW,AMTSET9_SS_SW
 From Lawson.AMTrans where
 to_char(creation_date,'yyyymmdd') >=
  to_char((current_date - " +
  (DT_WSTR, 10)@[User::NumOfDays] + " ),'yyyymmdd') and to_char(creation_date,'yyyymmdd') <
 to_char((current_date),'yyyymmdd')"

Also you have to ensure, that the total length of the string in the expression doesn't exeeds 4000 characters, what is limit for whatever expression in SSIS. If you reach the 4000 characters limit, you will have to construct the query and assing it to the variable in the `Script Task`. The SSIS string variable doesn't have the limit of 4000 haracters, however result of an expression has that limit.

more ▼

answered Oct 25, 2012 at 10:48 AM

avatar image

Pavel Pawlowski
22.7k 10 15 26

(comments are locked)
10|1200 characters needed characters left

Thanks, Pavel... this worked perfectly.

more ▼

answered Oct 26, 2012 at 01:30 PM

avatar image

paws27284
433 34 41 43

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2031
x1221

asked: Oct 22, 2012 at 04:43 PM

Seen: 1389 times

Last Updated: Oct 26, 2012 at 01:30 PM

Copyright 2017 Redgate Software. Privacy Policy