Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jan 30, 2006

SQL Server 2005 Import / Export Wizard - Page 2

By Gregory A. Larsen

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date