I cannot seem to get my flat file to write columns in error when inserting into a SQL table. I have tried a few examples from MS and did not get anything written to my flat file output. I have set the Source Error Output on this flat file and it uses a script task to created the error description and then write it to a Flat File Destination.
I am new to SSIS and have not had any formal training on it. However, I am very familiar with VS.Net/c# and SQL 2000 DTS - I need to get this working ASAP as there are 45 total flat files that need to be processed. Once I have this solved for one, the rest will follow suit.
If more details would help, I can provide them.
Kind Regards,
Ron
Yep, please provide more details. Any error messages? What doesn't work, exactly?|||I'm confused about what you're trying to do. Can you try the description again?|||
Well, I have my flat file source and my SQL server destination. One flat file represents a table and the SQL destination represents one table. The columns are all mapped one-to-one exactly. I had to modify the flat file source data types to get rid of the data type errors. When I run the package, this task fails because of a datatype error. One of the datetime fields is not valid. The flat file that I am having troubles with has 145 columns and about 25000 rows. I have 45 total flat files I need to process. The one that I am having troubles with and would like to log the errors for, is the longest in terms of columns.
Currently, I have the Error Output going to a Script Component that has an output column for Error Description. Under this I have the Flat File Destination that I would like to write the error to. I have tested with a smaller flat file and I manually corrupted the file so it would fail, but this still did not write any error output.
I honestly did not think that what I was doing with SSIS was going to be this challenging, but I was wrong. The high-level overview of what I am ultimately trying to do is process 45 flat files into a SQL DB with 45 tables. The field names in both source and destination are the same. We would eventually like to add some process logging and other check points, but we have to get this proof of concept going first.
Hope this helps a bit more.
|||
ronemac wrote:
Well, I have my flat file source and my SQL server destination. One flat file represents a table and the SQL destination represents one table. The columns are all mapped one-to-one exactly. I had to modify the flat file source data types to get rid of the data type errors. When I run the package, this task fails because of a datatype error. One of the datetime fields is not valid. The flat file that I am having troubles with has 145 columns and about 25000 rows. I have 45 total flat files I need to process. The one that I am having troubles with and would like to log the errors for, is the longest in terms of columns.
Currently, I have the Error Output going to a Script Component that has an output column for Error Description. Under this I have the Flat File Destination that I would like to write the error to. I have tested with a smaller flat file and I manually corrupted the file so it would fail, but this still did not write any error output.
I honestly did not think that what I was doing with SSIS was going to be this challenging, but I was wrong. The high-level overview of what I am ultimately trying to do is process 45 flat files into a SQL DB with 45 tables. The field names in both source and destination are the same. We would eventually like to add some process logging and other check points, but we have to get this proof of concept going first.
Hope this helps a bit more.
In the error output area of the Source Adapter, have you made sure that Redirect Row is set on all the appropriate choices (Error and Truncation for each column)?
|||Yes sir.|||So what I have is this:Flat File Source --> SQL Server Destination
After this fails (the SQL Server Destination is Red while the Flat File Source is green) I look at the Execution Results and I see that the SQL Server Destination had a conversion issue - (Error converting data type DBTYPE_DBTIMESTAMP to datetime)
Unfortunately, there are roughly 20 datetime columns in this table - 145 total columns - so I have no idea which column in which row is failing.
I would like to capture each row (row ID) and column that is failing so I can log it and reject the file. I have tried adding a script task and using the Error Output from the Flat File Source, but I don't even know if this is the correct place. I am sure the file structure looks just fine to SSIS, it's when SQL tries to insert the data that the DT conversion fails.
Suggestions?|||
Redirect errors from the SQL Server destination.
|||That is what I WANT to do, but did not think that was possible - there is no Red arrow for error output on the SQL Destincation Data Flow Component... DId I mention that I was new to SSIS? Sorry.
|||Ron,
Some stuff here that may help:
Get error descriptions
(http://blogs.conchango.com/jamiethomson/archive/2005/08/08/SSIS-Nugget_3A00_-Get-error-descriptions.aspx)
Enhanced Error Transform (adds the name of the column in which the error occurred):
(http://sqlblogcasts.com/blogs/simons/archive/2005/09/21/637.aspx)
-Jamie
|||Yep, sorry, try using an OLE DB destination instead.
SQL Server destinations are an "in-memory" hook to SQL Server. It is a strongly typed destination and requires that all fields are the correct data type and constraints have been met, etc...
|||Cool.. I will give it a rip. Thanks again.
|||Right on! And thank you for your help!sql
没有评论:
发表评论