How to Use Temp Table in SSIS

April 8, 2015June 23, 2016Aalam Rangi8 Comments

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.

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.

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.

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.

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.

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.

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.

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

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.

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.

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.

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

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

December 11, 2014August 31, 2016Aalam Rangi1 Comment

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.

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

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.

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.

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

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?

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

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.

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

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 –

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.

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.

[jetpack_subscription_form subscribe_text=”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

August 7, 2014Aalam Rangi5 Comments

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

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.

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

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.

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.

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

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.

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

Example of SSIS Foreach Loop Item Enumerator and Dynamic OLEDB Connection

August 6, 2014July 30, 2018Aalam Rangi10 Comments

Foreach Loop Container is one of the looping constructs available in SSIS packages. One of its enumerator options is the Foreach Item Enumerator. It enumerates through a list of items populated at design time. The list is static at execution time, i.e. you cannot load the list items dynamically from a configuration file or another source like an SQL table. It is probably due to the static nature of the item list, that this enumerator type is not used too often in packages.

On the other hand, there are some scenarios where a static list can prove to be useful. For example –

no need to create a database table just to pass on values for the Foreach Loop
enforce a specific order to the parameters passed to the loop
initialize variables values with the Item Enumerator instead of doing that in a Script task

Another frequent requirement in SSIS packages is to execute a set of operations against different SQL Servers or databases. The package should change the OLE DB connection string dynamically during execution to point towards the appropriate target. A looping construct iterates through the list of servers and databases, and the package Expressions change the OLE DB connection properties.

I am creating this step-by-step scenario to demonstrate the use of a Foreach Item Enumerator and use it to make a dynamic OLEDB connection. I will execute a single query in a loop against three different SQL databases and export the results to a single text file.

The Steps

Add a Foreach Loop Container to your SSIS package. Double-click on it to open its editor. Click on the Collections tab on the left of the editor. Select the enumerator as Foreach Item Enumerator. Click on the Columns… button. Add two columns of String type. As you add columns, the editor gives them names like Column0, Column1 and so on, and there is no way to customize these names.

Populate the column values by simply typing in the rows. Use Column0 for server names and Column1 for corresponding database names on that server. I have used my local server for all three rows but you can use your own server names here. This is the list of items that are iterated by the Foreach Loop.

Now you need two variables that will hold the values shredded from the item list as the Foreach Loop iterates through the rows. These variables are used later in the Property Expressions of the OLEDB connection to make it dynamic.

Click on Variable Mappings tab on the left and then on the <New Variable…>

Add two variables of String type and name them ServerName and DatabaseName.

The Index 0 and 1 of the variables correspond to the Column0 and Column1 of the Foreach Item list respectively.

That is all there is to it. Now the loop is ready to iterate through the item rows.

Let us move on to the dynamic OLEDB connection. Add a Data Flow Task to the Foreach Loop Container.

Double-click the Data Flow Task to view its design surface. Now add one OLE DB Source.

Double-click OLE DB Source to set its properties. The first window is of its connection manager.

Once the connection information is done, view its properties editor. Set the Data Access Mode to SQL Command and put the following query in the SQL Command Text –

select
@@SERVERNAME as ServerName
,DB_NAME() as DatabaseName
,GETDATE() as LogDateTime
,COUNT(*) as NumberOfIndexes
from sys.indexes;

Clicking the Columns tab is important so that the SSIS can find the metadata like column names, data types etc. from the underlying tables/views. If you don’t do this then you’ll find later that SSIS does not know of this metadata.

Again, it is a good idea to drag the connector from OLEDB source to the Flat File destination before modifying the Flat File destination properties. In this way, the SSIS Designer helps you by matching the destination with the source metadata automatically. Otherwise, you have to perform some extra steps to match columns. I hope you like to save time as much as I do!

Give a file path and check the box for Column Names in the First Row.

Verify the column mappings in the Mappings tab.

The trick to make the OLEDB connection string dynamic lies in the next couple of steps where you edit the properties of the OLEDB connection and assign expressions to its ServerName and InitialCatalog properties.

The property ServerName gets the variable @[User::ServerName] and the property InitialCatalog gets the variable @[User::DatabaseName]. Remember that these variables will get their values modified during each iteration of the Foreach Loop and as a result, the OLEDB connection string will get modified too.

As the last step, make sure that data will append to the contents of the destination text file. Otherwise, each iteration will overwrite the contents with the new source connection.

How to Fix Error Code: 80004005 in DTS Caused by 64-bit DB2 ODBC Driver

July 31, 2012Aalam RangiLeave a comment

Issue:

Data Transformation Services packages (DTS) that use DB2 as an ODBC source, fail when the execution environment is upgraded to 64-Bit Windows Server 2008, Windows 7 etc.

Error Message:

The error logs of the DTS would show the following message –

Step Error Source: Microsoft OLE DB Provider for ODBC Drivers
Step Error Description:[Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application
Step Error code: 80004005
Step Error Help File:
Step Error Help Context ID:0

Cause:

DTS are 32-bit applications and need the Data Source Names (DSN) based on 32-bit ODBC drivers. A 64-bit Operating System points to the 64-bit drivers by default and uses the ODBC Data Source Administrator at –

C:WINDOWSsystem32odbcad32.exe

The standard DSN creation scripts used by system administrators might use the 64-bit version because it is at the default location.

The 32-bit ODBC Data Source Administrator is in –

C:WINDOWSSysWOW64odbcad32.exe

The required DSN should be created again using this 32-bit version and it will update the Windows registry or the environment path variables appropriately.

Fix:

Start the 32-bit ODBC Data Source Administrator Wizard from the SYSWOW64 directory.

C:WINDOWSSysWOW64odbcad32.exe

Go to System DSN tab and click on Add.

Select the available IBM ODBC driver.

Give a DSN name.

Confirm the action.

The last message box verifies if you want to overwrite the existing DSN alias in DB2CLI.INI file. This file is at the below location on a 64-bit machine –

C:ProgramDataIBMDB2DB2_V95db2cli.ini

Clicking on No will abort the wizard. Click on Yes to continue. The timestamp for the INI file would be updated. If you are just recreating an existing alias as in the example above, the file content would appear to be the same, though the ODBC Data Source Administrator Wizard will create or modify the appropriate registry and environment variable entries.

Summary:

Microsoft maintains a backward compatibility feature known as WOW64 (Windows-32-On-Windows-64) that lets older 32-bit applications to run smoothly. This is implemented by maintaining a separate copy of the necessary 32-bit run time resources (configuration utilities, drivers, registry settings, other DLLs etc.) in different folders/locations.

This example refers to an ODBC DSN for a DB2 server but the concept could be relevant in other such scenarios where there is a conflict between 32 and 64-bit drivers.

Reference:

How to point to 32-bit IBM DB2 ODBC driver on 64-bit Microsoft system.
http://www-01.ibm.com/support/docview.wss?uid=swg21384435

SQLErudition.com

Learning SQL Server

How To