2016-0-SSIS-Generate Custom Spreadsheet & Workbook Excel Via SSIS

Generate Custom Spreadsheet & Workbook Excel Via SSIS

















Script Task:











just to view the code properly it is pasted in query window.

~
2nd:Execute Sql Task ( to create 2nd tab in excel).



Note:Based on your source query , can generate the schema of the excel tab.



Just to view properly.

Data Flow Task:








Data Conversion:



note:
Converting into unicode as in excel using the nonnumeric values.





Same way do for Dept:

note:For excel to run the below setting is set on the solution explorer.




For Data Flow task: Disabled the DelayValidation=True

So that run the package dynamically by changing the variables.




Set "ValidateExternalMetaData=False" on the excel destination.



~~
If need to create the file in different directory at the runtime



In the "Excel connection manager"





In the above we are overwriting the connection string,which is required.