ssis expression through variable

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

ssis expression through variable

Author

Topic

h.singh10
Starting Member

16 Posts

Posted - 2012-01-12 : 14:11:56

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-13 : 04:38:36

expression set for variable is not correct . it should be like


"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" ," + (DT_WSTR,10)(DT_DBDATE) @[System::StartTime] + "), 2) + "-" +RIGHT("0" + (DT_STR,4,1252)DATEPART( "yy" ,"+ (DT_WSTR,10)(DT_DBDATE)@[System::StartTime] +"), 2) + " " +RIGHT("0" + (DT_STR,4,1252)DATEPART( "hh" ," + (DT_WSTR,10)(DT_DBDATE) @[System::StartTime] +"), 2) + "." +RIGHT("0" + (DT_STR,4,1252)DATEPART( "mi" , " + (DT_WSTR,10)(DT_DBDATE)@[System::StartTime] + "), 2) + "." +RIGHT("0" + (DT_STR,4,1252)DATEPART( "ss" ," + (DT_WSTR,10)(DT_DBDATE) @[System::StartTime]+" ), 2) +'",'DD-MM-YY HH24.MI.SS')"

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources