避免SSIS脚本任务将utf-8转换为将AS400数据的Unicode转换为SQL Server [英] Avoiding SSIS script task to convert utf-8 to unicode for AS400 data to SQL Server

问题描述

经过多次尝试，我得出结论，将SSIS数据从AS400（非Unicode）传输到SQL Server的最佳方法是：

1. 使用本机传输实用程序将数据转储到tsv（制表符分隔）




2. 将文件从utf-8转换为unicode




3. 使用批量插入将它们放入SQL Server

在＃第2步，我找到了执行此操作的现成代码：

 来自= @ \\appsrv02\c的字符串$ \bg_f0101.tsv; 
字符串为= @ \\appsrv02\c $ \bg_f0101.txt; 
使用（StreamReader reader = new StreamReader（from，Encoding.UTF8，false，1000000））
使用（StreamWriter writer = new StreamWriter（to，false，Encoding.Unicode，1000000））
 {
 while（！reader.EndOfStream）
 {
 var line = reader.ReadLine（）; 
 if（line.Length> 0）
 writer.WriteLine（line）; 
} 
} 
  

我需要完全了解此处发生的情况

如何用更优雅的解决方案替换此脚本任务？

解决方案

我对为什么需要utf-8转换任务没有太多了解，只是说SQL Server（我相信）使用UCS-2作为其本机存储格式，并且这类似于UTF-16，这是您的任务将文件转换为的内容。我很惊讶SSIS不能使用UTF-8输入源。

我的主要观点是回答如何用一个脚本替换这个脚本任务？更优雅的解决方案？：

使用 HiT OLEDB / 400服务器。它允许您将AS / 400 / iSeries / System i / IBM本周将其称为SQL服务器中的链接服务器，然后您可以直接使用标准4链接到的服务器直接访问400的数据。部分SQL语法，例如选择*从my400.my400.myLib.myFile。
甚至更好，使用EXEC ... AT作为直通查询效率更高。

使用此功能，您根本不需要SSIS，您只需要一个简单的存储proc，即可直接从400数据插入目标表。 / p>

After many tries I have concluded that the optimal way to transfer with SSIS data from AS400 (non-unicode) to SQL Server is:

1. Use native transfer utility to dump data to tsv (tab delimited)

2. Convert files from utf-8 to unicode

3. Use bulk insert to put them into SQL Server

In #2 step I have found a ready made code that does this:

string from = @"\\appsrv02\c$\bg_f0101.tsv";
        string to = @"\\appsrv02\c$\bg_f0101.txt";
        using (StreamReader reader = new StreamReader(from, Encoding.UTF8, false, 1000000))
        using (StreamWriter writer = new StreamWriter(to, false, Encoding.Unicode, 1000000))
        {
            while (!reader.EndOfStream)
            {
                var line = reader.ReadLine();
                if (line.Length > 0)
                    writer.WriteLine(line);
            }
        }       

I need to fully understand what is happening here with the encoding and why this is necessary.

How can I replace this script task with a more elegant solution?

解决方案

I don't have much insight into exactly why you need the utf-8 conversion task, except to say that SQL server - I believe - uses UCS-2 as its native storage format, and this is similiar to UTF-16 which is what your task converts the file to. I'm surprised SSIS can't work with a UTF-8 input source though.

My main point is to answer the "How could I replace this script task with a more elegant solution?":

I have had a lot of success using HiT OLEDB/400 Server. It allows you to set up your AS/400 / iSeries / System i / whatever IBM are calling it this week as a linked server in SQL server, and you can then access the 400's data directly from the server its linked to using the standard 4 part SQL syntax, e.g. SELECT * FROM my400.my400.myLib.myFile. Or even better, it's much more efficient as a passthrough query using EXEC...AT.

Using this you would not need SSIS at all, you'd just need a simple stored proc with that does an insert into your destination table direct from the 400 data.

这篇关于避免SSIS脚本任务将utf-8转换为将AS400数据的Unicode转换为SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！