Create a Dyanamic Excel destination file with a filename base on the date

Problem Statement:
Create a Dynamic Excel destination file with a file name base on the date 


Things which we have to remember:
1.Delay validation of DFT(data flow task) should be True.
2.Should not run at 64 Bit run time in short (Run64Bit Run time should be False)
3.Check weather your drive have permission to create the excel file in the folder othere wise it will throw you below error:
 [Error: [Execute SQL Task] Error: Failed to acquire connection "Excel Connection Manager". Connection may not be configured correctly or you may not have the right permissions on this connection. ]

Solution steps:
1.Create a package level variable "XLFileRootDir" as string and set it to the root directory where you want to create excel file.
 Example: D:\
2.Create a package level variable "Client_detail" as string and set it to the file name,which we will use to create the dynamic file name.

 Example: "Client_Detail"
3. Create an Excel connection in the connection manager. Browse to the target directory and select the destination XL filename or type it in. It doesn't matter if the file doesn't exist.Later we will replace the file name with our dynamic generated file name.

4. Go to the Excel connection properties and expand the expressions ellipse (The button
with "..." on it).
Under the property drop down, select 'ExcelFilePath' and click on the ellipse to
configure the expression:
@[User::XLFileRootDir] + @[User::Client_Details]+(DT_WSTR, 2) DATEPART("DD", GETDATE()) + (DT_WSTR, 2) DATEPART("MM", GETDATE()) + (DT_WSTR, 4) DATEPART("YYYY", GETDATE()) +".xls"
This should create an exl file like Client_detail_05072011.xls.

5. Add a SQL task to package and double click to edit.

In the general tab, set 'ConnectionType' to 'Excel'.

For 'SQLStatement', enter the create table SQL to create destination table.

For example:

CREATE TABLE Employee_Detail (

     FirstName NVARCHAR(50)

    ,MiddleName NVARCHAR(50)

    ,LastName NVARCHAR(50)

    ,BirthDate NVARCHAR(50)

    ,Gender NVARCHAR(50)

    ,EmailAddress NVARCHAR(50)

)

Copy the create table command. It will come in handy later.

6. Add a Data Flow task. In the data flow editor, add an OLEDB source and an Excel destination.

7. Connect this to Excel destination. 

In the destination editor, select the Excel connection in the manager, choose 'table or view' for data access mode and for 'name of the Excel sheet' click on new button and paste the create table command from Step 5.

Map the columns appropriately in the mappings tab and you are done.