SSIS workaround for the 4000 byte character limit

Well, let’s call it an alternative and not a workaround. 

The 4000th blog posting on the 4000 byte limit in SSIS

I continue to read of SSIS developers running into the DT_WSTR – equivalent to the national character set, NVARCHAR, or UNICODE, if you will - 4000 byte limit for expressions; remember that 4k bytes is the length of a DT_WSTR data type for expressions and not for variables. (Jamie Thomson did a good job explaining this in May, and I blogged back in February 2009 on pasting code in an EXEC SQL Task window). If you haven’t hit up against any of this stuff yet, you are probably wondering…”this guy really has a query that takes up this much space?” The answer is yes. Here’s the deal – if you are putting code in an EXEC SQL Task window, or a in a DataReader Source (.Net) expression as I described in a blog (sql code in the data flow tab), you face at least two challenges: 1) You may need the space for a large DML statement, or 2) you may want to format your code in a halfway respectable manner so the poor sap who comes along after you at some later point in time to modify, change, or support the garbage that you left behind doesn’t go postal. A large query with seven, eight, or ten tables along with some carriage returns and formatting can hit the limit of the code window. I’ve done it. And, unfortunately, because of this limit, you have to revert to removing those carriage returns, spaces, and nice syntax formatting, which quickly leaves your code in a mess. And I’ve done that, too…a jammed together mish-mash of code that’s difficult to support.

“A man’s got to know his limitations”

When using SSIS Execute SQL Tasks, expressions, and variables, you have to keep in mind that there are limitations. I read a post a couple of weeks ago, I think it was on social.msdn.microsoft.com, where an MVP suggested that the reader try concatenating two variables in an expression together. This works, but in the Script Task only and not in a data flow tab expression using two or more variables:

public void Main()
       {
           string query = "";
           query = query.PadLeft(8000, ' ');
           Dts.Variables["String1"].Value = query.ToString() 
                 + "SELECT * from Humanresources.Shift";
           MessageBox.Show(Dts.Variables["String1"].Value.ToString());
           Dts.TaskResult = (int)ScriptResults.Success;
       }     

I bet you like that message box, don’t you? Oops. Don’t look now, but maybe there is a limit:

public void Main()
     {
         string query2 = "";
         query2 = query2.PadLeft(2147483647, ' ')
                 + "SELECT COUNT(1) FROM " + Dts.Variables["Table"].Value.ToString();          
         
         Dts.TaskResult = (int)ScriptResults.Success;
     }

“Put the evidence in the car!”

My fix is to simply bypass the expression altogether - run the query inside of the script task. This works fine, and you can still use your variables whenever you like by throwing their values inside of the SQL string to create dynamic TSQL; here I put the table in a variable called “Table”, build the string, and exec it. Sawheeeeeeeeeeeeeeeet!

public void Main()
      {
          string query = "";
          query = "SELECT COUNT(1) FROM " + Dts.Variables["Table"].Value.ToString();

          SqlConnection myConnection = new SqlConnection("user id=username;" +
                                                "password=password;server=BOOMERSOONER;" +
                                                "Trusted_Connection=yes;" +
                                                "database=AdventureWorks2008; " +
                                                "connection timeout=0");
          myConnection.Open();
          SqlCommand cmd = new SqlCommand(query, myConnection);
          cmd.CommandTimeout = 0;
          cmd.ExecuteNonQuery();
          myConnection.Close();

          Dts.TaskResult = (int)ScriptResults.Success;
      }

“Nah, Lumbergh's gonna have me come in on Saturday, I just know it.”

If you were overly anxious or weird, you could import System.Text and use StringBuilder to build your TSQL. You could also add an App.config file to the Script Task (not the SSIS package) and then place the value in an app setting, similar to what I did in a previous blog for calling SSIS with web services. Doing this will allow you to not have to recompile the script task each time that you wanted to change the TSQL.

Conclusion

SSIS variables and script task variables both have limits, the latter much more than the former. Hence my solution here, which is to take the TSQL out of an expression or a variable and place it into a Script Task, similar to what you would do if you were building an ASP.Net web page and you needed TSQL code to hit the database. This is pretty straightforward, really, and a way to get around this 4000 byte DT_WSTR size that seems to creep up while developing in SSIS. It’s also a flexible way to execute code, is very simple, and allows you to continue to concatenate SSIS variable values inside of the string at run-time. You can use the code as I have, above, to connect to SQL Server, or you can create DSNs to access third-party data sources as well.

Lee

-----------------------------------

No, I haven’t put this in production…you do it and let me know how it works out for you.