i have one doubt for SSIS

Amudhan Kuppan

8y1k

anyone please help me i dont know that task

Requirement.

i have update data in database from another datbase so prepare dynamic script where database name fetch from variable and build a query and which will be executed. using SSIS

how is this task i dont know please help me anyone

14 Replies

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

Shahriar Nikkhah8y ago
Accepted answer
use the mentioned in SSIS

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
Shahriar Nikkhah8y ago
if you don't want to use a SP then lets assume that the mentioned is you query and you are testing it in SSMS first
---

DECLARE @DBName as nVarChar(4000) = 'IT TEST1'
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
---
Make sure you test it in SSMS and it works
then change the DB name from ...
DECLARE @DBName as nVarChar(4000) = 'IT TEST1'

to.....

DECLARE @DBName as nVarChar(4000) = ?

and use that in the EXEC SQL Task in SSIS

good luck
Shahriar Nikkhah8y ago
Did you download the attached file?
I did create an SSIS file for you
Amudhan Kuppan8y ago
thank you Shahriar
Shahriar Nikkhah8y ago
Please see attached SSIS pkg
Shahriar Nikkhah8y ago
Hi

I am going to assume that you have a SP called .....

EXEC usp_YourSPName (@DBName)

that works and needs one parameter which will be the DBName

--Create an SSIS project with one SSIS package
-- create a connection object to the DB that your SP is located

-- make sure that the connection is OK and is working

-- Go to the pkg Parameter and create a Parameter and call it "uPkgParameter_DBName"
-- change the data type to string
-- give it a default value in my case my db name is "YourDBName"

-- go to Control flow
--open the toolbox
-- drag and drop a "EXEC SQL task"
-- give it a proper name "SQLT---- Update DBXyz with TableAbc"
--go to the property settings and set like

--Go to the "General" setting
-- go to Connection, mase sure the connection is the right connection
-- in the General now go to "SQLStatement"
-- use the mentioned code

DECLARE @DBName nVarChar(4000) = ?
EXEC usp_YourSPName (@DBName)

good luck
Amudhan Kuppan8y ago
i will send SP for updatedatabase please check it it is right or wrong
Shahriar Nikkhah8y ago
Just one thing, generally the name of the DB should be in a SQL table, so why don't you use a SQL table that has the DB name and use it in the Dynamic T-SQL string

DECLARE @DBName nVarchar(200) = (SELECT DBName FROM tblABC WHERE ....)
DECLARE @str nVarChar(4000)

SET @str = 'UPDATE ' + @DBName + '.[dbo].[tblXYZ SET ........'

I think that you don't need a SSIS for this, thats the way i work.
Amudhan Kuppan8y ago
thank you shahriar nikkhah
Shahriar Nikkhah8y ago
if i find one I'll post it
Amudhan Kuppan8y ago
please send me a link shahriar nikkhah
Amudhan Kuppan8y ago
NO debasis already iam trying that three link it will not help me debasis
Shahriar Nikkhah8y ago
This can be very easy depends on the details of the requirement/access/simplicity

you will need to create one SSIS one SP and a parameter of a DB name in SSIS
so lets start

-- Create a SP that does the update within the SP you will have ...
UPDATE [DBName].[dbo].[tblXYZ] Set .....
test it make sure it works

-- now replace it with a string, Use a String and create the above Update statment
DECLARE @str nVarChar(4000)

SET @str = 'UPDATE ' + @DBName + '.[dbo].[tblXYZ SET ........'

-- make sure that SP has the @DBName parameter that you are going to pass to within the SSIS
Test the SP in SSMS make sure it woks

-- Create a SSIS, From the SSIS just call the SP and pass the right DBName

Good luck
Debasis Saha8y ago
Please visit the below links. Hope this help you -

https://docs.microsoft.com/en-us/sql/integration-services/control-flow/execute-sql-task

https://stackoverflow.com/questions/11943588/from-an-ssis-package-how-should-i-execute-a-sql-script-stored-in-a-table-as-a-va

http://www.rad.pasfu.com/index.php?/archives/48-How-To-Run-Dynamic-T-SQL-In-SSIS-OLE-DB-Command.html

Hope this will help you.