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

SSIS not working with temp tables

Received this fine error message during ETL?:
The metadata could not be determined because statement ” in procedure ” uses a temp table.

If you are working on SSIS 2012, then it uses system stored procedure sp_describe_first_result_set to fetch the metadata of the tables and it does not support temporary tables. But you can go for other options like table variables and CTEs which are going to work fine.
In my case, since I’m a bit stubborn I looked for another solution and found a few:



Solution 1:
An option is to use a SET FMTONLY ON command in front of your actual query to send a fake “First result set” to SSIS with your correct column structure. So you can do something like:

SET FMTONLY ON
select 0 as a, 1 as b, 'test' as C, GETDATE() as D
SET FMTONLY OFF

select a, b, c, d from ##TempTable

When SSIS runs sp_describe_first_result_set, it will return the metadata and column names of your FMTONLY command, and won’t complain about not being able to determine the metadata of your temp table because it won’t even try.



Solution 2:
And the best solution is this one in my opinion, simply provide SSIS the metadata up front like:

EXEC [dbo].[usp_SSIS_DataSet] @Date = '2012-01-01'
WITH RESULT SETS
(
(
[DateID] [int] NOT NULL,
[Sales] [decimal](16, 4) NOT NULL
)
)

NOTE: Don’t know why, but syntax wise it needs the round brackets twice.
You can use this in your OLE DB Source component as a query (or in a variable).








Data driven subscriptions cannot be created because the credentials used to run the report are not stored,the report is using user-defined parameters values, or if a linked report, the link is no longer valid.

Well SSRS is stil bugging you huh?

ssrs-data-driven-subscription-cannot-be-created-user-defined-parameter-values

There are a few things you may need to check:

1) Make sure that you’ve stored the credentials of the data source IN the report

2) Since the data-driven subscription is running without an actual user-context, the use of User!UserID is not allowed.

Start by adding this custom code to your report (under ‘Report Properties‘ -> ‘Code‘):

Public Function UserName()
Try
Return Report.User!UserID
Catch
Return "System"
End Try
End Function

Now you need the find: User!UserID

And replace it by: code.UserName()

in the Code-view of the report.








SSISDB 2012 – Catalog Indexing Recommendations

Hi there!

Noticed that you’re SSISDB reports are starting to get really slow and thus annoying?
Well…it’s time to fix that! Found this post by Phil Brammer that fixes that by creating new indexes:
http://www.ssistalk.com/2013/01/31/ssis-2012-catalog-indexing-recommendations/



ssisdb-reports-slow


More info by Microsoft on this issue:
http://support.microsoft.com/kb/2829948

Also…on my server the total execution time of the SQL Agent Job ‘[SSIS Server Maintenance Job]‘ (by ##MS_SSISServerCleanupJobLogin##) went from 125 to 22 minutes! Make sure to reorganize/rebuild the newly created indexes.

Data Compression
Don’t forget to apply data compression (which will save you storage and decrease your I/O thus increase performance)!
https://clinthuijbers.wordpress.com/2014/08/01/ssisdb-2012-data-compression/








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