Início > Variables and Expressions > SSIS Expressions And 4,000 Character Limit

SSIS Expressions And 4,000 Character Limit

 

In Part 1 of this blog posting I demonstrated how a combination of SSIS package configurations and expressions can make your ETL process dynamic.  In this posting I am going to discuss the dreaded 4000 character limitation in expressions for SQL 2008R2 (and earlier versions).  In SQL Server 2012 (Denali) this limitation is gone.  The example I provided focused on using a variable expression to change the source T-SQL statement used in a Data Flow Task’s OLE_DB Source.

Unfortunately, sooner or later everyone encounters the dreaded 4000 character limitation.  For me, this usually happens when I need to construct a particularly involved source query.  Yes, 4000 characters sounds like a long string but I challenge you to look at some of your more involved queries.  Think about the ones that you were especially proud of.  Perhaps you (un)pivoted some data or used temporary tables.  And if you were really on your game you might have included some decent inline comments to spare those following in your footsteps the pain of dissecting the monster!  In this post, I will explore some different ways to overcome the expression length limitation.

Source System Stored Procedure or View

A quick fix is to use a stored procedure or view hosted in the source system database.  By using procedure parameters or by calling a view and adding a dynamic WHERE clause, the 4000 character limit can become a thing of the past.  However, I don’t like adding views or stored procedures to a source system database.  I don’t even like relying on stored procedures or views that already exist on the source system.  Too frequently, changes are made to source system objects without considering downstream implications.  However, if you have an interface agreement with the source system (or have control over that system) this can be an effective way to overcome the 4000 character limitation.

That said, I believe it’s better to think of a data warehouse as a separate entity from the source system on which it relies.  Tying the two together will likely complicate the data warehouse development because the source system’s development, testing and deployment schedule will generally take precedence over the data warehouse schedule.  So, if there is a change you need to complete or correct your ETL, you might have to sit on your hands for a while!

Data Flow Script Component

Script Component as Data Source

If you like cutting code, a Data Flow Task script component can be used as a data source.  SSIS variables can be passed into the script and used within the script to alter the data query.  Please note that this does not mean that a script task can be used to produce result sets with differing metadata.  Data Flow tasks must adhere to the metadata defined for the source query.  As long as your dynamic source query produces the same structure, it can be used to overcome the 4000 character expression limit.

I am very wary of this solution for performance reasons.  .Net code is a fine and wonderful thing, but it comes with an overhead price.  So, I would not use this solution unless there were other compelling reasons.

Cohosted Database Stored Procedure

The third solution involves the use of an ETL database that is hosted on the same server as your source system’s database.  This could be a staging database or even the data warehouse itself.  It this case, it would be easy to create stored procedures therein that use fully qualified object names (database.schema.object).   (I knew that if I tried hard I could fit in a highfalutin word like “therein”.)   If you do not have the luxury of a cohosted database, it would be possible to use a linked server and a four part naming convention in your stage database stored procedure.

However, I really don’t like the requirement of a cohosted database requirement.   In my experience, things change.  When one of these databases is required to move to another server without the other, you will be forced to react.  A Linked Server solution will avoid this problem but you will pay the price in data transfer performance.

Stage Database Stored Procedure

It is important to remember that the 4000 character expression limitation applies to expressions and NOT to variables.  Therefore, if the expression is a variable expression or can reference a variable, you simply need to find a way to populate the variable with the desired string.

The final technique uses a stored procedure in the stage database to generate a T-SQL query that is passed back to the SSIS package and stored in a variable.  For this demo, I have created a T-SQL statement that is almost 6000 characters long due to the number of joins and the NULL handling that occurs in the SELECT clause. Similar to what we reviewed in Part 1 of this blog posting, the query includes a WHERE clause that filters by a date field.   I have included this query below.

 

SELECT 
	CASE WHEN ISNULL(LEN(LTRIM(RTRIM(ee.[BusinessEntityID]))), 0) != 0 THEN LTRIM(RTRIM(ee.[BusinessEntityID]))  ELSE -999 END AS [BusinessEntityID]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(ee.[NationalIDNumber]))), 0) != 0 THEN LTRIM(RTRIM(ee.[NationalIDNumber]))  ELSE 'N/A' END AS [NationalIDNumber]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(ee.[LoginID]))), 0) != 0 THEN LTRIM(RTRIM(ee.[LoginID]))  ELSE 'N/A' END AS [LoginID]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(ee.[JobTitle]))), 0) != 0 THEN LTRIM(RTRIM(ee.[JobTitle]))  ELSE 'N/A' END AS [JobTitle]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(ee.[BirthDate]))), 0) != 0 THEN LTRIM(RTRIM(ee.[BirthDate]))  ELSE 'N/A' END AS [BirthDate]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(ee.[MaritalStatus]))), 0) != 0 THEN LTRIM(RTRIM(ee.[MaritalStatus]))  ELSE 'N/A' END AS [MaritalStatus]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(ee.[Gender]))), 0) != 0 THEN LTRIM(RTRIM(ee.[Gender]))  ELSE 'N/A' END AS [Gender]

	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(per.[Title]))), 0) != 0 THEN LTRIM(RTRIM(per.[Title]))  ELSE 'N/A' END AS [Title]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(per.[FirstName]))), 0) != 0 THEN LTRIM(RTRIM(per.[FirstName]))  ELSE 'N/A' END AS [FirstName]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(per.[MiddleName]))), 0) != 0 THEN LTRIM(RTRIM(per.[MiddleName]))  ELSE 'N/A' END AS [MiddleName]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(per.[LastName]))), 0) != 0 THEN LTRIM(RTRIM(per.[LastName]))  ELSE 'N/A' END AS [LastName]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(per.[Suffix]))), 0) != 0 THEN LTRIM(RTRIM(per.[Suffix]))  ELSE 'N/A' END AS [Suffix]

	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(hr_dept.[DepartmentID]))), 0) != 0 THEN LTRIM(RTRIM(hr_dept.[DepartmentID]))  ELSE -999 END AS [DepartmentID]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(hr_dept.[Name]))), 0) != 0 THEN LTRIM(RTRIM(hr_dept.[Name]))  ELSE 'N/A' END AS [DeptName]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(hr_dept.[GroupName]))), 0) != 0 THEN LTRIM(RTRIM(hr_dept.[GroupName]))  ELSE 'N/A' END AS [GroupName]

	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(salespers.[TerritoryID]))), 0) != 0 THEN LTRIM(RTRIM(salespers.[TerritoryID]))  ELSE -999 END AS [TerritoryID]
	,salespers.SalesQuota
	,salespers.Bonus
	,salespers.CommissionPct
	,salespers.SalesYTD
	,salespers.SalesLastYear

	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(ordhdr.[OrderDate]))), 0) != 0 THEN LTRIM(RTRIM(ordhdr.[OrderDate]))  ELSE 'N/A' END AS [OrderDate]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(ordhdr.[SalesOrderNumber]))), 0) != 0 THEN LTRIM(RTRIM(ordhdr.[SalesOrderNumber]))  ELSE 'N/A' END AS [SalesOrderNumber]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(ordhdr.[AccountNumber]))), 0) != 0 THEN LTRIM(RTRIM(ordhdr.[AccountNumber]))  ELSE 'N/A' END AS [AccountNumber]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(ordhdr.[SubTotal]))), 0) != 0 THEN LTRIM(RTRIM(ordhdr.[SubTotal]))  ELSE CAST(0 AS money) END AS [SubTotal]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(ordhdr.[TaxAmt]))), 0) != 0 THEN LTRIM(RTRIM(ordhdr.[TaxAmt]))  ELSE CAST(0 AS money) END AS [TaxAmt]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(ordhdr.[Freight]))), 0) != 0 THEN LTRIM(RTRIM(ordhdr.[Freight]))  ELSE CAST(0 AS money) END AS [Freight]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(ordhdr.[TotalDue]))), 0) != 0 THEN LTRIM(RTRIM(ordhdr.[TotalDue]))  ELSE CAST(0 AS money) END AS [TotalDue]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(ordhdr.[Comment]))), 0) != 0 THEN LTRIM(RTRIM(ordhdr.[Comment]))  ELSE 'N/A' END AS [Comment]

	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(orddet.[SalesOrderID]))), 0) != 0 THEN LTRIM(RTRIM(orddet.[SalesOrderID]))  ELSE -999 END AS [SalesOrderID]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(orddet.[SalesOrderDetailID]))), 0) != 0 THEN LTRIM(RTRIM(orddet.[SalesOrderDetailID]))  ELSE -999 END AS [SalesOrderDetailID]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(orddet.[CarrierTrackingNumber]))), 0) != 0 THEN LTRIM(RTRIM(orddet.[CarrierTrackingNumber]))  ELSE 'N/A' END AS [CarrierTrackingNumber]

	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(orddet.[OrderQty]))), 0) != 0 THEN LTRIM(RTRIM(orddet.[OrderQty]))  ELSE  CAST(0 AS money) END AS [OrderQty]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(orddet.[UnitPrice]))), 0) != 0 THEN LTRIM(RTRIM(orddet.[UnitPrice]))  ELSE  CAST(0 AS money) END AS [UnitPrice]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(orddet.[UnitPriceDiscount]))), 0) != 0 THEN LTRIM(RTRIM(orddet.[UnitPriceDiscount]))  ELSE  CAST(0 AS money) END AS [UnitPriceDiscount]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(orddet.[LineTotal]))), 0) != 0 THEN LTRIM(RTRIM(orddet.[LineTotal]))  ELSE 'N/A' END AS [LineTotal]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(orddet.[ModifiedDate]))), 0) != 0 THEN LTRIM(RTRIM(orddet.[ModifiedDate]))  ELSE 'N/A' END AS [ModifiedDate]

	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(prod.[ProductID]))), 0) != 0 THEN LTRIM(RTRIM(prod.[ProductID]))  ELSE -999 END AS [ProductID]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(prod.[Name]))), 0) != 0 THEN LTRIM(RTRIM(prod.[Name]))  ELSE 'N/A' END AS [ProdName]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(prod.[ProductNumber]))), 0) != 0 THEN LTRIM(RTRIM(prod.[ProductNumber]))  ELSE 'N/A' END AS [ProductNumber]

FROM HumanResources.Employee ee 
INNER JOIN HumanResources.EmployeeDepartmentHistory 
	ON ee.BusinessEntityID = HumanResources.EmployeeDepartmentHistory.BusinessEntityID 
INNER JOIN HumanResources.Department hr_dept
	ON HumanResources.EmployeeDepartmentHistory.DepartmentID = hr_dept.DepartmentID 
INNER JOIN Person.Person per
	ON ee.BusinessEntityID = per.BusinessEntityID 
INNER JOIN Sales.SalesPerson salespers
	ON ee.BusinessEntityID = salespers.BusinessEntityID 
INNER JOIN Sales.SalesOrderHeader ordhdr
	ON salespers.BusinessEntityID = ordhdr.SalesPersonID 
	AND 
    salespers.BusinessEntityID = ordhdr.SalesPersonID 
    AND 
    salespers.BusinessEntityID = ordhdr.SalesPersonID 
	AND salespers.BusinessEntityID = ordhdr.SalesPersonID 
INNER JOIN Sales.SalesOrderDetail orddet
	ON ordhdr.SalesOrderID = orddet.SalesOrderID 
	AND ordhdr.SalesOrderID = orddet.SalesOrderID 
	AND ordhdr.SalesOrderID = orddet.SalesOrderID 
	AND ordhdr.SalesOrderID = orddet.SalesOrderID 
INNER JOIN Production.Product prod
	ON orddet.ProductID = prod.ProductID
WHERE
	orddet.ModifiedDate	> '01/01/2000'

 I will use the same SSIS package I used in Part 1 to implement this technique.  All that is required is an Execute SQL task that will execute and capture the results of the stored procedure.  The steps required to set up this task are shown below.

 

Step 1 – Create a stored procedure to dynamically generate and return the T-SQL statement.  In this example I am using an output parameter to pass the T-SQL statement.

 

USE [Stage_ExpressionLimit]
GO

/****** Object:  StoredProcedure [dbo].[usp_AdWorksSourceQuery]    Script Date: 12/28/2011 09:25:54 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_AdWorksSourceQuery]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_AdWorksSourceQuery]
GO

CREATE PROC usp_AdWorksSourceQuery

@ModDate varchar(20)
,@SQLOut nvarchar(max) OUTPUT

AS 

SET NOCOUNT ON

DECLARE @SQL varchar(max)

SET @SQL = 

'
SELECT 
	CASE WHEN ISNULL(LEN(LTRIM(RTRIM(ee.[BusinessEntityID]))), 0) != 0 THEN LTRIM(RTRIM(ee.[BusinessEntityID]))  ELSE -999 END AS [BusinessEntityID]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(ee.[NationalIDNumber]))), 0) != 0 THEN LTRIM(RTRIM(ee.[NationalIDNumber]))  ELSE ''N/A'' END AS [NationalIDNumber]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(ee.[LoginID]))), 0) != 0 THEN LTRIM(RTRIM(ee.[LoginID]))  ELSE ''N/A'' END AS [LoginID]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(ee.[JobTitle]))), 0) != 0 THEN LTRIM(RTRIM(ee.[JobTitle]))  ELSE ''N/A'' END AS [JobTitle]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(ee.[BirthDate]))), 0) != 0 THEN LTRIM(RTRIM(ee.[BirthDate]))  ELSE ''N/A'' END AS [BirthDate]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(ee.[MaritalStatus]))), 0) != 0 THEN LTRIM(RTRIM(ee.[MaritalStatus]))  ELSE ''N/A'' END AS [MaritalStatus]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(ee.[Gender]))), 0) != 0 THEN LTRIM(RTRIM(ee.[Gender]))  ELSE ''N/A'' END AS [Gender]

	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(per.[Title]))), 0) != 0 THEN LTRIM(RTRIM(per.[Title]))  ELSE ''N/A'' END AS [Title]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(per.[FirstName]))), 0) != 0 THEN LTRIM(RTRIM(per.[FirstName]))  ELSE ''N/A'' END AS [FirstName]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(per.[MiddleName]))), 0) != 0 THEN LTRIM(RTRIM(per.[MiddleName]))  ELSE ''N/A'' END AS [MiddleName]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(per.[LastName]))), 0) != 0 THEN LTRIM(RTRIM(per.[LastName]))  ELSE ''N/A'' END AS [LastName]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(per.[Suffix]))), 0) != 0 THEN LTRIM(RTRIM(per.[Suffix]))  ELSE ''N/A'' END AS [Suffix]

	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(hr_dept.[DepartmentID]))), 0) != 0 THEN LTRIM(RTRIM(hr_dept.[DepartmentID]))  ELSE -999 END AS [DepartmentID]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(hr_dept.[Name]))), 0) != 0 THEN LTRIM(RTRIM(hr_dept.[Name]))  ELSE ''N/A'' END AS [DeptName]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(hr_dept.[GroupName]))), 0) != 0 THEN LTRIM(RTRIM(hr_dept.[GroupName]))  ELSE ''N/A'' END AS [GroupName]

	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(salespers.[TerritoryID]))), 0) != 0 THEN LTRIM(RTRIM(salespers.[TerritoryID]))  ELSE -999 END AS [TerritoryID]
	,salespers.SalesQuota
	,salespers.Bonus
	,salespers.CommissionPct
	,salespers.SalesYTD
	,salespers.SalesLastYear

	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(ordhdr.[OrderDate]))), 0) != 0 THEN LTRIM(RTRIM(ordhdr.[OrderDate]))  ELSE ''N/A'' END AS [OrderDate]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(ordhdr.[SalesOrderNumber]))), 0) != 0 THEN LTRIM(RTRIM(ordhdr.[SalesOrderNumber]))  ELSE ''N/A'' END AS [SalesOrderNumber]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(ordhdr.[AccountNumber]))), 0) != 0 THEN LTRIM(RTRIM(ordhdr.[AccountNumber]))  ELSE ''N/A'' END AS [AccountNumber]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(ordhdr.[SubTotal]))), 0) != 0 THEN LTRIM(RTRIM(ordhdr.[SubTotal]))  ELSE CAST(0 AS money) END AS [SubTotal]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(ordhdr.[TaxAmt]))), 0) != 0 THEN LTRIM(RTRIM(ordhdr.[TaxAmt]))  ELSE CAST(0 AS money) END AS [TaxAmt]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(ordhdr.[Freight]))), 0) != 0 THEN LTRIM(RTRIM(ordhdr.[Freight]))  ELSE CAST(0 AS money) END AS [Freight]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(ordhdr.[TotalDue]))), 0) != 0 THEN LTRIM(RTRIM(ordhdr.[TotalDue]))  ELSE CAST(0 AS money) END AS [TotalDue]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(ordhdr.[Comment]))), 0) != 0 THEN LTRIM(RTRIM(ordhdr.[Comment]))  ELSE ''N/A'' END AS [Comment]

	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(orddet.[SalesOrderID]))), 0) != 0 THEN LTRIM(RTRIM(orddet.[SalesOrderID]))  ELSE -999 END AS [SalesOrderID]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(orddet.[SalesOrderDetailID]))), 0) != 0 THEN LTRIM(RTRIM(orddet.[SalesOrderDetailID]))  ELSE -999 END AS [SalesOrderDetailID]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(orddet.[CarrierTrackingNumber]))), 0) != 0 THEN LTRIM(RTRIM(orddet.[CarrierTrackingNumber]))  ELSE ''N/A'' END AS [CarrierTrackingNumber]

	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(orddet.[OrderQty]))), 0) != 0 THEN LTRIM(RTRIM(orddet.[OrderQty]))  ELSE  CAST(0 AS money) END AS [OrderQty]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(orddet.[UnitPrice]))), 0) != 0 THEN LTRIM(RTRIM(orddet.[UnitPrice]))  ELSE  CAST(0 AS money) END AS [UnitPrice]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(orddet.[UnitPriceDiscount]))), 0) != 0 THEN LTRIM(RTRIM(orddet.[UnitPriceDiscount]))  ELSE  CAST(0 AS money) END AS [UnitPriceDiscount]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(orddet.[LineTotal]))), 0) != 0 THEN LTRIM(RTRIM(orddet.[LineTotal]))  ELSE ''N/A'' END AS [LineTotal]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(orddet.[ModifiedDate]))), 0) != 0 THEN LTRIM(RTRIM(orddet.[ModifiedDate]))  ELSE ''N/A'' END AS [ModifiedDate]

	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(prod.[ProductID]))), 0) != 0 THEN LTRIM(RTRIM(prod.[ProductID]))  ELSE -999 END AS [ProductID]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(prod.[Name]))), 0) != 0 THEN LTRIM(RTRIM(prod.[Name]))  ELSE ''N/A'' END AS [ProdName]
	,CASE WHEN ISNULL(LEN(LTRIM(RTRIM(prod.[ProductNumber]))), 0) != 0 THEN LTRIM(RTRIM(prod.[ProductNumber]))  ELSE ''N/A'' END AS [ProductNumber]

FROM HumanResources.Employee ee 
INNER JOIN HumanResources.EmployeeDepartmentHistory 
	ON ee.BusinessEntityID = HumanResources.EmployeeDepartmentHistory.BusinessEntityID 
INNER JOIN HumanResources.Department hr_dept
	ON HumanResources.EmployeeDepartmentHistory.DepartmentID = hr_dept.DepartmentID 
INNER JOIN Person.Person per
	ON ee.BusinessEntityID = per.BusinessEntityID 
INNER JOIN Sales.SalesPerson salespers
	ON ee.BusinessEntityID = salespers.BusinessEntityID 
INNER JOIN Sales.SalesOrderHeader ordhdr
	ON salespers.BusinessEntityID = ordhdr.SalesPersonID 
	AND 
    salespers.BusinessEntityID = ordhdr.SalesPersonID 
    AND 
    salespers.BusinessEntityID = ordhdr.SalesPersonID 
	AND salespers.BusinessEntityID = ordhdr.SalesPersonID 
INNER JOIN Sales.SalesOrderDetail orddet
	ON ordhdr.SalesOrderID = orddet.SalesOrderID 
	AND ordhdr.SalesOrderID = orddet.SalesOrderID 
	AND ordhdr.SalesOrderID = orddet.SalesOrderID 
	AND ordhdr.SalesOrderID = orddet.SalesOrderID 
INNER JOIN Production.Product prod
	ON orddet.ProductID = prod.ProductID
WHERE
	orddet.ModifiedDate	> ''' + @ModDate +  ''''

SET @SQLOut = @SQL

Step 2– Use package configuration to retrieve LastLoadDate value.  This is identical to the technique used in Part 1 of this blog posting.

 

Configuration Table

Step 3 – Use an Execute SQL Task in the Control Flow task to execute the stored procedure created in Step 1 and store the output parameter in the package variable called SQL_AdWorks_SELECT.  This variable will be used by the Data Flow Task and it is the same variable used in Part 1 of this blog post.  The settings of the Execute SQL task a shown below:

Execute SQL Task

Execute SQL Task - General

Execute SQL Task - Parameters

The remainder of the package is unchanged! This technique was used to great advantage in a project I was involved with recently.  The real beauty is that completely frees the ETL developer from this limitation, maintains the benefits of directly querying the source database and does not place unrealistic expectations for where the database(s) will be hosted.

In the third blog posting in this series, I will demonstrate how this ETL solution looks in SQL Server 2012 without the worry of the 4000 character limitation.

By Craig Love

  1. Ainda sem comentários.
  1. No trackbacks yet.

Deixe uma Resposta

Preencha os seus detalhes abaixo ou clique num ícone para iniciar sessão:

Logótipo da WordPress.com

Está a comentar usando a sua conta WordPress.com Terminar Sessão / Alterar )

Imagem do Twitter

Está a comentar usando a sua conta Twitter Terminar Sessão / Alterar )

Facebook photo

Está a comentar usando a sua conta Facebook Terminar Sessão / Alterar )

Google+ photo

Está a comentar usando a sua conta Google+ Terminar Sessão / Alterar )

Connecting to %s

%d bloggers like this: