Use Integrated Service Package in Sql Server

I am using the SQL Server Import/Export Wizard to import data from an Excel file into a table. I will need to do this exact import many times so I selected the option "Save SSIS Package." I tried saving to SQL Server and I tried saving to File System. Either way, I cannot figure out how to use the package in SQL Server. I read that I need to use Agent Job Task to do it but I cannot find that anywhere in the SQL Server program. I also looked for Integrated Services in SQL Server and I found an icon that doesn't seem to do anything.

How do I go from having a .dtsx somewhere on my computer to executing the import (in a query preferably) without having to go through the whole wizard process again and again?

Thank you

Solution

The Import/Export Wizard creates a file called .DTSX. This is your SSIS package. An SSIS package is a text file filled with a bunch of XML. Contrast this with something that ends in .EXE/.COM which is an executable that any windows machine will be able to run.

We need something to consume that .dtsx file and perform the ETL operations described therein. There are three options open to you: dtexec.exe, dtexecui.exe, and custom .NET code. dtexecui is just a graphical wrapper to the command line so really, your choices for running packages are the prebuilt command line utility or custom code. Running a package via dtexec can be as simple as dtexec.exe /file C:\MyPackage.dtsx

Since you want to run this from the context of SSMS, then you'll need to either Create a SQL Agent Job or create a stored procedure that calls dtexec. Since you're new to the whole business, the Job will provide the lowest barrier of entry.

In your SSMS, connect to an instance and navigate to the bottom. You should see "SQL Server Agent" Expand that node and Right-Click on "Jobs" and choose New Job. In the Steps tab, click New.... Change the Type to "SQL Server Integration Services Package" enter image description here Pick your source (file system or SQL Server) Fill out the server name or where the package lives and you should be good to go.

One very important thing you will need to know is that Excel drivers are only going to work in 32 bit mode. This means you will need to use the dtexec.exe that exists at C:\Program Files (x86)\Microsoft SQL Server\XXX\DTS\Binn\dtexec.exe To get that behaviour from the SQL Agent job, then you will need to add the /X86 option to the command line like

enter image description here

The x86 flag only works for SQL Agent job calls. From the command line, you must use the correct version of dtexec.exe