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.

SSIS-in-SQLServer-1.jpg

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.

SSIS-in-SQLServer-2.jpg
 
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

SSIS-in-SQLServer-3.jpg
 
Step 4: First we will select the control flow tab and then drag and drop the data flow task then rename it with DFTtextfiles.

SSIS-in-SQLServer-4.jpg

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

SSIS-in-SQLServer-5.jpg
 
Step 6: Select the OLE DB source and right-click the mouse to select the edit option.

SSIS-in-SQLServer-6.jpg
 
Step 7: Then click the new button in order to create the connection with SQL Server.

SSIS-in-SQLServer-7.jpg

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.

SSIS-in-SQLServer-8.jpg
 
Step 9: Click the Ok button to close the connection setting.

SSIS-in-SQLServer-9.jpg
 
Step 10: Select the SQL command option from the data access mode and write the SQL statement.

SSIS-in-SQLServer-10.jpg
 
Step 11: Now insert the fat file definition into the data flow tab.

SSIS-in-SQLServer-11.jpg

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.

SSIS-in-SQLServer-12.jpg

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:\".

SSIS-in-SQLServer-13.jpg
 
Step 14: Select yes from the message box with will ask for the delimited characters.

SSIS-in-SQLServer-14.jpg

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

SSIS-in-SQLServer-15.jpg

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.

SSIS-in-SQLServer-16.jpg

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

SSIS-in-SQLServer-17.jpg

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

SSIS-in-SQLServer-18.jpg

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

SSIS-in-SQLServer-19.jpg

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.

Up Next
    Ebook Download
    View all
    Learn
    View all