Using Data Transformation Services - Part 1 - Package creation details
(Page 4 of 5 )
Since a database object is being copied, the Copy SQL Server objects task is the appropriate task to be completed. Drag the Copy SQL Server Objects task from the left panel to the right. This opens up the wizard with three tabs: Source, Destination and Copy. The Source tab requires a description of the task, the server on which the source data is stored, and its authentication information. When all this information is keyed in, the window appears as shown in this picture:
When you click on OK to this window, you will be asked to provide information for the destination Server, where this copied data is to be stored. If a named object already exists, you can access it from the dropdown menu after specifying the database (in this example XPHTEK/Test), or choosing to create a new database.
Since the new database requires a name, you need to create a name. Since the database needs the creation of the data and log files, the needed file sizes can be configured. If the defaults are accepted, as in this case, each of them will be 2MB. Clicking on the Help button will open the context sensitive help item of creating a database from the SQL Server book on line. The name of the database chosen is [CopiedTest]. When you click on OK, the DTS has all the information (source and destination) for creating the database.
In the next tab, Copy, a number of parameters are configurable. Copying tables, data, stored procedures, referential integrity constraints, indexes, and so forth will be carried out. If Data already exists (in this example, new data is created and therefore is not relevant), it can copy over, or append. If you choose not to copy all objects, by deselecting the checkbox you will be able to select objects that you need to copy (it is assumed that all objects are copied in this simple example) and if you choose not to use default options, by unchecking this, you may be able to specify the options you want. The chosen configurations for this example are shown in the next picture. The creating database script will be stored in a file directory, the default being, "C:\Program Files\Microsoft SQL Server\80\Tools"
When you click on OK in the final tabbed window, the package creation will be completed. You will be able to see the package, cpTestWiz_to_XPHTEK/Test, in what was an empty design panel to start with, as shown:
Now this package can be saved and given a name [Jan2005_1] as shown here, by bringing up this window and clicking the menu item package, and using the SaveAs option.
Now the newly created package appears in the list of Local packages as shown in the next picture:
Next: Executing the package/step >>
More MS SQL Server Articles
More By Jayaram Krishnaswamy
/ 37 
