SQL Server Forums
Profile | Register | 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		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

andypgill
Starting Member

32 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
48076 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
48076 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

32 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

32 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
48076 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
22469 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

32 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
48076 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  
 New 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.09 seconds. Powered By: Snitz Forums 2000