Click here to Skip to main content
14,391,900 members
Rate this:
Please Sign up or sign in to vote.
See more:
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] +";"
Posted
Updated 23-Nov-15 18:45pm
v3
Comments
George Jonsson 23-Nov-15 20:31pm
   
Maybe you could share the relevant code that shows the variables returned from the SP and how you create the connection string.
Nandhini Devi 23-Nov-15 20:41pm
   
create a new connection using oledb and property-> expression-> "Provider=SQLNCLI10.1;Data Source="+ @[User::SourceSQL] +";Integrated Security=SSPI;Initial Catalog="+ @[User::SourceDB] +";"
I entered this connection string and evaluated. Its working fine.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100