StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>There isn't anything out-of-the-box that will do that in SSIS; however, it's relatively simple to code a Script Transformation that can act as a data flow destination and that will limit the size of its output file. It's not much harder to add some code to create <strong>multiple</strong> files (each smaller than the specified size) as needed to store all the resulting data.</p> <p>For example, assume your source query is </p> <pre class="lang-sql prettyprint-override"><code>SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS </code></pre> <p>and you're trying to write this to one or more CSV files, without exceeding some particular size per file. </p> <p>Define three package-level variables as follows:</p> <ul> <li>User::TargetFolder (<code>String</code> containing the folder name you want to write to)</li> <li>User::TargetFileNamePattern (<code>String</code> with the naming pattern for the output files; e.g. <code>SampleOutput{0}.csv</code>)</li> <li>User::MaxFileLength (<code>Int32</code> containing the maximum number of characters per file)</li> </ul> <p>Create your data flow like this:</p> <p><img src="https://i.stack.imgur.com/kYz3w.png" alt="data flow screenshot"></p> <p>And code the script transformation thusly:</p> <pre class="lang-cs prettyprint-override"><code>/* Microsoft SQL Server Integration Services Script Component * Write scripts using Microsoft Visual C# 2008. * ScriptMain is the entry point class of the script.*/ using System; using System.Data; using System.IO; using System.Text; using Microsoft.SqlServer.Dts.Pipeline.Wrapper; using Microsoft.SqlServer.Dts.Runtime.Wrapper; [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute] public class ScriptMain : UserComponent { private int _fileCounter; private int _bytesWritten; private TextWriter _tw; private TextWriter CurrentWriter { get { if (_tw == null) { string fileName = String.Format(this.Variables.TargetFileNamePattern, _fileCounter); string filePath = Path.Combine(this.Variables.TargetFolder, fileName); _tw = File.CreateText(filePath); } return _tw; } } public override void PreExecute() { base.PreExecute(); _fileCounter = 1; _bytesWritten = 0; _tw = null; } public override void PostExecute() { base.PostExecute(); if (_tw != null) { _tw.Flush(); _tw.Close(); } } public override void Input0_ProcessInputRow(Input0Buffer Row) { string thisLine = String.Format( "{0},{1},{2},{3},{4},{5},{6},{7},{8}", Row.TABLECATALOG, Row.TABLESCHEMA, Row.TABLENAME, Row.COLUMNNAME, Row.ORDINALPOSITION, Row.COLUMNDEFAULT_IsNull ? "NULL" : Row.COLUMNDEFAULT, Row.ISNULLABLE, Row.DATATYPE, Row.CHARACTERMAXIMUMLENGTH_IsNull ? "NULL" : Row.CHARACTERMAXIMUMLENGTH.ToString()); if (_bytesWritten + thisLine.Length > this.Variables.MaxFileLength) { _tw.Flush(); _tw.Close(); _tw = null; _fileCounter += 1; _bytesWritten = 0; } this.CurrentWriter.WriteLine(thisLine); _bytesWritten += thisLine.Length; } } </code></pre> <p>For each row in the source query, this will build the string to be written and then check to see if adding that string to the current <code>TextWriter</code> would cause the file to be too big. If that's the case, the current file is flushed to disk and closed; the next call to <code>this.CurrentWriter</code> will create a new <code>TextWriter</code> object for the next file in sequence.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload