title: Merging Two Different csv files(data) into one file in SSIS
body: <p>I have a situation right here: I created the table below:</p>
  
  <pre><code>CREATE TABLE temp1
  (
  ProcessName varchar(50),
  ProcessNo  varchar(50),
  ProcessPages varchar(50),
  ProcessClass varchar (50)
  )
  
  INSERT INTO temp1 VALUES ('PRO1','PR012','5','O');
  INSERT INTO temp1 VALUES ('PRO1','PR012','4','Y');
  INSERT INTO temp1 VALUES ('PRO1','PR012','10','Y');
  INSERT INTO temp1 VALUES ('PRO1','PR012','7','Y');
  INSERT INTO temp1 VALUES ('PRO1','PR012','6','Y');
  INSERT INTO temp1 VALUES ('PRO1','PR012','14','K');
  INSERT INTO temp1 VALUES ('PRO1','PR012','23','Y');
  INSERT INTO temp1 VALUES ('PRO1','PR012','45','L');
  INSERT INTO temp1 VALUES ('PRO1','PR012','52','Y');
  INSERT INTO temp1 VALUES ('PRO2','PR022','3','K');
  INSERT INTO temp1 VALUES ('PRO2','PR022','5','T');
  INSERT INTO temp1 VALUES ('PRO2','PR022','6','Y');
  INSERT INTO temp1 VALUES ('PRO2','PR022','5','Y');
  INSERT INTO temp1 VALUES ('PRO2','PR022','5','H');
  INSERT INTO temp1 VALUES ('PRO2','PR022','8','Y');
  INSERT INTO temp1 VALUES ('PRO2','PR022','5','T');
  INSERT INTO temp1 VALUES ('PRO2','PR022','2','Y');
  INSERT INTO temp1 VALUES ('PRO2','PR022','3','T');
  INSERT INTO temp1 VALUES ('PRO2','PR022','3','Y');
  INSERT INTO temp1 VALUES ('PRO3','PR032','5','Y');
  INSERT INTO temp1 VALUES ('PRO3','PR032','10','Y');
  INSERT INTO temp1 VALUES ('PRO3','PR032','15','Y');
  INSERT INTO temp1 VALUES ('PRO3','PR032','25','Y');
  INSERT INTO temp1 VALUES ('PRO3','PR032','35','Y');
  INSERT INTO temp1 VALUES ('PRO3','PR032','45','Y');
  INSERT INTO temp1 VALUES ('PRO3','PR032','55','Y');
  INSERT INTO temp1 VALUES ('PRO3','PR032','25','Y');
  INSERT INTO temp1 VALUES ('PRO3','PR032','25','Y');
  INSERT INTO temp1 VALUES ('PRO3','PR032','20','Y');
  INSERT INTO temp1 VALUES ('PRO3','PR032','3','O');
  INSERT INTO temp1 VALUES ('PRO3','PR032','3','K');
  </code></pre>
  
  <p>Here is what I am trying to do in SSIS: I am trying to merge these two data sources</p>
  
  <pre><code>   ----First Data Source
      DECLARE @ColumnNames NVARCHAR(MAX)
      DECLARE @SQL NVARCHAR(MAX)
  
      SELECT @ColumnNames=Stuff((SELECT DISTINCT ',' + Quotename(ProcessName)
                         FROM [temp1]
                         FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
      SET @SQL= '
      SELECT * FROM (
          SELECT [ProcessName], 
              COUNT(CASE WHEN ProcessPages &gt;=  1 and ProcessPages &lt;=  5 THEN ''1p'' END) AS [1p],
              COUNT(CASE WHEN ProcessPages &gt;=  6 and ProcessPages &lt;= 10 THEN ''2p'' END) AS [2p],
              COUNT(CASE WHEN ProcessPages &gt;= 11 and ProcessPages &lt;= 16 THEN ''3p'' END) AS [3p],
              COUNT(CASE WHEN ProcessPages &gt;= 17 and ProcessPages &lt;= 50 THEN ''4p'' END) AS [4p],
              COUNT(CASE WHEN ProcessPages &gt;  50 THEN ''5p'' END) AS [5p],
              COUNT([ProcessName]) AS Total
          FROM temp1
          GROUP BY [ProcessName]) AS SOURCE
          UNPIVOT ( val FOR Process_No_In_Cases IN ([1p],[2p],[3p],[4p],[5p],[Total]) ) U
          PIVOT ( MAX(val) FOR ProcessName IN('+@ColumnNames +') ) As PivotTable'
      EXECUTE sp_executesql @SQL
  
  
      ------Second Data Source
      DECLARE @ColumnNames2 NVARCHAR(MAX)
      DECLARE @SQL2 NVARCHAR(MAX)
      SELECT @ColumnNames2=Stuff((SELECT DISTINCT ',' + Quotename([ProcessName])
                         FROM temp1
                         FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
      SET @SQL2= '
      SELECT * FROM (
          SELECT [ProcessName], 
              COUNT(CASE WHEN [ProcessClass]=''Y'' THEN ''Coordinated'' END) AS [Coordinated],
              COUNT([ProcessName]) AS Total
          FROM temp1
          GROUP BY [ProcessName]) AS SOURCE
          UNPIVOT ( val FOR [''] IN ([Coordinated],[Total]) ) U
          PIVOT ( MAX(val) FOR [ProcessName] IN('+@ColumnNames2 +') ) As PivotTable'
  
      --print @sql
  EXECUTE sp_executesql @SQL2
  </code></pre>
  
  <p>And combine the output into one csv file like this:</p>
  
  <pre><code>Process_No_In_Cases PRO1    PRO2    PRO3
  1p                   2       8        3
  2p                   3       2        1
  3p                   1       0        1
  4p                   2       0        6
  5p                   1       0        1
  Total                9       10       12
  
  
  Coordinated          6       5        10
  Total                9       10       12
  </code></pre>
  
  <p>Any input?</p>
  
  <p>--------Here is an updated:</p>
  
  <p>With the same table temp1:</p>
  
  <p>I have this two queries:</p>
  
  <pre><code>SELECT  *
   FROM (
      SELECT [ProcessName], 
          COUNT(CASE WHEN [ProcessPages] &gt;=  1 and [ProcessPages] &lt;=  5 THEN '1p' END) AS [1p],
          COUNT(CASE WHEN [ProcessPages] &gt;=  6 and [ProcessPages] &lt;= 10 THEN '2p' END) AS [2p],
          COUNT(CASE WHEN [ProcessPages] &gt;= 11 and [ProcessPages] &lt;= 16 THEN '3p' END) AS [3p],
          COUNT(CASE WHEN [ProcessPages] &gt;= 17 and [ProcessPages] &lt;= 50 THEN '4p' END) AS [4p],
          COUNT(CASE WHEN [ProcessPages] &gt;  50 THEN '5p' END) AS [5p],
          COUNT([ProcessName]) AS Total
      FROM temp1
      GROUP BY [ProcessName]) AS SOURCE
      UNPIVOT ( val FOR [Pro_No_In_Cases] IN ([1p],[2p],[3p],[4p],[5p],[Total]) ) U
      PIVOT ( MAX(val) FOR [ProcessName] IN ([PRO1],[PRO2],[PRO3] 
              ) ) As PivotTable
  </code></pre>
  
  <p>--and</p>
  
  <pre><code>WITH CTE_Temp AS (
      SELECT 
         [ProcessName] =    
         CASE 
            WHEN [ProcessPages] &gt;=  1 AND [ProcessPages] &lt;=  5 THEN '1p'
            WHEN [ProcessPages] &gt;=  6 AND [ProcessPages] &lt;= 10 THEN '2p'
            WHEN [ProcessPages] &gt;= 11 AND [ProcessPages] &lt;= 16 THEN '3p'
            WHEN [ProcessPages] &gt;= 17 AND [ProcessPages] &lt;= 50 THEN '4p'
            WHEN [ProcessPages] &gt;  50   THEN '5p'
         END
         , [ProcessClass]    
      FROM temp1
  )
  SELECT
      [ProcessName] = CASE WHEN GROUPING([ProcessName]) = 0 THEN [ProcessName] ELSE 'Total' END 
      , Coordinated     = COUNT(CASE WHEN [ProcessClass]  =  'Y' THEN [ProcessClass]  END)
      , Uncordinated = COUNT(CASE WHEN [ProcessClass]  &lt;&gt; 'Y' THEN [ProcessClass]  END)
  FROM CTE_Temp
  GROUP BY GROUPING SETS ([ProcessName], ())
  </code></pre>
  
  <p>With SSIS, I placed them in two separate oledb sources and used a union all but it DIDN'T work.  How do I merge this tow sources to be on one csv(file) with spaces between them. Please see my new example:</p>
  
  <pre><code>Pro_No_In_cases PRO1    PRO2    PRO3    
  1p               2       8       3  
  2p               3       2       1  
  3p               1       0       1  
  4p               2       0       6  
  5p               1       0       1  
  Total            9       10      12 
  
  
  
   ProcessName    Coordinated Uncordinated    
     1p               5          8    
     2p               6          0    
     3p               1          1    
     4p               7          1    
     5p               2          0    
     Total            21         10
  </code></pre>
  
  <p>Any input?</p>