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.
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
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
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.