23
Oct
Microsoft Visual Studio

Writing a basic SSIS package

Install Visual Studio

You need a development environment to create your SSIS packages. Microsoft provide the popular free IDE, Visual Studio. Once you install this you can add support for SQL Server Integrated Services and start creating your packages. Download Visual studio from https://visualstudio.microsoft.com/downloads/
Select the free ‘Community’ edition.
Once you LAUNCH VS, select ‘Continue without code’ to skip the initial welcome screen.

Add the SQL Server integration Services Projects extension to VStudio.

Once downloaded (from within a bowser). run the installer. ** Make sure you close all other SQL Server windows.
This bit can take a while so go to the gym, finish a level of Call Of Duty or take the dog for a walk 🙂

Writing our first simple package

Create a new Integrated Services Project. I called mine BackupAndArchive This will be a very simple example.

Backup DB

Add elements to design window by dragging and dropping

double-click on the back up database task and add a connection, set the database you want and set any other important settings. I chose to

  • backup database AdventureWorks2014
  • use compression
  • backup to disk, c:\MSSQL_Backups\AdventureWorks2014_backup.bak

Archive files

Now, double click on ‘File System Task’. Modify the values of the DestinationConnection (existsing folder) and SourceConnection to move the from from C:\MSSQL_Backups to C:\Archive_Backups

Finally, you will end up a very simple project to backup a database and move it to a new folder. Test using the Start button and make sure it works. Any issues will be displayed in the Debug window and

Progress should look like this

Conclusion

This is a very simplistic example and has no error checking but that is not the purpose of my example. i just want to show how to get started with SSIS. In the next part of this series, we will look at running a package from SQL Server.

Leave a Reply

Your email address will not be published. Required fields are marked *