PICK DATA FROM ANY SERVER DYNAMICALLY : IT IS NOT POSSIBLE IN SSIS

Procedure for create linked server and get data from it dynamically


ALTER PROC [dbo].[USP_GetLinkedServerData]

AS




BEGIN
   DECLARE @prm_server NVARCHAR(100)

DECLARE @prm_srvproduct NVARCHAR(100)

DECLARE @prm_useself NVARCHAR(100)

DECLARE @prm_locallogin NVARCHAR(100)

DECLARE @prm_rmtuser NVARCHAR(100)

DECLARE @prm_rmtpassword NVARCHAR(100)

DECLARE @prm_DatabaseName NVARCHAR(100)

DECLARE @prm_TableName NVARCHAR(500)





　
  DECLARE @ServerID NVARCHAR(100)

DECLARE @SourceDBName NVARCHAR(100)

DECLARE @SourceTableName NVARCHAR(100)

BEGIN TRY

DECLARE db_Cursor CURSOR FOR

SELECT [Server],[Srvproduct],[Useself],[Locallogin],[Rmtuser],[Rmtpassword],DatabaseName,TableName FROM [dbo].[LinkedServersDTL]

OPEN db_Cursor

FETCH NEXT FROM db_Cursor into @prm_server,@prm_srvproduct,@prm_useself,@prm_locallogin,@prm_rmtuser,@prm_rmtpassword,@prm_DatabaseName,@prm_TableName

WHILE @@FETCH_STATUS = 0

BEGIN

IF NOT Exists (Select [SRVID] From sysservers Where [srvName]= @prm_server)

BEGIN

EXEC master.dbo.sp_addlinkedserver @server = @prm_server, @srvproduct= @prm_srvproduct

/* For security reasons the linked server remote logins password is changed with ######## */

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=@prm_server,@useself=@prm_useself,@locallogin=@prm_locallogin,@rmtuser=@prm_rmtuser,@rmtpassword=@prm_rmtpassword

END

IF OBJECT_ID(@prm_TableName, 'U') IS NOT NULL

BEGIN

DECLARE @Q NVARCHAR(100)

SET @Q = 'DROP TABLE '+@prm_TableName

EXEC SP_Executesql @Q

END

--SET @Query = 'SELECT * INTO '+@prm_TableName+' FROM ['+ @prm_server+'].['+@prm_DatabaseName+'].dbo.['+@prm_TableName+']'

--EXEC SP_Executesql @Query

--PRINT @Query

BEGIN TRY

DECLARE db_InnerCursor CURSOR FOR

SELECT [ServerID],[SourceDBName],[SourceTableName] FROM [dbo].[TablesDetail] GROUP BY [ServerID],[SourceDBName],[SourceTableName]

OPEN db_InnerCursor

FETCH NEXT FROM db_InnerCursor into @ServerID,@SourceDBName,@SourceTableName

WHILE @@FETCH_STATUS = 0

BEGIN

IF OBJECT_ID('tempdb..#Temp1') IS NOT NULL

DROP TABLE #Temp1

SELECT * INTO #Temp1 FROM [TestDB].[dbo].[TablesDetail] WHERE [ServerID] = @ServerID AND [SourceDBName]= @SourceDBName AND [SourceTableName]= @SourceTableName

DECLARE @DstTableName NVARCHAR(200)

DECLARE @SourceColumnName NVARCHAR(200)

SELECT TOP 1 @DstTableName = DestTableName,@SourceColumnName= SourceColumnName FROM #Temp1

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @DstTableName) AND (@SourceColumnName IS NOT NULL OR @SourceColumnName <> '*')

BEGIN

DECLARE @SrcColumns NVARCHAR(200)

DECLARE @DstColumns NVARCHAR(200)

SELECT @SrcColumns = STUFF((SELECT ',' + ' ' + [SourceColumnName] FROM #Temp1 ORDER BY ID FOR XML PATH('')),1,2,'')

SELECT @DstColumns = STUFF((SELECT ',' + ' ' + [DestColumnName] FROM #Temp1 ORDER BY ID FOR XML PATH('')),1,2,'')

PRINT @SrcColumns

PRINT @DstColumns

DECLARE @Q2 NVARCHAR(MAX)

DECLARE @Q3 NVARCHAR(MAX)

SET @Q2 ='TRUNCATE TABLE [dbo].'+@DstTableName

SET @Q3 = 'INSERT INTO [dbo].'+@DstTableName+'('+@DstColumns+')'+' SELECT '+@SrcColumns+' FROM ['+ @prm_server+'].['+@SourceDBName+'].[dbo].['+@SourceTableName+']'

EXEC SP_Executesql @Q2

EXEC SP_Executesql @Q3

END

ELSE

BEGIN

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @DstTableName)

BEGIN

DECLARE @Q4 NVARCHAR(MAX)

DECLARE @Q5 NVARCHAR(MAX)

SET @Q4 ='TRUNCATE TABLE [dbo].'+@DstTableName

SET @Q5 = 'INSERT INTO [dbo].'+@DstTableName+' SELECT * FROM ['+ @prm_server+'].['+@SourceDBName+'].[dbo].['+@SourceTableName+']'

EXEC SP_Executesql @Q4

EXEC SP_Executesql @Q5

END

ELSE

BEGIN

DECLARE @Q6 NVARCHAR(500)

SET @Q6 = 'SELECT * INTO '+@DstTableName+' FROM ['+ @prm_server+'].['+@SourceDBName+'].dbo.['+@SourceTableName+']'

EXEC SP_Executesql @Q6

END

END




  FETCH NEXT FROM db_InnerCursor into @ServerID,@SourceDBName,@SourceTableName

END

CLOSE db_InnerCursor

DEALLOCATE db_InnerCursor

END TRY

BEGIN CATCH

CLOSE db_InnerCursor

DEALLOCATE db_InnerCursor

INSERT INTO ErrorLog([ErrorNumber],[ErrorSeverity],[ErrorState],[ErrorProcedure],[ErrorLine],[ErrorMessage],[ErrorFrom],[ErrorPage]) VALUES(ERROR_NUMBER(),ERROR_SEVERITY(),ERROR_STATE(),ERROR_PROCEDURE(),ERROR_LINE(),ERROR_MESSAGE(),'DB-',NULL)

END CATCH



　
  FETCH NEXT FROM db_Cursor into @prm_server,@prm_srvproduct,@prm_useself,@prm_locallogin,@prm_rmtuser,@prm_rmtpassword,@prm_DatabaseName,@prm_TableName

END

CLOSE db_Cursor

DEALLOCATE db_Cursor

END TRY

BEGIN CATCH

CLOSE db_Cursor

DEALLOCATE db_Cursor

INSERT INTO ErrorLog([ErrorNumber],[ErrorSeverity],[ErrorState],[ErrorProcedure],[ErrorLine],[ErrorMessage],[ErrorFrom],[ErrorPage]) VALUES(ERROR_NUMBER(),ERROR_SEVERITY(),ERROR_STATE(),ERROR_PROCEDURE(),ERROR_LINE(),ERROR_MESSAGE(),'DB',NULL)

END CATCH




END