Importing flat file into SQL Server using import and export wizard

0
votes

I am trying to import data using SQL import & export wizard. I am getting errors below. The data I am importing has NULL values. Can you please highlight me where i am getting it wrong?

Please errors statements below.

Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Destination - SupplierDim.Inputs[Destination Input]" failed because error code 0xC020907B occurred, and the error row disposition on "Destination - SupplierDim.Inputs[Destination Input]" 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.

Error 0xc0047022: Data Flow Task 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Destination - SupplierDim" (50) failed with error code 0xC0209029 while processing input "Destination Input" (63). 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.

1
sqlsql-serverssis
Most likely, there are other error messages returned that will give you a better idea of what failed. Unfortunately, SSIS does a very, very bad job of reporting errors. (Import/Export wizard is just a wizard on top of SSIS). You will have to dig deeper to find the useful error messages. I often have to read through several times before I figure out exactly which error message gives me useful information. – jim

1 Answers

0
votes

At first, I'd troubleshoot it by having very small portion of a file, just a few rows, but perfect rows. And then see if that will cause an error.

At second, I'd save the package and run it in Visual Studio. It will be much easier to troubleshoot errors within SSIS package right there.

It is possible that you are trying to insert NULLs in a column, which does not allow nulls. Look at all columns' properties when you use wizard: type/size/nullability If it still won't work, VS is the way to go.

Here is a link on similar troubleshooting:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/260151a1-3deb-4d3c-a042-2087ab73982f/datalfow-error-detination-input-failed-with-error-code-0xc020907b?forum=sqlintegrationservices