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.

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.

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?