0

I have a dynamic pivot sql script (the pivoted columns are dynamic). I wanted to export the result set into excel file and email it with send mail task in ssis. Does anyone know how to do that? Below is my Dynamic column pivot sql script

Declare @SQL varchar(max) 
Select  @SQL = Stuff((Select Distinct ',' + QuoteName([Response Code]) From YourTable Order by 1 For XML Path('')),1,1,'') 
Select  @SQL = 'Select [Employee],' + @SQL + '
                From (
                        Select [Employee],[Response Code],Cnt=1,Lvl=0 from YourTable
                        Union All
                        Select [Employee],[Response Code],Cnt=0,Lvl=0 from (Select Distinct [Employee] from YourTable) A Join (Select Distinct [Response Code] from YourTable) B on 1=1
                        Union All
                        Select ''Total'',[Response Code],count(*),1 From YourTable Group By [Response Code]
                     ) A
                Pivot (sum(Cnt) For [Response Code] in (' + @SQL + ') ) p'
Exec(@SQL);

The above script will return the table like this

    Employee    ptb ulm vml wrn
    Emp A       0   0   2   1
    Emp B       0   2   0   1
    Emp C       1   0   1   0
    Total       1   2   3   2

I need to export the above result table in to excel file. I know how to do if the column is static using SSIS ;but I am struggling with the dynamic column pivot. Could anyone please help me. Thank you very much for your time and help

Please
  • 41
  • 1
  • 7
  • As stated [here](http://stackoverflow.com/a/41127450/181965) SSIS Data Flow Task doesn't do dynamic columns. Best bet would be to either use a Script Task and write to a CSV/TXT file (Excel will not be a pleasant experience) or an Execute SQL Task and use an openrowset to dump to a file. Or Execute Process Task and call BCP with the aforementioned query. All of that writes to a text file though. Excel will be [challenging and possibly expensive](http://stackoverflow.com/a/26122982/181965) – billinkc Dec 14 '16 at 18:49
  • @billinkc thank you for the guidance. I will try that. – Please Dec 14 '16 at 19:08
  • @billinkc I am getting the following error message An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "The metadata could not be determined because statement 'Exec(@SQL);' in procedure 'usp_DialerResponseReport2' contains dynamic SQL. Consider using the WITH RESULT SETS clause to explicitly describe the result set.". Error at Data Flow Task [OLE DB Source [1]]: Unable to retrieve column information from the data source. Make sure your target table in the database is available. – Please Dec 14 '16 at 19:29

0 Answers0