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

andypgill
Starting Member

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

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

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

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