export multiple table with ssis

Question

I have 2 tables are like this (Table1 and Table2)

ID  NAME             No      Addrress     Notes
------------        ----------------------------
1   John            111      USA          Done
2   Steve           222      Brazil       Done

Now I want to create a SSIS package which will create a csv file like:

Table1;ID;NAME             
Table2;No;Addrress;Notes  
"Detail1";"1";"John";"2";"Steve"           
"Detail2";"111";"USA";"Done";"222";"Brazil";"Done"

Can we achieve the same output? I have searched on google but haven't found any solution.

Please help ....

Looks simple enough but I wonder why you would want this format? — David Söderlund, Oct 17 '17 at 07:40
Isn't there a SAP migration tool that uses SQL Server? Or like a SAP connector for SSIS? Do you absolutely have no choice but to export to csv in this format first? — David Söderlund, Oct 17 '17 at 07:54
What have you tried so far? One way of solving it is casting the resulting set from the tables to XML and replacing characters for the tags to csv separators. — David Söderlund, Oct 17 '17 at 07:55
When looking at your output for a csv, it strikes me that SAP is expecting a concateneation of key-value pairs. You could make a function in C# in a dataflow transformation inside your SSIS package that converts each of the columns to a key value pair and then adds them to some output column. See this example: https://stackoverflow.com/questions/14495679/how-to-convert-datatable-to-listkeyvaluepairstring-int — David Söderlund, Oct 17 '17 at 08:24

SqlKindaGuy · Answer 1 · 2017-10-17T09:14:03.187

You can create a script task to generate a CSV file for you which can handle your issue:

You can try this:

 SqlConnection sqlCon = new SqlConnection("Server=localhost;Initial Catalog=LegOgSpass;Integrated Security=SSPI;Application Name=SQLNCLI11.1");

        sqlCon.Open();
        SqlCommand sqlCmd = new SqlCommand(@"Select ID,Name from dbo.Table1", sqlCon);
        SqlDataReader reader = sqlCmd.ExecuteReader();


        string fullpath = @"C:\Users\thoje\Desktop\stack\New folder\table1.csv";
        StreamWriter sw = new StreamWriter(fullpath);
        object[] output = new object[reader.FieldCount];

        for (int i = 0; i < reader.FieldCount; i++)
            output[i] = reader.GetName(i);

        sw.WriteLine(@"Table1;"+string.Join(";", output));

        List<object> values = new List<object>();
        while (reader.Read())
        {
            reader.GetValues(output);

            values.Add($"\"{output[0]}\"");
            values.Add($"\"{output[1]}\"");



        }

        sw.WriteLine(@"""Detail1"";"+ string.Join(";", values));
        sw.Flush();
        sw.Close();
        reader.Close();
        sqlCon.Close();


        Dts.TaskResult = (int)ScriptResults.Success;

Result:

score 0 · Accepted Answer · answered Oct 17 '17 at 08:15

You really should put in your question what you have tried so far, it helps out a lot and makes it more fun to help people.

The two ways I can think of in t-sql to solve this still need you to specify in your code what your column names are. You can get around this with using dynamic SQL and creating a view that spits out data in the same fashion for all the tables you need.

If SSIS is more your thing you could use the dynamic approach with BIML.

--Option 1 (SQL Server 2008 R2 and later)
with Table1 AS (
SELECT * FROM (values(1,'John'),(2,'Steve')) AS x(ID,NAME)
)
,Table2 AS (
SELECT * FROM (values(111,'USA','Done'),(222,'Brazil','Done'))AS y(No,Addrress,Notes)
)
SELECT '"Detail1"'+ CAST(foo as VARCHAR(4000))
FROM (
SELECT ';"' + CAST(ID AS VARCHAR(4))+'";"' + [NAME] +'"'  FROM Table1 FOR XML PATH('')
) AS bar(foo)
UNION ALL
SELECT '"Detail2"'+ CAST(foo as VARCHAR(4000))
FROM (
SELECT ';"' + CAST([No] AS VARCHAR(4))+'";"' + [Addrress] +'";"' + [Notes] +'"'  FROM Table2 FOR XML PATH('')
) AS bar(foo)



--Option 2 (SQL Server 2017 and later)
with Table1 AS (
SELECT * FROM (values(1,'John'),(2,'Steve')) AS x(ID,NAME)
)
,Table2 AS (
SELECT * FROM (values(111,'USA','Done'),(222,'Brazil','Done'))AS y(No,Addrress,Notes)
)
SELECT '"Detail1";' + STRING_AGG('"'+CAST(ID AS varchar(4))+'";"'+[NAME]+'"',';') FROM Table1
UNION ALL
SELECT '"Detail2";' + STRING_AGG('"'+CAST([No] AS varchar(4))+'";"'+[Addrress]+'";'+'"'+[Notes]+'"',';') FROM Table2
;

export multiple table with ssis

2 Answers2