i have one doubt for SSIS

Question

hi 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

Shahriar Nikkhah · 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 Nikkhah · Answer

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

Shahriar Nikkhah · Answer

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

Shahriar Nikkhah · Answer

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.

Debasis Saha · Answer

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.

Shahriar Nikkhah · Answer

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 Nikkhah · Answer

Did you download the attached file? I did create an SSIS file for you

Amudhan Kuppan · Answer

thank you Shahriar

Shahriar Nikkhah · Answer

Please see attached SSIS pkg

Amudhan Kuppan · Answer

i will send SP for updatedatabase please check it it is right or wrong

Amudhan Kuppan · Answer

thank you shahriar nikkhah

Shahriar Nikkhah · Answer

if i find one I'll post it

Amudhan Kuppan · Answer

please send me a link shahriar nikkhah

Amudhan Kuppan · Answer

NO debasis already iam trying that three link it will not help me debasis