I would like to know what is the size limit on the SSIS string variable. I have a OLE DB data source that queries a column (alpha-numeric 10 digit) values and feeds the result set into a script task, the script task then creates a set based delete sql statement(i.e. DELETE FROM Table Where ID IN("all id's go here") and assigns the sql statement to a string variable, a third task (execute sql) then executes the sql from the variable. I am wondering if I get 10,000 values in the IN clause would that cause any issues with the string variable? Please advise
0
Add a comment
|
2 Answers
There is no limit on a string variable size .SSIS data types are derived from .net sub system so incase if you do have a very very large string value then you may run out of memory
6 Comments
Irfarino
so there is a sort of a pseudo limit, running out of memory. On that note should i use a for loop container and run the last task for each agonizing row by row, at least i won't run out of memory or I guess since my records won't exceed more then 10000 records, string variable is fine.
praveen
Yes i think they limit is around 2GB as stated here stackoverflow.com/questions/140468/…
praveen
Yes you can use for loop (ADO or item enumerator) and you can restrict the values.Basically even for loop is not needed as you can store the column values in a recordset variable into a data table and then inside the script task loop all the records
Irfarino
But I will still have to construct the sql statement from the records I get back, to execute it againts another dB. Unless I execute the DELETE sql statment for each row value, I want to do it via the IN clause in one shot rather than looping through and executing DELETE statment for each of the 10,000 rows, I am on right track?
praveen
You can do that way .But you need to construct your sql statement as "Delete FROM YourTable WHERE ID IN (" + @[User::StoreID] + ")"
|
One possible solution can be to put the ID in a temporary table in your destination database and then you can do a DELETE statement with a join between the temporary table and the table you are deleting from.
Or, you can rewrite your statement: DELETE FROM Table WHERE ID IN (SELECT ID FROM TempTable)
