Archive for the ‘SSIS’ Category

The type of the value being assigned to variable differs from the current variable type.

August 12th, 2014

This post addresses the following error message.

The type of the value being assigned to variable differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

In my case, the error was because I tried to assign a varchar(max) to a string type variable in SSIS. It turns out that varchar(max) is not assignable to a string type object. The best option is to forcefully convert the value to a varchar(8000) or nvarchar(4000) before assigning it.

I previously stated that a string variable in SSIS can only contain 4000 characters, but that is not correct. A string in SSIS has an unlimited length, but the maximum length of an evaluated expression is 4000 characters, so concatenating over 4000 characters using an expression will result in an error. However, when assigning a value to a string from a resultset, the length is actually unlimited and you can assign over 4000 characters to a string using this method. This also means that you can work around the SSIS expression limitation by using a SQL box to concatenate two strings using a select ? + ?, for example.

Posted in SSIS | Comments (0)

How to differentiate in SSIS between design and execution mode

July 29th, 2014

Sometimes it is necessary to differentiate between design and execution mode. For example when you dynamically generate tables in your SSIS package. By making your package aware of the differences between design and execution mode, you can use existing tables during design mode but switch to the dynamically generated tables during execution mode. All you need to do is set the source for your table to a variable, and add the following expression to that variable.

@[System::CancelEvent] == 0 ? "Design mode" : "Execution mode"

The key is the System variable CancelEvent. This will be 0 when you are running in design mode, but will contain an integer during execution.

Posted in SSIS | Comments (0)

Using event handlers in Sequence Containers

January 7th, 2013

If you use event handlers on sequence containers, you will have noticed that the event handler will fire for each part of the sequence and then again for the sequence itself.

To prevent this from happening, you can disable the event handler until the sequence container itself fires the event. To do this, we simply compare the parent container of the source of the event to the parent container of the sequence container. When these match, the sequence container is the source of the event.

To simplify this, create a variable in the POSTEXECUTE scope of the sequence container and name it EndOfSequence. To do this, go to the OnPostExecute event handler for the Sequence Containet and create the variable. Set the value to the following expression.

@[System::SourceParentGUID] == @[System::ParentContainerGUID]

Now bring up the the properties for the container that you only wish to fire when the container's postexecute happens. Bring up the property expression list. Select the disable property and enter the following expression.

!@EndOfSequence

That's it. The sequence container in the event handler will remain disabled until the sequence container in the package actually fires the event.

Posted in SSIS | Comments (0)

SSIS – Execute SQL Task: Why you should use variables as a source type

September 27th, 2012

The Execute SQL Task in SSIS has three source types: Direct input, file connection and variable. For straightforward queries, direct input is the obvious choice. But when you need a dynamic statement, or a statement that includes parameters, the source type "variable" has a number of advantages over direct input with parameters or an expression. There are a number of advantages to using a variable over a direct input + expression or a parameterised statement.

The first and main advantage becomes clear when debugging SQL statements. When you run into an error on an Execute SQL Task, SSIS will inform you that something went wrong. Exactly what went wrong is often hard to figure out as the errors are rather generic and non-descriptive. When debugging, you cannot find what the source SQL is for an Execute SQL Task since the properties of the task aren't updated at runtime. But when you use a variable, you can place a breakpoint on the pre-execute phase and you can check the value of the variable in the locals window, or add a watch for that variable. You can then copy and paste that statement into SQL Management Studio and debug. The little wrinkles showing up will help you find the bug and squash it.

The second advantage is that it's easier and less time-consuming to see what your SQL looks like when you change conditions based on variables. For example, when variable aboolean is true, you want to insert a record and when it's not, you want to update an existing record. While you can fix this in SQL, you can also fix this with the expression on the variable. When you use an expression on your Execute SQL Task, you have to alter the value of aboolean, double click the Execute SQL Task, go to the Expressions tab, expand the Expression tree, expand the expression by clicking the [...] button and then hit "evaluate" and start peering through the very small three-line window at the bottom of the window. When using a variable, you can change the value of aboolean, expand the expression for the source variable and evaluate it. What's more is that the SQL Statement will always be ready for copying from your variable window.

A third advantage would be that you can manipulate the SQL Statement in your flow, although I would recommend against doing so as this as will make your flow harder to debug.

A final advantage, when using BIDS Helper, is that you can use the larger, more user-friendly expression window including the use of user-defined functions.

Posted in SSIS | Comments (0)

SSIS: Expression for finding the the first and last day of the month

April 18th, 2012

Have you ever needed to adjust dates in SSIS to the first or last day of the month? The following expressions will find the first or last day of the month for a given date. These expressions also account for leap years.

At the end is a short example of how to simply trim the time part or reset the time part to "00:00:00.000".

Find or adjust a date to the last day of the previous month

DATEADD("D",-(DAY(GETDATE())),GETDATE())

Find or adjust a date to the first day of the current month.
"2005-01-15" will become "2005-01-01".

DATEADD("d",- (DAY(Your_date_here)) + 1,Your_date_here)

Find or adjust a date to the last day of the month.
"2005-01-15" will become "2005-01-31". Credit also goes to Aaron for correcting me.

DATEADD("D",-(DAY(DATEADD("M",1,GETDATE()))),DATEADD("M",1,GETDATE()))

If you want to trim the time, simply convert the expression to (DT_DBDATE). If you need the time back, simple convert twice. (DT_DATE)(DT_DBDATE)(expression)

Posted in SSIS | Comments (7)

SEO Powered by Platinum SEO from Techblissonline