unas_sasing - Friday, October 20, 2017 1:35 PM

Jeff Moden - Thursday, October 19, 2017 5:24 PM

sgmunson - Thursday, October 19, 2017 1:50 PM

And just assuming that you have your data files properly described, the error you are getting clearly indicates that there's data in your file that doesn't fit into your data type for that column.   Only choice is to either manually correct that data, or change the data type to a character-based one and then create an error checking process for the data to validate that every value coming in is valid, and if not, flag it as an error row and mark the staging table record in some fashion as not valid or in need of correction.   There's a lot more work involved, because then you also need a business process to follow up with the data provider and either get a corrected file or a data correction that someone has to then apply to the staging table data.   Your load to production would also have to be a on a regular basis, and have to take into account that some records are not ready for production load.

The "overflowed the datatype" problem will also occur if you have the wrong delimiter selected because it tries to load the whole shebang into the first column.

Hai Guys, 
Thank you so much,I've found the solution for this,  I create a new table named dbo.beforestaging,
And then I create Stored Procedure to insert its textfile into that table.. 
After that,i use SSIS to insert from dbo.beforestaging onto dbo.afterstaging with column design exactly like the afterstaging table