SSIS The package contains two objects with the duplicate name of output column “ErrorCode”

Grrr…this one can be annoying!
But when you know how to cope with it, it’s an easy fix.

The problem: The SSIS package is failing validation with a message to the effect of “The package contains two objects with the duplicate name of “output column “ErrorCode” (123)” and “output column “ErrorCode” (456)”.

The explanation: The Data Flow source (for at least OLE DB Sources) will have a Error Output that by default will contain all the columns of the Source with two additional columns: ErrorCode and ErrorColumn.

The solution: The source column can be renamed as a last resort. This is often not practical or possible, the better solution is to rename the Error Output column. With the data flow open:

1) Choose the Data Flow Source
2) Choose Properties
3) Click ‘Show Advanced Editor’
4) Navigate to ‘Input and Output Properties’
5) Open the Source Error Output
6) Scroll to the bottom of the list and rename the last ‘ErrorCode’ column.

SSIS advanced editor errorcode errorcolumn

Thanks to:

SSIS BufferTempStoragePath: The buffer manager cannot write 8 bytes to file

Hi there!

This morning I received this nice little error during ETL:

"The buffer manager cannot write 8 bytes to file C:\....There was insufficient disk space or quota."


First thing Google told me was:

Check if you are experiencing any memory problems and errors ..

Okay…you’re right again Google, we do have a memory shortage (for heavy SSIS).
Then I found this blog post by BI Monkey explaining a few things:

Two innocuous properties on every Data Flow in SSIS. You’ve likely never noticed them. But they can easily kill your jobs if not set properly.

They tell SSIS where to write to on disk under given circumstances. BufferTempStoragePath is where on disk it will write buffer contents in the event that there is no available memory. BLOBTempStoragePath is where on disk it will write any BLOB data in the buffer.

One of the tips from MSSQLTips about this:

Best Practice #8 – BufferTempStoragePath and BLOBTempStoragePath

If there is a lack of memory resource i.e. Windows triggers a low memory notification event, memory overflow or memory pressure, the incoming records, except BLOBs, will be spooled to the file system by SSIS. The file system location is set by the BufferTempStoragePath of the data flow task. By default its value is blank, in that case the location will be based on the of value of the TEMP/TMP system variable.

Likewise SSIS may choose to write the BLOB data to the file system before sending it to the destination because BLOB data is typically large and cannot be stored in the SSIS buffer. Once again the file system location for the spooling BLOB data is set by the BLOBTempStoragePath property of the data flow task. By default its value is blank. In that case the location will be the value of the TEMP/TMP system variable. As I said, if you don’t specify the values for these properties, the values of TEMP and TMP system variables will be considered as locations for spooling. The same information is recorded in the log if you enable logging of the PipelineInitialization event of the data flow task as shown below.

User:PipelineInitialization,ARSHADALI-LAP,FAREAST\arali,Data Flow Task,{C80814F8-51A4-4149-8141-D840C9A81EE7},{D1496B27-9FC7-4760-821E-80285C33E74D},10/11/2009 1:38:10 AM,10/11/2009 1:38:10 AM,0,0x,No temporary BLOB data storage locations were provided. The buffer manager will consider the directories in the TEMP and TMP environment variables.

So far so good. What is important here is to change this default values of the BufferTempStoragePath/BLOBTempStoragePath properties and specify locations where the user executing the package (if the package is being executed by SQL Server Job, then SQL Server Agent service account) has access to these locations. Preferably both locations should refer to separate fast drives (with separate spindles) to maximize I/O throughput and improve performance.

In the Dataflow properties:

He advices to setup two package variables in every (template) SSIS package to configure these during deployment (which makes sense) or at least change the default C-drive (which is in our case the smallest disk) to a different one.

Put this one on your SQL Server / SSIS checklist!
Overwrite the default paths for BLOBTempStoragePath and BufferTempStoragePath if your C-drive is too little or too slow (SSD perhaps?).

SSIS: Extract package information (e.g. DataFlowTaskName, TaskDescription, PrecedenceConstraint, etc.)

Found this great blog post from Saravanan:

He describes a way to search through the XML coding of your SSIS packages, to for example, look for a certain DataFlow which is called ‘RAW_DWH_Main‘.

Step 1) Enable FILESTREAM via SQL Server Configuration Manager

Step 2) Create the FileTable (and a new database)
NOTE: Make sure to fill in an existing path for the directory.

EXEC sp_configure filestream_access_level, 2

CREATE DATABASE PackageDefinition
(Name = PackageDefinition,
FILENAME = 'E:\FileTableDB\PackageDefinition.mdf'),
(NAME = Package,
LOG ON (Name = PackageDefinitionLog,
FILENAME = 'E:\FileTableDB\PackageDefinitionLog.ldf')
DIRECTORY_NAME = N'PackageDefinition');

USE PackageDefinition;

CREATE TABLE FT.PackageDefinition AS FileTable
(FileTable_Directory = 'PackageDirectory');

Here’s your new FileTable:


Explore the directory and copy/paste your SSIS packages here


Now copy (or perhaps move/relocate) your TFS to this directory.

Almost done…run the query!


( /*Query the file content([file_stream]) from filetable*/
,FileTableRootPath() AS RootPath
,file_stream.GetFileNamespacePath() as FilePath
,path_locator.GetLevel() AS PathLevel
,PackageData = CAST(CAST([file_stream] AS VARBINARY(MAX)) AS XML )
FROM [FT].[PackageDefinition]
WHERE file_type = 'dtsx' --SSIS Packages
--AND Name LIKE '%RAW_DWH_Main%' --FileName
AND file_stream.GetFileNamespacePath() LIKE '\PackageDirectory\DWH.SSIS\%' --Project folder
AND path_locator.GetLevel() = 2


,PackageName = Exe.pkg.value ('@DTS:ObjectName' ,'VARCHAR(250)')
,CreationDate = Exe.pkg.value ('@DTS:CreationDate' ,'DATETIME2(0)')
,CreatorName = Exe.pkg.value ('@DTS:CreatorName' ,'VARCHAR(250)')
,DataFlowTaskName = Task.pkg.value ('@DTS:Description' ,'VARCHAR(250)')
,TaskDescription = Task.pkg.value ('@DTS:ObjectName' ,'VARCHAR(250)')
,PrecedenceConstraint = '['+REPLACE((pc.pkg.value ('@DTS:From','VARCHAR(250)')),'Package\','')+'] ' +'---->' + '['+REPLACE((pc.pkg.value ('@DTS:To' ,'VARCHAR(250)')),'Package\','') +']'
,refId = Con.pkg.value ('@DTS:refId' ,'VARCHAR(250)')
,ConnectionProvider = Con.pkg.value ('@DTS:CreationName' ,'VARCHAR(250)')
,ConnectionManagerName = Con.pkg.value ('@DTS:ObjectName' ,'VARCHAR(250)')
,ConnectionString = Par.pkg.value ('@DTS:ObjectName' ,'VARCHAR(250)')
,ParameterName = CM.pkg.value ('@DTS:ConnectionString' ,'VARCHAR(250)')
,ParameterValue = Par.pkg.value ('.' ,'VARCHAR(250)')
,VariableName = Var.pkg.value ('@DTS:ObjectName' ,'VARCHAR(250)')
,VariableValue = Var.pkg.value ('.' ,'VARCHAR(250)')
FROM PackageDefinition
OUTER APPLY PackageData.nodes('/DTS:Executable') AS Exe(pkg)
OUTER APPLY Exe.pkg.nodes('DTS:Executables/DTS:Executable') AS Task(pkg)
OUTER APPLY Exe.pkg.nodes('DTS:PackageParameters/DTS:PackageParameter') AS Par(pkg) --Will only return data while using the 'Package Deployment'-model I guess.
OUTER APPLY Exe.pkg.nodes('DTS:Variables/DTS:Variable') AS Var(pkg)
OUTER APPLY Exe.pkg.nodes('DTS:ConnectionManagers/DTS:ConnectionManager/DTS:ObjectData/DTS:ConnectionManager') AS CM(pkg)
OUTER APPLY Exe.pkg.nodes('DTS:ConnectionManagers/DTS:ConnectionManager') AS Con(pkg)
OUTER APPLY Exe.pkg.nodes('DTS:PrecedenceConstraints/DTS:PrecedenceConstraint') AS pc(pkg)
) AS r
WHERE r.DataFlowTaskName LIKE '%MyDataFlow%'

The results:


Important note:
The query doesn’t return any nested tasks, you may need to use a LIKE-operator for that:

If you have a solution for this, please let me know or post the solution in the comments. Thanks!

More info about FileTables:


Truncate certain tables (or in this case Error-tables)

Here’s another stored procedure which truncates certain tables (in this case *_Errors tables).
In case you’re using Fast Load on your SSIS Destination, it may occur that it fully loads that gigantic table into your Error-table.
So make sure to monitor those or truncate them every few days.

-- =============================================
-- Author: Clint Huijbers
-- Create date: 2014-08-07
-- Description: This SP is used to truncate *_Errors tables
-- =============================================
CREATE PROCEDURE [dbo].[usp_TruncateErrorTables]
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

DECLARE @SQL VARCHAR(800) --SQL query to execute

SELECT 'TRUNCATE TABLE [dbo].['+Name+']' AS SQLStatement
FROM sys.tables
WHERE is_ms_shipped = 0
AND Name LIKE '%_Errors'

OPEN CursorName
WHILE (@@fetch_status = 0)


PRINT @SQL+' --- Executed'


CLOSE CursorName


Note to myself, DO NOT USE a ‘Data Conversion’ transformation component in SSIS…:S

When it seems too easy with SSIS, you’ll probably need to fix it afterwards 🙂
Today in a blurry moment I didn’t think twice about using a ‘Data Conversion‘ transformation component and look what happened during processing:


The data value cannot be converted for reasons other than sign mismatch or data overflow.

A very vague error message that doesn’t really help. Googling doesn’t help either:

One suggestion, delete the ‘Data Conversion’-component and drag a ‘Devired Column’-component onto your data flow plane. That makes life easier.
Most common things are blanc/white spaces or dot (‘.’) versus comma (‘,’) issues:


SSIS: Delete old files with PowerShell (via an ‘Execute Process Task’) #FileRetentionPeriod

Today I wanted to delete old files from certain folders based on a given retention period of 60 days. In my case the folders ‘Processed’ and ‘Incorrect’.
A while a go I wrote a PowerShell maintenance script that just loops through a list of pre-defined paths/folders and deletes files which are older than the given number of days…but I realised today, why not execute that similair PowerShell-script within SSIS?
Well…here’s how you could do that and in fact it’s easier that you might think!

In my example I made a Squence Container at the end of my Control Flow in SSIS which holds all the Process Tasks that delete the so called ‘old files’:


First step to do is to create two variables:

@[User::ProcessedFolder] (or any other name) = "D:\DWH\Import\"
@[User::PowerShellProcessedArguments] = "ls -Path '"+ @[User::ProcessedFolder] +"' -Filter '*.csv' |? {$_.LastWriteTime -lt (get-date).AddDays(-60)} | Remove-Item"
IMPORTANT: Make sure to modify the extention-property above in case you want to delete other types of files.

Sub-folders and files:
In case you would like to have it to also look at sub-folders, add ‘-Recurse‘ just after the extention-argument, for example:
"ls -Path '"+ @[User::ProcessedFolder] +"' -Filter '*.csv' -Recurse |? {$_.LastWriteTime -lt (get-date).AddDays(-60)} | Remove-Item"

Test run (-WhatIf):
In case you would like to verify the syntax, just add ‘-WhatIf‘ at the end, for example:
"ls -Path '"+ @[User::ProcessedFolder] +"' -Filter '*.csv' -Recurse |? {$_.LastWriteTime -lt (get-date).AddDays(-60)} | Remove-Item" -WhatIf"

Next, drag an ‘Execute Process Task‘ to your Control Flow. Now double click on it to open the editor and go to ‘Process‘. Set these properties:

Execute Process Task Editor -> Process properties:
Executable = C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe

Arguments = (blank)
You can leave this one blank (will be filled by an expression during execution, see below).

WindowStyle = Hidden
Otherwise it will pop-up a PowerShell-window everytime it executes one of these. It will now pop-up a single command-window for less than a second.

It should now look like (perhaps except the Arguments-property):

Now go to ‘Expressions‘ and make sure that the ‘Arguments‘-property will be given the value of @[User::PowerShellProcessedArguments]:

That’s it!
Now run it a couple of times 🙂

I’ve used SQLCMD before to delete old files via a stored procedure, but this is a bit dangerous due to the fact that it requires you to enable SQLCMD:

Or use a single PowerShell script to delete all files in specific folders during maintenance?

More things you could do with PowerShell? Click here.

SSIS 2012 Custom Components

Have a look at these SQLIS SSIS custom components for SSIS 2008 R2 and SSIS 2012:

Trash Destination Adapter


Data Generator Source Adapter


Regular Expression Transformation

Checksum Transformation

SSIS PowerShell Script Task

Today, I wanted to create a SSIS package which uploads/publishes a CSV-file with deltas to (IBM) MQFT by using PowerShell.
Without going into details about MQFT, I would like to make a note here on how to execute a PowerShell script within an SSIS package.

Note: I’ve chosen to execute the PowerShell within a Script Task, but you’re also able to execute a PowerShell script within an Execute Process Task. More info on that can be found here, here and here.

I found this great blog post which helped me with my first steps:

By using a Script Task, you’re able to execute a PowerShell script.
You may want to save the following code as a template:

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 Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Management.Automation;
using System.Management.Automation.Runspaces;

namespace ST_e0f329a65fa94200835440a6efd0c0c2.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


/// This method is called when this script task executes in the control flow.
/// Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
/// To open Help, press F1.

public void Main()
// Code to create a runspace and execute the PowerShell script the pipeline:
Runspace ps = RunspaceFactory.CreateRunspace();
Pipeline pipe = ps.CreatePipeline();

using (pipe)
//---->>This psline variable will contain your PowerShell script:
string psline = "\"Hello World\"";

Dts.TaskResult = (int)ScriptResults.Success;
//Dts.TaskResult = (int)ScriptResults.Failure;

The issue I faced was that our server didn’t have the Windows SDK or PowerShell SDK installed. So building the project (C# 2008 in BIDS 2008R2) returned an error:


Apparently, it is unable to include:
using System.Management.Automation
using System.Management.Automation.Runspaces

To resolve this, run the following statement within a PowerShell console to generate the required DLL-file (more info here):
Copy ([PSObject].Assembly.Location) C:\Temp

A DLL-file is generated in C:\Temp which is called ‘System.Management.Automation.dll‘.

We need to refer to this DLL-file within the C#-project (Edit the Script Task within BIDS).
To accomplish this, Right-click on References and select ‘Add Reference‘:


Open the ‘Browse‘-tab and select the DLL-file in C:\Temp:


Last thing, we need to make sure that the DLL-file is copied with the VS project. Right-click on the DLL and open the Properties. Now, select True for ‘Copy Local’:

Close the window by clicking OK and (re-)Build the project.

That’s it! Click on ‘Save All‘ and close the Visual Studio environment (script editor) and return to SSIS.
Click OK to close the Script Task and save the SSIS package. You may want to execute the Script Task to verify that everything is working properly 🙂


Enjoy using PowerShell within SSIS!

Dynamic Flat File Destination with a Variable in SSIS

A nice little code snippet for your cookbook 🙂

In case you would like to export data via SSIS to a dynamic Flat File Destination (which includes the current date and time), like for example:


You might want to use this expression for a Variable value or directly for your ConnectionString (Flat File Connection):

"C:\\Temp\\CSV-File-Example-" +
(DT_STR,4,1252) DatePart("Year",getdate()) +
Right("0" + (DT_STR,4,1252) DatePart("Month",getdate()),2) +
Right("0" + (DT_STR,4,1252) DatePart("Day",getdate()),2) +
Right("0" + (DT_STR,4,1252) DatePart("Hour",getdate()),2) +
Right("0" + (DT_STR,4,1252) DatePart("Minute",getdate()),2) + ".csv"