asked
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?) :)
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.
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() + "'"
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?
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]
0 Likes 0
·
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.
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
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.
9 People are following this question.
