none
Using OPENQUERY against a linked server in SSIS? RRS feed

  • Question

  •  

    I'm trying something that should be simple. I want to set up a data refresh package using the SSIS Import and Export Wizard.

     

    My source is Sage's MAS200 which is written on top of the ProvideX "DB" (a DBA's nightmare). Unfortunately, there is NO ProvideX ODBC driver that exposes column names although the tables names ARE available.

     

    I'm forced to create a linked server and then use OPENQUERY to get at the data. So, something like SELECT * FROM OPENQUERY(LINKEDTABLE, 'SELCT * FROM AR_Division') works fine.

     

    Now I'm trying to build an SSIS package. I'm using the .Net Framework Data Provider for ODBC to get to the source data and it works just as I've explained above. It shows me the tables but gives me know mapping information because it doesn't show me the columns.

     

    So, I try the query approach, but the OPENQUERY can't be parsed.

     

    Do any of you have any ideas?

    Tuesday, October 7, 2008 3:51 PM

Answers

  • If the query doesn't return valid metadata, then you won't be able to use it in the data flow.

     

    Try this: Use the OPENQUERY in an Execute SQL task to populate a working table on the SQL Server side - something like :

    Code Snippet
    SELECT * INTO MyWorkingTable FROM OPENQUERY(LINKEDTABLE, 'SELCT * FROM AR_Division')

     

     

    Then use a data flow to read the information from the working table and do any necessary transformations on it.

    Thursday, October 16, 2008 2:46 PM
    Moderator

All replies

  • Can anyone point me in the right direction on this?  Please!?  I haven't bet my job on SSIS, but I have said that I'd be able to build a warehouse using it and almost all of the WH data is coming from this linked server.

     

    Thanks...

     

    Wednesday, October 8, 2008 1:07 PM
  • I know nothing at all about your source.

    To this point, I've done much more work using ADO DataTables than SSIS. When column headers are missing and/or column names can be changed easily (by idiots) - I would use Ordinals to grab specific data.

    I do not know if this is possible given your situation - but if it is something you haven't thought of, you may want to look into it.

    Another thought - can you set up anything on the source side that might do some of the work towards creating a view of the data that might work better for you?

    Sorry, nothing concrete here, just thoughts.

    Scott
    Sanford, Maine
    Wednesday, October 8, 2008 1:25 PM
  • Thanks for the thoughts, Scott.

     

    As I mentioned, ProvideX (and, therefore, MAS 90 and MAS 200, both of which are accounting packages) is a DBA's nightmare.  There is no admin tool that let's a DBA get into the DB's schema, so I'm SOL as far as a view goes.

     

    What I need is on the SSIS side: How can I use OPENQUERY against the linked server that points to the ProvideX DB?

     

    I'm hoping the MS might have a thought.

     

    Wednesday, October 8, 2008 2:19 PM
  • If the query doesn't return valid metadata, then you won't be able to use it in the data flow.

     

    Try this: Use the OPENQUERY in an Execute SQL task to populate a working table on the SQL Server side - something like :

    Code Snippet
    SELECT * INTO MyWorkingTable FROM OPENQUERY(LINKEDTABLE, 'SELCT * FROM AR_Division')

     

     

    Then use a data flow to read the information from the working table and do any necessary transformations on it.

    Thursday, October 16, 2008 2:46 PM
    Moderator
  • This is a GREAT idea, John. Thanks for taking the time.

     

    Thursday, October 16, 2008 4:20 PM
  • I am sure by now you have this figured out but I was about looking for a good insert into for a sql table coming from a MAS200 table where the column names are different. MAS does provide a dictionary with the SQL names exposed and here is the link.....

    http://support.sagesoftwareonline.com/mas/file_info/4.10/WebHelp/FileLayouts.htm 

    You can also used Data Display and Maintenance to get a clue as to the format of the data.
    Tuesday, July 28, 2009 8:08 PM
  • If the query doesn't return valid metadata, then you won't be able to use it in the data flow.

     

    Try this: Use the OPENQUERY in an Execute SQL task to populate a working table on the SQL Server side - something like :

    Code Snippet
    SELECT * INTO MyWorkingTable FROM OPENQUERY(LINKEDTABLE, 'SELCT * FROM AR_Division')

     

     

    Then use a data flow to read the information from the working table and do any necessary transformations on it.


    Thanks for this suggestion.  I was looking for help, with a similar issue and found this.

    -Al H

    Monday, May 5, 2014 2:53 PM