Hi All, Vision: "SSIS - Tips and Tricks" is a space for sharing the tips and tricks. Each of us unearths some work-around or tips with great effort. Let's share it with others who sail in the same ship. It may make our life easier and better. Unlike other posts where - You explain problem; Ill give solution, "SSIS - Tips and Tricks" is a place for I had a problem; and I got a solution. Mission: Post only the problems for which you know definitive solutions. It is a place for giving help; Post both the problem and solution(s) Do not post just the problem; it is not a place for asking help. Regards, Jose Varghese josekonoor@yahoo.com

Problem: I use OLEDB provide for Oracle. There isa Lookup (reference table), which gets input from an OLEDB source. Even though I have matching records in the reference table, I get the following errors Error: [LKP_HoleId [1512]] Error: Row yielded no match during lookup. [LKP_HoleId [1512]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "LKP_AroraId" (1512)" failed because error code 0xC020901E occurred, and the error row disposition on "output "Lookup Output" (1529)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure. Cause: This problem occurs because of the mismatch between the Oracle Data types and SSIS data types. Solution: To solve this,avoid the use of direct reference table. Instead, "use results of an SQL query" as reference table. In this query join the reference table with the source table with all the join conditions that you specify under the "Columns" tab. This way, SSIScanuse proper data types in reference table that match with the source table. Regards, Jose Varghese

Problem: I am using Oracle OLEDB provider for accessing Oracle database from SSIS. Using and "Execute SQL Task", I call an Oracle stored procedure resulting in the following error. Error: [Execute SQL Task] Error: Executing the query "BEGIN schemaname.spname(parameter1,parameter2); END; " failed with the following error: "ORA-02074: cannot ANALYZE TABLE in a distributed transaction ORA-06512: at "SYS.DBMS_DDL", line 245 ORA-06512: at "SYS.DBMS_UTILITY", line 472 ORA-06512: at line 1". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Cause: The Oracle stored-procedures dealing with distributed transactions do not work fine with OLEDB provider for oracle. Solution: Steps: 1 -Use ODBC provider and configure a system DSN. 1.a -Goto settings\controlpanel\admin tools\datasources(odbc) 1.b - Take the "systemDSN" tab 1.c - Use OLEDB provider for oracle (eg: oracle in orA102) 1.d - Take the "workarounds tab" 1.e - "Disable Microsoft Transaction Server" - Clear the check in this box to enable Microsoft Transaction Server (MTS) support. By default, MTS support is disabled. 2 - Use the ODBC connection manager in SSIS and configure it to use the DSN created in step1. 3 - Use "Execute SQL Task", to call the oracle stored procedure in ODBC syntax Regards, Jose Varghese 17-Sep-2008 josekonoor@yahoo.com

Problem: How to call an Oracle Stored Procedure using ODBC/OLEDB driver from SSIS. Solution: OLEDB Syntax: BEGIN dbms_misc.analyze_data('APAZE','ADDON');END; ODBC Syntax:{CALL dbms_misc.analyze_data('APAZE','ADDON')} Regards, Jose Varghese

Hi All, I also have list of Tips and Tricks in developing SSIS http://randypaulo.com/2011/06/27/ssis-tips-tricks-best-practice/