Feel free to translate to VB.NET as you see fit. Seeing as how I already have this code ~ written for a different project, I mashed your request in with how mine works
Passing in 3 SSIS variables: vFileName, vDestinationPath and vResultSet, the code in Main will convert the ado recordset into a DataTable which is then added to a DataSet and passed to the Persist method. Persist has a default parameter for delimiter of |.
This implementation does not attempt to deal with any of the corner cases, at all. It does not escape text columns with a qualifier, doesn't escape embedded qualifiers, do anything with newlines in the feeds and something in the OleDbDataAdapter's fill method fails with binary data, etc
public void Main()
{
string fileName = Dts.Variables["User::vFileName"].Value.ToString();
DataSet ds = null;
DataTable dt = null;
string outputFolder = Dts.Variables["User::vDestinationPath"].Value.ToString();
string fileMask = string.Empty;
string sheetName = string.Empty;
string outSubFolder = string.Empty;
string message = string.Empty;
bool fireAgain = true;
try
{
ds = new DataSet();
dt = new DataTable();
System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter();
adapter.Fill(dt, Dts.Variables["User::vResultSet"].Value);
string baseFileName = System.IO.Path.GetFileNameWithoutExtension(fileName);
baseFileName = System.IO.Path.GetFileName(fileName);
ds.Tables.Add(dt);
//foreach (DataTable dt in ds.Tables)
{
Persist(ds, fileName, outputFolder);
}
}
catch (Exception ex)
{
Dts.Events.FireInformation(0, "Data Dumper", string.Format("{0}|{1}", "fileName", fileName), string.Empty, 0, ref fireAgain);
Dts.Events.FireInformation(0, "Data Dumper", string.Format("{0}|{1}", "outputFolder", outputFolder), string.Empty, 0, ref fireAgain);
Dts.Events.FireInformation(0, "Data Dumper", string.Format("{0}|{1}", "ExceptionDetails", ex.ToString()), string.Empty, 0, ref fireAgain);
Dts.Events.FireInformation(0, "Data Dumper", string.Format("{0}|{1}", "InnerExceptionDetails", ex.InnerException), string.Empty, 0, ref fireAgain);
}
Dts.TaskResult = (int)ScriptResults.Success;
}
public static void Persist(System.Data.DataSet ds, string originalFileName, string outputFolder, string delimiter = "|")
{
// Enumerate through all the tables in the dataset
// Save it out as sub versions of the
if (ds == null)
{
return;
}
string baseFileName = System.IO.Path.GetFileNameWithoutExtension(originalFileName);
string baseFolder = System.IO.Path.GetDirectoryName(originalFileName);
System.Collections.Generic.List<string> header = null;
foreach (System.Data.DataTable table in ds.Tables)
{
string outFilePath = System.IO.Path.Combine(outputFolder, string.Format("{0}.{1}.csv", baseFileName, table.TableName));
System.Text.Encoding e = System.Text.Encoding.Default;
if (table.ExtendedProperties.ContainsKey("Unicode") && (bool)table.ExtendedProperties["Unicode"])
{
e = System.Text.Encoding.Unicode;
}
using (System.IO.StreamWriter file = new System.IO.StreamWriter(System.IO.File.Open(outFilePath, System.IO.FileMode.Create), e))
{
table.ExtendedProperties.Add("Path", outFilePath);
// add header row
header = new System.Collections.Generic.List<string>(table.Columns.Count);
foreach (System.Data.DataColumn item in table.Columns)
{
header.Add(item.ColumnName);
}
file.WriteLine(string.Join(delimiter, header));
foreach (System.Data.DataRow row in table.Rows)
{
// TODO: For string based fields, capture the max length
IEnumerable<string> fields = (row.ItemArray).Select(field => field.ToString());
file.WriteLine(string.Join(delimiter, fields));
}
}
}
}
Need to run but a Biml implementation looks like
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Connections>
<OleDbConnection Name="tempdb" ConnectionString="Data Source=localhost\dev2014;Initial Catalog=AdventureWorksDW2014;Provider=SQLNCLI11.0;Integrated Security=SSPI;"/>
</Connections>
<Packages>
<Package Name="so_37059747" ConstraintMode="Linear">
<Variables>
<Variable DataType="String" Name="QuerySource"><![CDATA[SELECT
S.name
, T.name
FROM
sys.schemas AS S
INNER JOIN
sys.tables AS T
ON T.schema_id = S.schema_id;]]></Variable>
<Variable DataType="String" Name="SchemaName">dbo</Variable>
<Variable DataType="String" Name="TableName">foo</Variable>
<Variable DataType="String" Name="QueryTableDump" EvaluateAsExpression="true">"SELECT X.* FROM [" + @[User::SchemaName] + "].[" + @[User::TableName] + "] AS X;"</Variable>
<Variable DataType="Object" Name="rsTables"></Variable>
<Variable DataType="Object" Name="vResultSet"></Variable>
<Variable DataType="String" Name="vFileName" EvaluateAsExpression="true">@[User::SchemaName] + "_" + @[User::TableName] + ".txt"</Variable>
<Variable DataType="String" Name="vDestinationPath">c:\ssisdata\so\Output</Variable>
</Variables>
<Tasks>
<ExecuteSQL
ConnectionName="tempdb"
Name="SQL Generate Loop data"
ResultSet="Full">
<VariableInput VariableName="User.QuerySource" />
<Results>
<Result VariableName="User.rsTables" Name="0" />
</Results>
</ExecuteSQL>
<ForEachAdoLoop SourceVariableName="User.rsTables" Name="FELC Shred rs" ConstraintMode="Linear">
<VariableMappings>
<VariableMapping VariableName="User.SchemaName" Name="0" />
<VariableMapping VariableName="User.TableName" Name="1" />
</VariableMappings>
<Tasks>
<ExecuteSQL
ConnectionName="tempdb"
Name="SQL Generate Export data"
ResultSet="Full">
<VariableInput VariableName="User.QueryTableDump" />
<Results>
<Result VariableName="User.vResultSet" Name="0" />
</Results>
</ExecuteSQL>
<Script ProjectCoreName="ST_RS2CSV" Name="SCR Convert to text">
<ScriptTaskProjectReference ScriptTaskProjectName="ST_RS2CSV" />
</Script>
</Tasks>
</ForEachAdoLoop>
</Tasks>
</Package>
</Packages>
<ScriptProjects>
<ScriptTaskProject ProjectCoreName="ST_RS2CSV" Name="ST_RS2CSV" VstaMajorVersion="0">
<ReadOnlyVariables>
<Variable Namespace="User" VariableName="vFileName" DataType="String" />
<Variable Namespace="User" VariableName="vDestinationPath" DataType="String" />
<Variable Namespace="User" VariableName="vResultSet" DataType="Object" />
</ReadOnlyVariables>
<Files>
<File Path="ScriptMain.cs" BuildAction="Compile">
<![CDATA[namespace DataDumper
{
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Xml.Linq;
using Microsoft.SqlServer.Dts.Runtime;
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
public void Main()
{
string fileName = Dts.Variables["User::vFileName"].Value.ToString();
DataSet ds = null;
DataTable dt = null;
string outputFolder = Dts.Variables["User::vDestinationPath"].Value.ToString();
string fileMask = string.Empty;
string sheetName = string.Empty;
string outSubFolder = string.Empty;
string message = string.Empty;
bool fireAgain = true;
try
{
ds = new DataSet();
dt = new DataTable();
System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter();
adapter.Fill(dt, Dts.Variables["User::vResultSet"].Value);
string baseFileName = System.IO.Path.GetFileNameWithoutExtension(fileName);
baseFileName = System.IO.Path.GetFileName(fileName);
ds.Tables.Add(dt);
//foreach (DataTable dt in ds.Tables)
{
Persist(ds, fileName, outputFolder);
}
}
catch (Exception ex)
{
Dts.Events.FireInformation(0, "Data Dumper", string.Format("{0}|{1}", "fileName", fileName), string.Empty, 0, ref fireAgain);
Dts.Events.FireInformation(0, "Data Dumper", string.Format("{0}|{1}", "outputFolder", outputFolder), string.Empty, 0, ref fireAgain);
Dts.Events.FireInformation(0, "Data Dumper", string.Format("{0}|{1}", "ExceptionDetails", ex.ToString()), string.Empty, 0, ref fireAgain);
Dts.Events.FireInformation(0, "Data Dumper", string.Format("{0}|{1}", "InnerExceptionDetails", ex.InnerException), string.Empty, 0, ref fireAgain);
}
Dts.TaskResult = (int)ScriptResults.Success;
}
public static void Persist(System.Data.DataSet ds, string originalFileName, string outputFolder, string delimiter = "|")
{
// Enumerate through all the tables in the dataset
// Save it out as sub versions of the
if (ds == null)
{
return;
}
string baseFileName = System.IO.Path.GetFileNameWithoutExtension(originalFileName);
string baseFolder = System.IO.Path.GetDirectoryName(originalFileName);
System.Collections.Generic.List<string> header = null;
foreach (System.Data.DataTable table in ds.Tables)
{
string outFilePath = System.IO.Path.Combine(outputFolder, string.Format("{0}.{1}.csv", baseFileName, table.TableName));
System.Text.Encoding e = System.Text.Encoding.Default;
if (table.ExtendedProperties.ContainsKey("Unicode") && (bool)table.ExtendedProperties["Unicode"])
{
e = System.Text.Encoding.Unicode;
}
using (System.IO.StreamWriter file = new System.IO.StreamWriter(System.IO.File.Open(outFilePath, System.IO.FileMode.Create), e))
{
table.ExtendedProperties.Add("Path", outFilePath);
// add header row
header = new System.Collections.Generic.List<string>(table.Columns.Count);
foreach (System.Data.DataColumn item in table.Columns)
{
header.Add(item.ColumnName);
}
file.WriteLine(string.Join(delimiter, header));
foreach (System.Data.DataRow row in table.Rows)
{
// TODO: For string based fields, capture the max length
IEnumerable<string> fields = (row.ItemArray).Select(field => field.ToString());
file.WriteLine(string.Join(delimiter, fields));
}
}
}
}
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
}
}
]]>
</File>
<File Path="Properties\AssemblyInfo.cs" BuildAction="Compile">
using System.Reflection;
using System.Runtime.CompilerServices;
[assembly: AssemblyTitle("AssemblyTitle")]
[assembly: AssemblyDescription("")]
[assembly: AssemblyConfiguration("")]
[assembly: AssemblyCompany("Bill Fellows")]
[assembly: AssemblyProduct("ProductName")]
[assembly: AssemblyCopyright("Copyright @ 2016")]
[assembly: AssemblyTrademark("")]
[assembly: AssemblyCulture("")]
[assembly: AssemblyVersion("1.0.*")]
</File>
</Files>
<AssemblyReferences>
<AssemblyReference AssemblyPath="System" />
<AssemblyReference AssemblyPath="System.Core" />
<AssemblyReference AssemblyPath="System.Data" />
<AssemblyReference AssemblyPath="System.Data.DataSetExtensions" />
<AssemblyReference AssemblyPath="System.Windows.Forms" />
<AssemblyReference AssemblyPath="System.Xml" />
<AssemblyReference AssemblyPath="Microsoft.SqlServer.ManagedDTS.dll" />
<AssemblyReference AssemblyPath="Microsoft.SqlServer.ScriptTask.dll" />
<AssemblyReference AssemblyPath="System.Linq" />
<AssemblyReference AssemblyPath="System.Xml.Linq" />
<AssemblyReference AssemblyPath="Microsoft.VisualBasic" />
</AssemblyReferences>
</ScriptTaskProject>
</ScriptProjects>
</Biml>
That dumped all of AdventureworksDW2014 in 15 seconds
Based on the comment that this line is failing IEnumerable<string> fields = (row.ItemArray).Select(field => field.ToString());
Ensure that you have the following using statements in your project. I think those extensions are in the Linq namespaces but it could have been the Collections
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Xml.Linq;
using Microsoft.SqlServer.Dts.Runtime;
Why was the original slow?
My assumption is the slowness boils down to all that concatenation. Strings are immutable in .Net and you are creating a new version of that string each time you add a column to it. When I build my line, I'm using the String.Join method to zip up each element an array into a single string. This also simplifies the logic required to append the field delimiters.
I also immediately write the current line to a file instead of bloating my memory just to dump it all with a call to WriteAllText
