ssis 超过 4000 个字符 [英] ssis more than 4000 chars

问题描述

我的来源 - 甲骨文；目标 - SQL Server

My source - Oracle ; Destination - SQL Server

要求 - 我必须从目标服务器获取 empid 并将相同的 empid 传递给 oracle 并获取数据

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

方法，我正在使用...我正在构建一个字符串(逗号分隔)包含 empid 的 -1,2,3,...10000.字符串的长度 10000(将来可能会增加).如何将此字符串传递给位于 oracle 和变量中的数据流任务.这意味着，在 DFT 中，我从类似这样的变量中选择这个字符串......select * from emp where empid in"+ @[user::empid]+ "

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]+ "

挑战 - 我看到表达式限制是 4000.如何将 4000 再次发送 4000 多次到数据流任务.我无法进行用户合并、查找或 forloop，因为它会影响性能.我也在这里发帖...

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...

有关此问题的更多说明

推荐答案

4k 限制，在 SQL Server Integration Services 2012 版中删除，仅适用于表达式.

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

因此，如果您必须通过将所有这些用户 ID 连接在一起来构建源查询来沿着您当前正在旅行的路线走下去，请停止使用表达式并在脚本任务中执行字符串连接.

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.

代码近似

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

POC

我创建了一个简单的包.使用定义为 QuerySource 的变量连接到数据流的脚本任务，并使用以下逻辑构建一个长字符串，然后对表进行查询.

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;

执行时，这里的屏幕截图显示了变量/查询中大于 4k 的字符.

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

在我的数据流中，我使用来自变量的 SQL 命令"，因为鉴于我们正在使用变量，这是唯一有意义的......

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...

这篇关于ssis 超过 4000 个字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！