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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 Running a package from excel
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

andypgill
Starting Member

34 Posts

Posted - 02/07/2013 :  06:17:02  Show Profile  Reply with Quote
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

India
52326 Posts

Posted - 02/07/2013 :  06:19:17  Show Profile  Reply with Quote
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/

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 02/07/2013 :  06:20:00  Show Profile  Reply with Quote
I dont understand why you need to trigger ssis execution from excel sheet though.

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

Go to Top of Page

andypgill
Starting Member

34 Posts

Posted - 02/07/2013 :  06:59:39  Show Profile  Reply with Quote
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.

Go to Top of Page

andypgill
Starting Member

34 Posts

Posted - 02/07/2013 :  07:02:00  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 02/07/2013 :  07:02:51  Show Profile  Reply with Quote
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/

Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22864 Posts

Posted - 02/08/2013 :  08:04:54  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
I would say you need to run this in a batch file which is very easy

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

andypgill
Starting Member

34 Posts

Posted - 02/08/2013 :  10:01:35  Show Profile  Reply with Quote
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 ?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 02/08/2013 :  11:35:56  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.22 seconds. Powered By: Snitz Forums 2000