June 8, 2010 at 5:28 am
All,
I am using an excel source and instaed to use from direct table, I am using SQL Command to read from the excel file.
1) In the excel source, I selected 'SQL Command' as Data Access Mode.
- Does it supports it this way?
2) I am trying to write a normal case statement like below but I am getting this error message:
===================================
Error at Data Flow Task [Excel Source [1]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "(null)" Hresult: 0x80004005 Description: "(null)".
(Microsoft Visual Studio)
===================================
Exception from HRESULT: 0xC0202009 (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
Program Location:
at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.ReinitializeMetaData()
at Microsoft.DataTransformationServices.DataFlowUI.DataFlowComponentUI.ReinitializeMetadata()
at Microsoft.DataTransformationServices.DataFlowUI.DataFlowAdapterUI.connectionPage_SaveConnectionAttributes(Object sender, ConnectionAttributesEventArgs args)
SELECT CVV_CODE, CVV_OMSCHR, EENHEID, LMR_CODE, CVV_OMLANG,
CASE STARTD_CVV WHEN '-' THEN NULL ELSE STARTD_CVV END AS STARTD_CVV,
CASE EINDD_CVV WHEN '-' THEN NULL ELSE EINDD_CVV END AS EINDD_CVV,
CASE STARTD_LMR WHEN '-' THEN NULL ELSE STARTD_LMR END AS STARTD_LMR,
CASE EINDD_LMR WHEN '-' THEN NULL ELSE EINDD_LMR END AS EINDD_LMR
FROM [myExcel$]
June 9, 2010 at 1:38 am
You can write a SQL query to read from the Excel source, but it doesn't support all the language syntax.
So, I would just read the contents of the file and do any logic in the SSIS package itself.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 9, 2010 at 1:43 am
da-zero (6/9/2010)
You can write a SQL query to read from the Excel source, but it doesn't support all the language syntax.So, I would just read the contents of the file and do any logic in the SSIS package itself.
Same goes here but when I am trying to use it other way, there are different errors due to the nature of data I am having in excel. Therefore the simplest way was to use a CASE statement.
any Idea, if it does support the CASE in T/SQL.
June 9, 2010 at 2:08 am
Try using IIF construct, Please remember you have to Microsoft Jet Database syntax while querying excel
SELECT CVV_CODE, CVV_OMSCHR
,IIF((STARTD_CVV='-'),NULL,STARTD_CVV) AS S
FROM [sheet1$]
June 10, 2010 at 3:55 am
Gopi Muluka (6/9/2010)
Try using IIF construct, Please remember you have to Microsoft Jet Database syntax while querying excel
SELECT CVV_CODE, CVV_OMSCHR
,IIF((STARTD_CVV='-'),NULL,STARTD_CVV) AS S
FROM [sheet1$]
Did not get chance to ry this out but will try once get chance and post the outcome.
Thanks Anyways to all of you.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply