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
 General SQL Server Forums
 New to SQL Server Programming
 Importing a table from Access
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

andypgill
Starting Member

34 Posts

Posted - 12/21/2012 :  05:37:36  Show Profile  Reply with Quote
Hi

I have an access table that contains two fields. Forecast_month and actual_month.

ON an access for the user enters the month via a text box and this updates the table.

I then want to run my SSIS job that imports the access table into my SQL table.

Looking on the web the best advice appears to be to use dtexec via a stores procedure, then to call the sp from access.

My procedure is

dtexec /f "G:\4.FinMgt\Proj_Forecast\pkg_import_months.dtsx"

When I try to execute I get Incorrect syntax near 'dtexec'.

Does anyone know what I have done wrong and is dtexec the best way to get the access table imported.

Thanks for your help

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 12/21/2012 :  06:36:16  Show Profile  Reply with Quote
where are you calling this from? if you want to call it from sp you need to use xp_cmdshell extended stored procedure

see

http://www.codeproject.com/Articles/27156/How-to-Call-SSIS-Package-from-the-Stored-Procedure

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

Go to Top of Page

andypgill
Starting Member

34 Posts

Posted - 12/21/2012 :  07:02:20  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

where are you calling this from? if you want to call it from sp you need to use xp_cmdshell extended stored procedure

see

http://www.codeproject.com/Articles/27156/How-to-Call-SSIS-Package-from-the-Stored-Procedure

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





Yes I want to call it from a sp.

Have tried the link but am totally confused by the code

set @params = '/set \package.variables[FileName].Value;"\"\\127.0.0.1\Common
\SSIS\NewItem.xls\"" /set \package.variables[CreatedBy].Value;
"\"Chirag\"" /set \package.variables[ContractDbConnectionString].Value;
"\"Data Source=myserver\SQL2K5;User ID=sa;Password=sapass;
Initial Catalog=Items;Provider=SQLNCLI.1;Persist Security Info=True;
Auto Translate=False;\"" /set \package.variables[BatchID].Value;"\"1\""
/set \package.variables[SupplierID].Value;"\"22334\""'


Also I don't want to pass a variable if possible. Just to run the SSIS import.

Thanks

Edited by - andypgill on 12/21/2012 07:03:14
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 12/21/2012 :  07:20:00  Show Profile  Reply with Quote
you dont need to worry about rest of code just look for xp_cmdshell invoke statement


...
declare @ssisstr varchar(8000)
set @ssisstr='dtexec /f "G:\4.FinMgt\Proj_Forecast\pkg_import_months.dtsx"'

DECLARE @returncode int
EXEC @returncode = xp_cmdshell @ssisstr
SELECT @returncode


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

Go to Top of Page

andypgill
Starting Member

34 Posts

Posted - 12/21/2012 :  08:29:10  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

you dont need to worry about rest of code just look for xp_cmdshell invoke statement


...
declare @ssisstr varchar(8000)
set @ssisstr='dtexec /f "G:\4.FinMgt\Proj_Forecast\pkg_import_months.dtsx"'

DECLARE @returncode int
EXEC @returncode = xp_cmdshell @ssisstr
SELECT @returncode


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





Sorry to be a paid I get Description: Unable to load the package as XML because of package does not have a valid XML format. A specific XML parser error will be posted.

Edited by - andypgill on 12/21/2012 08:46:50
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 12/21/2012 :  09:05:38  Show Profile  Reply with Quote
is the package created using same version?

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

Go to Top of Page

andypgill
Starting Member

34 Posts

Posted - 12/21/2012 :  09:38:09  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

is the package created using same version?

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





Yes both in 2008
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 12/21/2012 :  09:46:31  Show Profile  Reply with Quote
is the package in your local machine or server? try giving UNC path instead of local path

------------------------------------------------------------------------------------------------------
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.1 seconds. Powered By: Snitz Forums 2000