Data Replication 2
Results 1 to 2 of 2

Thread: CREATE VIEW statements issued with SELECTs; multiple instances of SELECT sent to db

  1. #1
    T. Wong is offline Junior Member
    Join Date
    Jun 2011
    Posts
    6
    Rep Power
    0

    CREATE VIEW statements issued with SELECTs; multiple instances of SELECT sent to db

    Hello,

    We are using the Microsoft SSIS Teradata Connector v2.0 (for SSIS 2012) and v1.2 (for SSIS 2008/2008R2).

    When we use the Teradata Source to extract data from Teradata (i.e., source statement is a SELECT statement) our Teradata DBA is seeing:

    1) CREATE VIEW statements being issued to Teradata, presumably issued behind the scenes by the Attunity Teradata Connector, where the CREATE VIEW statement is in the form:

    CREATE VIEW SSIS_YYYYMMDDHHMMSS_NNN AS (<SELECT sourcequery>)

    We have read-only access to the Teradata database, so these CREATE VIEW statements fail, but the extraction still works/succeeds. Can you help us to explain why the Attunity Connector is issuing the CREATE VIEW statements before the TPT Export, their potential benefits it those CREATE VIEW statements succeed, and the implications when the statements fail (due to lack of db privileges to create views)?

    I have attached a screenshot provided by the Teradata DBA that shows the SQL that Teradata sees.

    2) Secondly, a single SELECT defined with a Teradata source in SSIS apparently results in multiple (six) instances of the SELECT statement being issued to the Teradata database.

    Please see the same attached screenshot. The pertinent statement is the SELECT ... FROM ha_hap_com_laborder_p. From our SSIS developer perspective, the query should be issued once, but according to the Teradata logs provided by the Teradata DBA the query is issued six times. Could you help us to explain why multiple SELECTs are sent to the database?

    Any explanations you can provide to better understand the statements sent to the database with a SELECT/extraction using the Teradata Attunity Connector would be most appreciated. The need arises in troubleshooting performance issues, when we work with the DBA to correlate what our SSIS package is doing logically with the statements that the DBA sees that are being issued to the Teradata database.

    Thank you very much.
    Attached Images Attached Images  

  2. #2
    JPQuinn is offline Junior Member
    Join Date
    Feb 2017
    Posts
    6
    Rep Power
    0

    SSIS Extract Package Creating Illegal Views on Teradata in Background

    Hello,

    We too have this same issue with the Create View on TD via SS yet we aren't executing such code.
    *
    *We’ve got a weird thing happening and I wanted to run it past you for thoughts and ideas. We have several times a day, an Attunity provider/Teradata driver connection to Teradata, where we execute a Select statement query to extract data to the SQL Server been running about 1.5 years now.

    **We only extract data and its always done via a query with a select, we do not CREATE/REPLACE tables or views or exec procs, etc So I get notified by a Teredata DBA telling us that about 100 times day, for quite some time, our Logon ID used by the SQL Server fails to create views due to lack of privileges.

    **We are not creating views, and when he gave us a sample script, we are now thinking that there some background service that SQL Server may be doing like creating a temp dynamic DateNamed view when it connects to pull that data through, then dissolves when completed.

    **Views it created in code that he gave us that we did not code into the SSIS package: CREATE VIEW SSIS_20170726122537_994 AS ( Sel…

    **Please let me know if my hunch is right, and what we can do about that, or, if Teradata is going to need to learn how to deal with the ID needing to create the temp view from SQL Server.
    *
    Thanks,

    JPQ**

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •