drop proc s_CreateExportPackage
go

create proc s_CreateExportPackage
@SchemaName varchar(128) ,
@TableName	varchar(200) ,
@FileLoc varchar(1000) ,
@ServerName varchar(100) ,
@DatabaseName varchar(100) ,
@Coldelim varchar(20) = '_x002C_' ,
@rowdelim varchar(20) = '_x000D__x000A_'
/*
exec s_CreateExportPackage 'dbo', 'ExportTest_c001c002', 'c:\test\', '.', 'SSIS_Test'
*/
/*
CREATE TABLE [dbo].[Trace]
	(
    [Trace_id] [int] IDENTITY(1,1) NOT NULL,
    [EventDate] [datetime] NOT NULL default getdate(),
    [Entity] [varchar](100) NOT NULL,
    [key1] [varchar](100) NULL,
    [key2] [varchar](100) NULL,
    [key3] [varchar](100) NULL,
    [data1] [varchar](max) NULL,
    [data2] [varchar](max) NULL,
    [data3] [varchar](max) NULL,
    [UserName] [varchar](128) NULL default suser_sname()
    )
go

alter table Trace add constraint pk_Trace primary key clustered
	(Trace_id)
go
*/
as
declare @rowcount int, @inserted int, @updated int
declare @entity varchar(100)
declare @key1 varchar(100)
declare @data1 varchar(max)

	select @entity = OBJECT_NAME(@@PROCID)
/*
	-- Trace code for debugging, optimisation, maintenance	
	select @data1 =         '@SchemaName=''' + coalesce(convert(varchar(200),@SchemaName),'null') + ''''
						+ ', @TableName=''' + coalesce(convert(varchar(200),@TableName),'null') + ''''
						+ ', @FileLoc=''' + coalesce(convert(varchar(200),@FileLoc),'null') + ''''
						+ ', @ServerName=''' + coalesce(convert(varchar(200),@ServerName),'null') + ''''
						+ ', @DatabaseName=''' + coalesce(convert(varchar(200),@DatabaseName),'null') + ''''
						+ ', @Coldelim=''' + coalesce(convert(varchar(200),@Coldelim),'null') + ''''
						+ ', @rowdelim=''' + coalesce(convert(varchar(200),@rowdelim),'null') + ''''


	insert trace (entity, key1, key2, data1)
	select @entity, @key1, 'start', @data1
*/
	select @FileLoc = @FileLoc + '\\'
	where right(@FileLoc,1) <> '\'

declare @FileName varchar(200)
	select @FileName = @FileLoc + @DatabaseName + '.' + @SchemaName + '.' + @TableName + '.txt'

	-- Get the structure of the table to export
	declare @TableSchema table 
			(	COLUMN_NAME varchar(128)
				, ORDINAL_POSITION int
				, DATA_TYPE varchar(100)
				, CHARACTER_MAXIMUM_LENGTH int
				, NUMERIC_PRECISION int
				, NUMERIC_SCALE int
				, DATETIME_PRECISION int
				, coldelim varchar(20)
			)

	declare @sql varchar(1000)
	select @sql = 'select COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, DATETIME_PRECISION from ' + @ServerName + '.INFORMATION_SCHEMA.COLUMNS where  TABLE_SCHEMA = ''' + @SchemaName + ''' and TABLE_NAME = ''' + @TableName + ''''

	insert @TableSchema (COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, DATETIME_PRECISION)
	exec (@sql)
	update @TableSchema
	set coldelim = @Coldelim
	update @TableSchema
	set coldelim = @rowdelim
	where ORDINAL_POSITION = (select max(ORDINAL_POSITION) from @TableSchema)


	declare @Output table
		(
			seq1 int
			, seq2 int
			, seq3 int
			, s varchar(1000)
		)

	-- Static package Data
	insert @Output
	select seq, 0, 0, s
	from Export_Package_Template

	-- Database connection
	insert @Output
	select seq1, 0, 0
			, replace(replace(s,'||DatabaseName||', @DatabaseName), '||ServerName||', @ServerName)
	from Export_Package_Template_Replace
	where Type1 = 'DatabaseConnection'

	insert @Output
	select seq1, 0, 0
			, replace(s,'||FileName||', @FileName)
	from Export_Package_Template_Replace
	where Type1 = 'FileConnection'

	insert @Output
	select seq1, 0, 0
			, replace(replace(s,'||TableName||', @TableName),'||SchemName||', @SchemaName)
	from Export_Package_Template_Replace
	where Type1 = 'TableName'

	insert @Output
	select a.seq1, b.ORDINAL_POSITION, a.seq2
			, replace(replace(replace(s, '||colsize||', b.CHARACTER_MAXIMUM_LENGTH), '||colname||', b.COLUMN_NAME),'||coldelim||',b.coldelim)
	from Export_Package_Template_Replace a
		cross join @TableSchema b
	where a.Type1 in ('FileCol1','FileCol2','ColMap1','ColMap2','TableCol1','TableCol2')
	and a.Type2 = b.DATA_TYPE

	select * from @Output order by seq1, seq2, seq3
go