import flat file

I am trying to import a flat file using SQL Server Management Studio and am receiveing the error:

Error 0xc0202055: Data Flow Task: The column delimiter for column "Column 19" was not found.
(SQL Server Import and Export Wizard)

I would like to capture the rows that are causing the error and have the import continue. Am I able to edit the behavior somehow?

Thanks.

If I recall correctly; in one of the last steps of the import wizard you are asked to either execute the package immediately or to save it as a .dtsx file.

Chose to save it as a .dtsx files and the use BIDS to edit it according to your needs.

|||

Hi Rafael, thanks for responding. I get to the screen where it tells me that ' The package will be saved to the instance of the SQL Server Database Engine ...', but it doesn't allow me to choose the file extension. Yes, I'm very new at this.

Thank you again, I really appreciate your help.

|||

Hi, I was able to save the .dtsx file. Now on to BIDS.

Thanks.

|||

I've been trying to edit the package in BIDS but am not having success. I'm importing a flat file and am trying to write the records that do not get imported successfully to an error file. If anyone can help me out.....

Thanks.

|||

tantz wrote:

I've been trying to edit the package in BIDS but am not having success. I'm importing a flat file and am trying to write the records that do not get imported successfully to an error file. If anyone can help me out.....

Thanks.

You will have to tell us what you are not having success with first.|||

Well, I read that you can 'use the Error Output Page of the Flat File Source Editor dialog box to select error-handling options'. How do I do this? Thanks.

|||

tantz wrote:

Well, I read that you can 'use the Error Output Page of the Flat File Source Editor dialog box to select error-handling options'. How do I do this? Thanks.

On the data flow, you grab the red arrow of the flat file source and connect it to a destination (flat file, sql server, etc...)|||

Thanks Phil. I was able to connect it to a flat file, and am getting the same errors. Where can we find this file with the errors? Also, is there a way to make the package continue processing even when there are errors?

I really appreciate your time and help.

|||

Open the Flat file Source editor; go to the error output page and make sure all the values in Error and truncation columns are 'Redirect Row'. Tip You can Shift+ Select al of them and then use the 'Set this value to the selected Cells'. After doing that, make sure you connect the red arrow to a file or any other destination; so you can examine it later.

|||Hi, thanks for your reply.
I don't think I'm looking in the right place - can you tell me exactly how you're getting to the error output page. Thanks so much.|||When you double click on the flat file source, there is a button, "error output" or something like that.|||

Cool, I got it, but I still am not able to see the records. I've connected the flat file source with a red arrow 'flat file source error output'. What else am I missing? Anything with mappings? An error list is displayed at the bottom, but it has 0 errors, 0 warnings, 0 messages, but I know I am getting errors.

Thanks so much.

|||

Hi, I think I've worked out the error output, but I seem to have created a different problem. I am now getting a message 'cannot open the data file. the process cannot access the file because it is being used by another process'.

It's a .txt file. Nothing else is using it. I just ran something in SQL Server Management Studio using the same file and it works fine. Is it possible that I changed something in the .dtsx package when I was trying to get the error output to work?

If anyone can help me out -
Thanks.