I need to be able to join various datasets from two different sources. Due to the size of some of the tables involved, I cannot pull the entire tables into work files. In order to limit the data being pulled to only the relevant records (based on a customer number) , I first query one dataset to get a list of customer numbers, populate an object variable, then parse this variable via script task to create a string. The string is saved into another variable, which is then used in the SQLCommand expression on the Data Flow Task.
It is working, but my question is how long of a string can I build and pass through into the expression? From what I have gathered, the character limit for the expression is 4000 characters. Does this mean total characters once the expression has "resolved", that is, the SQL code and the text stored in the string variable? Or is that limitation just the text that is in the Expression Builder box?
The strings below are my expressions. Is this text what is limited to 4000 characters? Or is it this text plus whatever text is stored in the variable? Or is it just what is in the variable?
"SELECT SOURCE_CUSTOMER_ID, SOURCE_SYSTEM, UNIVERSAL_CUSTOMER_NUMBER, RELATED_SOURCE_SYSTEM, RELATED_CUSTOMER_ID
FROM CDBDATA.CSISRCUCN
WHERE SOURCE_SYSTEM = 'BCOE' AND SOURCE_CUSTOMER_ID IN (" + @[User::parmBCOEListString] + ")"
"SELECT CDCD_CUSTOMER_NUMBER, CDCD_FIRST, CDCD_LAST
FROM BCOEDATA.BCCDCDLA
WHERE CDCD_CUSTOMER_NUMBER IN (" + @[User::parmBCOEListString] + ")"
"SELECT CDHD_Customer_Number, CDHD_EMAIL_1, CDHD_EMAIL_2
FROM BCOEDATA.BCCDHD
WHERE CDHD_Customer_Number IN (" + @[User::parmBCOEListString] + ")"
"SELECT VIP_CUST_NO, VIP_TEST_FLAG
FROM BCOEDATA.VIPFILE
WHERE VIP_CUST_NO IN (" + @[User::parmBCOEListString] + ")"
"SELECT U.UNIVERSAL_CUSTOMER_NUMBER, sum(CDCL_CY_DOLLARS) as CYTotal, sum(CDCL_PY_DOLLARS) as PYTotal
FROM BCOEDATA.BCCDCL C INNER JOIN CDBDATA.CSISRCUCN U ON C.CDCL_CUSTOMER_NUMBER = U.SOURCE_CUSTOMER_ID and U.SOURCE_SYSTEM = 'BCOE'
WHERE CDCL_CUSTOMER_NUMBER IN (" + @[User::parmBCOEListString] + ")
GROUP BY U.UNIVERSAL_CUSTOMER_NUMBER"
"SELECT AB_ADDRESS_NUMBER, AB_ADDRESS_LINE_1, AB_ADDRESS_LINE_2, AB_ADDRESS_LINE_3, AB_ADDRESS_LINE_4, AB_ADDRESS_LINE_5, AB_STATE, AB_ZIP_CODE_POSTAL
FROM BORDDTA.F0101LA
WHERE AB_ADDRESS_NUMBER IN (" + @[User::parmAccountListString] + ")"