SQL SERVER SSIS: BULK INSERT Task

As we have seen SSIS Data Flow TASK in previous article, today I am going to explain SSIS BULK INSERT TASK. The Bulk Insert task provides an efficient way to copy large amounts of data into a SQL Server table or view. Let's say company is using Online Product Marketing and every day they have to update their databases with the latest information provided by their suppliers in Tab Separated/Comma Separated file. Here BULK INSERT provides efficient way. We can move data to SQL SERVER Table or View by BULK INSERT Task. Let see How to configure SSIS BULK INSERT Task: 1. Drag Control: Drag and drop, BULK INSERT TASK from Toolbox to Design surface: 2. Configure connection: Right click on Task and click on Edit Specify Destination Connection: Specify the OLE DB connection manager to connect to the destination SQL Server database and the table or view into which data is inserted. NOTE: The Bulk Insert task supports only OLE DB connections for the destination database. Define Format: We need to Define the format that is used by the Bulk Insert task, either by using a format file or by defining the column and row delimiters of the source data. If using a format file, specify the File connection manager to access the format file. We need to specify Row Delimiter and Column Delimiter, as per our requirement. Specify Source Connection: Finally, we need to specify Flat or File Connection Manager to access the source file. BULK INSERT Task is process this file as per defined format on previous step. 3. Configure Options: SSIS BULK INSERT Task also provides to specify some options which needs to perform when BULK TASK Insert some data to destination Table/View, like: Check Constraint, Keep Nulls, Enable Identity Insert, Table Lock, Fire Triggers. That's it. Now execute the package, you find that your Table/view is populated with data from provided text file. Why we have both BULK INSERT AND DATA FLOW TASK ? We can say that Data Flow Task is advanced version of BULK INSERT TASK, which provides more features and more scalable. The Bulk Insert task can transfer data only from a text file into a SQL Server table or view. To use the Bulk Insert task to transfer data from other database management systems (DBMSs), you must export the data from the source to a text file and then import the data from the text file into a SQL Server table or view. With Data Flow Task, we can import/export data from any database, we don't need to convert it to text and then import. So, we can use either of these as per our requirements. Let me know if you have  any questions. Reference: SQL SERVER Books Online, Tejas Shah (http://www.SQLYoga.com)