SSIS Excel redirect bad row to a flat file

Hello Experts Exchange
I have a SSIS package that fails every once in a while with the following error message.

The value could not be converted because of a potential loss of data.

I want to push data like this to a flat file and that the SSIS package will continue and finish with suggest.

The data is coming from a Excel spread sheet and I save the data into a SQL Server database.

Can you provide step by step instructions on  how I can reconfigure the SSIS package to push bad rows into a flat file please?

Thank you for reading.

Regards

SQLSearcher
SQLSearcherAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SQLSearcherAuthor Commented:
Hello faheem29484
I have followed the steps to redirect bad rows and it has worked on most of my project files but I have one that it does not work on and it write no data to the SQL Database or the flat file.

Can you think of anything I can try to get my last project working?

Regards

SQLSearcher
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
In your data flow task, looking at the arrow between source and destination, have you tried creating another destination for the 'bad rows', creating another arrow from the source to that destination, then right-click: Properties on that arrow and changing the Success constraint to Failure?  This changes the color of the arrow from green to red.

OR

Add a transform data task in between source and destination, changing the data type(s) into the same as the destination?

OR

Changing the destination so that the data types match the source?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SQLSearcherAuthor Commented:
Hello Jim
Thank you for you comments, I had to recreate the SSIS package using SSIS wizard, save the package, then configure the redirect to a flat file.  When I did this it worked fine.

Regards

SQLSearcher
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.