I am working on a SSIS package using Visual Studio 19 that executes a relatively complex SQL query using global tables for a data feed. The structure of the package is not too complex:
- Execute SQL task 1 - runs most of the query and inserts it into a global temp table (len 42606 characters, 776 lines per Notepad++)
- Execute SQL task 2 - takes task 1 data and formats it into the required structure for the data feed (200~ columns) into another global temp table
- Data flow - pulls SQL task 2's data (simple Select * from ##thefinaltable) and sends to formatted CSV with Zappysys sftp
All tasks use the same SQL server connection.
The problem is in step 2. It seems like if I go over a certain length between the SQL commands I receive a "No Connection Is Specified" error. What is odd is if I truncate the second query to 2502 characters / 49 lines, the query succeeds and writes to the share as desired. So I'm wondering if the length of the query between SQL commands matters?
Things I've checked:
- RetainSameConnection
- BypassPrepare (since global table)
- Project level connection -> DelayValidation
- I found some non-printable characters when I pasted SSMS info into Notepad++. This didn't seem to matter, though
Is there a length limit between multiple SQL tasks? I'm just stuck on that it works / doesn't throw the "No Connection Is Specified" error if I shorten the second query.
Edit: I resolved the length issue by using the Execute SQL task "Browse" function to open the query. There must be limitations with the editor within the task itself. Getting rid of task #2 allowed the data flow task to run correctly.
发布者：admin，转转请注明出处：http://www.yc00.com/questions/1744191625a4562460.html
评论列表（0条）