Importing a table from Access - 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

General SQL Server Forums

New to SQL Server Programming

Importing a table from Access

Author

Topic

andypgill
Starting Member

34 Posts

Posted - 2012-12-21 : 05:37:36

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

52326 Posts

Posted - 2012-12-21 : 06:36:16

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/

andypgill
Starting Member

34 Posts

Posted - 2012-12-21 : 07:02:20

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-21 : 07:20:00

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/

andypgill
Starting Member

34 Posts

Posted - 2012-12-21 : 08:29:10

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-21 : 09:05:38

is the package created using same version?

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

andypgill
Starting Member

34 Posts

Posted - 2012-12-21 : 09:38:09

quote:
Originally posted by visakh16

is the package created using same version?

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

Yes both in 2008

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-21 : 09:46:31

is the package in your local machine or server? try giving UNC path instead of local path

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources