Q. How to create SSIS package without using BIDS
A. Method1: We can create a SSIS package by using SQL Server Management Studio(SSMS). Follow the below steps for creating a SSIS package using SSMS.
This package is able to create a copy of SQL Server database.
Execute below SQL Query to create a sample database on SQL Server.
Assuming TestDB is not exist in current database server
Execute below SQL Query to create a dummy database.
Create Database TestDB
- Right click on database select Tasks -> Copy Database... from property window.
- Click next in Copy Database Wizard to skip the welcome window.
- Provide the Source Server details and appropriate log-in information to connect the source server Click 'Next' button to continue.
- In next window we need to provide the destination server details. Provide appropriate information and click on 'Next' button to continue.
- The next window is asking for the method which is used to create a copy of database. The difference in both the method will be soon available in this article. For the time being don't change the default settings. Click 'Next' button to continue.
- In the next window we need to select the database need to copy between two server selected in step 2 and 3. Insure the required database is selected. For this example TestDB must be checked in Copy column. Click 'Next button to continue.
- Configure Destination Database is the next window copy database wizard. In this window we need to provide our new database name. You can also set the name for MDF and LDF files ( to know more about the MDF and LDF files check this article). Don't change the rest of the settings. Click 'Next' button to continue.
- The next window is asking for the package name in which we have to save all the above information. Rename the Package to CopyTestDB. This step is generate a SSIS package. Don't change remaining settings. Click on 'Next' button to continue.
- Next window is asking for the schedule on which we have to execute the SSIS package. Don't change the default settings. Click 'Next' buttons to continue.
- This is the Summary window for all the above steps. Click on Finish to execute your SSIS pakage created by using SSMS. Once the execution is completed click on 'Close' button to close the Copy Database Wizard.
You can find your newly created SSIS package on Integrated Services -> Stored Packages -> MSDB -> <ServerName> -> DTS Packages -> Copy Database Wizard Packages -> CopyTestDB.
Your comments, queries and suggestions are welcome.