Today I used SSIS packages for the first time. SSIS packages are used by SQL Server Integration Services. There are a couple things I have found about SSIS packages that I want to write about.

I am using an SSIS package to copy data from an Access database to SQL Server. This SSIS package can be executed on a schedule, or from a web browser. I couldn’t figure out how to execute an SSIS package, so I found some excellent instructions on this blog.

1. I believe you can only run SSIS packages on SQL Server 2005. SQL Server Integration Services doesn’t come with SQL Server Express. 
    
2. In order to run my SSIS package, I needed to have xp_cmdshell enabled in SQL Server Surface Area Configuration.
    
3. I can execute the stored procedure from VB or CS code in ASP.Net, but it is more secure to hard-code the command line in a Stored Procedure and call that from the VB or CS code.
    
4. The command line I use is:
   

   Declare @SSISPackage varchar(50)
   exec master..xp_cmdshell ‘dtexec /F “‘ + @SSISPackage + ‘”‘

5. To run this stored procedure, the user must have access to the mssqlsystemresource database. Thus, as far as I know, you have to log in with the sa account. I use the sa account only on this one page that needs it. Everywhere else on this site cannot access the sa account. If you don’t have privileges, you will get this message:
   The EXECUTE permission was denied on the object ‘xp_cmdshell’, database ‘mssqlsystemresource’, schema ‘sys’.
   

These are just my notes for SQL Server Integration Services. SSIS allows me to easily perform this import process very easily.

Zachary Lyons