ssis more than 4000 chars

Question

My source - Oracle ; Destination - SQL Server

Requirement - I have to fetch empid's from destination server and pass same empid's to oracle and get data

Approach, am using...i am building a string(comma seperated by) contains empid's -1,2,3,...10000. The length of the string 10000 (it may increase in future). How do I pass this string to data flow task which is in oracle and in a variable. It means, in DFT, i am picking this string from variable something like this..."select * from emp where empid in " + @[user::empid]+ "

CHallenge - I see expression limit is 4000. How do I sent 4000 once again 4000 in multiple times to data flow task. I cannot user merge, lookup or forloop as it is hitting performance. I have posted here as well...

for more description about this issue

Answer 1

The 4k limit, which is removed in the 2012 release of SQL Server Integration Services, only applies to Expressions.

Therefore, if you must go down the route you are currently traveling by building out your source query by concatenating all of those user ids together, stop using an Expression and perform the string concatenation in a Script Task.

Code approximate

Dts.Variables[SourceQuery].Value = string.Format("select * from dept where dept in ({0}), Dts.Variables[EmpList].Value.ToString());

POC

I have created a simple package. A script task connected to a Data Flow with a variable defined as QuerySource and use the following logic to build out a long string which will then query against a table.

            // 551 characters
            string baseQuery = @"
SELECT
    AC.object_id
,   AC.name
,   AC.column_id
,   AC.system_type_id
,   AC.user_type_id
,   AC.max_length
,   AC.precision
,   AC.scale
,   AC.collation_name
,   AC.is_nullable
,   AC.is_ansi_padded
,   AC.is_rowguidcol
,   AC.is_identity
,   AC.is_computed
,   AC.is_filestream
,   AC.is_replicated
,   AC.is_non_sql_subscribed
,   AC.is_merge_published
,   AC.is_dts_replicated
,   AC.is_xml_document
,   AC.xml_collection_id
,   AC.default_object_id
,   AC.rule_object_id
,   AC.is_sparse
,   AC.is_column_set
FROM
    sys.all_columns AS AC
WHERE
    AC.object_id IN (0{0});";
            System.Text.StringBuilder sb = new System.Text.StringBuilder();
            for (int i = 0; i < 1000; i++)
            {
                sb.Append(',');
                sb.Append(i);
            }
            string queryFinal = string.Format(baseQuery, sb.ToString());
            MessageBox.Show(queryFinal.Length.ToString());
            Dts.Variables["QuerySource"].Value = queryFinal;

When executing, here's a screen shot showing the greater than 4k characters in the variable/query.

Inside my Data Flow, I use "SQL command from variable" as that's the only thing that will make sense given that we're using a variable...