In this article, I will explain about making the SSIS package in SQL Server. We make the SSIS package to export the SQL Server data into Excel file.
For making SSIS package, we have to install the SQL Server Data Tools, first.
For making the SSIS package, follow the below steps.
- Start the SQL Server and login with valid credentials.(I am using SQL Server 2008).
- Select the database from which you want to export the data in Excel file. Now, right click on it and go to Tasks>>Export Data, as show in the image below. It will open a new window.
![new window]()
- Now, click on Next button.
![Next]()
- Now, select data source.
- Data Source : SQL server native client 10.0
- Server Name: Your SQL Server instance name (Here, I have ‘Nirav-pc\sql2008’).
- Select proper authentication mode, either Windows Authentication or SQL Server Authentication.
- Select the database from the dropdown list and click on Next button.
![data source]()
- Now, select the destination data source,
- Data source : Microsoft Excel.
- Now, before continuing, we, first, have to make Excel file and save it to any location in the computer. Then, give this path in the Excel file path.
- In the next field, select your Excel file version. I used Microsoft Excel 2010.
- And, check the “First row has column names” checkbox. So, it will copy your table's column names to the Excel sheet's column name. Then, click on Next button.
![destination]()
- Now, you have to opt for one of the two options. If you want to copy all the column data from a table to your Excel sheet, select the first option, Copy data from one or more table, or views. If you want to copy only some specific columns after applying some filter conditions and joins, select the second option , Write query to specify the data to transfer. Then, click the Next button.
![Next]()
.
Here, if you want to copy data from multiple servers to single excel file, you have to select the second option and write the query, as shown below.
- SELECT * FROM [SERVER1\SQL2012].[DATABASE1DB].[dbo].[tblPayment]
- union all
- SELECT * FROM [SERVER2\SQL2012].[DATABASE2DB].[dbo].[tblPayment]
- Here, we will continue by selecting the first option. Select the table that you want to copy. If you want to change the mapping of the columns between Excel file and SQL Server table, then go to the “Edit mapping” button. This will help you change the data type of the columns in Excel file. Again, click on the Next button.
![Next]()
![Next]()
- Now, the next window will ask if you want to run the SSIS package immediately. Check on it. Here, you have two options for saving the package. Either you can save this on SQL Server or on your local File System. We choose the second option.
You can also add protection to you package. We chose the Encrypt sensitive data with user key option. Continue by clicking on the Next button.
![Next]()
- Give proper name and description to the package and path to save this package file in local file system. Click on the Next button.
![Next]()
- This window gives the basic information about the path of the saved file and more. Click on the Finish button.
![Finish]()
- This window runs the SSIS package and shows the result. The package runs immediately because we opted for it in step 8.
![SSIS package]()
- Now, it will copy the package file to your file system. For checking the SSIS package, first add some data to the SQL Server table, then open the SSIS package file and execute it. After successful execution of the this package, open the EXCEL file and check the sheet. The new data from the SQL table is now copied to the Excel file.
![Excel file]()
Note: Here, if you want to update the excel file automatically after every 1 hour, then make an SQL Server job and give this package file to it. Schedule the time interval for executing the package file, This will update the Excel file every hour, automatically. Making a job in SQL Server, we will learn in new article.
Thanks for reading my article. If you have any question about SSIS package, please mention in the comment section.