SSIS ISNULL 为空字符串
因此，我目前正在使用 SSIS 2008 从旧的 Advantage 数据库服务器迁移到 SQL 2005。旧的 Advantage 数据库中的列之一是 MEMO 类型。默认情况下，这会转换为 DT_TEXT 列。那么在新数据库中我不需要这么大的字段，但可以将其限制为诸如 VARCHAR(50) 之类的字段。我成功设置了派生列转换，以使用以下表达式进行转换：
(DT_STR,50,1252)[ColumnName]
现在我想更进一步，用空字符串替换所有 NULL 值。使用 ISNULL([ColumnName])?"":(DT_STR,50,1252)[ColumnName] 表达式似乎很容易，但问题是 OLE DB 目标包含以下错误
无法在 unicode 和非 unicode 字符串之间转换...
因此显然整个 ISNULL 表达式将数据类型转换为 Unicode 字符串 [DT-WSTR]。我已经尝试对整个表达式或不同部分进行各种转换，但我无法获得与我需要的数据类型相匹配的数据类型。
首先，是否可以将DT_TEXT类型直接转换为unicode？据我所知，演员阵容并不是这样工作的。如果没有，是否有办法让表达式起作用，以便将 NULL 值转换为空字符串？
感谢您的帮助！
So I am currently working on a migration from an old Advantage database server to SQL 2005 using SSIS 2008. One of the columns in the old Advantage database is a MEMO type. By default this translates to a DT_TEXT column. Well in the new database I do not need this large of field, but can limit it to something such as VARCHAR(50). I successfully set up a derived column transformation to convert this with the following expression:
(DT_STR,50,1252)[ColumnName]
Now I want to go a step further and replace all NULL values with an empty string. This would seem easy enough using an ISNULL([ColumnName])?"":(DT_STR,50,1252)[ColumnName] expression, but the problem is that the OLE DB Destination contains the following error
Cannot convert between unicode and non-unicode strings...
So apparently the whole ISNULL expression converts the data type to Unicode string [DT-WSTR]. I have tried a variety of casts upon the whole expression or different parts, but I cannot get the data type to match what I need it.
First, is it possible to convert the DT_TEXT type directly to unicode? From what I can tell, the casts don't work that way. If not, is there a way to get an expression to work so that NULL values get converted to empty strings?
Thank you for all your help!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
在您的派生专栏中尝试一下。
它包括一个附加类型转换，并在括号中使用条件 (?:)，以确保所需的处理顺序。我认为您的原始表达式隐式转换为 DT_WSTR，因为“”默认为 DT_WSTR。在这个新版本中，您可以在计算表达式后强制转换为 DT_STR。
Give this a try in your derived column.
It includes an additional type cast with the Conditional (?:) in parentheses to ensure the desired processing sequence. I think your original expression was implicitly casting to DT_WSTR because the "" defaults to DT_WSTR. With this new version, you force the cast to DT_STR after the expression is evaluated.
我想出了一些有效的方法。这可能不是最好的解决方案，但它适合我的情况。
从我的 OLE DB 源中，我首先创建了一个派生列。我使用了 ISNULL，最终将其转换为 DT_WSTR unicode 类型。尽管我无法进行任何强制转换以将其恢复为所需的类型，但我随后在派生列和 OLE DB 目标之间添加了数据转换转换。这将获取输入字符串并将其转换回 DT_STR。这一切都让人感觉有点烦人，多次转换，但该专栏不包含任何我应该担心的时髦信息，所以我想它会起作用。
感谢所有思考该解决方案的人，如果您找到一些很棒的方法来解决它，我将非常感兴趣。
I figured something out that works. It may not be the best solution, but it will work for my situation.
From my OLE DB source I first did a Derived Column. This I used the ISNULL which ended up converting it to a DT_WSTR unicode type. although I could not get any casts to get it back to the type required, I then added a Data Conversion transformation in-between the Derived Column and the OLE DB Destination. This would take the input string and convert it back to a DT_STR. This all feels a little annoying converting so many times, but the column does not contain any funky information that I should have to worry about, so I suppose it will work.
Thanks for all those who pondered the solution, and if you find some awesome way to tackle it, I would be more than interested.