Database Administrators and SSIS Developers have always come across scenarios where you need to create a comma separated delimited text file for very large tables in SQL Server 2008. Once the delimited text file is generated the files can be compressed and they can be easily FTP to client locations. This is one among the fastest and easiest way to distribute the data. In this article you will see the steps which you need to follow to create a SQL Server Integration Services Package which can be used to generate a comma separated delimited text file.
Different Methods to Create an SSIS Package
You can create an SSIS package either by using Business Intelligent Development Studio or SQL Server Management Studio (SSMS). In this article you will see how to create an SSIS package using SQL Server Management Studio (SSMS) to create a comma separated delimited text file.
Create 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 generating a comma separated delimited text file for the data which is available in Sales.Customer table.
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” screen you need to select the Data Source as “SQL Server Native Client 10.0” from the drop down list if you are using SQL Server 2008. And if you are using SQL Server 2005 as data source then you need to choose the option as “SQL Native Client”, Next you need to provide the Source SQL Server Name and choose the appropriate Authentication mode among the two choices. However in this example Windows Authentication mode is chosen. Next you need to select the source database as AdventureWorks. Click Next to continue with the wizard.
