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: http://bturnip.com/nerdalerts/archives/146








Advertisements

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:

ssis-2012-data-conversion-data-value-cannot-be-converted-for-reasons

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:
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/7307def1-79d3-4203-bec0-ba8d7311c30c/ssis-convertion-function-from-integer-to-string
http://stackoverflow.com/questions/11990178/date-casting-issue-in-data-conversion-task

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:

(DT_UI4)LTRIM(RTRIM())
(DT_NUMERIC,19,4)REPLACE(,".",",")








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’:

Delete-old-files-ssis-process-task-powershell-sequence-container

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):
Delete-old-files-ssis-process-task-powershell-editor

Now go to ‘Expressions‘ and make sure that the ‘Arguments‘-property will be given the value of @[User::PowerShellProcessedArguments]:
Delete-old-files-ssis-process-task-powershell-expressions

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:
https://clinthuijbers.wordpress.com/2011/08/04/ssis-sqlcmd-delete-old-txt-files/

Or use a single PowerShell script to delete all files in specific folders during maintenance?
https://clinthuijbers.wordpress.com/2014/07/09/powershell-delete-old-files-based-on-a-given-retention-period-days-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
http://www.sqlis.com/post/Trash-Destination-Adapter.aspx

TrashDestination

Data Generator Source Adapter
http://www.sqlis.com/sqlis/post/Data-Generator-Source-Adapter.aspx

DataGeneratorEditor-SourceAdapter

Regular Expression Transformation
http://www.sqlis.com/sqlis/post/Regular-Expression-Transformation.aspx

Checksum Transformation
http://www.sqlis.com/sqlis/post/Checksum-Transformation.aspx








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:
http://www.maxtblog.com/2012/03/orlando-code-camp-2012-integrating-powershell-into-ssis-script-task-session/

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
};
#endregion

///

/// 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();
ps.Open();

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

pipe.Commands.AddScript(psline);
pipe.Invoke();
}
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:

Visual-Studio-Runspace-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‘:

VS-Add-Reference


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

Browse-for-the-DLL-file


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’:
DLL-properties-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 🙂

SSIS-2008R2-ScriptTask-Green

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:

C:\Temp\CSV-File-Example-201309061121.csv

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"

SSIS-Dynamic-Flat-File-Destination-FileName