Once you are done specifying
all of the data source information, you need to specify the destination. For
my example, I want to place my source data into a SQL Server database table. I
will use the "Choose a Destination" pane below to specify where to place my
data:
One of the new features here
is you now can create a new database on the fly. This is done by clicking on
the "New" button. When you do this, the following screen will be displayed:
On this screen, you can
specify the properties for your new database. Since I want to create a new
database named "Wizard_Example", I specify my database properties on this pane.
When done defining the properties of my new database I click on the "OK" button.
Doing this takes me back to the "Choose a Destination" window, and also creates
my database at the same time. Clicking on the "Next>" button on the "Choose
a Destination" pane will display the window below:
On this screen, you can
click on the "Edit..." button to modify how the Wizard handles populating the
destination table. I my case I am creating a new database table, so I used
this button to verify that the table that is being created is correct. After
clicking on the "Next>" button the following screen is displayed:
Here you can execute the
package immediately and/or save your SSIS package. I will choose to execute
and save my package in a File System. Once I check the "Save SSIS Package"
checkbox, the grayed out options become active and I am able to select the
"File System" radio button. After clicking on the "Next>" button the
following window is displayed:
The SQL Server 2005 SSIS
tool allows five different package protection levels. Here is the SQL Server
2005 Books Online explanation of each of these protection levels:
|
Do not save sensitive
|
Suppresses sensitive information in the
package when it is saved. This protection level does not encrypt, but instead
it prevents properties that are marked sensitive from being saved with the
package and therefore makes the sensitive data unavailable to other users. If
a different user opens the package, the sensitive information is replaced
with blanks and the user must provide the sensitive information.
|
|
Encrypt sensitive with user key
|
Encrypts only the sensitive information
in the package by using keys based on the current user. Only the same user
using the same profile can load the package. If a different user opens the
package, the sensitive information is replaced with blanks and the current
user must provide new values for the sensitive data. If the user attempts to
execute the package, package execution fails. DPAPI is used for this
encryption.
|
|
Encrypt sensitive with password
|
Encrypts only the sensitive information
in the package by using a password. DPAPI is used for this encryption.
Sensitive data is saved as a part of the package, but that data is encrypted
by using a password that the current user supplies when the package is
created or exported. To open the package in SSIS Designer, the user must
provide the package password. If the password is not provided, the package
opens without the sensitive data and the current user must provide new values
for sensitive data. If the user tries to execute the package without
providing the password, package execution fails. For more information about
passwords and command line execution, see dtexec Utility.
|
|
Encrypt all with user key
|
Encrypts the whole package by using a
key based on the user profile. Only the same user using the same profile can
load the package. The package is encrypted by using a key that is based on
the user who created or exported the package. Only the user who created or
exported the package can open the package in SSIS Designer or run the package
by using the dtexec command prompt utility.
|
|
Encrypt all with password
|
Encrypts the whole package by using a
password. The package is encrypted by using a password that the user supplies
when the package is created or exported. To open the package in SSIS Designer
or run the package by using the dtexec command prompt utility, the
user must provide the package password. Without the password the user cannot
access or run the package.
|
When saving my example
package I will save it using the default protection level, which is "Encrypt
sensitive data with user key". Once I have selected a protection level, the
"Save SSIS Package" window is displayed. Here is what I specified on this
screen:
After clicking on "Next>"
on the above screen the final wizard screen will be displayed. Here is that
final screen:
This screen summarizes your
packages specifications. If you need to change your package parameters you can
still use the "<Back" button to make changes. Once you click on the
"Finish" button, the wizard completes and you will not be able to go back and
modify your package specification. If you checked the "Execute immediate"
button then as the wizard finishes it will start executing your package. In my example,
I selected the "execute immediate" option. As my package executes I can see the
progress of the different actions. For my simple package, the following screen
is the final display after my package has completed. Note a similar screen is
displayed while my package was running, only the statuses of each action where different
depending on the progress of the package execution.
Conclusion
The SSIS Import/Export wizard
in SQL Server 2005 makes it extremely easy to import and export data to and
from SQL Server. Microsoft has made numerous improvements over the
Import/Export wizard of SQL Server 2000. If you have not had a chance to look
into using SSIS within SQL Server 2005, it might be worth using the Import/Export
wizard as the first step in learning about SSIS.
»
See All Articles by Columnist Gregory A. Larsen
» 
IE 7
Firefox 2.0
Comment and Contribute
