June 22, 2012 at 9:58 am
I'm trying to import an excel file into my table via SSIS, I have a date field in the excel file formatted as 21-March-12, I have a [data conversion] task in my SSIS package to make it to {Date}, the column in my SQL table is defined as {date}, however the SSIS process keeps failing on this column.
these are the errors I'm getting:
[Data Conversion [951]] Error: Data conversion failed while converting column "EVENT_UPDATED" (318) to column "Copy of EVENT_UPDATED" (1308). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
[Data Conversion [951]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output column "Copy of EVENT_UPDATED" (1308)" failed because error code 0xC020907F occurred, and the error row disposition on "output column "Copy of EVENT_UPDATED" (1308)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Data Conversion" (951) failed with error code 0xC0209029 while processing input "Data Conversion Input" (952). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Excel Source" (240) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
any ideas or suggestions, how can I import my date field from Excel to my SQL Table? What am I missing?
June 22, 2012 at 3:48 pm
I created a simple 2012 SSIS package with an Excel Source, Data Conversion, and OLE DB Destination components. The package ran successfully; the SQL Server table contains a "Copy of Birthday" column with a 2012-03-21 value. Here are the details:
1) The Excel file (Office 2010) has a Custom cell formatted d-mmm-yy, which contains the 21-March-12 date. The cell is in the "Birthday" column.
2) The Excel connection manager is configured to use the Microsoft Excel 2007 version.
3) The Data Conversion transformation is configured to changed the data type of the "Birthday" column to date [DT_DATE] and output the data to a "Copy of Birthday" column
3) The OLE DB Destination component runs the following statement to create the SQL Server table.
CREATE TABLE [OLE DB Destination_DateField] (
[Name] nvarchar(255),
[Birthday] datetime,
[Copy of Birthday] date
)
4) The OLE DB Destination component is configured to ignore the "Birthday" column (Mappings page of the component editor).
June 25, 2012 at 5:20 am
thanks, i was able to get that field working last night, the processs is now failing on another text field. I'm not sure how the excel file was created when the user did an export to excel from the MS Access app. Its also excel '07
June 25, 2012 at 11:42 am
I got the file to upload from SSIS, for whatever reason I had to seperate the file from blank fields to populated field and it worked. Not 100% sure why but doing that it worked.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply