locked
SSIS extracting from Oracle and generating .csv file RRS feed

  • Question

  • Hi,

    I have a simple requirement, I will try to explain the below, I would like to know how can I achieve this using SSIS.

    1. I have a table in SQL Server which contains only one column 'LastUpdated' this is of type DateTime. Please note that this table will always contain one row.

    2. In my SSIS Package, I would want to extract the value of LastUpdated

    3. Next, I need to connect to Oracle database and extract values from a table with filter condition of > LastUpdated from SQL Server table.

    4. Export the data to .CSV file

    My Solution:

    I have an Execute SQL Task on my Control Flow and from there, I am able to execute SQL query and extract the value in a variable named @LastUpdDt

    I want to pass @LastUpdDt to Oracle query using ADO.Net source by specifying SQLCommand. Is this the correct approach, if not, can you please suggest me what should I be using. My query should be: Select * from OracleTable where objDate > @LastUpdDt

    Once I get the result set, I need to export the data to .CSV file.

    Thanks for looking into this post.

    Cheers!!!

    Friday, July 5, 2013 11:50 AM

Answers

  • I believe the correct expression would be

    "SELECT * FROM OracleTable WHERE lastupdated > '" + (DT_STR,30,1252) @[User::LastUpdDt] + "'"

    • Proposed as answer by Koen VerbeeckMVP Friday, July 5, 2013 12:13 PM
    • Marked as answer by Mike Yin Thursday, July 11, 2013 5:12 PM
    Friday, July 5, 2013 12:10 PM

All replies

  • Your set-up is quite good. But parameters in the source component can sometimes behave a bit clunky, so I would use dynamic SQL for this.

    Create a string variable and put in the following expression (don't forget to set the property EvaluateAsExpression to True):

    "SELECT * FROM OracleTable WHERE " + (DT_STR,30,1252) @[User::LastUpdDt]

    In your OLE DB source, now use the SQL from variable option, using the variable you just created.
    In order for this to work, you need to use the OLE DB Source component.


    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.

    Friday, July 5, 2013 12:00 PM
  • Hi,

      • Execute SQL Task is fine
      • Second step,

      Create a SSIS String variable, and set the Evaluate as Expression property of it to TRUE. Then in the expressions, set the following

      Replace the variable name with the one in your package.

    "Select * from Table where Date >" + [User::LastUpdatedDate]


    Rajkumar

    Friday, July 5, 2013 12:03 PM
  • Hi,

      • Execute SQL Task is fine
      • Second step,

      Create a SSIS String variable, and set the Evaluate as Expression property of it to TRUE. Then in the expressions, set the following

      Replace the variable name with the one in your package.

    "Select * from Table where Date >" + [User::LastUpdatedDate]


    Rajkumar

    Your expression will fail as you are trying to add a string to a datetime.

    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.

    Friday, July 5, 2013 12:05 PM
  • Hi Koen - Thanks for looking into my query. I have a question. I believe, OLE DB source component and ADO.Net source component both are different components available as part of the SSIS Toolbox. You want me to use OLE DB Source component instead of ADO.Net, right?

    Friday, July 5, 2013 12:06 PM
  • Hi Koen - Thanks for looking into my query. I have a question. I believe, OLE DB source component and ADO.Net source component both are different components available as part of the SSIS Toolbox. You want me to use OLE DB Source component instead of ADO.Net, right?

    Yes I do. They basically do the same thing: extract data from a source, but apparently the ADO.NET source cannot use a variable for the SQL statement. Normally there shouldn't be much difference in performance.

    Be sure to have an Oracle OLE DB provider installed.


    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.

    Friday, July 5, 2013 12:09 PM
  • I believe the correct expression would be

    "SELECT * FROM OracleTable WHERE lastupdated > '" + (DT_STR,30,1252) @[User::LastUpdDt] + "'"

    • Proposed as answer by Koen VerbeeckMVP Friday, July 5, 2013 12:13 PM
    • Marked as answer by Mike Yin Thursday, July 11, 2013 5:12 PM
    Friday, July 5, 2013 12:10 PM
  • Ok - Thanks, I will try this.
    Friday, July 5, 2013 12:11 PM
  • I believe the correct expression would be

    "SELECT * FROM OracleTable WHERE lastupdated > '" + (DT_STR,30,1252) @[User::LastUpdDt] + "'"

    You're absolutely right, I forgot the string literals :)
    Nicely spotted.


    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.


    Friday, July 5, 2013 12:13 PM
  • Hi,

      • Execute SQL Task is fine
      • Second step,

      Create a SSIS String variable, and set the Evaluate as Expression property of it to TRUE. Then in the expressions, set the following

      Replace the variable name with the one in your package.

    "Select * from Table where Date >" + [User::LastUpdatedDate]


    Rajkumar

    Your expression will fail as you are trying to add a string to a datetime.

    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.

    Hi Koen,

    Thanks, I was proposing a solution to create SSIS String Variable(Which will work fine in the expression), But I should have mentioned that needs a cast in Execute SQL Task source command.


    Rajkumar

    Friday, July 5, 2013 12:17 PM