0

I work with Microsoft Access 2016 with a SQL Server 2008 R2 backend. I have used SQL Server Management Studio to create some SSIS package for quick copying of data (right-click DB -> Tasks -> Import/Export).

I was able to create a button in Access to kick off the SSIS package successfully using VBA. I use the Shell function to run the following command: "C:\Program Files (x86)\Microsoft SQL Server\140\DTS\Binn\DTExec.exe" /F "\\MyPath\MySSISPackage.dtsx". This works on my machine because I'm running my particular instance of DTExec.exe to execute the SSIS package.

I need to set up users' Win10 64-bit computers now so that this process will work for them as well. I don't know if DTExec was installed when I installed SSMS or my local SQL Server Express Edition DB, but the user doesn't need either of these programs. What is the bare minimum I need to install on the users' computers for it to work for them also?

I've done a lot of googling but either the scenario didn't quite fit mine, or I just wasn't understanding what they were talking about. The users will have Microsoft Access only as a start.

Links to download locations would be extra appreciated!

Thanks!

Michael Bruesch
  • 632
  • 7
  • 23

2 Answers2

1

For running SSIS packages in a production environment you must install and license SQL Server. A user who only has Microsoft Access installed cannot run SSIS packages.

You can install and run SSIS packages on your SQL Server (Standard Edition or higher), but not on your clients' PCs.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
  • I don't have control of the server, I have to tell the DBAs what I want. Currently we have SQL Server 2008 R2. Can I check from SSMS if the server has Integration Services already installed? Would they need to install something for it to work? Sorry for so many questions, I just need to be clear before asking for something. – Michael Bruesch Nov 14 '18 at 15:46
  • 1
    SSIS is an optional component when installing SQL Server, and you'll need to ask the DBAs if it is installed. – David Browne - Microsoft Nov 14 '18 at 16:06
  • Thanks David. I'm checking with them to gather more info. I may be back with more questions. :) – Michael Bruesch Nov 14 '18 at 16:09
  • 1
    Well @Michael while your at it you will also need to have service account created because in near future with DBA help this SSIS dtsx will reside on Integration services database. And either your or DBA will have to create a sql server job to execute this job either on demand or on set schedule. Next steps. . – junketsu Nov 14 '18 at 20:43
  • @junketsu DBA told me we do have SSIS running on the SQL Server. I tried connecting through the IS Deployment Wizard but am getting error "Cannot open database "SSISDB" requested by the login. The login failed.". I also do not see SQL Server Agents in my SSMS Object Explorer which I think I need to create a job? – Michael Bruesch Nov 14 '18 at 21:31
  • 1
    SSISDB is the Integration Services Catalog database, introduced in SQL 2012. You'll need to use the older package deployment methodology, probably storing the packages in MDSB. – David Browne - Microsoft Nov 14 '18 at 22:17
  • Thanks David and @junketsu for the advice. I've decided to push for a SQL Server upgrade from 2008 R2 to 2017 and start over with SSIS on a newer version. – Michael Bruesch Nov 15 '18 at 14:02
  • 1
    Good idea. SQL 2008 goes out of support next summer, July of 2019. – David Browne - Microsoft Nov 15 '18 at 14:19
  • 1
    gave you steps below for deployment in VS2017 once your there. Expect you get your data ETL-ed quick. – junketsu Nov 15 '18 at 15:24
1

Once you have credentials all sorted out with DBA team. At min you will need read (select permission) on desired database(s) . Build your ssis in VS2017 locally and test. It will run great I'm sure. And when your ready to deploy you can use steps below. Steps below outline package deployment mode.

enter image description here

enter image description here

enter image description here

enter image description here

junketsu
  • 533
  • 5
  • 17
  • This is great! Thanks so much for the additional info. Marking this as a favorite so I can come back for reference when we've upgraded. Cheers! – Michael Bruesch Nov 15 '18 at 15:50