3

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

Irfarino
  • 522
  • 1
  • 8
  • 13

2 Answers2

4

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

praveen
  • 12,083
  • 1
  • 41
  • 49
  • 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. – Irfarino Aug 10 '12 at 04:44
  • Yes i think they limit is around 2GB as stated here http://stackoverflow.com/questions/140468/what-is-the-maximum-possible-length-of-a-net-string – praveen Aug 10 '12 at 04:46
  • 1
    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 – praveen Aug 10 '12 at 04:48
  • 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? – Irfarino Aug 10 '12 at 05:05
  • 1
    You can do that way .But you need to construct your sql statement as "Delete FROM YourTable WHERE ID IN (" + @[User::StoreID] + ")" – praveen Aug 10 '12 at 05:14
  • Thanks will give it a shot and message if I get stuck. – Irfarino Aug 10 '12 at 05:20
0

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)

Zsombor Zsuffa
  • 121
  • 5