USEFUL SITES :
Write for Us
Database Administrators and SSIS Developers will often come across scenarios where they need to import a comma separated delimited text file into SQL Server. Providing the data in comma separated delimited text file is a very common practice which is used by many organizations around the world. Once the delimited text file is generated the files can be compressed and FTP’d to a client location from where it can be picked up and loaded into SQL Server 2008 using SQL Server Integration Services. In this article you will see the steps which you need to follow in order to create a SQL Server Integration Services Package which can be used to import data from a comma separated delimited text file into SQL Server. Different Methods to Create an SSIS Package You can create an SSIS package either by using Business Intelligent Development Studio or by using SQL Server Management Studio (SSMS). In this article you will see how to create an SSIS package using SQL Server Management Studio (SSMS) to import a comma separated delimited text file into SQL Server 2008.
Create an SSIS Package Using SQL Server Management Studio (SSMS) 1. Connect to SQL Server 2008 Instance using SQL Server Management Studio. 2. In the Object Explorer, expand the Databases Node and select the AdventureWorks databases. In this example you will be importing data from SalesCustomer.txt comma separated delimited text file which was generated using the SSIS package created in my previous article titled “How to Create a Comma Separated Delimited Text File Using SQL Server 2008”. The data will be imported into dbo.SalesCustomer table in TempDB database. 3. Right click the AdventureWorks database and select Tasks ? Export Data… as shown in the below snippet. This will open up SQL Server Import and Export Wizard. You can also open the same wizard by typing DTSWIZARD from Start | Run | CMD. 4. Once the SQL Server Import and Export Wizard opens up you will see a welcome screen as shown in the below snippet. If you are not interested in seeing the welcome screen next time whenever you are using the SQL Server Import and Export wizard then you can select the check box “Do not show this starting page again” so that next time when you are using this wizard the welcome screen does not show up. Click Next to continue with the wizard. 5. In Choose a Data Source wizard screen you need to select the Data Source as Flat File Source from the drop-down list. Next you need to browse and provide the path of the file which has data that needs to be imported into SQL Server 2008. Select the Locale value from the drop down list as “English (United States)” and then you need to choose the appropriate code page, for this example the code page value which you need to selected is 1252 (ANSI- Latin 1). As your source file is a delimited text file, you need to choose Delimited as the format from the drop down list. There are basically three formats supported and they are Delimited, Fixed width and Ragged right. However, the Text qualifier filed can be left as default <none> as shown in the snippet below. Moreover, you also need to provide the value for Header row delimiter as {CR}{LF}. Select the “Column names in the first data row” checkbox as the first row in our delimited text file mentions the column names. In the same screen on the left panel there are four different options like General, Columns, Advanced and Preview. You can click on Preview left panel to quickly check how the data in the comma separated delimited text file looks like. Click Next to continue with the wizard. 6. In Choose a Destination wizard screen you need to select Destination as SQL Server Native Client 10.0 from drop-down list if you are importing the data into SQL Server 2008. And if you importing the data into SQL Server 2005 then you need to choose the destination as SQL Native Client from the drop-down list. Then you need to provide Destination SQL Server Name and you also need to choose the appropriate Authentication mode. However in this example Windows Authentication mode is used. Then you need to select the destination database as TempDB from the databases drop-down list. Click Next to continue with the wizard. //breakk// 7. In Select Source Tables and Views wizard screen you will be able to see under Source column the location of the text file and under destination column you will be able to see dbo.SalesCustomer the destination table name. On the same screen there are two more button namely Edit Mappings & Preview. Click on Edit Mappings… button to view the column level mapping details. In this screen there are many choices available. As the destination table doesn’t exist create destination table option will be by default selected by the wizard. Click OK to save the changes in Column Mapping screen. In Configure Flat File Destination wizard screen you can click on Preview… button to preview the data which will be imported into dbo.SalesCustomer table of TempDB database. Click Close to return to the parent screen and in the parent screen click next to continue with the wizard. 8. In Save and Run Package wizard screen there are basically two options which are available. One option will be to run the SSIS package immediately and the second option is to save SSIS package. However, there are two ways to save an SSIS package, one option is to save the package in SQL Server and the second option is to save the package as a File system. If you have chosen the option to save the package in SQL Server then the package will be stored in MSDB database in SQL Server. However if you are choosing to save the SSIS package as a File system then the package can be saved as a DTSX file on the disk just like any other file. In this example File system option is selected and the package protection level is chosen as “Do not save sensitive data”. Click Next to continue with the wizard. 9. In Save SSIS package wizard screen you can provide the Name and Description for the SSIS package which you are saving as a File system. Next you need to specify the path where the SSIS Package needs to be stored as file system. Click Next to continue with the wizard. 10. In Complete the Wizard screen you can see a quick summary of all the choices which you have made till now within the wizard. Finally in order to execute and save the SSIS package, click on Finish button. 11. Once the package has executed successfully all the 19185 rows will be imported into dbo.SalesCustomer table which will be created in TempDB database. The package execution details are shown in the below snippet. You can view and save the package execution report by clicking the reports button. Finally click on close button to exit the SQL Server Import and Export Wizard. SSIS Package Execution Result Once the SSIS package has executed successfully, all the 19185 rows will be successfully imported to dbo.SalesCustomer table in TempDB database. Conclusion In this article you have seen how easily you can create a SSIS package using the SQL Server Import and Export Wizard to import the data from a comma separated delimited text file into SQL Server 2008. However you can use the same steps in order to import a comma separated delimited text file into SQL Server 2005.
