Exporting to Excel in SSIS

How to export data to excel. I did follow other blogs, but they leave out vital information to get it working, so here is my attempt.

Preview of what's to come from Control Flow view:

 

You're going to need to first create a Execute SQL Task, because this is going to be used to create your sheet in excel. The syntax is similar to SQL Server's Create Table, except that it uses the accent quote `  instead of squaring the names in brackets i.e.: [name]. It also seems to have a problem with numeric and character cell size limits.





In this case, I'm creating an excel sheet called Process:



CREATE TABLE `Process`(
 `SuperClientVendorID` INT,
 `LoanNumber` varchar(25),
 `OpenIndicator` char(3),
 `CloseIndicator` char(3),
 `UpdateIndicator` char(3),
 `ParentRefID` INT,
 `Open_RailDescription` varchar(200),
 `AssignedVendorID` INT ,
 `CloseReason` varchar(200) ,
 `RefID` numeric(18, 0),
 `CloseDate` date ,
 `InheritedAttribute` varchar(3) ,
 `ProcessorCd` varchar(50) ,
 `ProcessStartDate` date ,
 `ReOpenIndicator` varchar(3) ,
 `NoteType` varchar(255) ,
 `Note` text
)

Now, lets create a Excel Connection Manager to dynamically create our file. You're going to need a dummy file to initially create an connection instance. In this dummy file, I had my first rows of names, but I'm not sure if this is really needed.

 You're going to point the connection to the excel file for now. Right click on the newly created connection and select properties. In this window, under expressions you're going to create a ExcelFilePath expression:

My text, I use a variable called Processed to represent the file path and folder. I then add a datetime string to the end of the file.

@[User::Processed] + "\\FileName " + RIGHT("0" + (DT_STR,2,1252)DATEPART("MM" ,GETDATE()), 2) +
 RIGHT("0" + (DT_STR,2,1252)DATEPART("DD" ,GETDATE()), 2) + (DT_STR,4,1252)DATEPART("YYYY" ,GETDATE())  + "_"  + Right("0" + (DT_STR,4,1252) DatePart("hh",getdate()),2) + "" + Right("0" +  (DT_STR,4,1252) DatePart("n",getdate()),2)  +""+ ".xls"


I had a problem with the connection at this point, I took the ExcelFilePath name from properties and moved my dummy excel there and renamed it. This won't be a problem when it runs, since the name will change given the time. If you need to edit the file, you will need to recreate/rename your dummy file.

Now create a Data Flow Task and inside that task, create your source file, where you will be pulling the data from. Setup your query to pull the data, etc.... And create your destination excel file.

Point it to your dummy excel file and dummy sheet name.


Run the program, and hope for no errors.



Helpful resources on the same thing:
http://geekepisodes.com/sqlbi/2011/creating-excel-files-xls-dynamically-from-ssis/
http://jandho.blogspot.com/2012/03/ssis-package-to-export-to-new-excel.html