-1

I was trying to import excel file to sql server for the first time and I get this error. Could you please help me? No idea what's going on :|

Operation stopped...

  • Initializing Data Flow Task (Success)

  • Initializing Connections (Success)

  • Setting SQL Command (Success)

  • Setting Source Connection (Success)

  • Setting Destination Connection (Success)

  • Validating (Success)

  • Prepare for Execute (Success)

  • Pre-execute (Success)

  • Executing (Error) Messages Messages Error 0xc020901c: Data Flow Task 1: There was an error with Source - Sheet1$.Outputs[Excel Source Output].Columns[Threat] on Source - Sheet1$.Outputs[Excel Source Output]. The column status returned was: "Text was truncated or one or more characters had no match in the target code page.". (SQL Server Import and Export Wizard)

Error 0xc020902a: Data Flow Task 1: The "Source - Sheet1$.Outputs[Excel Source Output].Columns[Threat]" failed because truncation occurred, and the truncation row disposition on "Source - Sheet1$.Outputs[Excel Source Output].Columns[Threat]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component. (SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Source - Sheet1$ returned error code 0xC020902A. 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. (SQL Server Import and Export Wizard)

  • Copying to [dbo].[Sheet1$] (Stopped)

  • Post-execute (Success) Messages Information 0x4004300b: Data Flow Task 1: "Destination - Sheet1$" wrote 0 rows. (SQL Server Import and Export Wizard)

Rocket128
  • 123
  • 3
  • 13
  • [Using Import wizard to import Excel data into table in SQL Server 2012 failing - text truncation](https://stackoverflow.com/questions/45844676/using-import-wizard-to-import-excel-data-into-table-in-sql-server-2012-failing) – Lukasz Szozda May 08 '18 at 15:41
  • Your `Threat` column contains data that is longer than the column type you chose (or didn't choose... SSIS will default to nvarchar(255) by default I think). – Jacob H May 08 '18 at 15:41
  • 1
    If you literally have "no idea what's going on" as you say, then that must mean you haven't googled the error message. You should start there. – Tab Alleman May 08 '18 at 16:08

4 Answers4

0

The data which you are importing might be having text bigger than columns size of table.

Maximize the size of the table column and then try.

Anusha Subashini
  • 387
  • 5
  • 17
0

Check the length for the column Threat on source and destination. Please adjust that according to the length on excel, bascally increase it.

Alternatively, In the Excel source editor and click on error output tab.

Select the column Threat and in Truncation column set the value as Ignore failure.

This should help.

DEEPAK LAKHOTIA
  • 993
  • 5
  • 10
0

Check out the ideas in this Stackoverflow link enter link description here, then. It might be trying to import some extra blank rows, which you can either allow (by changing the ALLOW NULLS option), or pipe the failed rows into a different dataset in SSIS, to handle the failing rows separately

Anusha Subashini
  • 387
  • 5
  • 17
0

Are you creating a DFT manually? If so:

1) Create New Excel Connection Manager - Link your excel file
2) Create New OLE DB Connection Manager - Choose your Database and Table.
3) Excel Source
4) Data Conversion - Convert the EXCEL column to DT_STR which makes it Varchar instead of NVARCHAR.
5) OLE DB Destination which is your SQL server table.

Alternatively you could set the column to NVARCHAR and it would match the EXCEL But if it's a VARCHAR column the data needs to be converted to DT_STR in the data conversion. Let me know if you need more help.

speedinfusion
  • 365
  • 2
  • 9