May 27, 2009 at 7:45 am
Hi there,
I'm quite new to creating SSIS packages, so please bare with me as I try to explain the problem I am having.
I have created a DTSX package that does the following:
1. SQL Task - Create a tempTable (15 columns, each field being CHAR of different sizes)
2. ForEach Loop (with Data Flow Task)
- Loop through all flatfiles in a certain folder
- Import all data from flatfiles into the tempTable
3. Data Flow Task
- Connect back to tempTable
- Convert data to match the field datatypes from the raw_data table that I have created on my SQL server
- Populate the raw_data table with valid data
It's in Step 3 that I have my error. Once I cannot convert my datatype from the tempTable value of a single field, the whole table is transferred to my error log (stored in a flatfile). What I want to do is actually just redirect the bad row to my error log (or just delete it somehow) and continue inputting the good records into my raw_data table.
This person wrote a helpful article, but when it came to customizing the solution to suit my needs.... I fully admit that I fail. :pinch:
Here's a link to the article I was referring to.
If anyone can help me out, it would be much appreciated.
May 27, 2009 at 10:41 am
This article[/url] may be helpful. Be sure to check the discussion as well.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 1, 2009 at 7:10 am
You could handle the issue by pre-validating the data for output, and directing accordingly.
> Select Data that meets all your file conditions for output: a view in SQL might be useful, or use a TSQL statement as the data source in SSIS. This might get quite complex, depending on the issues.
> Select the remainder for your error output (to return to supplier etc.)
You could also add a step to weed out the bad, or, if this is within your project scope, fix the bad data in SQL.
June 1, 2009 at 7:16 am
Hi Jack,
Thank you for the article. It was very helpful and has enabled me to weed out the bad records so far.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply