CODEWITHSUNDEEP
sqlsql-serverparametersssis
AceAlfred
AceAlfred

Reputation: 1181

SSIS parameterized datasource from SQL table

This is what I have been trying to do:

SSIS package : Items

enter image description here

With the following sql statement, using linked server: 

USE [TARGET]
GO

INSERT INTO [dbo].[Item]
       ([No_]
       ,[Description]
       ,[Unit Price]
       )
SELECT [No_]
      ,[Description]
      ,[Unit Price]
FROM [SOURCE].[Database].[dbo].[Item]

enter image description here

What I want to do:

enter image description here

Store the datasource for both TARGET and SOURCE in sql table Datasource: 

Data Source=localhost;Initial Catalog=TARGET;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;

Data Source=localhost;Initial Catalog=SOURCE;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;

And finally update the sql statement in the SSIS package pointing at a parameter.

INSERT INTO @SOURCE +'.[Item]'+
       ([No_]
       ,[Description]
       ,[Unit Price]
       )
SELECT [No_]
      ,[Description]
      ,[Unit Price]
FROM @SOURCE +'.[Item]'+

But not been successful so far, any suggestions?

Update:

Was missing one picture:

enter image description here

In the connection manager I would want to get the datasource information from the table Datasource (first picture after "what I want to do")

Update 2:

Is this possible??

enter image description here

Upvotes: 0

Views: 443

Answers (1)

praveen
praveen

Reputation: 12271

You can try this in Execute SQL Task:-

DECLARE @query nvarchar(max) 
DECLARE @TARGET nvarchar(200) = ?
DECLARE @SOURCE nvarchar(200) = ?
Set  @query ='INSERT INTO ' + @TARGET +'.[Item]
   ([No_]
   ,[Description]
   ,[Unit Price]
   )
   SELECT [No_]
  ,[Description]
  ,[Unit Price]
  FROM ' +  @SOURCE +'.[Item]'
EXEC(@query)

In the Parameter Mapping map the ? to the variables created ie Target and Source

Upvotes: 2

Related Questions