SSIS Variable Expression Limits

     I recently blogged about using SSIS variables in data flow or execute SQL tasks, the post can be found here.  I received a couple of questions about this technique specifically regarding the limit of 4,000 characters in a variable expression.  The first thing I want to clarify is that this character limitation is not a limitation for SSIS string variables, but only a limitation in regards to the variable expression.  Consider an outrageously large query such as this:

SELECT HumanResources.EmployeePayHistory.*, Person.Person.BusinessEntityID AS Expr1, Person.Person.PersonType, Person.Person.NameStyle, Person.Person.Title,
                  
Person.Person.Demographics, Person.Person.AdditionalContactInfo, Person.Person.EmailPromotion, Person.Person.Suffix, Person.Person.LastName,
                  
Person.Person.rowguid, Person.Person.ModifiedDate AS Expr2, Sales.PersonCreditCard.BusinessEntityID AS Expr3, Sales.PersonCreditCard.CreditCardID,
                  
Sales.PersonCreditCard.ModifiedDate AS Expr4, Person.PersonPhone.PhoneNumber, Person.PersonPhone.PhoneNumberTypeID, Employee_1.BusinessEntityID AS Expr5,
                  
Employee_1.NationalIDNumber, Employee_1.LoginID, Employee_1.OrganizationNode, Employee_1.OrganizationLevel, Employee_1.JobTitle, Employee_1.BirthDate,
                  
Employee_1.CurrentFlag, Employee_1.SickLeaveHours, Employee_1.VacationHours, Employee_1.SalariedFlag, Employee_1.HireDate, Employee_1.rowguid AS Expr6
FROM     Sales.SalesOrderHeader INNER JOIN
                  
Sales.SalesOrderDetail ON Sales.SalesOrderHeader.SalesOrderID = Sales.SalesOrderDetail.SalesOrderID AND
                  
Sales.SalesOrderHeader.SalesOrderID = Sales.SalesOrderDetail.SalesOrderID AND Sales.SalesOrderHeader.SalesOrderID = Sales.SalesOrderDetail.SalesOrderID AND
                  
Sales.SalesOrderHeader.SalesOrderID = Sales.SalesOrderDetail.SalesOrderID AND
                  
Sales.SalesOrderHeader.SalesOrderID = Sales.SalesOrderDetail.SalesOrderID INNER JOIN
                  
Sales.SalesOrderHeaderSalesReason ON Sales.SalesOrderHeader.SalesOrderID = Sales.SalesOrderHeaderSalesReason.SalesOrderID INNER JOIN
                  
Sales.SalesPerson ON Sales.SalesOrderHeader.SalesPersonID = Sales.SalesPerson.BusinessEntityID AND
                  
Sales.SalesOrderHeader.SalesPersonID = Sales.SalesPerson.BusinessEntityID AND Sales.SalesOrderHeader.SalesPersonID = Sales.SalesPerson.BusinessEntityID AND
                  
Sales.SalesOrderHeader.SalesPersonID = Sales.SalesPerson.BusinessEntityID AND
                  
Sales.SalesOrderHeader.SalesPersonID = Sales.SalesPerson.BusinessEntityID INNER JOIN
                  
Person.Person INNER JOIN
                  
Sales.PersonCreditCard ON Person.Person.BusinessEntityID = Sales.PersonCreditCard.BusinessEntityID INNER JOIN
                  
Person.PersonPhone ON Person.Person.BusinessEntityID = Person.PersonPhone.BusinessEntityID INNER JOIN
                  
HumanResources.Employee AS Employee_1 ON Person.Person.BusinessEntityID = Employee_1.BusinessEntityID INNER JOIN
                  
HumanResources.EmployeeDepartmentHistory ON Employee_1.BusinessEntityID = HumanResources.EmployeeDepartmentHistory.BusinessEntityID AND
                  
Employee_1.BusinessEntityID = HumanResources.EmployeeDepartmentHistory.BusinessEntityID AND
                  
Employee_1.BusinessEntityID = HumanResources.EmployeeDepartmentHistory.BusinessEntityID INNER JOIN
                  
HumanResources.EmployeePayHistory ON Employee_1.BusinessEntityID = HumanResources.EmployeePayHistory.BusinessEntityID AND
                  
Employee_1.BusinessEntityID = HumanResources.EmployeePayHistory.BusinessEntityID AND
                  
Employee_1.BusinessEntityID = HumanResources.EmployeePayHistory.BusinessEntityID ON Sales.SalesPerson.BusinessEntityID = Employee_1.BusinessEntityID AND
                  
Sales.SalesPerson.BusinessEntityID = Employee_1.BusinessEntityID AND Sales.SalesPerson.BusinessEntityID = Employee_1.BusinessEntityID INNER JOIN
                  
Production.Product ON Sales.SalesOrderDetail.ProductID = Production.Product.ProductID INNER JOIN
                  
Production.ProductInventory ON Production.Product.ProductID = Production.ProductInventory.ProductID AND
                  
Production.Product.ProductID = Production.ProductInventory.ProductID AND Production.Product.ProductID = Production.ProductInventory.ProductID AND
                  
Production.Product.ProductID = Production.ProductInventory.ProductID AND Production.Product.ProductID = Production.ProductInventory.ProductID AND
                  
Production.Product.ProductID = Production.ProductInventory.ProductID AND Production.Product.ProductID = Production.ProductInventory.ProductID AND
                  
Production.Product.ProductID = Production.ProductInventory.ProductID AND Production.Product.ProductID = Production.ProductInventory.ProductID AND
                  
Production.Product.ProductID = Production.ProductInventory.ProductID CROSS JOIN
                  
Production.ProductCategory

The above query obviously exceeds the 4,000 character variable expression maximum, but it is still possible to use an expression to contain the entire query.

     To demonstrate this create three variables all of a data type of string.

 variables

Paste half of the query into the expression of variables Part1 and Part2 enclosing it within double quotes.  In the expression of the Whole variable use the expression:

@[User::Part1] +  @[User::Part2]

    You can validate the variable values by dragging a script task from the toolbox onto the control flow design tab and configure it to use all three variables at read only variables:

script

Use three different message boxes to display the values of each variable:
MessageBox.Show(Dts.Variables["Part1"].Value.ToString());
MessageBox.Show(Dts.Variables["Part2"].Value.ToString());
MessageBox.Show(Dts.Variables["Whole"].Value.ToString());

      Executing the package displays three different message boxes, Part1 and Part2 which contain half of the query and Whole, which contains the entire query:

Part1

 Part1

Part2

Part2

Whole

Whole

     It is obvious that the character length of the Whole variable exceeds 4,000, which proves that the limitation of character size is specific to the expression and not the variable value.  I know that this seems quite inconvenient if you are working with a long and complex expression for a variable value, but unfortuntaely, this is the only work around avaialable.  This drawback has been posted on Microsoft Connect, but unfortunately has been closed as “By design“. 

     The sample package displayed in this post can be downloaded here.

Leave a Reply


Hit Counter provided by Sign Holders