Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Create your own DTS Transformation using stored pr

Author  Topic 

shaileshpatangay
Starting Member

4 Posts
Posted - 2009-06-29 : 09:39:15
Create your own DTS Transformation using stored procedures.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

USE [msdb]
CREATE TABLE [dbo].[RefreshTable](
[RowId] [int] IDENTITY(1,1) NOT NULL,
[SourceServerName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SourceDBName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SourceObjectOwner] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SourceTableName] [varchar](300) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SourceColumnName] [varchar](300) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DestinationServerName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DestinationDBName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DestinationObjectOwner] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DestinationTableName] [varchar](300) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DestinationColumnName] [varchar](300) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ColumnOrder] [int] NULL,
[ProcessedOn] [datetime] NOT NULL CONSTRAINT [DF_RefreshTable_ProcessedOn] DEFAULT (getdate()),
[Validate] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_RefreshTable_Validate] DEFAULT ('N'),
[Enabled] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_RefreshTable_Enabled] DEFAULT ('Y'),
CONSTRAINT [IX_RefreshTable] UNIQUE NONCLUSTERED
(
[SourceServerName] ASC,
[SourceDBName] ASC,
[SourceObjectOwner] ASC,
[SourceTableName] ASC,
[SourceColumnName] ASC
) ON [PRIMARY]
) ON [PRIMARY]

GO

/*-----------------------------------------------------------------------------------
** Author : Shailesh Patangay
** Description : Copies the data from Source Database to Destination Databases.
**-----------------------------------------------------------------------------------
** Following is a example for executing the DTS with mode 0.
** Exec [master].[dbo].[DBA_RefreshTable] @DBName = 'DBName' ,@mode = 2
**------------------------------------------------------------------------------------
*/

Create Proc [dbo].[DBA_RefreshTable]
@DBName varchar(1000)
,@mode int = null
as
SET NOCOUNT ON

IF @mode is null
BEGIN
set @mode = 0
END

update msdb.dbo.RefreshTable set validate = 'Y'
where DestinationDbname = @DBName

/*
** validate column exists in the Source Database.Table
*/
declare @RowID bigint
declare @SourceServerName varchar(300)
declare @SourceDBName varchar(300)
declare @SourceObjectOwner varchar(300)
declare @SourceTableName varchar(300)
declare @SourceColumnName varchar(300)
declare @DestinationServerName varchar(300)
declare @DestinationObjectOwner varchar(300)
declare @DestinationDBName varchar(300)
declare @DestinationTableName varchar(300)
declare @DestinationColumnName varchar(300)
declare @ColumnOrder int
declare @ProcessedOn datetime
declare @Validate varchar(1)
declare @Enabled varchar(1)

declare @str varchar(8000)
declare @strCol varchar(8000)
declare @RaiseErrorStr varchar(1000)

/*
** make sure the linked server is present.
*/
DECLARE cur_validate_Linked_Server CURSOR
READ_ONLY
FOR select Distinct SourceServerName from msdb.dbo.RefreshTable where enabled = 'Y'

OPEN cur_validate_Linked_Server

FETCH NEXT FROM cur_validate_Linked_Server INTO @SourceServerName
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
IF NOT EXISTS (select * from master.dbo.sysservers where srvname = @SourceServerName)
BEGIN
EXEC master.dbo.sp_addlinkedserver @server = @SourceServerName , @srvproduct=N'SQL Server'
EXEC master.dbo.sp_serveroption @server=@SourceServerName , @optname=N'collation compatible' , @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=@SourceServerName , @optname=N'data access' , @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=@SourceServerName , @optname=N'dist' , @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=@SourceServerName , @optname=N'pub' , @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=@SourceServerName , @optname=N'rpc' , @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=@SourceServerName , @optname=N'rpc out' , @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=@SourceServerName , @optname=N'sub' , @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=@SourceServerName , @optname=N'dpub' , @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=@SourceServerName , @optname=N'connect timeout' , @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=@SourceServerName , @optname=N'collation name' , @optvalue=null
EXEC master.dbo.sp_serveroption @server=@SourceServerName , @optname=N'lazy schema validation', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=@SourceServerName , @optname=N'query timeout' , @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=@SourceServerName , @optname=N'use remote collation' , @optvalue=N'false'
END

END
FETCH NEXT FROM cur_validate_Linked_Server INTO @SourceServerName
END
CLOSE cur_validate_Linked_Server
DEALLOCATE cur_validate_Linked_Server

/*
** Validate the Destination DB Exists.
*/
update msdb.dbo.RefreshTable
set validate = 'N'
where DestinationDBName not in (select name from master.dbo.sysdatabases)
and Enabled = 'Y'
and DestinationDBName = @DBName

IF exists (select top 1 * from msdb.dbo.RefreshTable where enabled = 'Y' and validate = 'N' and DestinationDBName = @DBName)
BEGIN
set @RaiseErrorStr =
'-----------------------------------------------------------------------------------
Validation Destination Database entered, please make sure that the Destination Db Exists.
-----------------------------------------------------------------------------------
Run : select * from msdb.dbo.RefreshTable where enabled = ''Y'' and validate = ''N''
and DestinationDBName = '''+@DBName+'''
-------------------------------------------------------------------------------------'
RAISERROR (@RaiseErrorStr, 16, 1)
RETURN
END



/*
** Validate if the source Table exists
*/
create Table #TempT1 (name varchar(4000))

DECLARE cur_validate_columns CURSOR
READ_ONLY
FOR select Distinct
SourceServerName
,SourceDBName
,SourceObjectOwner
,SourceTableName
FROM
msdb.dbo.RefreshTable
where
Enabled = 'Y'
and DestinationDBName = @DBName

OPEN cur_validate_columns
FETCH NEXT FROM cur_validate_columns INTO
@SourceServerName
,@SourceDBName
,@SourceObjectOwner
,@SourceTableName

WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
set @str = '
select
so.name
from
['+@SourceServerName+'].['+@SourceDBName+'].['+@SourceObjectOwner+'].[sysobjects] so
where
so.name = '''+@SourceTableName+''''

Truncate Table #TempT1
Insert into #TempT1
Exec(@str)
-- print @str
IF (select count(*) from #TempT1) > 0
BEGIN
UPDATE msdb.dbo.RefreshTable
SET Validate = 'Y'
where
SourceServerName = @SourceServerName
and SourceDBName = @SourceDBName
and SourceObjectOwner = @SourceObjectOwner
and SourceTableName = @SourceTableName
END
ELSE
BEGIN
UPDATE msdb.dbo.RefreshTable
SET Validate = 'N'
where
SourceServerName = @SourceServerName
and SourceDBName = @SourceDBName
and SourceObjectOwner = @SourceObjectOwner
and SourceTableName = @SourceTableName
END

END
FETCH NEXT FROM cur_validate_columns INTO
@SourceServerName
,@SourceDBName
,@SourceObjectOwner
,@SourceTableName

END
CLOSE cur_validate_columns
DEALLOCATE cur_validate_columns

IF exists (select top 1 * from msdb.dbo.RefreshTable where enabled = 'Y' and validate = 'N'
and DestinationDBName = @DBName)
BEGIN
set @RaiseErrorStr =
'-----------------------------------------------------------------------------------
Validation Failed for Source Tables , Check table RefreshTable.
-----------------------------------------------------------------------------------
select * from msdb.dbo.RefreshTable where enabled = ''Y'' and validate = ''N''
and DestinationDBName = '''+@DBName+'''
------------------------------------------------------------------------------------'
RAISERROR (@RaiseErrorStr, 16, 1)
RETURN
END




/*
** Validate if the source colum exists
*/
DECLARE cur_validate_columns CURSOR
READ_ONLY
FOR select Distinct
RowID
,SourceServerName
,SourceDBName
,SourceObjectOwner
,SourceTableName
,SourceColumnName
FROM
msdb.dbo.RefreshTable
where
Enabled = 'Y'
and DestinationDBName = @DBName
order by RowID

OPEN cur_validate_columns

FETCH NEXT FROM cur_validate_columns INTO @RowID
,@SourceServerName
,@SourceDBName
,@SourceObjectOwner
,@SourceTableName
,@SourceColumnName

WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
set @str = '
select
sc.name
from
['+@SourceServerName+'].['+@SourceDBName+'].['+@SourceObjectOwner+'].[syscolumns] sc
JOIN ['+@SourceServerName+'].['+@SourceDBName+'].['+@SourceObjectOwner+'].[sysobjects] so on sc.id = so.id
where
so.name = '''+@SourceTableName+'''
and sc.name = '''+@SourceColumnName+'''
'
Truncate Table #TempT1
Insert into #TempT1
Exec(@str)
-- print @str
IF (select count(*) from #TempT1) > 0
BEGIN
UPDATE msdb.dbo.RefreshTable
SET Validate = 'Y'
where RowID = @RowID
END
ELSE
BEGIN
UPDATE msdb.dbo.RefreshTable
SET Validate = 'N'
where RowID = @RowID
END

END
FETCH NEXT FROM cur_validate_columns INTO @RowID
,@SourceServerName
,@SourceDBName
,@SourceObjectOwner
,@SourceTableName
,@SourceColumnName
END
CLOSE cur_validate_columns
DEALLOCATE cur_validate_columns

IF exists (select top 1 * from msdb.dbo.RefreshTable where enabled = 'Y' and validate = 'N'
and DestinationDBName = @DBName)
BEGIN
set @RaiseErrorStr =
'-----------------------------------------------------------------------------------
Validation Failed for Source Columns, Check table RefreshTable.
-----------------------------------------------------------------------------------
select * from msdb.dbo.RefreshTable where enabled = ''Y'' and validate = ''N''
and DestinationDBName = '''+@DBName+'''
------------------------------------------------------------------------------------'
RAISERROR (@RaiseErrorStr, 16, 1)
RETURN
END


/*
** Prepare Sql Statement
*/
DECLARE Cur_Prepare_Statement CURSOR
READ_ONLY
FOR select Distinct
SourceServerName
,SourceDBName
,SourceObjectOwner
,SourceTableName
FROM
msdb.dbo.RefreshTable
where
Enabled = 'Y'
and DestinationDBName = @DBName
Order by
SourceTableName


DECLARE @name varchar(40)
OPEN Cur_Prepare_Statement

FETCH NEXT FROM Cur_Prepare_Statement INTO @SourceServerName
,@SourceDBName
,@SourceObjectOwner
,@SourceTableName
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
set @strCol = ''
select @strCol = @strCol + SourceColumnName + ' As ' + DestinationColumnName + ','
from msdb.dbo.RefreshTable
where
SourceServerName = @SourceServerName
and SourceDBName = @SourceDBName
and SourceObjectOwner = @SourceObjectOwner
and SourceTableName = @SourceTableName
order by ColumnOrder
/*
** Get the top 1 name from refresh table for Destination DB Name.
*/
set @DestinationDBName = (select top 1 DestinationDBName from msdb.dbo.RefreshTable
where
SourceServerName = @SourceServerName
and SourceDBName = @SourceDBName
and SourceObjectOwner = @SourceObjectOwner
and SourceTableName = @SourceTableName)
/*
** Get the top 1 name from refresh table for Destination Object Owner.
*/
set @DestinationObjectOwner = (select top 1 DestinationObjectOwner from msdb.dbo.RefreshTable
where
SourceServerName = @SourceServerName
and SourceDBName = @SourceDBName
and SourceObjectOwner = @SourceObjectOwner
and SourceTableName = @SourceTableName)
/*
** Get the top 1 name from refresh table for Destination Table Name.
*/
set @DestinationTableName = (select top 1 DestinationTableName from msdb.dbo.RefreshTable
where
SourceServerName = @SourceServerName
and SourceDBName = @SourceDBName
and SourceObjectOwner = @SourceObjectOwner
and SourceTableName = @SourceTableName)
set @Str =
'/*
** Drops the table if exists
*/
IF exists (select * from ['+@DestinationDBName+'].dbo.sysobjects where type = ''u'' and name = '''+@DestinationTableName+''')
BEGIN
DROP TABLE ['+@DestinationDBName+'].dbo.'+@DestinationTableName+'
END
'
If @mode = 0
BEGIN
print @Str
Exec (@Str)
END
else IF @mode = 1
BEGIN
Exec (@Str)
END
else
BEGIN
print @Str
END

set @strCol = '
/*
** Bulk Copies the data from remote server.
*/
SELECT '+ substring(@strCol,1, len(@strCol)-1) + ' INTO ['+@DestinationDBName+'].['+@DestinationObjectOwner+'].['+@DestinationTableName+'] FROM [' + @SourceServerName +'].['+ @SourceDBName+'].['+@SourceObjectOwner+'].['+@SourceTableName+']'

If @mode = 0
BEGIN
print @strCol
Exec (@strCol)
END
else IF @mode = 1
BEGIN
Exec (@strCol)
END
else
BEGIN
print @strCol
END
END
/*
** UPDATE the Processed Data field, in the refresh table.
*/
UPDATE
msdb.dbo.refreshtable
set ProcessedOn = getdate()
where
DestinationDBName = @DestinationDBName
and DestinationObjectOwner = @DestinationObjectOwner
and DestinationTableName = @DestinationTableName

FETCH NEXT FROM Cur_Prepare_Statement INTO @SourceServerName
,@SourceDBName
,@SourceObjectOwner
,@SourceTableName
END
CLOSE Cur_Prepare_Statement
DEALLOCATE Cur_Prepare_Statement


cheers
shailesh patangay
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site