ssis generate json file remove return

Question

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

The c# code in the script task:

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 ?

What does "The generated output file is incomplete" mean? How do you know it is incomplete. You need to trace through your code and find out where it turns from complete to incomplete. — Nick.Mc, Jan 06 '19 at 12:16
Your code is writing multiple lines. Is this meant to write one JSON entity per line? To simplify, first write JSON for only _one_ record `WHERE id = 1` and see if it is valid — Nick.Mc, Jan 06 '19 at 12:18
The query suppose to return data of only one column and one row. What I found is it actually returns data with crlf in it so this may cause the above code return partial of the json data. So I was asking how to remove crlf from the output, so the output become a single string without crlf. — thotwielder, Jan 06 '19 at 13:20
Have you checked that it returns only one column and one row? The code allows for multiple rows and SQL certainly appears to be meant to return multiple rows. Again you need to inspect what you’re getting. — Nick.Mc, Jan 06 '19 at 13:22

score 1 · Accepted Answer · answered Jan 08 '19 at 03:53

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;
            }


        }

score 0 · Answer 2 · answered Jan 06 '19 at 20:31

Workaround

You can remove the for json auto part from the SQL command and import data into a DataTable, then export the DataTable to Json file.

Import data into DataTable

Read SQL Table into C# DataTable

Export DataTable to Json file

Convert datatable to JSON in C#

ssis generate json file remove return

2 Answers2

Workaround

Linked