I'm using SSIS to perform data migration.

I'm using an Excel destination file to output everything that's going wrong.

In this Excel file, I want to output the two Errors column (Error number and Error column) and also all columns from my input component.

This is nearly working except when I have string columns having more than 255 characters. When I set up my Excel destination, I create a new Table.

The Create Table statement defines Longtext properly as the data type :

CREATE TABLE `My data` (
    `ErrorCode` Long,
    `ErrorColumn` Long,
    `ID` Long,
    `MyStringColumn` LongText
)

This works the first time. Then, I remove all data from the Excel file because I want to clean up the excel file before outputing errors.

When I return in the Package designer, my columns definitions are messed up. Every text columns are handled as nvarchar(255), and no more ntext. That breaks my component as my data is exceeding 255.

How can I properly manage excel destinations ?

thx

[Edit] As I'm not sure of my interpretation, here is the errors message when I run the task :

Error: 0xC0202009 at MyDataTask, To Errors file [294]: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E21.
Error: 0xC0202025 at MyDataTask, To Errors file [294]: Cannot create an OLE DB accessor. Verify that the column metadata is valid.
Error: 0xC004701A at MyDataTask, SSIS.Pipeline: component "To Errors file" (294) failed the pre-execute phase and returned error code 0xC0202025.

In SSIS packages that involve Excel Destination, I have used an Excel Template file format strategy to overcome the error that you are encountering.

Here is an example that first shows how to simulate your error message and then shows how to fix it. The example uses SSIS 2008 R2 with Excel 97-2003.

Simulation

Created a simple table with two fields Id and Description. Populated the table with couple of records.

Created an SSIS package with single Data Flow Task and the Data Flow Task was configured as shown below. It basically reads the data from the above mentioned SQL Server table and then tries to convert the Description column to Unicode text with character length set to 20.

Since the table has two rows that have Description column values exceeding 20 characters in length, the default Error configuration setting on the Data Conversion transformation would fail the package. However, we need to redirect all the error rows. So the Error configuration on the Data conversion task has to be changed as shown below to redirect the error rows.

Then, I have redirected the Error output to an Excel Destination that is configured to save the output to a file in the path C:\temp\Errors.xls. First execution of the package would be successful because the Excel file is empty to begin with.

The file will contain both the rows from table because both would have encountered the truncation error and hence redirected to the error output.

After deleting the contents in the Excel file without changing the column header, if we execute the package again it will fail.

Cause of the failure would be due to the error messages shown below.

That completes the simulation of the error mentioned in the question. And here is one possible way that the issue could be fixed.

Possible Solution

Delete the existing Excel File Destination to which the error output is redirected to. Create a new Excel Connection manager with the path C:\temp\Template.xls. Place a new Excel Destination and point it to the new Excel connection manager and also create the sheet within the new Excel file using the New button on the Excel Destination.

Create two package variables named TemplatePath and ActualPath. TemplatePath should have the value C:\temp\Template.xls and the ActualPath should have the value C:\temp\Errors.xls. the actual path is the path where you would like the file to be created.

Right-click on the Excel Connection Manager and set the DelayValidation property to False and set the ServerName expression to the variable @[User::ActualPath]. DelayValidation will make sure that the package doesn't throw errors during design time if the file C:\temp\Errors.xls doesn't exist. Setting the ServerName expression will ensure that the package will use the file path mentioned in the variable ActualPath to generate the file.

On the Control Flow tab, place a File System Task above the Data Flow task.

Configure the File System Task as shown below. So, the File System Task will copy the Template file C:\temp\Template.xls and will create a new destination file C:\temp\Errors.xls every time the package runs. If the file C:\temp\Errors.xls already exists, then the File System Task will simply overwrite the file when the OverwriteDestination property within the File System Task is set to True.

Now, you can continue to run the package any number of times. The package will not fail and also you will have only the error messages from the last execution without having to manually clear the Excel file content.

Hope that helps.

[Edit] Added by Steve B. to provide a bit more details directly in the post because its too long for a comment

In my solution, I have in my SSIS project tow Excel files: Errors_Design_Template.xls and Errors_Template.xls'. The former file contains my sheets with the headers and one line of data (using formulas like =Rept("A",1024)` for input columns having 1024 length max), the latter is exactly the same without the first line of data.

Both files are copied at the start of the package from my source directory to temp directory. I use two files because I want to keep the design time validation, and I’m pointing to the copy of the template file in the Excel connection. I’m duplicating the template file also because I’m often executing a single data flow task of my package, and I want to populate a temp file, not the template file in my project (which has to remain empty but the headers and the first dummy line of data).

I also used two variables, one to use in Excel connection expression, one for the actual output file. I also had to write a script having my two variables as input. ActualFilePath is read/write. The script copies at run-time the value of the ActualFilePath to the ErrorFilePath variable. (I don’t have the source code by now, but I can paste it next week if it can helps).

Using this component together allows me to have the Excel connection pointing to the design file while designing, and pointing to the actual error file at run-time, without having to set the delayvalidation to true.