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
 SQL Server 2012 Forums
 SSIS and Import/Export (2012)
 executing an SSIS package with parameter from a sp

Author  Topic 

mksdf
Starting Member

26 Posts

Posted - 2015-01-27 : 06:12:28
I am trying to do some research into executing a SSIS package from a stored procedure. I have multiple access databases. I need to transfer the tables in these DBs to a database in SQL. The Access DBs change each month. So I ran the Import Wizard to get a dtsx file. I want to write a stored proc that would allow me to define the path of the access database and use a dtsx execution to transfer the tables to the database. I originally tried using openrowset to achieve the same thing but I am not allowed access to this functionality at work. Am I heading down the right path for this? I'm finding the tutorials online are not particularly easy to understand. Any guidance would be greatly appreciated.
Thanks

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-27 : 11:28:56
See here: https://msdn.microsoft.com/en-us/library/jj820152.aspx

and here: http://blogs.msdn.com/b/biblog/archive/2013/05/07/step-by-step-of-executing-ssis-2012-package-through-stored-procedure.aspx
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-27 : 11:35:41
Also here: http://www.sqlservercentral.com/articles/Integration+Services+%28SSIS%29/120925/
Go to Top of Page

mksdf
Starting Member

26 Posts

Posted - 2015-01-28 : 05:57:00
Thank you gbritton

So I currently have

EXEC xp_cmdshell 'dtexec /sq "Test Package" /ser SPROTO0001\SQL200801'

but I am getting the error

Description: SSIS Error Code DTS_E_OLEDB_NOPROVIDER_64BIT_ERROR. The requested OLE DB provider Microsoft.Jet.OLEDB.4.0 is not registered -- perhaps no 64-bit provider is available. Error code: 0x00000000.

Does this mean I need to get Jet.OLEDB.4.0 installed on teh server?

Thanks
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-28 : 12:06:02
yes, thats correct
Go to Top of Page

mksdf
Starting Member

26 Posts

Posted - 2015-01-28 : 12:21:37
that's great. thanks
Go to Top of Page
   

- Advertisement -