| Author |
Topic |
|
Hommer
Aged Yak Warrior
808 Posts |
 Posted - 2008-04-17 : 16:12:14
|
| Hi, all,I have this SSIS data flow ( Flat file to sql server) that I want to add a step to redirect any "bad" data instead of fail out. I had the red arrow hocked up to a sql new table to dump the bad data, but the flow still failed. Here is the first error, and I knew what was wrong. A description field in that line has pipe(|) character in it, which also happen to be the column delimiter in this case. [Flat File Source [1]] Error: Data conversion failed. The data conversion for column "Column 22" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.". I knew if I fixed the data, every thing will be fine, but I just want to use this redirect feature of SSIS. Is there a place where I can turn off validation, or do something to make it work?Thanks! |
|
|
jrea8830
Starting Member
4 Posts |
Posted - 2008-04-17 : 23:30:49
|
| Turning off validation won't help because that is just a way to determine errors before run-time. If you were to turn it off then you'd simply encounter the error during run-time.If you can live without the pipes then you could use a derived column and use the REPLACE expression to find the pipes and switch with something else like ## or @@ or - or whatever. Or you could just put in an empty string (space) in place of the pipes. Then if you needed the pipes you could go into SQL and use a REPLACE in a SELECT statement to put them back in.Another idea would be to go into your Flat File connection manager and change the settings to use something else to delimit the text....again like ## or $$. Drawback here would be that you'd have to go into the file and change it to match your new settings.If you are going to try the first idea the look on BOL for ?Integration Services Expression Reference?. This will cover all the expressions you can use in SSIS and what their syntax and output are.Hope this helps.Thanks,James |
 |
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2008-04-18 : 09:27:53
|
| James,Thank you for the reply! I agree with you on how to solve the problem in this case.I was hoping SSIS Flat File Source Error Output will give me some features to deal with this kind of issue in general.My objective is more on finding a robust way of using SSIS as data profiling tool to sort out complying data and bad data. Nevertheless, thanks again1Hommer |
|
|
|
igorblackbelt
Constraint Violating Yak Guru
407 Posts |
Posted - 2008-04-18 : 11:27:53
|
| Can you use conditional split between your flat file and the SQL Server table? If there's a bad record, based on a condition that you're going to write is bad, go to the new sql dump table, otherwise, go to the "good" table.---http://www.ssisdude.blogspot.com/ |
|
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2008-04-18 : 11:59:23
|
| Yah, I have been trying to make that conditional split logic work.My challenge is that I do not know all the conditions that will define as bad data in advance. Ironically, if I do, my mission is complete. I don't need to design this step anymore.I am really exploing SSIS as if to ask it load whatever it can handle to the good table, and load anything chock it, along with error message, into another table for further analysis. |
|
|
|
igorblackbelt
Constraint Violating Yak Guru
407 Posts |
Posted - 2008-04-18 : 14:18:39
|
| You're kind of stuck, I've been through this scenario before back when we were using DTS. I had to create a huge VB script that read and cleaned up the file into a new file so then I could load this new file into the database. But I knew exactly what to remove from the file, I knew what needed to be cleaned up and you seem that you don't know...---http://www.ssisdude.blogspot.com/ |
|
|
|
jrea8830
Starting Member
4 Posts |
Posted - 2008-04-18 : 19:35:56
|
| Hommer,Well, you do already have the ability to log the error and the bad record. In SSIS Data Flow Task you can use the "Failed" precedent to point to another flat file connection manager (that will act as a log file).Basically, in SSIS Data Task flow you drag the 'failed' precedent over to your flat file connection and fill in the appropriate information.Here's a brief example (step-by-step):First create an empty text file and place it somewhere on your harddrive (I'll use C:\ for my example). Add another 'Flat File Destination' to your data flow task. On your 'Flat File Source [1]' task you will click the red 'failed' precedence output line and drag it to the new 'Flat File Destination' you just added. A 'Configure Error Output' dialog box will appear; here is where the most important step is! You change the "Error" column from "Fail component" to "Redirect Row". Click OK. Now, go into your 'Flat File Destination' task, this will pop up the 'Flat File Destination Editor'; here you will click on "New..." and that will bring up another window where you will select the file format for your new flat file. I typically choose the default option; but I'm sure you are aware of the different options here...so choose which is appropriate for what you want, then click OK. This now brings up the "Flat File Connection Manager Editor" (looks like the destination editor, just a little different..this is where you can put in what you want logged). Click on "Browse" next to the File Name textbox, and browse to that blank text file you made at the beginning. Now, here comes another important step...click on the "Columns" node to the right. This will automatically fill in the columns that will get logged, notice that there are two additional columns "Error Code" and "Error Column"? These will provide you with the error logging information you were just asking for.Now, select OK to set the settings for the "Flat File Connection Manager Editor". Select OK to set the settings for the "Flat File Destination Editor".Now you are all set to go! Try it out; watch it pass the failed data into your text file and it will log the error that it failed with.Sorry I didn't understand what you were wanting first. The basic idea behind the above here is that you are telling SSIS that if something fails, instead of failing the package I want you to "Redirect" the row to this text file. Now, also take a look around inside the Flat File Destination editor and Flat File Connection Manager editor. There are options such as how to delimit the data passed into the tex file, optional to add a header that will appear ONLY at the very beginning of the file (not at top of the rows, this isn't replacing your row headers if you included them). You can tell it to APPEND or OVERWRITE existing data.I use this technique whenever I have a database in another format than SQL (such as Access) and during the conversion I'll sometimes run into problems with dates! Instead of stopping the package each time and restarting the whole process I just have SSIS log the failed row and error code; and I can later review these "error logs" I created and manually handle the failed records.I hope this helps you a little more than the previous reply.Have a good weekend,James |
|
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2008-04-21 : 09:05:21
|
| Thank for both of you.James' idea of adding another flat file between my flat source and sql destination sovlves my problem.That middle step gave me the capability to letting the process to ignore the invalid column delimiter and rwo delimitor, which are two main irregularities of my source data. Again, I am not trying to sovle a specific case, rather I am building a generic solution that can be resued in many SSIS to handle various unknown possible bad data as the first step to get them into sql server. Then use t-sql's power, instead of text editor to analyze them. |
|
|
|
barb0822
Starting Member
1 Post |
Posted - 2011-02-16 : 11:01:36
|
| Hello, had same problem and wanted to redirect row to find out what data is causing problem but this did not work for me. i have a flat file source connected to an oledb destination - added a failed constraint from flat file source to flat file destination and followed the instructions but error did not get logged in the flat file destination and process inserted partial records. can you give me any ideas what could have went wrong?barb |
|
|
|
|
redirect bad data from flat file source