Running a package from excel - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

Running a package from excel

Author

Topic

andypgill
Starting Member

34 Posts

Posted - 2013-02-07 : 06:17:02

Hi All

I'm trying to run a package from an excel macro but really struggling with the syntax.

Sub Button1_Click()
Shell "cmd.exe /c dtexec /sq pkgOne /ser productionServer"
End Sub

My package is is saved in f:\packages\budgetimport.dtsx my server is called 4BPSWSQL1\SQLSQ

I have tried different combinations but get errors each time.

Thanks for your help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-07 : 06:19:17

if you've saved package in filesystem you should be using /File switch option rather than /SQL. /SQL is for SQL Server deployment.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-07 : 06:20:00

I dont understand why you need to trigger ssis execution from excel sheet though.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

andypgill
Starting Member

34 Posts

Posted - 2013-02-07 : 06:59:39

quote:
Originally posted by visakh16

I dont understand why you need to trigger ssis execution from excel sheet though.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I was hoping to enable the user to run a package without actually going into SSIS.

They input a number in cell A1 in excel and that is used to update a field in my SQL table.

andypgill
Starting Member

34 Posts

Posted - 2013-02-07 : 07:02:00

quote:
Originally posted by visakh16

if you've saved package in filesystem you should be using /File switch option rather than /SQL. /SQL is for SQL Server deployment.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Would that make it

Sub Button1_Click()
Shell "cmd.exe /c dtexec /file f:\packages\budgetimport.dtsx /4BPSWSQL1\SQLSQ"
End Sub

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-07 : 07:02:51

why cant it be a part of job?

like have a webform where users will click a button and on backend it will call a stored procedure which will execute ssis package using a dtexec called from xp_cmdshell

see

http://www.mssqltips.com/sqlservertip/2135/run-ssis-using-xpcmdshell-in-a-sql-server-stored-procedure/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-02-08 : 08:04:54

I would say you need to run this in a batch file which is very easy

Madhivanan

Failing to plan is Planning to fail

andypgill
Starting Member

34 Posts

Posted - 2013-02-08 : 10:01:35

Thanks for your reply

quote:
Originally posted by madhivanan

I would say you need to run this in a batch file which is very easy

Madhivanan

I'm getting closer

If I run

"C:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" /f "G:\4. Financial Mgmt\4.2 Management Accounting\4.2.5 Departmental\4.2.5.5 SQL FEEDS\Proj_Forecast\Proj_Forecast\Budgets 2.dtsx "

from winXP run it works fine.

However If I add this to a .bat file it doesn't work, do I need to amend it slightly ?

Will I be able to call the .bat file from excel ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-08 : 11:35:56

quote:
Originally posted by andypgill

Thanks for your reply

quote:
Originally posted by madhivanan

I would say you need to run this in a batch file which is very easy

Madhivanan

I'm getting closer

If I run

"C:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" /f "G:\4. Financial Mgmt\4.2 Management Accounting\4.2.5 Departmental\4.2.5.5 SQL FEEDS\Proj_Forecast\Proj_Forecast\Budgets 2.dtsx "

from winXP run it works fine.

However If I add this to a .bat file it doesn't work, do I need to amend it slightly ?

Will I be able to call the .bat file from excel ?

why do you need this to be triggred from excel?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources