Creating a SQL Connection in SSIS

First you need to have a database already if you don’t you can download a sample adventureworks2016.bak database or any other data base.

• Open SSMS
• Select windows authentication
• In the object explorer right click on databases, then click on Restore Database

In the window which appears select device and then click on elipses …

• After clicking ellipses click on Add and select the location of the .bak file
• After selecting the location of the file click on it and select ok OR Create an own data base table
• Open SSIS
• And create / open an existing integration project
• Select connection manager on the right in solution explorer

• Right click on it and select new connection
• This window appears in this window select OLEDB

Click on ADD and then this window appears

• Here click on new
• Select the provider
• And type the server name used in SSMS

Select database from dropdown

Click on test connection to check if everything is good

• Click on ok
• The new connection can be seen in connection manager