SSIS - 2005 - Overcome String Limitation of 4000 characters for sqlcommand

Question

Hello

I have package and sqlcommand property of data reader source is set dynamically by a query from variable and the query is more than 4000 characters and SSIS does not like that and truncates the query which will result in package failure. any ideas would be appreciated greatly in solving this issue.

Answer 1

Take a script task at control flow.
Make the SQLCommand varibale as read/write and set its value inside script task.
Now use this SQL Command variable in the DFT.

---

Nitesh Rai- Please mark the post as answered if it answers your question

Answer 2

but i am getting the query as string from database and is stored in recordset object and iterated for each record and the variable that is mapped to hold the query does not allow more than 4000 characters.
I can have the script task as you mentioned but how do i set the value inside of the script task? do i have to loop inside the script task to read the recordset and set the variable value or is there any easier way for it?
Below is what i have

Execute sql task  - retrieves conn string, query, date for each database and stores it in recordset object

For each loop container - loops each record from above and dynamically sets the conn string, sqlcommand and retrieves data and store the data in DFT


Really appreciate your help

Answer 3

Execute SQL Task: Select connstring,date and store it in a object variable. (Dont select the query)
Then inside For Each Loop, select Foreachado enumerator and select the object variable from the drop down box.
Go to results and select variable var1 (index 0) and var2 (index 1).

Then take a DFT inside for each loop and inside DFT take the source with Data Access Mode as SQL Command
SQL Command will be like: select query from table where date = ? and connstring = ?
Then hit the parameters button and do the parameter mapping.
Give some valid initial values to both the variables (var1 and var2).
Complete the DFT as per yur requirement.

---

Nitesh Rai- Please mark the post as answered if it answers your question

Answer 4

Harris,
Use this post as reference.http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/1031126d-f971-4311-9874-c18130fd912d

---

Thanks, Bharani M - Please mark the post as answered if it answers your question. - Please vote the post as Helpful if you find the post as helpful.