Need help with SQL2005 SSIS and ASP.NET code

Question

Need help with SQL2005 SSIS and ASP.NET code

John Masciantoni

Mr or Mrs. 500

Points: 503
More actions
September 3, 2007 at 7:09 am

#70662

I've used the BI module to create a working import job from Excel to an SQL table.
Can anyone point me toward some help on how to execute this using VB in an ASP.NET website?
Thanks,
John

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

You have some options. The simplest way to do this is to use shell and the DTExec command line utility. Look for it in books online - it will be something like:

dtexec /f "c:\pkgOne.dtsx" /conf "c:\pkgOneConfig.cfg"

You can also create a job agent job and use the sp_Start_Job stored procedure to run it. This limits your ability to pass in parameter information, but it works pretty well and gives you job agent logging and error messaging.

Take note of one important thing. If you are using the command line execution, you will need to have your website on an SSIS server. The SQL client tools will not be enough to run an SSIS package if you have any components in the package that are not part of the basic maintenance plan components (which is basically anything). So, if you have your website and SQL server on two different machines, the job agent is probably the easiest approach.

John Masciantoni Mr or Mrs. 500 Points: 503 More actions · Answer 2

Michael,

Thanks. I've tried to use the shell method.

Should I be executing this directly from the VB code or from and EXEC statement in a stored proc? Or does it matter?

Thanks, John

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 3

If the place that the VB code is running (your web server in this case) is also an SSIS server, just call DTExec through the shell from the VB code. Don't make a trip to the database server unless you gain something from it.

If the web server is not an SSIS server, either call xp_Cmdshell through the database server, or (the better alternative), use the job agent and call sp_Start_Job. I call the job agent the better alternative because you do not have to open the large security hole of xp_Cmdshell. However, if you are already using this for anything else, feel free to use it for this as well.

John Masciantoni Mr or Mrs. 500 Points: 503 More actions · Answer 4

Michael,

Will do. I'm glad to know that I am on the right track with this.

Thanks for your knowledeable and rapid help.

John