SSIS excel Destination，如何强制LongText？
我正在使用 SSIS 执行数据迁移。
我正在使用 Excel 目标文件来输出出现问题的所有内容。
在此 Excel 文件中，我想输出两个错误列（错误编号和错误列）以及输入组件中的所有列。
这几乎可以正常工作，除非我的字符串列超过 255 个字符。当我设置 Excel 目标时，我创建一个新表。
Create Table 语句将 Longtext 正确定义为数据类型：
CREATE TABLE `My data` (
`ErrorCode` Long,
`ErrorColumn` Long,
`ID` Long,
`MyStringColumn` LongText
)
这是第一次。然后，我从 Excel 文件中删除所有数据，因为我想在输出错误之前清理 Excel 文件。
当我返回包设计器时，我的列定义混乱了。每个文本列都作为 nvarchar(255) 处理，不再是 ntext。由于我的数据超过 255，这会破坏我的组件。
如何正确管理 Excel 目标？
thx
[编辑] 由于我不确定我的解释，这里是我运行任务时的错误消息：
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.
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
在涉及 Excel 目标的 SSIS 包中，我使用了 Excel 模板文件格式策略来克服您遇到的错误。
这是一个示例，首先展示如何模拟错误消息，然后展示如何修复它。该示例使用 SSIS 2008 R2 和 Excel 97-2003。
模拟
创建了一个包含两个字段
Id和Description的简单表。用几条记录填充表。使用单个数据流任务创建了一个 SSIS 包，并且数据流任务的配置如下所示。它基本上从上述 SQL Server 表中读取数据，然后尝试将“描述”列转换为字符长度设置为 20 的 Unicode 文本。
由于表中有两行的描述列值长度超过 20 个字符，因此数据转换转换上的默认错误配置设置将使包失败。但是，我们需要重定向所有错误行。因此，必须如下所示更改数据转换任务上的错误配置以重定向错误行。
然后，我将错误输出重定向到 Excel 目标，该目标配置为将输出保存到路径
C:\temp\Errors.xls。第一次执行该包将会成功，因为 Excel 文件一开始就是空的。该文件将包含表中的两行，因为这两行都会遇到截断错误，因此重定向到错误输出。
在不更改列标题的情况下删除Excel文件中的内容后，如果我们再次执行包将会失败。
失败的原因可能是由于以下所示的错误消息。
这样就完成了问题中提到的错误的模拟。这是解决该问题的一种可能方法。
可能的解决方案
删除错误输出重定向到的现有 Excel 文件目标。创建一个新的 Excel 连接管理器，路径为
C:\temp\Template.xls。放置一个新的 Excel 目标并将其指向新的 Excel 连接管理器，并使用 Excel 目标上的“新建”按钮在新的 Excel 文件中创建工作表。创建两个名为
TemplatePath和ActualPath的包变量。 TemplatePath 的值应为C:\temp\Template.xls，ActualPath 的值应为C:\temp\Errors.xls。实际路径是您希望创建文件的路径。右键单击 Excel 连接管理器并将
DelayValidation属性设置为 False 并将ServerName表达式设置为变量 @[User::ActualPath]。如果文件C:\temp\Errors.xls不存在，DelayValidation 将确保包在设计时不会引发错误。设置 ServerName 表达式将确保包将使用变量 ActualPath 中提到的文件路径来生成文件。在“控制流”选项卡上，将“文件系统任务”置于“数据流任务”之上。
配置文件系统任务，如下所示。因此，文件系统任务每次都会复制模板文件
C:\temp\Template.xls并创建一个新的目标文件C:\temp\Errors.xls包运行。如果文件C:\temp\Errors.xls已存在，则当文件系统任务中的OverwriteDestination属性设置为正确。现在，您可以继续运行该包任意次。该包不会失败，而且您只会看到上次执行的错误消息，而无需手动清除 Excel 文件内容。
希望有帮助。
[编辑] 由 Steve B. 添加，直接在帖子中提供更多详细信息，因为评论太长
在我的解决方案中，我的 SSIS 项目中有两个 Excel 文件：
Errors_Design_Template .xls和Errors_Template.xls'。前一个文件包含我的工作表，其中包含标题和一行数据（对于最大长度为 1024 的输入列使用像=Rept("A",1024)` 这样的公式），后者与第一个文件完全相同数据行。这两个文件都在包的开头从我的源目录复制到临时目录。我使用两个文件是因为我想保留设计时验证，并且我指向 Excel 连接中的模板文件的副本。我复制模板文件也是因为我经常执行包的单个数据流任务，并且我想填充临时文件，而不是项目中的模板文件（它必须保持为空，但标题和第一个虚拟数据行）。
我还使用了两个变量，一个用于 Excel 连接表达式，一个用于实际输出文件。我还必须编写一个脚本，将两个变量作为输入。实际文件路径是读/写的。该脚本在运行时将 ActualFilePath 的值复制到 ErrorFilePath 变量。 （我现在还没有源代码，但如果有帮助的话我可以在下周粘贴它）。
一起使用该组件使我能够在设计时让 Excel 连接指向设计文件，并在运行时指向实际的错误文件，而无需将延迟验证设置为 true。
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
IdandDescription. 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
TemplatePathandActualPath. TemplatePath should have the valueC:\temp\Template.xlsand the ActualPath should have the valueC:\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
DelayValidationproperty to False and set theServerNameexpression to the variable @[User::ActualPath]. DelayValidation will make sure that the package doesn't throw errors during design time if the fileC:\temp\Errors.xlsdoesn'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.xlsand will create a new destination fileC:\temp\Errors.xlsevery time the package runs. If the fileC:\temp\Errors.xlsalready exists, then the File System Task will simply overwrite the file when theOverwriteDestinationproperty 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.xlsandErrors_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.
最好在控制流中使用“执行任务”。在执行任务中指定与 Excel 连接管理器的连接。在 SQL 语句中，将在 Excel 目标中创建工作表期间创建的 Excel 表删除。删除后创建相同的表。因此下次数据将插入 Excel 表中。
its better to use a 'execute task' in control flow.In execute task specify the connection to excel connection manager.In the SQL statement drop the excel table which is created during the sheet creation in excel destination. after drop create the same table.hence next time the data will be inserted in excel table.