SSIS Error DTS_E_OLEDBERROR with code: 0x80040E55

SQL Agent and SSIS packages have multiple failure codes but DTS_E_OLEDBERROR or 0x80040E55 don’t tell you much about the cause.

One of my jobs failed sporadically with the following error.

Message
Code: 0xC0202009
Source: [29]
Description: SSIS Error Code DTS_E_OLEDBERROR.
An OLE DB error has occurred.
Error code: 0x80040E55.
An OLE DB record is available.
Source: "Microsoft SQL Server Native Client 10.0"
Hresult: 0x80040E55
Description: "Column does not exist.".
End Error

Error: 2016-11-30 11:20:03.11
Code: 0xC0047038
Source: SSIS.Pipeline
Description: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.
The PrimeOutput method on returned error code 0xC0202009.
The component returned a failure code when 
the pipeline engine called PrimeOutput().
The meaning of the failure code is defined 
by the component, but the error is fatal and 
the pipeline stopped executing.
There may be error messages posted before this
with more information about the failure.
End Error

The error message “Column does not exist.” led me to think that the stored procedure or one of the tables have been altered.

A quick look at the created date and modified date of the stored procedure and the tables confirmed that there were no schema changes.

So why the missing column error?

I executed the stored procedure manually and that revealed the issue.

There was TRY CATCH block in the procedure. It didn’t re-raise the error but returned it with a SELECT statement.

For example –

BEGIN TRY
  -- some sample sql
  SELECT
    o.OrderNumber,
    o.OrderLineNumber
  FROM
    dbo.Order as o
END TRY
BEGIN CATCH
  SELECT
     ERROR_NUMBER() AS ErrorNumber
    ,ERROR_STATE() AS ErrorState
    ,ERROR_PROCEDURE() AS ErrorProcedure
    ,ERROR_LINE() AS ErrorLine
    ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

So when there was any error, the error message was returned as a SELECT output.

The column names of this output were different from the original columns. This caused the SSIS package to fail.

Take away –
Use RAISERROR or THROW in TSQL to return the errors to the calling application. Using a SELECT for this purpose can confuse the calling application to consider that as a data set.

SSIS Naming Convention – Updated for SSIS 2016

I use an SSIS naming convention that is based on assigning a prefix to each type of component. My first version of SSIS naming convention list was based on SSIS 2008 R2. Since then, Microsoft has introduced new connections, tasks, sources and destinations that are mostly related to Azure, Hadoop, ODATA and CDC.

With the launch of SQL Server 2016, I have updated my list to include all those new components and assigned prefixes to them. There are minor changes to some existing prefixes too e.g. RBLDIDXT is now REBLDIDXT and few others like this.

Why Should You Use a SSIS Naming Convention

A naming convention may not appear to be a big deal at the time of SSIS development because you have a GUI development environment that makes it very easy to identify the components. But during the support and maintenance phase, when trying to read the execution logs or error messages, bad naming choices make it difficult to identify the source of errors quickly. Having a consistent naming convention across the enterprise makes a lot of difference in supporting the packages.

Download

Feel free to reuse the conventions in the list or use it as a template to define your own. I’d love to hear your thoughts and suggestions in the comments, Twitter or Facebook.

You can download the complete list at the TechNet Gallery link below –

=> Download SSIS Naming Convention (Updated for SSIS 2016) <=

Example screenshot –

SSIS Naming Convention
Screenshot of the SSIS Naming Convention Excel Workbook

Gotcha – SSIS ImportExport Wizard Can Kill Your Diagrams

Some things are meant to be learnt the hard way. And that is how I learnt about today’s gotcha.

Overview of [sysdiagrams]

I have been working on an ERD (Entity Relationship Diagram) recently and used the Database Diagram feature in SSMS for this purpose. When you try to create a diagram for the first time in a database, a message box asks you if you would like to create diagramming objects.

Confirmation dialog
Image 1 (Click to enlarge)

On clicking Yes, a system table by the name of [sysdiagrams] is created in the same database that you are creating the diagram in. The diagrams are stored in this table. SSMS shows the diagram in the Database Diagrams node of the Object Explorer tree view.

Diagram and its table
Image 2 (Click to enlarge)

The Scenario

I was creating my diagram in a test environment. At some point I had to refresh all data from the production environment. The easiest way for me to do a full refresh is to use the Import and Export Wizard. The wizard can be launched either via SSMS context menu or in the Business Intelligence Developer Studio as an SSIS project. As usual in the case of quick data refreshes, I selected all tables using the top-left Source checkbox in the wizard, and chose the options to delete and reinsert all rows with identity values.

Select all tables and reinsert rows
Image 3 (Click to enlarge)

When the wizard ran successfully, my database diagram at the destination test system was missing!

Gotcha

Upon some research and trials I found that that if the diagramming capabilities in the source and destination servers are enabled, the wizard includes the [sysdiagrams] table automatically in the list of tables to refresh. As you can see, there are no other system tables in the wizard except the [sysdiagrams] table so it is easy to miss it in a long list.

sysdiagrams is included
Image 4 (Click to enlarge)

So in my case, all data in the destination [sysdiagrams] table was deleted. There were no diagrams at the source so nothing was imported for this table. This outcome would have been the same with the drop and recreate option too because the destination table would have been recreated.

Conclusion

One needs to be careful while using the Import and Export Wizard. Uncheck this table in the selection list to preserve the diagrams at destination.

What is the RetainSameConnection Property of OLEDB Connection in SSIS?

I recently wrote about How to Use Temp Table in SSIS. One of the requirements to successfully reuse a temporary table across multiple tasks in SSIS is to set the RetainSameConnection property of the OLEDB Connection to TRUE. In this post, I will discuss the property and also use a Profiler Trace to find out its behavior.

The RetainSameConnection Property

RetainSameConnection is a property of an OLEDB Connection Manager. The default value of this property is FALSE. This default value makes SSIS execution engine open a new OLEDB connection for each task that uses the connection and closes that connection when the task is complete. I believe the idea behind this is to not block a connection to a server unnecessarily and release it until it is needed again. And it makes sense too because some packages can run for an extended duration and may not need to be connected to an OLEDB server all the time. For example, an OLEDB in not required to be open while parsing text files, sending emails, ETL operations not involving the OLEDB server in question etc. Releasing connections unless really required can be certainly helpful on busy servers because SQL Server needs some memory for each open connection.

On the other hand, some scenarios require a persistent connection e.g. temporary table reuse across multiple tasks. We can set the property value to TRUE and then it will open just one OLEDB connection with a server and keep it alive until the end of the package execution. The property can be set via the Properties window for the OLEDB Connection Manager.

aalamrangi.wordpress.com-SSISRetainSameConnection2

The Temporary Table Scenario

Local temporary tables (with a # in front of their name) in SQL Server are scoped to a session. SQL Server drops them when the session is closed. This means, local temporary tables created in one session are not available in another session. In SSIS, with the RetainSameConnection set to FALSE (the default), a new session is opened for each task. Therefore, temporary tables created by a task are not available to another task.

Demo

I have a demo package with two Execute SQL Tasks and one OLEDB Connection Manager. The Execute SQL Tasks have a simple SELECT statement and they both use the same connection manager.

aalamrangi.wordpress.com-SSISRetainSameConnection1

I have a Profiler Trace to monitor the number of connections created by the SSIS package.

The first execution of the package is with the RetainSameConnection set to the default value of FALSE. The trace captures two pairs of login/logout events, one for each task. The second execution is with the property value set to TRUE. This time, the trace captures only one pair of login/logout events.

aalamrangi.wordpress.com-SSISRetainSameConnection3

Conclusion

In most cases, the default value of RetainSameConnection=FALSE will be suitable. A developer should make a decision to enable it when the package tasks really need a persistent connection. In addition to the temporary table reuse, a TRUE value for this property can also be useful in managing transactions and reducing the number of recurring connection requests to a server.

How to Have Standard Event Logging in SSIS and Avoid Traps

Event logging in SSIS gives a lot of valuable information about the run-time behavior and execution status of the SSIS package. Having a common minimum number of events is good for consistency in reports and general analysis. Lets say your team wants to ensure that all packages must log at least OnError, OnWarning, OnPreExecute and OnPostExecute events. If the package has a DataFlowTask then the BufferSizeTuning should also be logged. The developer can include more events to log as required but these mentioned previously are the minimum that must be included.

You can create pre-deployment checklists or documentation to ensure this minimum logging. Probably you already have that. As the number of developers and/or packages increase, it becomes difficult to ensure consistency in anything, not just for event logging. Therefore documentation, checklists and training are helpful to an extent only. Your requirement could be more complex than my five-event example above and thus more prone to to oversight.

The easiest way to ensure a common logging implementation would be a logging template that has all the minimum event pre-selected. The developer should just need to apply that to the package.

Event Logging in SSIS with a Template

I assume that you are already familiar with the concepts of event logging in SSIS so this post is not going to be a beginners level introduction to event logging. I will rather discuss options to have a minimum standard event logging across SSIS packages and teams with minimal effort. I’ll also mention some traps to avoid.

I am using a demo SSIS package with two Data Flow Tasks and an Execute SQL Task. I have enabled event logging in SSIS for the first few events at the package level for the sake of demonstration. The logging configuration options for the package node (which is the top node) are shown in the first image.

Image 1 - Logging configuration window for the package node
Image 1 – Event logging configuration window for the package node

The logging options at the child container node Data Flow Task 1 are shown in the second image. The configuration for other Data Flow and the Execute SQL Task look the same.

Image 2 - Logging configuration window at the child container node
Image 2 – Event logging configuration window at the child container node

The check marks for the tasks are grayed out which means they are inheriting the logging options from their parent, i.e. the package. To disable logging for a task, remove its check mark in the left tree view window.

TIP: Logging can also be disabled by going to the Control Flow canvas and changing the LoggingMode property of the task to Disabled.

The Trick

Now look at the bottom of the images again. Notice the Load… and Save… buttons? They do exactly what they say. You can set your logging options and save them as an XML template. Later, this XML template can be loaded into other packages to enable the same logging options.

The XML template file has nodes for each event. For example, the logging options for OnError event are saved like this –


-<EventsFilter Name="OnError">

-<Filter>

<Computer>true</Computer>

<Operator>true</Operator>

<SourceName>true</SourceName>

<SourceID>true</SourceID>
<ExecutionID>true</ExecutionID>

<MessageText>true</MessageText>

<DataBytes>true</DataBytes>

</Filter>

</EventsFilter>

Notice that the XML just mentions the event name, not the name of any task. This means that when the template file is loaded, this logging option will be set for any task where the event is applicable. More on this later.

The Traps

The OnError event is a generic event applicable to all tasks. Lets talk about events that are specific to tasks. For example, the BufferSizeTuning event is applicable just to the Data Flow Tasks, not Execute SQL Tasks.

When I proceed to set logging for BufferSizeTuning event, I have to set it individually in the Data Flow Task tree node. Notice the message at the bottom of the second image that says –

To enable unique logging options for this container, enable logging for it in the tree view.

This message is important in the context of saving and loading a template file too. When I save a template file, the logging options of just that tree view node are saved. For example, the BufferSizeTuning event will be saved in the template only if I am at the Data Flow task in the tree view. It will not be saved if I am at the Package or the Execute SQL task in the tree view.

The reverse is also true. When I load a template, its logging options are applied to just that node which I select in the tree view. For example, if I load a template at the Data Flow Task 1, the options will not be applied to the Data Flow Task 2 or the Execute SQL Task. If the template has an event that is not applicable to the task then that event’s settings will be ignored. For example, the BufferSizeTuning event logging option is meant for Data Flow Tasks so it will be ignored for the Execute SQL Task. The fact that non-relevant options are ignored can be helpful for us to consolidate all logging options in a single template file.

Conclusion

A package level Save and Load of a logging template is straight forward. But if you need to have logging for events that are specific to a task type, then consider creating a logging template for each type of task. Also, if your logging configuration requires anything else than the package level settings, remember to load the template for each task in the tree view.

Number of Template Files How Pros and Cons
Individual File per Task Create one template file for each type of task. The file will have events applicable to that task. Pros –
Easier to know what type of tasks have a template and which ones do not.Cons –
More files to manage.
Single File for All Tasks Create a template file for each task. Then copy all event options in a single XML file. Pros –
One file is easier to manage.Cons –
Not obvious which tasks are include. Need to put in comments in the XML file.

How to Use Temp Table in SSIS

Using a temporary table in SSIS, especially in a Data Flow Task, could be challenging. SSIS tries to validate tables and their column metadata at design time. As the Temp table does not exist at the design time, SSIS cannot validate its metadata and throws an error. I will present a pretty straight forward solution here to trick SSIS into believing that the Temp table actually exists and proceed as normal.

Temporary Table Reference Across Two Tasks

To begin with, I will demonstrate that a Temp table can be referenced across two tasks. Add two Execute SQL Tasks in your package. Both of them use the same OLEDB connection. The first task creates a Local Temp table and inserts one row into it. The second task tries to insert one more row in the same table.

www.sqlerudition.com - Temp Table In SSIS - 1

TSQL script in the first task –

/* Create a LOCAL temp table*/
IF
(
Object_id('[tempdb].[dbo].[#LocalTable]')
IS NOT NULL
)
DROP TABLE
[tempdb].[dbo].[#LocalTable]
GO

CREATE TABLE [#LocalTable]
(
id INT IDENTITY,
label VARCHAR(128)
);
GO

/* Insert one row */
INSERT INTO [#LocalTable]
(label)
VALUES ('First row');
GO

TSQL script in the second task –

/* Insert one row */
INSERT INTO [#LocalTable]
(label)
VALUES ('Second row');
GO

Invalid Object Name Error

When executed, the SSIS package gives the following error because the second task cannot see the Temp table created in the first task. Local Temp tables are specific to a connection. When SSIS switches from one task to another, it resets the connection so the Local Temp table is also dropped.

Error: 0xC002F210 at ESQLT-InsertSecondRow
, Execute SQL Task: Executing the query
&quot;/* Insert second row */
INSERT INTO [#LocalTable]...&quot;
failed with the following error:
&quot;Invalid object name '#LocalTable'.&quot;.
Possible failure reasons:
Problems with the query
, &quot;ResultSet&quot; property not set correctly
, parameters not set correctly
, or connection not established correctly.
Task failed: ESQLT-InsertSecondRow
Warning: 0x80019002 at SSIS-DemoTempTable:
SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.
The Execution method succeeded, but the number
of errors raised (1) reached the maximum
allowed (1); resulting in failure. This occurs
when the number of errors reaches the number
specified in MaximumErrorCount. Change the
MaximumErrorCount or fix the errors.
SSIS package &quot;SSIS-DemoTempTable.dtsx&quot;
finished: Failure.

The Fix

The fix is pretty simple. Right-click on the OLEDB connection manager and go to the Properties window. Change the RetainSameConnection property to True. This will force the connection manager to keep the same connection open. I have another post with more details about the RetainSameConnection property of OLEDB connection managers.

www.sqlerudition.com - Temp Table In SSIS - 2

This fixes the error and the package executes successfully.

Temporary Table in a Data Flow Task

Now let me demonstrate that a Temp table can be used in a Data Flow Task.

Add a Data Flow Task to the package.

www.sqlerudition.com - Temp Table In SSIS - 3

In the Data Flow task, add an OLEDB source that will use the same OLEDB connection as used by the Execute SQL Tasks earlier. In the OLEDB Source Editor window, there is no way to find our Local Temp table in the list so close the Editor window.

The Development Workaround

Open a SSMS query window and connect to the SQL Server used in the OLEDB connection. Now create a Global Temp table with the same column definition. You can just copy the CREATE TABLE script and add one more # symbol to the table name.

The Global Temp table is just a development workaround for the restriction imposed by the volatility of the Local Temp table. You can even use an actual physical table instead of the Global Temp table. We will switch to the Local Temp table in the end of this post and then the Global Temp table (or the actual physical table) can be dropped.

Script –

/* Create a GLOBAL temp table
with the same schema as the
earlier LOCAL temp table.
Note the ## in the table name */

CREATE TABLE [##LocalTable]
(
id INT IDENTITY,
label VARCHAR(128)
);
GO

Come back to the SSIS Control Flow. Create a new package scoped variable of String data type. Give it the name TableName and put the Global Temp table name as its value.

www.sqlerudition.com - Temp Table In SSIS - 4

Go to the Data Flow > OLEDB Source and double click to open the OLEDB Source Editor window. Choose the Data Access Mode as Table name or view name variable. In the Variable name drop-down, choose the new variable that we created. This means that now the OLEDB Source is going to use the GLOBAL Temp table. Of course, it is not the same as the LOCAL Temp table but we will get to that in a minute. Click on the Columns tab to load the table metadata. Then click on OK to close the OLEDB Source Editor.

www.sqlerudition.com - Temp Table In SSIS - 5

Now add a Flat File Destination and configure its properties. I’ll not go into those details. Please let me know in the comments or via email if you need information on how to configure a Flat File Destination.

The final Data Flow Task looks like this.

www.sqlerudition.com - Temp Table In SSIS - 6

You can execute the package now to verify if it runs successfully. Although it will run fine, the flat file will not have rows because the source of the data is the Global Temp table, not the Local Temp table populated by the Execute SQL Tasks.

A Global Temp table (or a physical table) is common to all users so it could cause issues in multi-user environments. Local Temp tables are specific to a connection, hence more scalable. All that is needed now is to remove one # in the variable value and the OLEDB Source will point to the correct Local Temp table. To clean up, you can drop the Global Temp table.

www.sqlerudition.com - Temp Table In SSIS - 7

The flat file will have the rows inserted by the Execute SQL Tasks.

www.sqlerudition.com - Temp Table In SSIS - 8

Avoid Validation

Subsequent runs of the package will show validation errors because the Local Temp table is not available when the package starts. To go around this, you can set the DelayValidation property of the package to TRUE. As the package is the parent container for all other tasks, this property will be applied to all tasks in the package. If you do not wish to disable validation for all tasks, then you can set it for individual tasks, i.e. the first Execute SQL Task and the Data Flow Task. Again, the Data Flow Task may contain multiple sources, destinations and transformations and you may not want to disable validation for all of them. In that case you can be more granular and set just the ValidateExternalMetadata property of the OLEDB Source to FALSE.

Further Reading:

How to Concatenate SSIS Variables with a For Loop Container

Variable value concatenation in SSIS is usually done with a Script Task. I am going to demonstrate variable concatenation with a For Loop Container in this post while working on a real world scenario of sending all files from a folder as email attachments.

Drag a ForEach Loop Container in the Control Flow. Then drag a For Loop Container within the ForEach Loop Container. There are no other tasks in these two containers. Finally add a Send Mail Task to the Control Flow and join it to the ForEach Loop Container with a precedence constraint.

The final package looks like this.

Concat1-Package

ForEach Loop Container is used to iterate through all files in a folder and return their full paths. For Loop Container will concatenate the file paths in a variable. Send Mail Task will use the concatenated file paths as an attachment list in an email.

Declare two variables of string type at the package level. Variable @[User::FELC_Iterate] will hold the single file path returned by the ForEach Loop. Variable @[User::FLC_Concat] will hold the concatenated list of file paths passed from the other variable.

Concat2-Variables

Double click the ForEach Loop Container to open the properties editor. In the Collections tab, give the folder path and select the radio button for fully qualified file name. You can also set the filter for the file types (e.g. *.txt, *.xls etc.). I let it be at its default of all files (i.e. *.*).

Concat3-FELCEditorCollectionsTab

In the Variable Mappings tab, set the variable @[User::FELC_Iterate] to index zero so that it can hold the file paths returned by the ForEach Loop Container. Click OK to close the editor.

Concat4-FELCEditorVariableMappingsTab

Now double click the For Loop Container to open its properties editor. Set the InitExpression to –

@[User::FLC_Concat] = @[User::FLC_Concat] + @[User::FELC_Iterate] + "|"

This expression will append the @[User::FELC_Iterate] value coming from the outer ForEach Loop along with a pipe symbol to the @[User::FLC_Concat] in each iteration. The pipe symbol is the separator required by the Send Mail Task if there are multiple files as attachments.

One iteration of the For Loop will suffice our requirement to append the variable value. So set EvalExpression to 1<0, or any improbable boolean condition. The For Loop executes once and then evaluates this condition to see if it can go for another execution. The improbable condition will exit the loop on second iteration because the condition will evaluate to FALSE. Click OK to close the editor.

Concat5-ForLoopEditor

Double click the Send Mail Task to open its editor. Setup the SMTP connection and other fields in the Mail tab. In the Expressions tab, add an expression for FileAttachments to use the @[User::FLC_Concat] variable. Click OK to close the editor.

Concat6-SendMailTaskEditorExpressions

Execute the package and the file paths are concatenated as shown on the Locals window. These files would be emailed as attachments.

Concat7-LocalsWindowVariableValues

How to Log SSIS Variable Values During Execution in the Event Log

Log entries created during the execution of an SSIS package help in monitoring, analysis, and issue resolution. In addition to logging events, you might want to capture the run-time values of variables in the package. I couldn’t find a native feature to log SSIS variable values so the following post shows how I did it.

The Setup

For this demo, I’ll do an INSERT operation on a table named TableA and use variables to save the before and after INSERT row count. The TableA is always blank at the beginning of the package i.e. the row count is zero. The Execute SQL Task named ESQLT-InsertRowsInTableA inserts 502 rows in TableA. The simple package looks like the image below. If you are wondering about the funny prefixes in the object names then you can read about my naming conventions mentioned in my other blog post.

Log SSIS Variable Value 1 - Package

I have two integer variables named rcTableA_PreRefresh and rcTableA_PostRefresh scoped at the package level.

Log SSIS Variable Value 2 - Variables Init Zero

Enable the Event

A log entry is generated when an event is triggered. Each object in SSIS has its own events that can be logged. I’ll use the event called OnVariableValueChanged, which as the name denotes, is triggered whenever the value of the variable changes. This event is disabled by default. To enable it, go to the Properties window of the variable and make the RaiseChangedEvent property to True. It must be enabled for each variable individually.

Log SSIS Variable Value 3 - Variable Properties

Next, I include the OnVariableValueChanged event in the logging configuration. It has to be included at the container level where the variables are scoped to. In my case, at the package level. I’m using the SSIS Log Provider for SQL Server in this package.

Log SSIS Variable Value 44 - Configure Log

Then I execute the package and look at the [dbo].[sysssislog] table for the log entries.

Log SSIS Variable Value 5 - Log Entries 1

There are some log entries but something is missing. I see the OnVariableValueChanged event logged for the Post Refresh variable but not the Pre Refresh variable.

The reason is that the initial value of the variable is set to zero in the package. The row count of a brand new empty table is also zero. So there was no change in variable value. The OnVariableValueChanged event fires only when the value actually changes! Overwriting with the same value doesn’t fulfill this condition.

To resolve that, I change the initial values in the package to -1. Now even if the row count turns out to be zero, the variable value will still change from -1 to zero. The COUNT function can’t count below zero, can it?

Log SSIS Variable Value 6 - Variables Init Minus One

I run the package again and check out the [dbo].[sysssislog] table.

Log SSIS Variable Value 7 - Log Entries 2

Things are better. The OnVariableValueChanged event for both the variables show up in the log. But the variable values are still not there.

Log SSIS Variable

The reason for missing values is that the event logging just captures the fact that the variable value changed. It doesn’t capture the value by itself. I’ll make an addition to the event handler to get the variable values too. I add an Execute SQL Task to the package level event handler for OnVariableValueChanged event.

Log SSIS Variable Value 8 - Event Handler

The General tab of the Execute SQL Task has the following properties and SQL command –

Log SSIS Variable Value 8 - Event Handler - ESQLT 1

INSERT INTO [dbo].[sysssislog]
([event]
,[computer]
,[operator]
,[ source]
,[sourceid]
,[executionid]
,[starttime]
,[endtime]
,[datacode]
,[databytes]
,[message])
VALUES
('*SSIS-OnVariableValueChanged' -- Custom event name
,? -- param 0
,? -- param 1
,? -- param 2
,? -- param 3
,? -- param 4
,? -- param 5
,? -- param 6
,0 -- Zero
,'' -- Blank string
,?) -- param 7

Notice that I precede the custom event name with an asterisk to differentiate it from the log entries created by the system.

The Parameter Mapping tab of the Execute SQL Task has the following properties –

Log SSIS Variable Value 9 - Event Handler - ESQLT 2

Pay attention to the System::VariableValue (last variable) in this screen. Its data type is LONG, which is appropriate for the numeric row counts in my example. You may have different data types for your variables. Do adjust the data type and length appropriately. Using a wrong type could lead to no value logged at all.

I run the package again and this time, the variable values are also logged in the table.

Log SSIS Variable Value 10 - Log Entries 2

Summary

A single event handler will take care of all variables in that scope. In my case, two package scoped variables are handled by a single package level event handler.

The variable value really has to change to fire the event.

The OnVariableValueChanged event is triggered for the container that has the variable in its scope. The container triggering the change in variable value could be different than the container that has the variable in its scope. In my demo, the variables were scoped to the package. Even though an Execute SQL Task is changing the variable values, I still put the event handler at the package level. As another example, assume there is a variable declared in the scope of a ForEachLoop container and there is a Script Task in the ForEachLoop. The Script Task changes the variable value. The OnVariableValueChanged event will be triggered for the ForEachLoop task.

I have used the default [sysssislog] logging table to log my variable values. You can easily use a different custom table by changing the OLEDB connection and making appropriate changes to the INSERT statement.

Subscribe to Blog via Email

If you liked this post then please subscribe to get new post notifications in email.

How to Use SSIS to Export Clickable URLs to Excel

When SSIS is used to export data to a MS Excel workbook, Excel tries to guess the data type from the first row of data. (Tip: This can be overridden by the IMEX=n parameter in the Excel connection string, where n is the number of rows you want it to scan). As it happens, Excel can not always win the guessing game (or maybe it just plays it safe) and treats data as plain text. Sometimes even numbers and URLs are treated as text and an apostrophe is appended at the beginning of the data value.

If you have a requirement to export hot (i.e. clickable) URLs to Excel, you can use the workaround that I am going to demonstrate below.

We will use the following SQL table. The sample data is intentionally in bad format. We will fix it with a query –

create table URLDemo
(
 id int identity
,url varchar(100)
,friendlyname varchar(100)
);

insert into URLDemo
(url, friendlyname)
values
 ('www.google.com', 'Google')
,('http://www.yahoo.com', 'Yahoo')
,('bing.com', NULL);
-- Data in table ------------------------
select id, url, friendlyname 
from URLDemo

-- Clean data with query ----------------
select
id,
convert(varchar(100),
case
when left(url, 4) = 'www.' then 'http://'+url
when left(url, 11) &amp;amp;lt;&amp;amp;gt; 'http://www.' then 'http://www.'+url
when left(url, 7) &amp;amp;lt;&amp;amp;gt; 'http://' then 'http://'+url
else url
end
) as url,
convert(varchar(100),
case
when isnull(friendlyname, '') = '' then url
else friendlyname
end
) as friendlyname
from URLDemo

ExcelURLDemo1

The Clean Data query is an attempt to add any missing http://www. to the URL and also show a friendly name if missing. These will be useful later for the Excel HYPERLINK formula.

The problem –

We can export this data to Excel, even try to encode the Excel HYPERLINK formula as a derived column, the url and the formula will show up just as plain text, that is they will not be clickable.

The workaround –

Create an Excel Workbook that will be the template for data export. Type in the column names [id], [url] and [friendlyname] for column A, B and C respectively. Put the following Excel formula in the column D :-

=IF( AND(B1<>"", C1<>""), HYPERLINK(B1,C1), "")

Drag this formula cell to the number of rows as appropriate for your expected data volume. I did it for the first 20 rows. The idea here is to populate the [url] and [friendlyname] columns via SSIS and let the formula create a clickable link for us.

ExcelURLDemo2

Then select cells A1 to C1 and give this range a name, e.g. MyDataRange.

ExcelURLDemo3

Now create an SSIS package. Add a Data Flow Task and double-click to open its designer. Add a OLEDB Source in the Data Flow designer. The query in this editor is the Clean Data query above.

ExcelURLDemo4

Add a Data Conversion Task. It will be used to convert non-unicode (VARCHAR) columns namely [url] and [friendlyname] to unicode. Excel likes to have character data in unicode.

ExcelURLDemo5

Add an Excel Destination. It will use the data range name. Verify the column mappings.

ExcelURLDemo6

Gyaan: Excel tries to identify the last “dirty” row in the worksheet and puts new data from SSIS in the next row. In my case it will be row 21 because I dragged the formula till row 20. Even if the cells once used are emptied by deleting the values/formulas, they are still considered “dirty”. The only way to remove the “dirty” flag from a cell is to delete the whole row! If the package will put data in row 21 and beyond, we miss out on the formulas that we put in the first 20 rows. Using the range tells Excel to look for dirty cells only within the range, thus allowing us to use the formulas in the adjacent cells.

Now verify the column mappings. Note that Excel columns [url] and [friendlyname] are mapped to the [urlWSTR] and [friendlynameWSTR] of the Data Conversion.

ExcelURLDemo7

That is it, execute the package and click away at the URLs!

ExcelURLDemo8

SSIS Components Naming Conventions

Read a New Version of This Blog Post – SSIS Naming Convention – Updated for SSIS 2016

I wrote earlier about my naming conventions for SSIS variables that let me quickly figure out the source of the variable value very easily. This post continues on to the prefix-based naming conventions I use (or plan to use) for the 120+ components in SSIS packages.

While looking around online for SSIS component naming conventions, most of the suggestions are based on Jamie Thompson’s (Blog | @JamieT) post SSIS: Suggested Best Practices and naming conventions. He has tried to limit the prefixes to 3 or 4 characters and included most of the frequently used components. Using the prefixes as described in his post has helped me a lot in the maintenance phase of a package when my memory has faded about the functionality and the workflow. Some more motivation to adopt a naming convention can be found in 31-days of SSIS series by Jason Strate (Blog | @StrateSQL).

My goal was to make an exhaustive list of components with their prefixes so that –

  • I do not need to put on my thinking hat every time I start using a component for the first time.
  • The naming convention can be future-proof and avoid confusion with new components being introduced in SSIS platform.
  • Remove the guesswork (especially for new developers and maintenance teams who may not be SSIS developers primarily) by avoiding prefixes that sound too similar to another.

I created an Excel sheet of all the components (more than 120) that I found listed in MSDN, i.e. containers, connection managers, sources, destinations, tasks, and transformations. My prefixes are more elaborate than Jamie’s for the reasons mentioned above, but that is just my preference. You can use the Excel as a template for you organization and modify the prefixes to you liking.

The following is an example of one section from the Excel.

Download: Get the full Excel workbook at the TechNet Galleries

Example –

SSIS Component Naming Conventions