SSIS 平面文件源文本限定符被忽略 [英] SSIS Flat File Source Text Qualifier being ignored

问题描述

我正在使用 SSIS 将数据从平面文件插入数据库.

I am using SSIS to insert data from flat file to database.

我为此创建了数据流任务.我使用平面文件作为源和 ADO NET 目标来插入数据.

I have created Data Flow Task for that. I am using Flat File as Source and ADO NET Destination to insert data.

以下是我的平面文件源设置.

Below is how my setting looks like for Flat File Source.

下面是我的列"标签的样子

Below is how my "Columns" tab look like

当我运行使用出价并且数据正确插入数据库时​​，这可以正常工作.在本地运行时，它甚至可以与 DTEXEC.EXE 一起使用.

THIS WORKS FINE WHEN I RUN THAT USING BIDS AND DATA IS INSERTED PROPERLY INTO DATABASE. IT EVEN WORKS WITH DTEXEC.EXE WHEN RUN LOCALLY.

现在，问题在于使用 dtexec.exe 在服务器上执行包.在服务器上，数据已正确插入，但在将数据插入数据库时​​完全忽略给定的文本限定符(双引号).在本地运行时，同样的工作完全正常.我在下面附上了图片，说明它是如何存储在数据库中的.

Now, The problem is with executing the package on the server using dtexec.exe. On the server, data is inserted properly but the text qualifier (double quotes) given is totally ignored while inserting data to database. THE SAME WORKS TOTALLY FINE WHEN RUN LOCALLY. I have attached image below for how its stored in database.

我已经在本地和远程服务器上检查了 SQL SERVER 版本和 SSIS 版本，两者都相同.

I have checked SQL SERVER version and SSIS version locally and on remote server and both are same.

可能是什么问题?有人可以帮忙吗?

What can be the problem? Can anyone help?

推荐答案

于是我找到了解决这个问题的方法.感谢 LukeBI 的回答 这里

So I found a solution for this problem. Thanks to LukeBI answer here

创建一个名为 TextQualifier 的字符串变量并赋值(双引号)

Create a string variable called TextQualifier and assign the value " (double quotes)

选择连接管理器，然后在属性"窗口中选择表达式".见下文.

Select the connection manager, and in the Properties window select 'Expressions'. See below.

单击...，添加属性TextQualifier"并分配变量@[User::TextQualifier].见下图

Click ..., add the property 'TextQualifier' and assign the variable @[User::TextQualifier]. See below image

现在它工作正常.它现在甚至可以在 64 位操作系统上运行.

Now its working fine. It will even work on 64 bit OS now.

这篇关于SSIS 平面文件源文本限定符被忽略的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！