Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2005

    Unanswered: Advantage of SSIS Package over OpenRowSet/OpenDataSource

    I have this project that do these tasks using SSIS package (.dtsx file)
    1.) Imports data from excel file
    2.) Inserts these data to SQL tables
    3.) Formulate a query, then export the result set of the query to Excel File

    I'm executing this SSIS packages from a DOS command prompt.

    Later did I find out that there is this "OpenRowSet" and "OpenDataSource" thing that I can do in my SQLServer stored procedure to do the same task. And call this stored procedure from a command promt. Correct me if im wrong, can i really call an SP or job from a DOS commnd prompt?

    Now my questions are:
    What are the advantages of SSIS package over OPenRowSet/OpenDataSource?
    Which is easier to maintain?

    I'll appreaciate your reply.

    Thanks a lot.
    Last edited by LimaCharlie; 07-02-09 at 04:17.

  2. #2
    Join Date
    Jun 2004
    Long Island
    Simple, create a .bat/.cmd file with the following:

    sqlcmd -E -SServer -Q"exec sprochere 'parameter1', 'parameter2' " -ddatabase

    I'd rather go the stored procedure way, more portable and easier to maintain.

  3. #3
    Join Date
    Oct 2005
    thanks for your reply. =)

    i'm already finish w/ the project. what i'm doing right now is to defend why I used SSIS package. I can't find any info on the advantage of SSIS over openrowset.

    Anyway, how about the performance? Which is faster in importing and exporting data to and from SQL Server?

Posting Permissions

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