Bulk insert task is used to copy large amount of data into SQL Server tables from text files. For example, imagine a data analyst in your organization provides a feed from a mainframe system to you in the form of a text file and you need to import this into a SQL server table. The easiest way to accomplish this is in SSIS package is through the bulk insert task.
Configuring Bulk Insert Task
Drag the bulk insert task from the toolbox into the control flow window.
For loop defines a repeating control flow. It has similar behavior as the “FOR” loop available in programming languages. The For Loop container uses 3 expressions as definition
Initialization expression: it assigns value to the loop counters. This expression is optional.
Evaluation expression : It contains the expression to test whether the loop should continue executing or exit.
An optional iteration expression that increments or decrements the loop counter.
The expressions used must be valid SSIS expression. A variable is generally used in the expression.
Using and Configuring For Loop Container
(Task in hand: print counter variable value for each iteration times.)
Drag a for loop container into the SSIS Control flow window from the Toolbox. Double click on the for loop container to view the “For Loop Editor”. As mentioned earlier it contains 3 expressions. Now create a variable by name “TestVariable” of type “int32”.
Use this variable as counter in the container using expressions as shown below.
Sequence containers is used to group the package into multiple separate control flows, each containing one or more tasks and containers that run within the overall package control flow. There are several advantages in using sequence container
Provides facility to set property of multiple tasks by setting the property of sequence container.
Provides the facility of disabling groups of tasks to focus debugging on one subset of the package control flow.
Scope for variables that a group of related tasks and containers use can be controlled easily.
Managing and visualization of group of tasks becomes easier using sequence container.
We can also expand and collapse the container, hence making the package look neat.
In this blog, I will walk you through the creation of excel files ( xls / Excel 2003) dynamically through SSIS.
Scenario: every day one of my process runs to pull data from SQL server table to excel file. I need to use unique file everyday with name as “taxonomy_<date>.xls” , for example on 14th of January 2011 the file name should be “Taxonomy_01142011.xls” with the excel sheet name “TaxonomyValues”.
It is pretty simple to create this file dynamically. At first we need to set up an excel connection manager pointing to the file. The connection manager needs to be dynamically configured to point to the correct file everyday, in our case “Taxonomy_01142011.xls” on 14th Jan. To do this,
go to properties window of the excel connection manager
click on expression and the browse ( … symbol) and choose “excel File Path” property. ( please refer the pictures below)
Copy and paste the expression given below or develop similar expression. Click on evaluate expression and it will display the file path as evaluated value.
This video takes you through the creation of SQL Server Integration Services (SSIS) project creation. The video will also demonstrate the usages execute sql tasks and will take you through various components and windows in SSIS UI and the purpose of each. you can also download high quality video from by clicking here
Let us know if you would like to watch some specific videos.
In 64 bit operating systems, SSIS transformations ( especially excel) and tasks throws errors that could be annoying. You will often come across errors as shown below.
SSIS package “Package.dtsx” starting.
Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning.
Error: 0xC00F9304 at Package, Connection manager “Excel Connection Manager“:SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available. Read more…
Today, I came across a requirement, where the source excel sheet had data starting from row number 6 and column B. This was due to formating in the excel, which was also used as a report by many stake holders.
Although at first it might seem like there are no options in excel source to skip certain rows from the beginning of the sheet, it is not true. Fortunately, excel source provides the feature to select only required records from any sheet. the OpenRowset property on the Excel Datasource component allows you to specify the range to be considered by the datasource.
I stumbled upon a new error yesterday. I developed a simple and straight forward SSIS package in my machine. It was meant to copy data from an Excel 2007 sheet to a table. It worked fine in my machine, but as soon as I moved it to the server the package started failing. I had never come across this particular error before. Read more…
sometimes, you may need to copy some/ all objects from one database to another. well this can easily done in SSIS using the task “Transfer SQL Server Objects Task“. The usage of this task is very simple. Drag the task from toolbar for control flow. Double click on the task to start using it.
The editor for the task opens. First thing that you need to do is to create the SMO connection managers for both source and destination SQL servers. This can be done by clicking on the drop down provided for source and destination server tabs as shown below.
Then select the source and destination database. You can then select the objects that need to be migrated. A detail lists of objects are given in the editor as shown below. Assume, you need to copy all the tables and two views. To achive this, set the property“copy all table = true”.
You also need to select few views hence, click on “collectionlist/browse button” in “viewlist”. A pop up appears with the list of views, check the views that need to be copied.
click ok and then execute the task to copy all selected objects to destination database. You can also copy the data by setting the property “Copy Data” to true. To copy all objects in the database, you need not set each property, Instead you can set “CopyAllObjects” to true.
Adding audit information to your dataset such as user/ machine that modified the data, package that inserted data into your table etc,. might be required while performing an ETL task. In SSIS you can achieve this by using the transformation “AUDIT” available in DFTs. Inside the DFT you can add “audit transformation” after any other transformation as shown.
You need to select the “Audit type” of your choice like package name, package ID, user name etc and assign appropriate column name to each of these audit type .
Click OK. All the records in your dataset will now have the selected audit types as the extra columns.
In the above image you can see two new columns “executed_package_id” and “Execution_instanceID” added to the dataset. You can use these columns in the following transformations as well as push it into a DB table. Hence any thing that gets modified in your table through packages can be tracked.
DT_WSTR,2)MONTH( GETUTCDATE() ))
ou need to select the “Audit type” of your choice like package name, package ID, user name etc and assign appropriate column name to each of these audit type .
