Articles

SSIS: Cannot open Data Flow Task: DTS.Pipeline.2 issue

: Category: SQL Server Integration Services; 09 June 2014; Hits: 20025

What?
I started getting this error:

The task with the name "Data Flow Task" and the creation name "DTS.Pipeline.2" is not registered for use on this computer

Why?
We use a combination of MS Visual Studio 2008, 2010 and 2012. VS2008 for our old SSIS packages and VS2010 for database solutions running against SQL Server 2008 R2. We can use VS2012 for both but this requires upgrading all the packages and then making them suitable for our new SQL Server 2012 instances.

How?
This applies to a workstation (hopefully you're not doing development directly on the server). I re-installed sql server setup, re-ran the repair to no avail. A clue came from the MS site for an older version of the pipeline in that you access the SQL Server Configuration Manager. This may not be the fix for you but it was for me:

Open Start > All Programs > SQL Server 2012 (or your latest)
Expand "Configuration Tools" and open "Sql Server Configuration Mnaager".
Right-click on "SQL Server Integration Services 10.0" and select "STOP".
Right-click on "SQL Server (SQLEXPRESS)" and select "STOP".

SSIS: Add Reference Microsoft.SqlServer.TxScript

: Category: SQL Server Integration Services; 03 June 2014; Hits: 21794

What?
This article is so that I don't spend so long in googling what the TxScript referenced assembly is when creating an SSIS custom component.

How?
This is simply the "Script Component" under "Extensions". No MSDN website said this... obvious to Microsoft staff so why should their documentation include it?

: Category: SQL Server Integration Services; 04 October 2013; Hits: 100135

Applies to:

Microsoft Business Intelligence Development Studio (BIDS) Visual Studio 2008
Microsoft Windows 7
Microsoft .NET Framework 3.5
Microsoft Visual C# 2008

What?
So like lots of people on the net, I've been given a datasource with names and addresses all in UPPERCASE. Initially, no one seemed bothered but now the request came through asking if this could be restored to a normal case, a mixture of mostly lowercase and some UPPERCASE.

Why?
I'm writing this article because other solutions on the net were either incomplete, did not work for me or simply did not match the requirements (do they ever?).

The script must be able to be copied into other SSIS packages.
Need the option to merely tick input column names rather than hardcode these.
"It " should be an acronym for IT, so "IT Department" and "Director of IT".
"O'REILLY" should become "O'Reilly" and not "O'reilly".
"MCDONALDS" should become "McDonalds" but do not apply for "MacDonalds" because of names like "MACK" and "MACHURA".
" and ", "la", "le", " van ", " de ", " of " and similar should remain lowercase.

How?

: Category: SQL Server Integration Services; 14 May 2013; Hits: 25837

What?
Some methods of removing trailing spaces, tabs, carriage returns and line feeds (new lines).

How?
First in Transact-SQL:

: Category: SQL Server Integration Services; 23 April 2013; Hits: 68040

What?
This is a quick article to remind me on how to skip blank rows when using a Flat file as a data source. I would receive another Microsoft error as clear as mud:

Error: The conditional operation failed.

Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "component 
"MyDerivedColumns" (4228)" failed because error code 0xC0049063 occurred, and the error
row disposition on "output column "DC_MyDate" (7349)" specifies failure on error. An 
error occurred on the specified object of the specified component.  There may be error 
messages posted before this with more information about the failure.

Why?
So the solution must be obvious to you by now. At least that's what Microsoft people think to themselves every time they see the error they programmed in.

Apparently this problem also happens when you have a Data File of varying column numbers. My solution below will also fix this.

How?

: Category: SQL Server Integration Services; 04 April 2013; Hits: 43581

What?
A quick article on how to skip rows or even specify a range to extract from the Excel file when using as the DataSource.

Why?
If you are simply using a text file as your data source, then the options in the connection manager will let you skip rows and specify column datatypes. You don't get that with Excel but you can still control the data range.

How?
My example is that I have an excel sheet where the first 10 rows are descriptions to the column, and then I have 38 columns ("A" to "AL"):

: Category: SQL Server Integration Services; 14 March 2013; Hits: 87555

What?
Please note: the following article is not a solution but a page of various methods and date conversions which I try and use depending on the situation.

This is a quick article on how I got time conversion working in an SSIS package which read from a text file. Note that the example below converts a string in European Date Format (ie. "ddmmyyyy" to "dd/mm/yyyy"). Also, my data flow imports two dates one which was imported as a string (DT_WSTR) and another as a integer (DT_R8).

Why?
It took me a long time to figure this and it was only by trawling through columns that someone mentioned that maybe all the data in that column is not consistent? This made sense as I need to parse the string to extrapolate the date, month and year.

How?

: Category: SQL Server Reporting Services; 15 April 2014; Hits: 19703

Applies to:

Microsoft SQL Server Reporting Services 2012
Microsoft Visual Studio 2012 Premium
Microsoft Windows 7 Enterprise

What?
So I have a column in an SSRS report which displays a date. Being rather pernickety, I would like a question mark to display if there is no date to populate the field.

Why?
At the moment, the expression in there is something like this:

=Format(Fields!MyCompletionTime.Value, "dd/MM/yyyy HH:mm:ss")
-- yields 11/02/2014 11:21:32

Sounds good, where's the problem?

Articles

Subcategories