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
