Rather new to SSIS Development and needing assistance. I have a simple SSIS package designed that will take data from an Excel (.xlsx) source data file and push to Oracle DB by:
- Reading a directory via Script Task
- If an expected file is found, variables are updated to set the ConnectionManager path for Excel Source
- A [Data Conversion] task sets the expected DataType and level of precision source values should go into the database as.
- A [Lookup] determining if the record/row in memory should update all fields (already existing), or, insert new record not yet in database.
Everything was working fine until several new test records were thrown at it. Example below:
When my package executes for this data, some of the edited records for testing show up in processing as blank values ("") instead of the expected NULL value, causing errors:
The thrown error is akin to:
Error: 0xC02020C5 at Data Flow Task, Data Conversion 2: Data conversion failed while converting column "COLUMN_Y" (147) to column "CONV_COLUMN_Y" (109). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
Error: 0xC0209029 at Data Flow Task, Data Conversion 2: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Data Conversion.Outputs[Data Conversion Output].Columns[CONV_COLUMN_Y]" failed because error code 0xC020907F occurred, and the error row disposition on "Data Conversion.Outputs[Data Conversion Output].Columns[CONV_COLUMN_Y]" 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 at Data Flow Task, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Data Conversion" (2) failed with error code 0xC0209029 while processing input "Data Conversion Input" (3). 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.
As seen in M/[ColumnX] above, I manually opened the file, highlighted cells, and clicked [DELETE], which caused NULL to appear for those blank row values and errors to disappear relating to [ColumnX]; now throwing for N/[ColumnY].
What is a good/best-practice way to pre-process or clean the data automatically to make this work without the need for manual intervention or remembering to do this if someone different provides the data file in ex. 5 years?
I am used to doing such operations with a (.txt) or (.csv) file, but not with [Excel Source] (.xlsx). Thanks!