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.
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:
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
Part2
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.
