Categories: SSIS

Reblog: Building the Script Component

SQLKover update: For some reason, this was my most popular blog post on the old LTD site. Over 110,000 views. People must be building them script components. 

The problem

From time to time I have to script out some logic using a Script Component in a SSIS package. Business as usual for most developers, but since I hardly have a .NET background, it’s always a very stressful experience (I may be over exaggerating a bit). Sometimes it happens I write a little piece of code with a small bug, it happens to the best of us.

public override void Input0_ProcessInputRow(InputBuffer row)
{
    byte test = -1; // do something stupid here
    // rest of the code
}

For those who haven’t noticed: you cannot store -1 in a byte. Anyway, the wonderful designer that is the Visual Studio shell fails to warn me of my little indiscretion. Leaving me with a script component that won’t compile.

Yes, that’s SSIS 2008 folks!

The matching errors aren’t helpful either.

The solution

The solution is elegant yet simple: we force the designer to tell us what’s wrong by building the code inside the script component.

This results in a blue squiggly being added to the code, together with a decent explanation.

The moral of this story: if the script component is giving you a hard time, hit that build button!


------------------------------------------------
Do you like this blog post? You can thank me by buying me a beer 🙂
Koen Verbeeck

Koen Verbeeck is a Microsoft Business Intelligence consultant at AE, helping clients to get insight in their data. Koen has a comprehensive knowledge of the SQL Server BI stack, with a particular love for Integration Services. He's also a speaker at various conferences.

Recent Posts

Free webinar – Tackling the Gaps and Islands Problem with T-SQL Window Functions

I'm hosting a free webinar at MSSQLTips.com at the 19th of December 2024, 6PM UTC.…

5 days ago

dataMinds Connect 2024 – Session Materials

The slides and scripts for my session "Tackling the Gaps & Islands Problem with T-SQL…

4 weeks ago

Connect to Power BI as a Guest User in another Tenant

Sometimes your Microsoft Entra ID account (formerly known as Azure Active Directory) is added as…

2 months ago

How to use a Script Activity in ADF as a Lookup

In Azure Data Factory (ADF, but also Synapse Pipelines and Fabric Pipelines), you have a…

4 months ago

Database Build Error – Incorrect syntax near DISTINCT

I wrote a piece of SQL that had some new T-SQL syntax in it: IS…

4 months ago

Speaking at dataMinds Connect 2024

I'm very excited to announce I've been selected as a speaker for dataMinds Connect 2024,…

5 months ago