March 16, 2012 at 5:03 am
I am using an OLEDB source to get my data in SSIS and use a variable to hold the SQL. The SQL is quite complex and combines SQL and other control variables in the package. Unfortunatelly for me the expression when evaluated exceeds the 4000 characters.
I rather like using this method but can see that a lot of the dynamic SQL statements that I would put into a variable could exceed the 4000 limit.
Is there a good alternative to this method that would allow me to combine SQL statements and variable to give me a nice dynamic SQL statement?
Many Thanks
😎
March 16, 2012 at 5:18 am
What version of SQL Server are you using? I think the 4000 character limit only applies in 2000 and below.
John
March 16, 2012 at 5:22 am
2008R2
March 16, 2012 at 5:26 am
Are you building the code and then executing it in an Execute SQL task? That's the simplest way, and, as far as I know, it isn't subject to any limits.
John
March 16, 2012 at 5:40 am
This is an OLEDB source rather than an execute SQL task. The SQL for it is sourced from a variable. In my case PkgSQLBanana. PkgSQLBanana is a package scoped variable of data type string (SSIS then treats this as DT_WSTR 4000).
When I put my SQL text into the variable and evaluate it the length exceed 4000 characters.
The SQL I use contains many references to package variables, thus making it dynamic.
Hope that helps
March 16, 2012 at 6:48 am
Are there any parts of the SQL that are constant? You could put those into stored procedures that accept parameters. That way, your SQL string will be shorter (and less text will be sent across the nextwork in order to execute it). It also has all the other advantages that stored procedures bring - reusability, security and so on. I hope I haven't reignited the stored prcoedure v ad hoc code debate by saying that!
John
March 16, 2012 at 6:55 am
I really did not want to start creating stored procedures as well as packages. Always my methodology is to keep it as simple as possible.
I have looked at using the OLE DB Source and building the SQL command in there with parameters. However this also seems a bit flakey.
I really loved the way you could build a dynamic SQL statement in a variable that in itself contained variables. This was just an ace moment. Unfortunatelly the limit of 4000 characters is too easy to break.
:ermm:
March 16, 2012 at 6:59 am
In a way, stored procedure do keep it simple. It's much easier to change code in a database than it is to change it in an SSIS package. But, as they say - you pay your money, you take your choice. Not everybody is an advocate of all-out use of stored procedures.
John
March 16, 2012 at 7:00 am
I am not sure this is true in SSIS, but....
Have you tried DT_STR? In SQL Server an NVARCHAR is limited to 4000 characters because each character takes 2 bytes, but VARCHAR can go to 8000 because each character only takes 1 byte.
Does your statement use any characters that require you to use DT_WSTR, and therefore be subject to the 4k character limitation?
March 16, 2012 at 7:04 am
Daniel Bowlin (3/16/2012)
I am not sure this is true in SSIS, but....Have you tried DT_STR? In SQL Server an NVARCHAR is limited to 4000 characters because each character takes 2 bytes, but VARCHAR can go to 8000 because each character only takes 1 byte.
Does your statement use any characters that require you to use DT_WSTR, and therefore be subject to the 4k character limitation?
The variable I use is at package scope and is defined as string. This I believe makes it a DT_WSTR by default?
I could not see any other options in the drop down list. Will have a look in a mo. Right in the middle of rewriting some SQL!
Cheers
M
March 16, 2012 at 7:14 am
Tried Char rather than string, same thing! Just put 5000 characters in and it would not evaluate
:crying:
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply