SSIS: EXECUTE SQL Task, ORA-01795: maximum number of expressions in a list is 1000

While building an SSIS package, I had to sub-select from another table. This subset had over 1000 entries and resulted into an error by SSIS

Error

Description: …. Executing the query “UPDATE OPS$PROFAI.MONEY_MOVEMENTS_STG SET STATUS =…” failed with the following error: “ORA-01795: maximum number of expressions in a list is 1000“. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.

Solution

You’ll have to select subsets of 1000 IDs from the table, and would have to arrange it under an OR clause

--- Split AND clause to OR
WHERE MESSAGE_ID IN (40766,40767,40768,40769) -- MAX: 1000
OR MESSAGE_ID IN (40770,40771,40772) - Next 1000

Complete code under the script block is as follows:

string sqlUpdateCommand = "UPDATE {0} SET STATUS = '{1}', COMMENTS = '{2}', LAST_MODIFIED_DT = '{3}' WHERE MESSAGE_ID IN ({4})" + Environment.NewLine + "GO";
            string updateCommands = String.Empty;
            string sqlUpdateCommandLarge = "UPDATE {0} SET STATUS = '{1}', COMMENTS = '{2}', LAST_MODIFIED_DT = '{3}' WHERE {4}" + Environment.NewLine + "GO";                    
 
 
            HashSet<string> successMessageIdList = new HashSet<string>();
            for (int i = 0; i <= 1000; i++)
                successMessageIdList.Add(i.ToString());
            
 
            if (successMessageIdList.Count < 1000)
            {
                updateCommands += String.Format(sqlUpdateCommand,
                                          "tableName",
                                          "COMPLETED",
                                          "Transfer completed",
                                          DateTime.Now.ToString("yyyy-MMM-dd HH:mm:ss"),
                                          String.Join(",", successMessageIdList.ToArray())
                                         ) + Environment.NewLine;
            }
            else
            {
                /*
                 * 
                    .... 
                    WHERE MESSAGE_ID IN (40766,40767,40768,40769)
                    OR MESSAGE_ID IN (40770,40771,40772)
                 * 
                 */
 
 
                List<string> successMessageList = successMessageIdList.ToList();
                StringBuilder messageIds = new StringBuilder();
                int ctr = 0;
 
                messageIds = messageIds.Append("MESSAGE_ID IN (");
                for (int i = 0; i < successMessageList.Count; i++)
                {
 
                    if (ctr == 499)//500 items in list; 0 based index
                    {
                        messageIds.Remove(messageIds.Length - 1, 1); //Remove last comma
                        messageIds = messageIds.Append(")" + Environment.NewLine + "OR MESSAGE_ID IN (");
                        ctr = 0;
                    }
 
                    messageIds = messageIds.Append(successMessageList[i] + ",");
                    ctr++;
                }
 
                messageIds.Remove(messageIds.Length - 1, 1); //Remove last comma
                messageIds = messageIds.Append(")");
                updateCommands = String.Format(sqlUpdateCommandLarge,
                                               "tableName",
                                               "COMPLETED",
                                               "Transfer completed",
                                               DateTime.Now.ToString("yyyy-MMM-dd HH:mm:ss"),
                                               messageIds);

Happy coding !