What?
A quick article on an alternative to multiple lookups in SSIS. For any SSIS developer trying to create packages that need to decode a number of values into their full name/description (eg. "M" to "Male", "Prof" to "Professor").

Why?
Our extract phase in the SSIS package takes data from a source database and puts it in staging in the exact form it came through. Our transform package will convert the data and output it to the target system. For our package to decode what "M" means, it needs to look this up in a table on the original server. We had around 12 lookups to do, so you could do this:
But we didn't want to do this.

How?

What?
This is an article to demonstrate a quick step-by-step on having an SSIS package loop through a directory/folder of files in order to populate a database table. We could add each file as a separate connection manager but this is inefficient and not versatile enough to accommodate files that get added later.

Why?
I am creating an extract SSIS package intended to take a text file as its source and to populate a database table with this data. Note that this only works if all the text files to be used as source data have the same number of columns and where the column widths match.

How?
I've adapted my real working product with an example. As my work was for a Personnel/HR project, and data confidentiality is somewhat important, some of the images will be censored or data changed in the example below.

What?
I started getting this error:
copyraw
The task with the name "Data Flow Task" and the creation name "DTS.Pipeline.2" is not registered for use on this computer
  1.  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:
  1. Open Start > All Programs > SQL Server 2012 (or your latest)
  2. Expand "Configuration Tools" and open "Sql Server Configuration Mnaager".
  3. Right-click on "SQL Server Integration Services 10.0" and select "STOP".
  4. Right-click on "SQL Server (SQLEXPRESS)" and select "STOP".


Category: SQL Server Integration Services :: Article: 566

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?).
  1. The script must be able to be copied into other SSIS packages.
  2. Need the option to merely tick input column names rather than hardcode these.
  3. "It " should be an acronym for IT, so "IT Department" and "Director of IT".
  4. "O'REILLY" should become "O'Reilly" and not "O'reilly".
  5. "MCDONALDS" should become "McDonalds" but do not apply for "MacDonalds" because of names like "MACK" and "MACHURA".
  6. " and ", "la", "le", " van ", " de ", " of " and similar should remain lowercase.

How?

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:
copyraw
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.
  1.  Error: The conditional operation failed. 
  2.   
  3.  Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "component 
  4.  "MyDerivedColumns" (4228)" failed because error code 0xC0049063 occurred, and the error 
  5.  row disposition on "output column "DC_MyDate(7349)" specifies failure on error. An 
  6.  error occurred on the specified object of the specified component.  There may be error 
  7.  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 :: Article: 505

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"):

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: The information on this website is provided without warranty and any content is merely the opinion of the author. Please try to test in development environments prior to adapting them to your production environments. The articles are written in good faith and, at the time of print, are working examples used in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

Related Articles

Joes Revolver Map

Joes Word Cloud

following   table   database   value   google   list   used   code   program   select   data   work   find   user   script   name   function   parameter   version   could   display   zoho   search   server   note   files   solution   website   same   site   first   page   windows   system   error   would   mysql   uploaded   joomla   source   report   license   added   time   date   need   where   file   form   using   JoelLipman.Com

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin - Valid till 8 May 2022 3QnhmaBX7LQSRsC9hh6Je9rGQKEGNQNfPb
© 2021 Joel Lipman .com. All Rights Reserved.