SSIS Data Flow Task SQL 2008

I think the issue was by ths following , Solution:
Set the property RetainSameConnection on the Connection Manager to True so that temporary table created in one Control Flow task can be retained in another task.

USE Sora;
GO

CREATE PROCEDURE dbo.PopulateTempTable
AS
BEGIN

    SET NOCOUNT ON;

    IF OBJECT_ID('TempDB..##tmpStateProvince') IS NOT NULL
        DROP TABLE ##tmpStateProvince;

    CREATE TABLE ##tmpStateProvince
    (
            CountryCode     nvarchar(3)         NOT NULL
        ,   StateCode       nvarchar(3)         NOT NULL
        ,   Name            nvarchar(30)        NOT NULL
    );

    INSERT INTO ##tmpStateProvince 
        (CountryCode, StateCode, Name)
    VALUES
        ('CA', 'AB', 'Alberta'),
        ('US', 'CA', 'California'),
        ('DE', 'HH', 'Hamburg'),
        ('FR', '86', 'Vienne'),
        ('AU', 'SA', 'South Australia'),
        ('VI', 'VI', 'Virgin Islands');
END
GO

USE Sora;
GO

CREATE TABLE dbo.StateProvince
(
        StateProvinceID int IDENTITY(1,1)   NOT NULL
    ,   CountryCode     nvarchar(3)         NOT NULL
    ,   StateCode       nvarchar(3)         NOT NULL
    ,   Name            nvarchar(30)        NOT NULL
    CONSTRAINT [PK_StateProvinceID] PRIMARY KEY CLUSTERED
        ([StateProvinceID] ASC)
) ON [PRIMARY];
GO

hope this fix your issue So long as the files are all the same structure, you'd use a for-each loop, of type file. Point it at the folder with the files in, and assign a variable to the file+path. Then use that variable as an expression on the flat file connection manager.

will help you Try to avoid declaring a variable, just place the ? in the direct place that you need to pass a parameter.

SELECT [ServiceActivities]
  ,[ServiceID]
  ,[ClientID]
  ,[WorkerID]
  ,[CostCode] 
  ,[ProviderID]
  ,[CostUnit]
  ,[Units]
  ,[OccurrenceDate]
FROM [dbo].[PlannedAppointmentsView] 
WHERE datepart (yy, OccurrenceDate) = ?

"SELECT [ServiceActivities]
  ,[ServiceID]
  ,[ClientID]
  ,[WorkerID]
  ,[CostCode] 
  ,[ProviderID]
  ,[CostUnit]
  ,[Units]
  ,[OccurrenceDate]
FROM [dbo].[PlannedAppointmentsView] 
WHERE datepart (yy, OccurrenceDate) = " + (DT_WSTR,50)@[User::year]