none
Variable for attunity connector

    Question

  • We have started to use the attunity connector and have seen a drastic increase in performance on our imports from Oracle into SQL tables.  The problem I'm having is that there are a few imports that are based on a date (snapshot date) and I'm not coming up with any ideas on how to only pull the records based on the date.  The date is in a SQL lookup table and is necessary to restrict the number of records that are pulled down.

    This is what we are currently using and is horribly slow:

    Declare @Dt  Char(10)

    Select @Dt = convert(char(10),TwoAgo_Date,101)
    From SnapShot_Date

    select *
    into ss_ps_Oad_Act 
    From orcl1..SYS.SS_PS_Table
    Where Snapshot_Date = @Dt

    Thursday, November 14, 2013 9:34 PM

Answers

  • You can do this using Property Expressions to dynamically construct the string you want to pass to Oracle.

    Instructions:

    1.  Create a variable of type String, called "snapshotDate"; set it to 20990101

    2.  Create a variable of type String called "OracleSQL"

    3.  Set the value of your OracleSQL variable Expression to:

    "select * From SYS.SS_PS_Table Where Snapshot_Date = '" + @[User::snapshotDate] + "'"

    4.  Make sure the EvaluateAsExpression property of the variable is set to True

    5. In the relevant Data Flow area, click F4 and then expressions.

    6. Set the SqlCommand property to be @[User::OracleSql] eg

    You should now be ready to add the Dataflow Destination.  You will also need a mechanism to populate your date variable with the relevant value earlier on in the package.  I suggest and Execute SQL task with an output variable.  Post back if you need more help with that bit.

    Hope that makes sense!


    NB  I'm using a yyyymmdd format and there is no easy way to do SELECT INTO - the table will have to be there already.

    • Proposed as answer by mhm_2004de Monday, November 18, 2013 6:33 PM
    • Marked as answer by nickswoca Tuesday, November 19, 2013 1:30 PM
    • Edited by wBob Tuesday, November 19, 2013 9:15 PM tidy up
    Monday, November 18, 2013 4:09 PM

All replies

  • I have suspicion the lookup is executed for each row you are trying to pull from the source table. This would explain why it is terribly slow. Try to do trace of the query to see what is being done.

    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

    Monday, November 18, 2013 12:25 AM
  • The Oracle connection is a remote machine and I don't have access to
    run a trace.  The performance of the connector is great and this is the
    approach that I'm going to take.  There has to be some work around in
    the Attunity connector.

    Please advise.

    Monday, November 18, 2013 2:41 PM
  • The Oracle connection is a remote machine and I don't have access to
    run a trace.  The performance of the connector is great and this is the
    approach that I'm going to take.  There has to be some work around in
    the Attunity connector.

    Please advise.

    Ask your Oracle admin for assistance. There is no workaround for crappy SQL. You have to analyze and fix it.

    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

    Monday, November 18, 2013 2:47 PM
  • Hmm.  This:

    Declare @Dt  Char(10)
    
    Select @Dt = convert(char(10),TwoAgo_Date,101) 
     From SnapShot_Date
    
    select *
     into ss_ps_Oad_Act  
     From orcl1..SYS.SS_PS_Table 
     Where Snapshot_Date = @Dt 
    

    is TSQL using a linked server.  So the Attunity connector is not involved in any way.  If you want to use the attunity connector, you'll need to write an un-parameterized Oracle SQL query in SSIS and use that in a data flow.

    The reason your performance is bad might be that your predicate 'Where Snapshot_Date = @Dt' is not being pushed to Oracle and you are evaluating it after bringing the roles over.  You might try configuring the linked server as "collation compatible", and otherwise you'll need to replace the parameterized query with a dynamic SQL query you send to oracle.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Monday, November 18, 2013 3:02 PM
  • You can do this using Property Expressions to dynamically construct the string you want to pass to Oracle.

    Instructions:

    1.  Create a variable of type String, called "snapshotDate"; set it to 20990101

    2.  Create a variable of type String called "OracleSQL"

    3.  Set the value of your OracleSQL variable Expression to:

    "select * From SYS.SS_PS_Table Where Snapshot_Date = '" + @[User::snapshotDate] + "'"

    4.  Make sure the EvaluateAsExpression property of the variable is set to True

    5. In the relevant Data Flow area, click F4 and then expressions.

    6. Set the SqlCommand property to be @[User::OracleSql] eg

    You should now be ready to add the Dataflow Destination.  You will also need a mechanism to populate your date variable with the relevant value earlier on in the package.  I suggest and Execute SQL task with an output variable.  Post back if you need more help with that bit.

    Hope that makes sense!


    NB  I'm using a yyyymmdd format and there is no easy way to do SELECT INTO - the table will have to be there already.

    • Proposed as answer by mhm_2004de Monday, November 18, 2013 6:33 PM
    • Marked as answer by nickswoca Tuesday, November 19, 2013 1:30 PM
    • Edited by wBob Tuesday, November 19, 2013 9:15 PM tidy up
    Monday, November 18, 2013 4:09 PM
  • Correct, that is using linked server.  We have/had several linked server jobs that pulled data from a oracle source and found a vast improvement using attunity.

    Monday, November 18, 2013 5:53 PM
  • >We have/had several linked server jobs that pulled data from a oracle source and found a vast improvement using attunity.

    No.  The Attunity connector is an SSIS component, not an OleDB provider. So it is not involved in linked server connections.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Monday, November 18, 2013 5:57 PM
  • This looks like exactly what I'm trying to do.

    How do I reference the snaphotdate variable in SQL command?


    • Edited by nickswoca Monday, November 18, 2013 8:40 PM
    Monday, November 18, 2013 8:38 PM
  • Have you tried following the numbered instructions in my post above?  That should get you most of the way there.
    Monday, November 18, 2013 8:54 PM
  • I did but I'm not seeing how this will configure the connection manager.  The properties for the oracle source doesn't contain any connection properties.

    Monday, November 18, 2013 9:21 PM
  • As stated in the instructions, the Expressions properties are in the Dataflow task, ie click F4 when you do not have anything selected. 

    The Connection Manager contains only the Oracle connection string.

    Monday, November 18, 2013 9:24 PM
  • Sorry, user error...

    I need to build the sql task to populate the date.  Would that be a single row result set mapped to the snapshotdate variable?

    Monday, November 18, 2013 10:02 PM
  • I would use an OUTPUT variable, so use ResultSet None, something like this:


    You should double-check that the date logic is behaving correctly.
    • Edited by wBob Monday, November 18, 2013 10:17 PM test test test
    Monday, November 18, 2013 10:17 PM
  • wBob,  thanks for all the help.

    Tuesday, November 19, 2013 1:30 PM
  • Glad you got it working. We've had some superb results with the Attunity connectors too.
    Tuesday, November 19, 2013 3:11 PM
  • I hate to bug you again.  You know any reason why the user variables wouldn't show in the parameter mapping screen.


    • Edited by nickswoca Tuesday, November 19, 2013 3:35 PM
    Tuesday, November 19, 2013 3:32 PM
  • Variables in SSIS have scope depending on where you were when you created your variable. This can be package level or an individual container, eg an Execute SQL task. If you have created a variable with limited scope you won't be able to "see it" in all containers. The easiest thing to do is click the 'Show All Variables' button on the main SSIS > Variables tab. If your variable is not package scope, delete and recreate it whilst you don't have anything in the package selected, ie you are in the Control Flow section of your package and have no containers selected.
    Tuesday, November 19, 2013 3:57 PM
  • That's it. 

    I'm still having issues with the oracle source This look right

    Tuesday, November 19, 2013 5:02 PM
  • No. You have put the variable directly into the SqlCommand. You cannot do that. Please go back over the instructions and the screen prints.
    Tuesday, November 19, 2013 5:13 PM
  • Keep on getting an zero iteration error.

    Tuesday, November 19, 2013 5:40 PM
  • See instruction 5 above.  You have not followed it correctly.
    Tuesday, November 19, 2013 5:48 PM
  • I've done that.

    Tuesday, November 19, 2013 5:56 PM
  • So you've set the expression, but you have left the original SqlCommand text as your variable.  Enter a normal query as the SqlCommand text.  What I did was enter the original statement with a date far in the future.  This will ensure the metadata is available to build the target but the original statement won't return any rows.  Something like this:

    select * From SYS.SS_PS_Table Where Snapshot_Date = '20990101'

    Tuesday, November 19, 2013 6:06 PM
  • That got rid of the red 'x' and when I execute the data flow, the sql command text is the value from OracleSql. 

    But i'm getting a package validation error:

    Tuesday, November 19, 2013 8:26 PM
  • This suggests there is something wrong with your variable expression.  It's actually the variable Expression value you need to set to:

    "select * From SYS.SS_PS_Table Where Snapshot_Date = '" + @[User::snapshotDate] + "'"

    When you click 'Evaluate Expression' in the expression editor, it should look like a valid SQL statement, eg





    • Edited by wBob Wednesday, November 20, 2013 2:40 PM tidy up
    Tuesday, November 19, 2013 9:07 PM
  • Gotcha.  I will look it over and see if I have a syntax issue.

    Thanks again for all the help...

    Tuesday, November 19, 2013 9:32 PM
  • Do you see anything wrong with the syntax?  The evaluation of the expression is always returning the string.

    "select * From SS_PS_Table Where Snapshot_Date = '" +@[User:snapshotDate] + "'"

    Wednesday, November 20, 2013 2:03 PM
  • Yep, there should be two :: in between User and snapshotDate.
    Wednesday, November 20, 2013 2:39 PM
  • I added it and still not evaluating.

    Wednesday, November 20, 2013 2:59 PM
  • You need to set the Expression property of the OracleSQL variable.

    1)  Go to your Variables tab

    2)  Select your OracleSQL variable

    3)  Click the three dots (...) on the Expressions property

    4)  From here, set the Expression property to the text we have discussed.

    5)  When you click 'Evaluate Expression', you should see something similar to my screen print above where I have the Evaluated value displayed.

    Wednesday, November 20, 2013 3:17 PM
  • That did it.  I am able to run the package but get an oracle error about "not a valid month" so I need to get the date format corrected.

    Thanks..

    Wednesday, November 20, 2013 4:56 PM
  • Do you know what SQL date format will work with oracle?  For Example in the slow linked server connection a MM/DD/YYYY format was used.  The oracle data type is a date.

    Wednesday, November 20, 2013 5:37 PM
  • So in my example, as mentioned earlier, I used a yyyymmdd format and that worked for me.

    Remember this query is passed straight through to Oracle so you can test your query in SQLPlus or some other tool.  Get that bit right, then come back to your expression.

    Wednesday, November 20, 2013 5:44 PM
  • Yeah, I'm using SQL tools and none of the options I'm trying are working.  When I output the date in sql tools it is in this format 15.06.2008 00:00:00. 
    Wednesday, November 20, 2013 6:38 PM
  • Can you show me the section of your code where you lookup the snapshot date.  It should look like the my post above which started with "I would use an OUTPUT variable...".

    Wednesday, November 20, 2013 11:01 PM
  • I got it working once I found the correct date format.

    Thanks again for all your help!

    Thursday, November 21, 2013 1:28 PM
  • Good news.  I think it's probably worth you sharing which format you ended up with as a reference for the future?
    Thursday, November 21, 2013 1:49 PM
  • The format that worked for me is the YYYY-MM-DD.

    select ? = convert(char(10), SnapShotDate, 120) From Year_Ago.dbo.Snapdates

    Thursday, November 21, 2013 7:17 PM