ssis expression through variable

• h.singh10

  Right there with Babe

  Points: 731

  More actions

  January 12, 2012 at 12:11 pm

  #253442

  Can anybody help me.

  I am using an oledb source. the query is coming from a variable. The database to which oledb source is connected is Oracle.

  Mt variable contains the following query:

  "SELECT REQUEST_ID FROM COMPLIANCE_REQUEST

  where LOAD_TMSTP between (select max(END_TMSTP) FROM BATCH_JOB_LOG) and

  TO_DATE("'+RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" , @[System::StartTime] ), 2) + "-"+RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" , @[System::StartTime] ), 2) + "-" +RIGHT("0" + (DT_STR,4,1252)DATEPART( "yy" , @[System::StartTime] ), 2) + " " +RIGHT("0" + (DT_STR,4,1252)DATEPART( "hh" , @[System::StartTime] ), 2) + "." +RIGHT("0" + (DT_STR,4,1252)DATEPART( "mi" , @[System::StartTime] ), 2) + "." +RIGHT("0" + (DT_STR,4,1252)DATEPART( "ss" , @[System::StartTime] ), 2) +'",'DD-MM-YY HH24.MI.SS')"

  I am getting error as :

  Error at Data Flow Task [OLE DB Source 2 [2177]]: No column information was returned by the SQL command.

  Error at Data Flow Task [OLE DB Source 2 [2177]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4A.

  An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E4A Description: "Command was not prepared.".

  Error at Data Flow Task [OLE DB Source 2 [2177]]: Unable to retrieve column information from the data source. Make sure your target table in the database is available.

  (Microsoft Visual Studio)

  i CHECKED. THE TABLE TABLE NAME, COLUMN NAME ARE CORRECT.

  pLEASE HELP ME RESOLVE THIS ISSUE.

  Thanks

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  January 12, 2012 at 2:04 pm

  #1432877

  Have you tried just entering the query directly in the data source to verify you have everything correct?

  Is there a schema missing from the query? FOr example should it be schema.COMPLIANCE_REQUEST or SCHEMA.BATCH_JOB_LOG?

  Are you sure permissions are setup correctly in ORACLE?

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question