SSIS生成JSON文件删除返回 [英] ssis generate json file remove return

问题描述

我正在使用脚本任务通过sql查询生成json文件。

I am using a script task to generate a json file from a sql query.

脚本任务中的c＃代码：

public void Main()
{
    // TODO: Add your code here

    ConnectionManager cm;
    string sqlString = "";

    System.IO.StreamWriter file = new System.IO.StreamWriter(@"f:\JSONOutput.txt");

    sqlString = "SELECT * FROM[dbo].[JJVCACUProductElectron] where id in (1,2,3) for json auto";
    System.Data.SqlClient.SqlConnection sqlConn;
    System.Data.SqlClient.SqlCommand sqlComm;

    cm = Dts.Connections["crm_vm_2017_cs_dotnet"];

    sqlConn = (System.Data.SqlClient.SqlConnection)cm.AcquireConnection(Dts.Transaction);
    sqlComm = new System.Data.SqlClient.SqlCommand(sqlString, sqlConn);
    System.Data.SqlClient.SqlDataReader reader = sqlComm.ExecuteReader();
    try
    {
while (reader.Read())
{

    file.WriteLine(reader[0]);
}
    }
    finally
    {
// Always call Close when done reading.
reader.Close();
    }

    cm.ReleaseConnection(sqlConn);
    Dts.TaskResult = (int)ScriptResults.Success;
}

生成的输出文件不完整，我想某些文件可能会返回柱。如何删除输出中的返回字符？

The generated output file is incomplete, I guess there is probably a return in some column. How to remove the return characters in the output ?

推荐答案

工作代码：从select发送数据时，似乎是sql server ...对于json auto，将自动吐出一定大小的行。但是，行末没有crlf。因此，file.write方法可以连续地将字符串连接起来，以在文件中形成一个大的单行字符串，这是有效的json文件。我以前的代码使用了writeline，它可能在每个字符串的末尾添加了crlf。而且关键是using（文件）部分，否则可以看到输出文件被截断了，这就是我原始帖子中的问题。

The working code: it seems sql server when sending out data from the select ... for json auto will automatically spit rows at certain size. But there is no crlf at the end of the row. So the file.write method can continuously concatenate the strings to form one big single line string in the file, which is valid json file. My previous code used writeline, which probably added crlf at the end of each string. And also the key is the using (file) part, otherwise can see the output file got truncated, which is the problem in my original post.

        public void Main()
        {
            ConnectionManager cm;

            using (System.IO.StreamWriter file = new System.IO.StreamWriter(@"e:\jsontest"))
            {
                System.Data.SqlClient.SqlConnection sqlConn;
                System.Data.SqlClient.SqlCommand sqlComm;

                cm = Dts.Connections["crm_vm_2017_cs_dotnet"];

                sqlConn = (System.Data.SqlClient.SqlConnection)cm.AcquireConnection(Dts.Transaction);
                sqlComm = new System.Data.SqlClient.SqlCommand("select * from JJVCProduct for json auto", sqlConn);
                System.Data.SqlClient.SqlDataReader reader = sqlComm.ExecuteReader();
                System.Text.StringBuilder sb = new System.Text.StringBuilder();
                try
                {
                    while (reader.Read())
                    {
                        file.Write(reader.GetValue(0).ToString());
                    }

                }
                finally
                {
                    reader.Close();
                }

                cm.ReleaseConnection(sqlConn);
                Dts.TaskResult = (int)ScriptResults.Success;
            }


        }

这篇关于SSIS生成JSON文件删除返回的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！