Hungry DBA .com for DBAs who like food

The following solution describes how to perform a generic multiple table export to a flat file via SSIS.

Each table has a different number of columns and attributes.  The only common attribute that they share is that all the tables are named with the same post fix "Load".

The solution includes three main parts: 

 

Prepare Data For Export

Drops and Creates a series of tables named with a post fix "LoadOut" based on the tables named with the post fix "Load" within the database.

This particular solution also logs the data that is exported into a series of "LoadHistory" tables and assigns each export with a Load Id

Generate List of Tables to ExportPasses a list of the created "LoadOut" tables to be used in the For Each Loop Container
C# Script Task

Runs within the loop container to output each table into a CSV file with a given delimiter - This particular example uses a pipe delimiter "|"

The output files are produced in the directory given by the "Path" variable.

 

Variables


 

Solution


Config for "Prepare Data for Export" task



"Prepare Data for Export" SQL Statement

Declare @outcur cursor
Declare @sql nvarchar(max)
Declare @tabname nvarchar(max)
Declare @tabcur cursor
Declare @ExportDate datetime

set @ExportDate = GETDATE()

-- Drop all out tables

set @outcur = CURSOR FOR
select name from sysobjects
where type = 'U'
and name like '%Out'
order by 1

open @outcur

fetch next from @outcur into @tabname

while @@FETCH_STATUS = 0
begin

set @sql = 'drop table ' + @tabname

exec (@sql)

fetch next from @outcur into @tabname

end

close @outcur
deallocate @outcur


set @tabcur = CURSOR FOR
select name from sysobjects
where type = 'U'
and name like '%Load'
order by 1

open @tabcur

fetch next from @tabcur into @tabname

while @@FETCH_STATUS = 0
BEGIN

-- Populate Load History Table with records to be Exported

set @sql = 'insert into ' + @tabname + 'History
('

select @sql = @sql + name + ',' + char(13) + CHAR(10) from syscolumns
where OBJECT_NAME(id) = @tabname
and name <> 'LastExportDate'
and name <> 'LastExportLoadId'
order by colid

set @sql = SUBSTRING(@sql,1,len(@sql) - 3) + ')' + char(13) + CHAR(10) + 'select '

select @sql = @sql + name + ',' + char(13) + CHAR(10) from syscolumns
where OBJECT_NAME(id) = @tabname
and name <> 'LastExportDate'
and name <> 'LastExportLoadId'
order by colid

set @sql = SUBSTRING(@sql,1,len(@sql) - 3) + char(13) + CHAR(10)

set @sql = @sql + 'from ' + @tabname + '
where ApprovedForExport = 1
and (LastExportDate is null or ApprovedDate > LastExportDate)'

exec (@sql)

-- Update Load History table with Export Date and Latest Load Id

set @sql = 'update ' + @tabname + 'History
set ExportDate = ''' + convert(nvarchar(30),@ExportDate,120) + ''',
ExportLoadId = source.part3
from
(select part3 from NewIds
where part1 = ''' + @tabname + '''
) as source
where ExportDate is null'

exec (@sql)

-- Increment the LoadId, in preparation for the next load, if anything was exported

declare @maxid int
declare @newid int

set @sql = 'select @tmaxid = MAX(ExportLoadId)
from ' + @tabname + 'History'

exec sp_executesql
@sql, --dynamic query
N'@tmaxid int output', --query parameters
@tmaxid = @maxid output --parameter mapping

select @newid = part3
from NewIds
where part1 = @tabname

if @maxid >= @newid
begin
update NewIds
set part3 = part3 + 1
where part1 = @tabname
end

-- Update Load Table with the relevant Export Date and LoadId

set @sql = 'update ' + @tabname + '
set LastExportDate = source.ExportDate,
LastExportLoadId = source.ExportLoadId
from
(select LoadId, ApprovedDate, ExportDate, ExportLoadId
from ' + @tabname + 'History) source
where ' + @tabname + '.LoadId = source.LoadId
and ' + @tabname + '.ApprovedDate = source.ApprovedDate
and ISNULL(' + @tabname + '.LastExportLoadId,0) <> isnull(source.ExportLoadId,0)'

exec (@sql)

-- Create OUT Table for Export

declare @maxdate datetime

set @sql = 'select @tmaxdate = MAX(ExportDate)
from ' + @tabname + 'History'

exec sp_executesql
@sql, --dynamic query
N'@tmaxdate datetime output', --query parameters
@tmaxdate = @maxdate output --parameter mapping

if convert(nvarchar(30),@maxdate,120) = convert(nvarchar(30),@ExportDate,120)
begin

set @sql = 'select * into ' + @tabname + 'Out
from ' + @tabname + 'History
where ExportDate = ''' + convert(nvarchar(30),@ExportDate,120) + ''''

exec(@sql)

end

fetch next from @tabcur into @tabname

end

close @tabcur
deallocate @tabcur

Config for "Generate List of Tables to Export" Task

"Generate List of Tables to Export" SQL Statement

select name from sysobjects
where name like '%LoadOut'
and type = 'U'
order by 1

For Each Loop Container Configuration


Script Task Configuration


C# Script

/*
Microsoft SQL Server Integration Services Script Task
Write scripts using Microsoft Visual C# 2008.
The ScriptMain is the entry point class of the script.
*/

using System;
using System.Data;
using System.IO;
using Microsoft.SqlServer.Dts.Runtime;
using System.Data.SqlClient;
using System.Data.OleDb;

namespace ST_927f536e09554134bdd806aabefc666e.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{

#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion


public void Main()
{
string strConn = Dts.Connections["SourceConnectionOLEDB"].ConnectionString;
object path = Dts.Variables["Path"].Value;
object table = Dts.Variables["TableName"].Value;
/* string generatedFileName = String.Format("{0}_{1}.csv", table, String.Format("{0:yyyyMMdd}", DateTime.Today)); */
string generatedFileName = String.Format("{0}.csv", table);
string pathToSaveFile = Path.Combine(path.ToString(), generatedFileName);

try
{
var oldbda = new OleDbDataAdapter(String.Format("select * from {0}", table.ToString()), strConn);
var ds = new DataSet();
oldbda.Fill(ds);

CreateCSVFile(ds.Tables[0], pathToSaveFile);
Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception exception)
{
Dts.TaskResult = (int)ScriptResults.Failure;
Dts.Log(exception.Message, 0, new byte[0]);
Dts.Events.FireError(0, "CSV file generator script task", String.Format("While trying to create {0} file, following error occured: {1}", pathToSaveFile, exception.Message), String.Empty, 0);
}
}

public void CreateCSVFile(DataTable dt, string strFilePath)
{
bool fileEventAgain = true;
StreamWriter sw = new StreamWriter(strFilePath, false);
try
{
int iColCount = dt.Columns.Count;

for (int i = 0; i < iColCount; i++)
{
sw.Write("\"" + dt.Columns[i] + "\"");
if (i < iColCount - 1)
{
sw.Write("|");
}
}

sw.Write(sw.NewLine);

foreach (DataRow dr in dt.Rows)
{
for (int i = 0; i < iColCount; i++)
{
if (!Convert.IsDBNull(dr[i]))
{
sw.Write("\"" + dr[i].ToString() + "\"");
}
if (i < iColCount - 1)
{
sw.Write("|");
}
}
sw.Write(sw.NewLine);
}
sw.Close();
Dts.Events.FireInformation(0, "CSV file creation", String.Format("Created {0} file successfully", strFilePath), string.Empty, 0, ref fileEventAgain);
}
catch
{
if (sw != null)
sw.Close();
throw;
}
}
}
}