x

SSIS Package Variable Problems

HI, I am trying to set a package variable(Transdate) to the date to pass to a package variable(SQLSource) that will hold my sql query. I plan to use variable SQLSource as the data source for an Oracle OLE DB connection using the evaluate as expression concept. I cannot seem to get my package variable(Transdate) defined as datetime ,to change to the date I want. When I create the package variable Transdate defined as Datetime, it defaults to the current date 08/23/2012. I then call an execute SQL task to manipulate the date using this code:

select  (Cast(Floor(Cast( DateAdd(Day,-1, GetDate()) as Float)) as Datetime))

In the Execute Sql Task, I have "Result set " = Single row, I have tried 0 as the result name, and assign it to the package variable [User::Transdate] defined as datetime.

When I execute the query in sql I get exactly what I need in the format I need: 2012-08-22 00:00:00.000

When I execute the task the Transdate variable[User::Transdate] never changes.

So I could continue working for now I hardcoded the Transdate variable[User::Transdate] to 8/22/2012. I put the select sql into the package variable SQLSource using the expression panel like this...

Select
 [ASSET], 
 [BOOK],
 [COMPANY] ,
 [SEQ_NUMBER], 
 [LEASE_COMPANY] ,
 [LEASE],
 [ASSET_GROUP], 
 Case
 When left(trans_Date,4) = '1700' then 
 '1900' + Right(Trans_Date,15)
 Else Trans_Date
 End as Trans_Date, 
 [PROCESS_DATE], 
 [REFERENCE], 
 [CREATION_DATE], 
 [CREATION_TIME], 
 [AMTSET9_SS_SW]
From Lawson.AMTrans
Where  CREATION_DATE = @[User::Transdate]

When I hit evaluate expresssion I get :

Attempt to parse the expression "Select
 [ASSET], 
 [BOOK],
 [COMPANY] ,
 [SEQ_NUMBER], 
 [LEASE_COMPANY] ,
 [LEASE],
 [ASSET_GROUP], 
 Case
 When left(trans_Date,4) = '1700' then 
 '1900' + Right(Trans_Date,15)
 Else Trans_Date
 End as Trans_Date, 
 [PROCESS_DATE], 
 [REFERENCE], 
 [CREATION_DATE], 
 [CREATION_TIME], 
 [AMTSET9_SS_SW]
From Lawson.AMTrans
Where  CREATION_DATE =   @[User::Transdate]" 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.  

I hope you can help!
Thanks!
paws27284 PS Why does my question look readable in the preview but looks like crap in the actual view? I added spaces and bold to help facilitate reading but it does not show up in the actual view. (or would that be a different question?) :)

more ▼

asked Aug 23 '12 at 08:32 PM in Default

paws27284 gravatar image

paws27284
353 23 28 32

Hi.

I fixed the formatting for you. When writing code, you can mark the lines of code and hit the Code-button in the edit-control. The preview does not always reflect what the post will look like once posted (unfortunately).

Regarding your actual question, I can't help out just now, it's a little too late and I don't speak SSIS very well.
Aug 23 '12 at 09:20 PM Magnus Ahlkvist
Sweet! thanks for the formatting! PAWS27284
Aug 23 '12 at 09:54 PM paws27284
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

Sorry, but there are many ambiguities in the question. But for the error part of evaluating as expression, you need to double quote the query and then concatenate the UserVariable. But if you have defined the @[User::Transdate] as date, then it needs to be type cast to String. So the final expression should be something like

"Select
 [ASSET], 
 [BOOK],
 [COMPANY] ,
 [SEQ_NUMBER], 
 [LEASE_COMPANY] ,
 [LEASE],
 [ASSET_GROUP], 
 Case
 When left(trans_Date,4) = '1700' then 
 '1900' + Right(Trans_Date,15)
 Else Trans_Date
 End as Trans_Date, 
 [PROCESS_DATE], 
 [REFERENCE], 
 [CREATION_DATE], 
 [CREATION_TIME], 
 [AMTSET9_SS_SW]
From Lawson.AMTrans
Where  CREATION_DATE = '" + (DT_WSTR, 30) (DT_DBDATE) GETDATE() + "'"
more ▼

answered Aug 24 '12 at 01:00 PM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

I apologize for the lack of clearness in my question. I used your sample for my final expression and I still get no result set. I tried using the where clause on a different column and it worked. I know it has something to do with the date. When I do not use a where I get the Creation_Date in the following format

8/8/2012 12:00:00 AM

When I hardcode the date in the format above I get no results. I also tried 2012-08-08, 2012-8-8, 2012-08-08 00:00:00, and finally 2012-08-08 12:00:00 AM. Any suggestions?

Aug 24 '12 at 08:48 PM paws27284

What happens when you change date comparison from equals to greater than equal to i.e. [CREATION_DATE] >= .... Does that work?

If your data does not have the time part i.e. all with 12 A.M., then the best option would be to use YYYYDDMM date format i.e.

...[CREATION_DATE] =  '" + REPLACE( (DT_WSTR, 30) (DT_DBDATE) GETDATE() , "-", "")+ "'"

But if you do have the records with time portion then you need to use >= and < comparison operators. Something like

 [CREATION_DATE] >=  '" + REPLACE( (DT_WSTR, 30) (DT_DBDATE) GETDATE() , "-", "")+ "'" + "AND [CREATION_DATE] < '" + REPLACE( (DT_WSTR, 30) (DT_DBDATE) DATEADD( "DAY", 1, GETDATE())  , "-", "")+ "'"

If still your problem is not solved then please let us know.

Aug 27 '12 at 07:54 AM Usman Butt
(comments are locked)
10|1200 characters needed characters left

Hi I have tried your examples and am curious about something.

When I use your example

"Select ASSET, TRANS_AMOUNT, CREATION_DATE From Lawson.AMTrans Where [CREATION_DATE] >= '" + REPLACE( (DT_WSTR, 30) (DT_DBDATE) DATEADD( "DAY", -1, GETDATE()) , "-", "")+ "'" + " AND [CREATION_DATE] < '" + REPLACE( (DT_WSTR, 30) (DT_DBDATE) GETDATE() , "-", "")+ "'"

It resolves to this...

Select ASSET, TRANS_AMOUNT, CREATION_DATE From Lawson.AMTrans Where [CREATION_DATE] >= '20120826' AND [CREATION_DATE] < '20120827'

Which is fine, but I know there are no transactions yesterday. So I replaced the -1 with -13, I have confirmed there are transactions between 20120814 and 20120827.

My new evaluated Expression is ... Select ASSET, TRANS_AMOUNT, CREATION_DATE From Lawson.AMTrans Where [CREATION_DATE] >= '20120814' AND [CREATION_DATE] < '20120827'

Either way I get this error message Hresult: 0x80040E14 Description: "ORA-00936: missing expression".

There is a time but it is always 00:00:00, because the actual time is in a separate field.

I tried the other sugesstion

"Select ASSET, TRANS_AMOUNT, CREATION_DATE From Lawson.AMTrans [CREATION_DATE] = '" + REPLACE( (DT_WSTR, 30) (DT_DBDATE) DATEADD( "DAY", -12, GETDATE()) , "-", "")+ "'"

It resolves to this

Select ASSET, TRANS_AMOUNT, CREATION_DATE From Lawson.AMTrans [CREATION_DATE] = '20120827'

But in tring to preview I get this Hresult: 0x80040E14 Description: "ORA-00933: SQL command not properly ended ".

I also created a query based on the one above but using dataadd ("DAY" ,-13,getdate()) to obtain transactions I know exist. but I received the same error as above.

More help is welcome, my basic premise is I want to obtain all the trsnsaction fro the AM Trans table from yesterday. Is there possibly an Oracle date function I could use? I am reading an oracle database and writing to a MS Dtatbse.
more ▼

answered Aug 27 '12 at 03:10 PM

paws27284 gravatar image

paws27284
353 23 28 32

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

HI, I found the answer! I was making it to difficult by adding the variable trans_date because I did not know PL/SQL.

Select ASSET, TRANS_AMOUNT, CREATION_DATE From Lawson.AMTrans where to_char(creation_date,'yyyymmdd') = to_char((current_date - 5),'yyyymmdd')"

For my normal processing , I would use Current_date - 1
more ▼

answered Aug 27 '12 at 10:03 PM

paws27284 gravatar image

paws27284
353 23 28 32

Ohhhh...You should have tagged it with Oracle keyword to get a more swift and precise answer. Anyhow I am doing it now for you. Glad to know you sorted it out.
Aug 28 '12 at 01:27 PM Usman Butt
(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x899
x375
x341

asked: Aug 23 '12 at 08:32 PM

Seen: 1932 times

Last Updated: Aug 28 '12 at 01:28 PM