SIVANANDA REDDY GANGIREDDY: How to split table data into separate named Excel files using an SSIS package?

Step-by-step process:

Execute the scripts given under SQL Scripts section in an SQL Server database to create a table named dbo.Source and populate with data (similar to data given in the question). It also creates a stored procedure named dbo.GetSiteData.
On the SSIS package's connection, create an OLE DB connection to connect to the SQL Server. We will get to Excel connect later in the steps.
On the SSIS package, create 6 variables as shown in screenshot #1. Populate the variable Site with value Template, this is a sample value that will be used to evaluate other expressions. Set the variable SQLUniqueSites with value SELECT DISTINCT Site FROM dbo.SourceData. Set the variable ExcelFolder with value C:\temp\
Select the variable ExcelFilePath and press F4 to view Properties. Change the propertyEvaluateAsExpression to True and set the property Expression with value @[User::ExcelFolder] + @[User::Site] + ".xls". Refer screenshot #2.
Select the variable ExcelSheet and press F4 to view Properties. Change the propertyEvaluateAsExpression to True and set the property Expression with value given underExcelSheet Variable Value. Refer screenshot #3.
On the SSIS package's Control Flow tab, place an Execute SQL Task and configure it as shown in screenshots #4 and #5. This task will fetch the unique Site names.
On the SSIS package's Control Flow tab, place a Foreach Loop container after the Execute SQL Task and configure the Foreach Loop container as shown in screenshots #6 and #7 This loop will loop through the result set and will read each site into a variable. This variable is then used to provide the name for Excel file as well as the parameter to the stored procedure in a Data Flow Task that will be added shortly.
Inside the Foreach Loop container, place an Execute SQL Task and then place a Data Flow Task. At this point, the Control Flow tab should look like as shown in screenshot #8. Your package might show errors at this point, we will fix that soon in the next steps. We will configure the Data Flow Task and then will get back to Execute SQL Task within the Foreach Loop container.
Inside the Data Flow Task, place an OLE DB Source and configure it as shown in screenshots #9and #11. This will fetch the data from table based on a given site. Click on the Parameters... button to set the query parameters.
If the table field datatypes are in VARCHAR, then we need to convert it into NVARCHAR (unicode format), otherwise this step is not needed. In the Data Flow task, place a Data Conversiontransformation after the OLE DB Source and configure it as shown in screenshot #12.
Next, inside the Data Flow Task, place an Excel Destination, click on the first New... button as shown in screenshot #13.
On the Excel Connection Manager dialog, provide an Excel File Path and click OK. Refer screenshot #14. Back in the Excel Destination, click on the second New... button as shown in screenshot #15. On the Create Table dialog, make sure the script is as shown in screenshot #16 and click OK. When displayed with a warning as shown in screenshot #17, click OK. Select the value Template from the Name of the Excel sheet dropdown as shown in screenshot #18. Configure the columns as shown in screenshot #19.
On the SSIS package's connection manager, select the newly created Excel connection manager and press F4 to view the properties. Change the Name property value to Excel. ChangeDelayValidation to True so that if the file Template.xls doesn't exist, you don't get an error message. Set the ServerName Expression with value @[USer::ExcelFilePath]. Refer screenshot #20. NOTE: An Excel file should have been created in the path C:\temp\Template.xls. You might want to save it so that you don't encounter during future design changes. You can still recreate it if the file is deleted.
Go back to the Excel Destination and configure it as shown in screenshot #21. Once the Data Flow Task is configured, it should look like as shown in screenshot #22.
Back in Control Flow tab, configure the Execute SQL Task within the Foreach Loop container as shown in screenshot #23. This task will create new Excel spreadsheets for each Site name.
Screenshot #24 shows contents in folder c:\temp\ before the package execution.
Screenshots #25 and #26 show the package execution.
Screenshot #27 shows contents in folder c:\temp\ after the package execution.
Screenshots #28 and #29 show the contents of the newly created Excel spreadsheets North.xls and South.xls. Both the sheets contain the data respective the sites of the same name.

Hope that helps.

ExcelSheet Variable Value:

CREATE TABLE `Template` (`Id` Long, `FirstName` LongText, `LastName` LongText)

SQL Scripts:

CREATE TABLE [dbo].[SourceData](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Site] [varchar](50) NOT NULL,
    [FirstName] [varchar](40) NOT NULL,
    [LastName] [varchar](40) NOT NULL,
CONSTRAINT [PK_SourceData] PRIMARY KEY CLUSTERED ([Id] ASC)
) ON [PRIMARY]
GO

INSERT INTO dbo.SourceData (Site, FirstName, LastName) VALUES
    ('North', 'Jim', 'Smith'),
    ('North', 'Tim', 'Johnson'),
    ('North', 'Sachin', 'Tendulkar'),
    ('South', 'Horatio', 'Alger'),
    ('South', 'Jimi', 'Hendrix'),
    ('South', 'Bugs', 'Bunny');
GO

CREATE PROCEDURE dbo.GetSiteData
(
    @Site   VARCHAR(50)
)
AS
BEGIN   
    SET NOCOUNT ON;

    SELECT  Id 
        ,   FirstName
        ,   LastName
    FROM    dbo.SourceData
    WHERE   Site = @Site
END 
GO