Dynamic SQL Connection using Variables

See more:

SQL

SSIS

I am having 2 database with same table name. I want to transfer data from 2 databases to another database using SSIS. I have created a table with ID, SourceSqlName ,Source Database Name , Destination Sql Name ,Destination database Name . I created stored procedure to fetch data from that table. I created 5 variables in SSIS . I created a connection string in oldb with variables. I want to overwrite the values of variables which is coming from the stored procedure. How to do that ?. Number of source database changes so I am using dynamic SQL connection to oledb.

ALTER PROCEDURE [dbo].[DTSX]
	-- Add the parameters for the stored procedure here

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE	@totalcount INT
	SELECT @totalcount= COUNT(*)  from dim.Metadata
    -- Insert statements for procedure here
    DECLARE @intFlag INT
SET @intFlag = @totalcount 
WHILE (@intFlag >=1)
BEGIN
	SELECT SourceSQL,SourceDB,DestinationDB,DestinationSQL FROM dim.Metadata Where ID=@intFlag
    PRINT @intFlag
    SET @intFlag = @intFlag - 1
    CONTINUE;
    IF @intFlag = 0 -- This will never executed
        BREAK;
END
	
END

My connection String using variables
"Provider=SQLNCLI10.1;Data Source="+ @[User::SourceSQL] +";Integrated Security=SSPI;Initial Catalog="+ @[User::SourceDB] +";"