March 16, 2016 at 6:05 am
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] + ")"
March 16, 2016 at 6:17 am
If you are open to alternative ideas, have you considered creating a table of 'selected customer numbers', which can be repopulated every time the job runs?
The table, or tables, would reside on your target instances.
Logic would be
1) Truncate selected customer number table(s)
2) Populate selected customer table(s) (use one or more data flows)
3) Get selected data
Select ...
from TargetTable
join SelectedCustomerNumber on CustomerNumber
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 16, 2016 at 7:54 am
The source tables reside on different servers. One server is AS400 (6 tables, many contain over 10 million records), one server is MySQL (one table). SQL Server 2008 is my "go between". I need to join that MySQL table to the AS400 tables to resolve customer number to personal info, and associated account number (resolved by one of the AS400 tables) to organization info. So writing the relevant accounts to a table in SQL Server is not an option because then i am still needing to cross from one server to another.
March 16, 2016 at 10:45 am
pjk_tgk (3/16/2016)
The source tables reside on different servers. One server is AS400 (6 tables, many contain over 10 million records), one server is MySQL (one table). SQL Server 2008 is my "go between". I need to join that MySQL table to the AS400 tables to resolve customer number to personal info, and associated account number (resolved by one of the AS400 tables) to organization info. So writing the relevant accounts to a table in SQL Server is not an option because then i am still needing to cross from one server to another.
I was not suggesting writing the data to SQL Server; I was suggesting writing it to wherever the query needs to run. But no doubt you are not in a position to make physical changes to all of these databases, so this idea is not going to work.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 16, 2016 at 10:53 am
Ah, gotcha. You are correct. I would not be able to write to the AS400 source. I could only write to the SQL server and the MySQL server.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply