Learn SSIS Microsoft SQL Server 2008: Part1

SQL Server to Text File Using SSIS

Dear reader, today we will discuss SQL Server Integration Services (SSIS), a powerful feature of Microsoft SQL Server. SSIS is an upgrade of Data Transformation Services (DTS), which is a feature of the previous version of SQL Server. SSIS packages can be created in Business Intelligence Development Studio.

Use of SSIS

The primary use for SSIS is for data warehousing since the product features a fast and flexible tool for data Extraction, Transformation and Loading (ETL). The tool may also be used to automate the maintenance of SQL Server databases, update the data warehouse and perform other functions.

Step1: Open the business intelligence development studio.

Step 2: Click on "File" -> "New" -> "Project...".

Select an Integration Services project in the New Project window and provide the appropriate name and location for the project. And click Ok.

Step 3: The new project screen contains the following:

Left side Tool Box bar
Solution Explorer on upper right bar
Property Window on lower right bar
Control flow, data flow, event Handlers, Package Explorer in tab windows
Connection Manager Window in the bottom

Step 4: First we will select the control flow tab and then drag and drop the data flow task then rename it with DFTtextfiles.

Step 5: In this step we will select the dataflow tab and drag and drop the OLE DB Source.

Step 6: Select the OLE DB source and right-click the mouse to select the edit option.

Step 7: Then click the new button in order to create the connection with SQL Server.

Step 8: Provide the necessary details depending on your SQL Server settings, for example user name password and then database and then click the Test Connection button.

Step 9: Click the Ok button to close the connection setting.

Step 10: Select the SQL command option from the data access mode and write the SQL statement.

Step 11: Now insert the fat file definition into the data flow tab.

Step 12: Now select the fat file definition and then right-click and select the edit option. In that option select delimited option and click Ok.

Step 13: Select the general tab and browse for the text file that you want to populate the data from. You need to create the text file manually on your system; in my case I created the file at drive "C:\".

Step 14: Select yes from the message box with will ask for the delimited characters.

Step 15: Select preview to see the result of the select table and then click the Ok button to close the window.

Step 16: Now select the mapping option in order to map the both tables from SQL Server and text table. It's a very important step; if there will be no relation between the two tables then it will not work. Click Ok when it is finished.

Step 17: Now right-click the data flow and click execute task option as show in the picture.

Step 18: Now it will execute the command and shown in the picture.

Step 19: Now you can check the text files that all the data is there.

Conclusion

At the end of the tutorial it will create packages.dtx file in the same folder. Next time we need to run the dtx file and it will generate the same text file.