SSIS(SQL Server Integration Services)

Amudhan Kuppan

8y1.4k

anyone please help me

Requirement.

i have update data in database from another datbase so prepare dynamic script where database name fetch from variable and build a query using SSIS Package

this is my requirement

do not use SP for this u use only just simple SQL Query

for eg:

UPDATE t1

SET t1.[Name] = t2.[Name]

FROM [dbo].[Namelist] t1

INNER JOIN [IT TEST1].[dbo].[addresslist] t2 ON t1.[id] = t2.[id]

this is my update query

how to put this query in execute SQL task component

and one more thing you will create variables for dbname,tablename,SQL Query

i do not change query every time i want just change database name and table name

thats all anyone please help me

10 Replies

Know the answer? Post it — somebody with the same question will find it here.

Shahriar Nikkhah8y ago
Accepted answer
Your update statement is not working, in your SET clasue you dont need t1

DECLARE @DBName as nVarChar(4000) = ?
DECLARE @SQLString as nVarChar(4000) = ''
SET @SQLString = @SQLString + 'UPDATE t1SET [Name] = t2.[Name] FROM [dbo].[Namelist] t1 INNER JOIN '
SET @SQLString = @SQLString + '[' + @DBName + '].[dbo].[addresslist] t2 ON t1.[id] = t2.[id]'
EXECUTE sp_executesql @SQLString
Amudhan Kuppan8y ago
hi Shahriar

S ur right

Iam removing t1 from set clause its working fine

In sql ok but its not working in SSIS execute sql task

Iam getting new error

[Execute SQL Task] Error: Executing the query "Declare @db1 varchar(255)=Amudhan,@db2 varchar(255..." failed with the following error: "Must declare the table variable "@tb1".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.this is my error

Declare @db1 varchar(255)=Amudhan,@db2 varchar(255)=ITTEST,@tb1 varchar(255)=Namelist,@tb2 varchar(255)=Addresslist,@Id int=0
UPDATE t1
SET [Name] = t2.[Name]
FROM @tb1 t1
INNER JOIN [@db2].dbo.[@tb2] t2 ON t1.[id] = t2.[id]
this is my query please check it why in this query not working in SSIS package in execute sql task
Amudhan Kuppan8y ago
ok Shahriar please send me a update query already iam send please recorrect and send me shahriar
Amudhan Kuppan8y ago
hi Shahriar
iam using variable in that execute sql task please send me query for using that variable i will send variable document please see and send me
Shahriar Nikkhah8y ago
use my code in SSIS
Shahriar Nikkhah8y ago
DECLARE @DBName as nVarChar(4000) = ?
DECLARE @SQLString as nVarChar(4000) = ''
SET @SQLString = @SQLString + 'UPDATE t1SET t1.[Name] = t2.[Name] FROM [dbo].[Namelist] t1 INNER JOIN '
SET @SQLString = @SQLString + '[' + @DBName + '].[dbo].[addresslist] t2 ON t1.[id] = t2.[id]'
EXECUTE sp_executesql @SQLString
Amudhan Kuppan8y ago
wait piyush iam again tell my requirement and conditions
Requirement.
i have update data in database from another datbase so prepare dynamic script where database name fetch from variable and build a query using SSIS Package

this is my requirement
do not use SP for this u use only just simple SQL Query

for eg:
UPDATE t1
SET t1.[Name] = t2.[Name]
FROM [dbo].[Namelist] t1
INNER JOIN [IT TEST1].[dbo].[addresslist] t2 ON t1.[id] = t2.[id]
this is my update query
how to put this query in execute SQL task component
and one more thing you will create variables for dbname,tablename,SQL Query
i do not change query every time i want just change database name and table name
what ever database name Provide in variable, query should be execute accordingly
Piyush Pansuriya8y ago
Hello,
Do you want to schedule a task to perform update operation ? or something else?
Amudhan Kuppan8y ago
this link not use piyush
Piyush Pansuriya8y ago
Hello,

Go through below link :
https://www.red-gate.com/simple-talk/sql/ssis/ssis-basics-using-the-execute-sql-task-to-generate-result-sets/

https://www.red-gate.com/simple-talk/sql/ssis/passing-variables-to-and-from-an-ssis-task/