Import Column Transformation in SSIS 2008R2 Example

By

-

October 19, 2013

The Import Column transformation reads data from files and adds the data to columns in a data flow. Using this transformation, a package can add text and images stored in separate files to a data flow. For example, a data flow that loads data into a table that stores product information can include the Import Column transformation to import customer reviews of each product from files and add the reviews to the data flow.
A column in the transformation input contains the names of files that hold the data. Each row in the dataset can specify a different file. When the Import Column transformation processes a row, it reads the file name, opens the corresponding file in the file system, and loads the file content into an output column. The data type of the output column must be DT_TEXT, DT_NTEXT, or DT_IMAGE.
Now let start step by step, Here I am moving a text file from a flat file source to a table in the database.
For the importing a text file into a database, we must have a table that hold the text file value and the path of the files. Let’s create a table named as Text_test.

Here I am having some text file and file called path, where it has all the files path.

Let start the BIDS and select a data flow task .

Click on the data flow task and then drag a Flat file source .

Now configure the Flat file source and connect to the file that has all the files path as I connected to path.txt which have all the files path.

You can see the data present in the file in Column tab.

Now take the Import Column and connect it to the Flat file source.

Configure the Import Column Transform

In the first page I mean Component Properties Tab you don’t need to do anything ,select Input Column tab where you will see source available columns check the filepath column.

Now select Input and Output Properties Tab where you will see these three option.

Import Column Input is already set to the selected source columns input.

Select Import Column Output and where add an output column by clicking Add Column.

I created an output column with the name of TextFileColumn.

We have to do one important step here we have to put this LineageID 29 highlighted in above screenshot. Into the Import column Input’s Filepath. Now press ok .

Now let’s configure the OLE DB destination and set the connection to the database where you created a table called Text_test.

Now mapping tab map the import Column output to the table Text_test Column and press “OK”.

Now execute the task.

The task executed successfully. Let’s check the output table in SSMS.

Here you can see all the files are saved to the Text files Column.

This is it guys .. Give a try !!

Thanks Swathi Srivatsava for the post.

Regards,
Roopesh Babu V

What is the use of Input columns tab in RowCount transformation

By

roopesh.valluru

-

October 19, 2013

779

The answer is NOTHING. It is of no use. the only property yu can set in Rowcount transformation is VariableName and if you try to select any column in input columns tab then it will throw error. So Beware and don’t touch the highlighting tabs in the below pic.

Regards,
Roopesh Babu V

RowCount Transformation in SSIS 2008R2 Example

By

roopesh.valluru

-

October 19, 2013

622

RowCount Transformation : The RowCount transformation provides a way to track how many rows are flowing through a particular path. You can use this information in logs as diagnostic information or for sending in an email as a notification of the work a package has completed.

Counts the rows flowing through the data flow and writes the final count in a variable. Here we are gonna discuss an example on the same.

In the Control Flow tab, add a variable named “RowCnt.” Ensure that the variable is package-scoped and of type Int32 .

Add a dataflow task and create a connection manager that connects to the AdventureWorksDW database. Add an OLE DB Data Source to the Data Flow design surface. Configure the source to point to your AdventureWorksDW database’s connection manager.

Double Click on the OLE DB Component and Select SQL Command to write customized sql.

Add a Row Count Transform to the Data Flow.

Open the Advanced Editor and select the variable named “User::RowCnt” as the Variable Name property. Your editor should resemble.

When you edit Row Count Transformation you could see the Input Columns and Input and Output Properties Tabs along with Component Properties. Please ignore both of them.

RowCount Transformation has been configured. Now Return to the Control Flow tab and add a Script Task. This task is not really going to perform any action. Instead it will be used to show the conditional ability to perform steps based on the value returned by the Row Count Transform.

Connect the Data Flow Task to the Script Task. Double click on Script Task and Set up the ReadOnlyVariable as User::RowCnt which was created as package variable and then Edit the script to write the message box popup code.

Write the piece of script code to show the count value in alert message box.

Now the package is ready to be executed. Go to the Solution Explorer and right click on the package and select “Execute Package”. If all components turn “GREEN”, it means package has run successfully, if there is any error, the component which has failed to execute will be shown in “RED” Color.

Package executed successfully and shows the rows count as in message box. This is it guys !!

Thanks Sushil for the document.

Regards,
Roopesh Babu V

Character Map Transformation in SSIS 2008R2 with Example

By

roopesh.valluru

-

October 18, 2013

0

Friends,

In this post we are gonna discuss about Character Map Transformation in SSIS 2008R2 with Example. Character map transformation is used to make changes to string type columns only. String functions can be applied using this transformation like upper case, lower case, byte reversal and so on. To understand in detail let’s take one data flow task and add character map transformation as per below screen print.

In this transformation we have to select the column. Note: we can only use string type columns in this transformation.

Here as per screen print we can select any string type column in InputColumn option. Either we can select from drop down or we can select through check box in Available Input columns window.

Once that is done we have option to select whether output of this transformation should be a new column or on same column we want the change. We can use New Column or In-Place change as per our requirement.

Then we have to choose operation, we can select from drop down what operation we want to perform.

As per the list we can see we have uppercase, lowercase, byte reversal etc operations available.

Please find below all operation details of Character Map Transformation in SSIS.

Byte reversal: Reverses byte order.
Full width: Maps half-width characters to full-width characters.
Half width: Maps full-width characters to half-width characters.
Hiragana: Maps katakana characters to hiragana characters.
Katakana: Maps hiragana characters to katakana characters.
Linguistic casing: Applies linguistic casing instead of the system rules. Linguistic casing refers to functionality provided by the Win32 API for Unicode simple case mapping of Turkic and other locales.
Lowercase: Converts characters to lowercase.
Simplified Chinese: Maps traditional Chinese characters to simplified Chinese characters.
Traditional Chinese: Maps simplified Chinese characters to traditional Chinese characters.
Uppercase: Converts characters to uppercase.

The above given operations are Mutually exclusive and the list of the same is given below.

Operation A	Operation B
Lowercase	Uppercase
Hiragana	Katakana
Half width	Full width
Traditional Chinese	Simplified Chinese
Lowercase	Hiragana, katakana, half width, full width
Uppercase	Hiragana, katakana, half width, full width

(Source: MSDN)

Let’s see one example

Uppercase:

Here as per our selection, we will apply Uppercase operation on AccountDescription column as In-Place change so no new column will be generated, we will see changes on same column.

Click on OK and Execute. I added two data viewers one before the character map and another after the same to show how the data transforms. In Before character map data viewer we can see Account description column’s data.

Let’s see after character map transformation data for that column. We can see everything is in uppercase.

Same way we can use different transformations.

Hope this helps you guys .. Give a try .. 🙂

Thanks Hardik Modi for the document.

Regards,
Roopesh Babu V

How to move data from Multiple files to different destinations using Conditional Split in SSIS 2008R2

By

roopesh.valluru

-

October 18, 2013

0

Summary: Here we will be doing a demonstration for conditional split transformation provided in SSIS. To demonstrate this we will upload data from two text file to two different table based on some condition. Below is a screen shot of text file which will be uploaded.

Here we will upload all rows with PlantType Seasonal to Seasonal table and all Plant Type Perennial to other table named Perennial.

In our example we will use following objects/task of SSIS:

1)      ForEach Loop container
2)      Data Flow Task
3)      Flat File Source
4)      Conditional Split Transformation
5)      ADO.Net Destination

Below is a well draft sample on how to use Conditional Split transformation.

Step 1: Click on Start —-> Programs —–> Microsoft SQL Server 2008 —-> SQL Server Business Intelligence Development Studio

Step 2: Now On below screen Go to File —-> New —-> Project

Step 3: By clicking Project we will get below window. In this window select Integration Services Project and give a proper name to solution and project. Then click OK.

Step 4: You will get following screen. As you can see, it has four tabs Control Flow, Data Flow, Event Handlers and Package Explorer.

Step 5: While staying on Control Flow tab, from toolbox side menu drag and drop For Each Loop Container on Control Flow Pane. And inside drag a Data Flow task, your screen will look like below. I have edit name as per my requirement.

Step 6: Configure you Foreach loop container as below. Right click on For Each Loop Container select EDIT. Below screen will appear, fill in the required properties:

Below is the list of properties and values to be supplied

Enumerator: For each File Enumerator
Folder: Give full path of folder in which we need to iterate.
Files: *.* (this will iterate through all files)
Retrieve file name: Fully qualified

Step 7: Now go to Variable Mappings. Create a new variable as shown on screen. Then click OK

Step 8: Now double click on DataFlow task and this will take you to Data Flow tab as shown below. Here drag Flat File Source, Conditional Split transformation and two ADO.Net Destination components from side menu. Join them with each other as shown below.

Step 9: As we have two input files, we need to configure Flat File Connection Manager in such a way that it takes both of them. To do it, click on the FlatFile connection manager you have created, in my case it is Data File. Then press F4 to open Properties menu for Flat File connection manager. There go to Expression as shown below. Both things are highlighted in red.

or

Step 10: Click on doted button of Expression and it will open Property Expression Editor. Select ConnectionString from Property and click on expression, it will open Expression Builder there select the variable which you have created to store each file name in the folder. In our case it is vFLowerSourceFile. By setting this we are saying that connection will be done to each file which this variable holds. And then this will process all files present in the folder. Now click OK.

Step 11: Configure Flat File source with the files which you want to import. Now double click on Conditional Split and configure it as shown below:

Here you can see we have specified two conditions

a) PlantType==”Seasonal”
b) PlantType==”Perenial”

So when this task will run, it will split the flow of rows to two different destination based on condition

Default Output Name: This is default condition which means if program encounters any row which does not satisfy any mentioned condition then it will flow through this default one.

Step 12: When you join green arrow from conditional split transformation towards ADO.Net destination it will pop up a window, there you need to specify which condition row will go to which destination. This is shown below

Step 13: Configure ADO.Net destination component as below. Mention the data source and table where you want to direct rows

Step 14: Now run the package and you will see all green task as shown here (obviously when you configure everything correctly J )

Step 15: Now you can check SQL server table to check whether rows has been correctly inserted or not.

So here we have seen how to use conditional split transformation. This transformation is very helpful while uploading data. We can have n numbers of conditions here.

Thanks Pallavi for the clean document.

Regards,
Roopesh Babu V

SSIS Logging to Text Files Example

By

roopesh.valluru

-

October 18, 2013

0

Friends,

As you all know, Logging is a very useful feature given in SSIS which helps in Auditing and Troubleshooting. Logs are associated with packages and are configured at the package level. In this post, let’s discuss how to send LOG Info to a text file. Please follow the below steps –

1) Open the package to which you wish to enable logging. Right click on the control flow of the package and select Logging option.

2) We have option at which level of package we have to capture log details,it can either at the whole package level or at a particular task level. Select the appropriate value.

3) Select Text logging type at the provider type and select add. This will add TEXT Logging option.

4) Click on the cell under CONFIGURATION option and set the FILE path to which the logs needs to be written.

5) In details tab, we can select the events for which the logging should be enabled. Check the events as per your requirement and select OK.

6) Go back to Providers tab and set the File connection to which the errors needs to be logged if any.

7) In this case my Package failed (which i made it intensionally) and now go and Execute the package.

8) As set, Package failed and now lets go to the file which you have specified in file Connection and check whether the logs are written to it.

You can see all log details are captured. that’s it guys .. Try it !!

Thanks Kalaivannan for the doc.

Regards,
Roopesh Babu V

Data Conversion Transformation in SSIS 2008R2 Example

By

roopesh.valluru

-

October 18, 2013

0

Friends,

The data conversion transformation takes input columns and creates a copy of that column with selected (new) data type. If source columns data types are not matched with target columns, some cases SSIS will throw validation/failure error.

SQL Server to SSIS data type:

SQL server	SSIS
smallint	two-byte signed intger (DT_I2)
int	four-byte signed integer (DT_I4)
bigint	eight-byte signed integer (DT_I8)
float	double-precision float (DT_R8)
char,varchar	string (DT_STR)
nchar,nvarchar	unicode string (DT_WSTR)
decimal,numeric	numeric(DT_NUMERIC)
decimal	decimal (DT_DECIMAL)
smallmoney,money	currency (DT_CY)
date	database date (DT_DBDATE)
datetime	database timestamp (DT_DBTIMESTAMP)

Now we gona discuss with the help of one simple package in SSIS. Let’s say we have two tables one is test master and second is subject master. Lets create a table called wrk_atnd where we put the attendance of guards

CREATE TABLE [wrk_atnd] (
[atnd_ou] float,
[atnd_rptno] nvarchar(255),
[atnd_emp_code] nvarchar(255),
[atnd_date] datetime,
[atnd_assign_no] nvarchar(255),
[atnd_pd_line_no] float,
[atnd_contract_no] nvarchar(255),
[atnd_cont_lineno] float,
[atnd_shift] nvarchar(255),
[atnd_data_type] nvarchar(255),
[atnd_reg_hours] float,
[atnd_ot_hors] float,
[atnd_site_id] nvarchar(255),
[atnd_status] nvarchar(255),
[atnd_post] nvarchar(255),
[atnd_wrk_rpt_no] nvarchar(255),
[atnd_remarks] nvarchar(255),
[atnd_from_time] nvarchar(255),
[atnd_to_time] nvarchar(255),
[OU] float
)

This table work as a destination table where the excel data will be loaded. Let’s take a excel source file and move the excel data into sql server table.
Now configure the excel source file.

Now add the data conversion transformation.

To configure the data conversion double clicks on Transformation.

Check the available input columns for which the data type will be changed. We can alias the input columns also. Select the compatible data type in Data type column and click on OK. Now we gona take the OLEDB Destination for moving the excel source data into table.

For checking the data we have added the data viewer.

Now Execute the package and see the result.

You can see the data is inserted to the database table and we changed the datatypes using data conversion transformation while moving from Source to Destination. Give a try guys !! Thanks Sushil for the doc.

Regards,
Roopesh Babu V

Row Sampling and Percentage Sampling in SSIS 2008R2 Example

By

roopesh.valluru

-

October 18, 2013

0

Friends,
Row Sampling and Percentage Sampling both are similar task and perform the same function with little difference. The Row Sampling transformation is used to obtain a randomly selected subset of an input dataset. You can specify the exact size of the output sample, and specify a seed for the random number generator. There are many applications for random sampling. For example, a company that wanted to randomly select 50 employees to receive prizes in a lottery could use the Row Sampling transformation on the employee database to generate the exact number of winners.

While, The Percentage Sampling transformation creates a sample data set by selecting a percentage of the transformation input rows. The sample data set is a random selection of rows from the transformation input, to make the resultant sample representative of the input.

Both will take one input and provide two output. In easy term you can say. If 5000 records in my source then In Row Sampling, if I take 25 as value then output is 25 records and In Percentage Sampling, If I take 25 as value then 25% of 5000 i.e 1250 records will be the output.

Let start with the source first, here I am taking and OLE DB source in a Dataflow task.

Now configure the source, I am taking a table DimCustomer from AdventureWorksDW the database provided by Microsoft.

Now press “OK”. Source configured now . Now take a Row Sampling Task .

Let configure Row Sampling Task. In that you will find.

Number of rows
Specify the number of rows from the input to use as a sample. The value of this property can be specified by using a property expression.
Sample output name
Provide a unique name for the output that will include the sampled rows. The name provided will be displayed within SSIS Designer.
Unselected output name
Provide a unique name for the output that will contain the rows excluded from the sampling. The name provided will be displayed within SSIS Designer.
Use the following random seed
Specify the sampling seed for the random number generator that the transformation uses to create a sample. This is only recommended for development and testing. The transformation uses the Microsoft Windows tick count as a seed if a random seed is not specified.

Here I have selected 25 row as a sampled data and Named a sample output name as Sampled Data Input and Unselected output name as Unsampled Data Input. And I don’t check the random seed option because I do not want it. Now I select two output destination one is Flat file Destination for Sampled Data input and another Flat File destination for Unsampled Data Input.

When you drop the pipeline from Row Sampling to any destination it will open a pop up and it will ask that which input you want to select for flat file destination.

After selecting the input it will show like this.

Now configure both the Flat file destination one for Sample Data and Another for Unsampled data.

Now it all set.

Now Run the task , after running the task you can see that only 25 row will go to the sampled destination and all the rest of the rows will go to the unsampled.

That is it for the Row Sampling . As I explained before that Percentage sampling is similar like row sampling it differs only at how much column it take for sampling will depend on the % not on the defined number of row.

Let see what result comes when we replace the row sampling task with percentage sampling task. Select Percentage Sampling instead of Row Sampling

And Configure it , I select 25% of the whole data .

Now run the task and see the difference.

Here you can see that transformation is sending 4469 rows in sampled data destination which is 25% of 18484 I mean whole data and Row sampling was sending only 25 rows to the sampled data.

Give a try guys !!
Thanks Swathi Srivatsava for the post.

Regards,
Roopesh Babu V

How to configure MULTIPLE aggregations in one Aggregate Transformation in SSIS 2008R2 with Example

By

roopesh.valluru

-

October 17, 2013

0

Friends,

In last post we have seen the use of Aggregate Tranformation with example (Click Here) and in this we are gonna discuss about How to Configure more than one Aggregation in one Aggregate transformation. The Aggregate Transformation can support multiple outputs – this means you can read the data set into memory once, then aggregate it up as many ways as you like. By clicking the Advanced button on the Aggregate tab of the component editor, a new grid is revealed. If you enter a new value in the “Aggregation Name” column, the column selector is enabled and you can create a new set of aggregations which will be delivered as a new output for the component, as demonstrated with 2 sort Transformations. In this example Color sales and product sales as Aggregation Names for multiple outputs.

Now Execute the Task to see the Results.

Now we can see in the above example We are getting multiple outputs. It is as simple as this. Hope you will give a try !!

Thanks Aparna for the simple and short document.

Regards,
Roopesh Babu V

Aggregate Transformation in SSIS 2008R2 with Example

By

roopesh.valluru

-

October 17, 2013

0

Friends,

The Aggregate transformation is used to perform aggregate operations/functions on groups in a dataset. The aggregate functions available are- Count, Count Distinct, Sum, Average, Minimum and Maximum. The Aggregate transformation has one input and one or more outputs. It does not support an error output. The Aggregate transformation supports the following operations.

Group By: Divides datasets into groups. Columns of any data type can be used for grouping.
Sum: Sums the values in a column. Only columns with numeric data types can be summed.
Average: Returns the average of the column values in a column. Only columns with numeric data types can be averaged.
Count: Returns the number of items in a group.
Count distinct: Returns the number of unique non null values in a group.
Minimum: Returns the minimum value in a group. This operation can be used only with numeric, date, and time data types.
Maximum: Returns the maximum value in a group. This operation can be used only with numeric, date, and time data types.

The Aggregate transformation handles null values in the same way as the SQL Server relational database engine.

In a GROUP BY clause, nulls are treated like other column values. If the grouping column contains more than one null value, the null values are put into a single group.
In the COUNT (column name) and COUNT (DISTINCT column name) functions, nulls are ignored and the result excludes rows that contain null values in the named column.
In the COUNT (*) function, all rows are counted, including rows with null values.

Now let me demonstrate how you can create an SSIS package with Aggregate transformation

Go to START -> Microsoft SQL Server 2008 -> SQL Server Business Intelligence Development Studio to launch BIDS.

Then go to File menu -> New -> Project -> Select “Business Intelligence Projects” in the left tree pane -> Select “Integration Services Projects” and name the project as you wish and click OK.

Here in this example we want to get the sum of the sales amount for each Color and English product name based on the Dimproduct and FactInternetsales tables data from AdventureworkDW Database. We want to perform database equivalent of SUM(SALESAMOUNT) GROUP BY Color and EnglishproductName operation.

Here, we have Dimproduct and FactInternetsales tables are OLEDB Source.

Now Drag and Drop Aggregate Transformation As Show below.

Double-click the Aggregate transform to open the editor. Next in the lower pane we select the Input Column, set Output Alias to columns, select the Operation i.e. Group By clause or any aggregate functions as below:

Note: The operation will appear in drop down depending upon the data type of field. If your input column is string type, you will not see Sum, Count and other aggregate functions but only Group by. Always make sure to convert data type of column to proper data type before providing as input to Aggregate Transformation.

Instead of using Table as destination, I used Sort Transformation for testing purpose and used Data viewer to show output data produced by Aggregate Transformation. After testing our package successfully we can delete Sort Transformation and Data Viewer and bring our destination and connect to aggregate transformation for input columns for our destination.

Now Execute the package to see the Results. This is the aggregated output of the data we provided as input.

This is it guys .. Give a try !!

Thanks Aparna for the document.

Regards,
Roopesh Babu V

Flat File Source in SSIS 2008R2 with Example

By

roopesh.valluru

-

October 17, 2013

112

Friends,

A Flat File source is a data flow component that uses metadata defined by a Flat File connection manager to specify the format and structure of the data to be extracted from the flat file by a transform process. The Flat File source can be configured to extract data from a single flat file by using the file format definition provided by the Flat File connection manager. PFB the steps in configuring Flat file source:

For configuring Flat file source, you should have a Data Flow Task. You can Take Data Flow task in Control Flow tab and then double click on it will take you to the Data flow tab .

Here you can drag Data Flow from Left hand Side OR you can directly go to the Data Flow Tab and on that page click on the message shown. It will automatically add a Data Flow Task in the Control Flow Tab and Enable the all components of Data Flow Tab.

Now Drag a Flat File source From Left hand side Data Flow Source into the Data flow Tab.

Double click on the Flat file Source or Right-Click on it and select Edit. It will Open the Flat File Source Editor.

Now Select New to add a new flat file source Connection.

It will again open a new Wizard Called Flat File Connection Manager. Here you can write connection manager name as in my case I have written Flt File Connection and you can also write some description if you want.

As you see the entire component all in disable state

File name: Type the path and file name to use in the flat file connection.
Browse: Locate the file name to use in the flat file connection.
Locale: Specify the locale to provide language-specific information for ordering and for date and time formats.
Unicode: Indicate whether to use Unicode. If you use Unicode, you cannot specify a code page.
Code page: Specify the code page for non-Unicode text.
Format: Indicate whether the file uses delimited, fixed width, or ragged right formatting.
Text qualifier: Specify the text qualifier to use. For example, you can specify that text fields are enclosed in quotation marks.
Header row delimiter: Select from the list of delimiters for header rows, or enter the delimiter text.
Header rows to skip: Specify the number of header rows or initial data rows to skip, if any.
Column names in the first data row: Indicate whether to expect or provide column names in the first data row

So first you have to browse your flat file source by clicking on the Browse, In my case my flat file source is a text file and it is on Desktop. After Browsing you will see everything got enabled.

My source is Beauty.txt (Names might look funny but remember this is just an example)

Here my file name is Beauty.txt and Accordingly I set all the properties. Like I don’t need any Text qualifier and don’t want to skip any rows and want first row as a Column Names. Now go the next section called Columns in Left hand Side.

As you can see my first row becomes column name here and the data get stored in respective column. Now go to the Advanced Section On the Left Hand Side.

In this you can Configure the existing column or you can Add Column ,Insert Before and Insert After. By selecting Suggest Types… you can suggest data type for the selected Column. You can find these option in suggest types…

Number of rows: Type or select the number of rows in the sample that the algorithm uses.
Suggest the smallest integer data type: Clear this check box to skip the assessment. If selected, determines the smallest possible integer data type for columns that contain integral numeric data.
Suggest the smallest real data type: Clear this check box to skip the assessment. If selected, determines whether columns that contain real numeric data can use the smaller real data type, DT_R4.
Identify Boolean columns using the following values: Type the two values that you want to use as the Boolean values true and false. The values must be separated by a comma, and the first value represents True.
Pad string columns: Select this check box to enable string padding.
Percent padding: Type or select the percentage of the column lengths by which to increase the length of columns for character data types. The percentage must be an integer.

Now select Preview Tab on the left hand side where you can see the preview of the file that getting upload.

Now select ok and you will be again on the File system source Editor.

You can check the the box Retain null values from the source ,if you want to retain the null values. Now Go to the Column Tab in the Left hand side where you can see the column mapping. Where you can change the output column name if you want in Output Column.

Now select Error Output tab here you can handle your Errors result. As you see have Column name , Error, Truncation and Description.

You can change it into three options. Ignore Failure- If you want to ignore failure and want to move forward, Redirect Rows – if you want to redirect he rows to another output. Fail Component- If you want to fail the component on failure of error and Truncation.

And press ok you are good to go to the next section either transformation or direct to the destination.

Thanks Swati Srivatsava for the clean and complete document.

Regards,
Roopesh Babu V

SSIS Detailed Training Videos by Roopesh Valluru

By

roopesh.valluru

-

October 12, 2013

98

Friends,

Thanks for all your comments for my SSAS and SSRS Videos and also thanks for the interest you have shown on my SSIS Videos. To be honest I didnot touch SSIS in last 2 years and hence I should accept that I am not the best bet for the same tool. But, after getting requests from hundreds (believe me pls) of MSBI techies for my SSIS videos, I am uploading the same here. Please do forgive me if some mistakes rolled in my videos 🙂

Again, I am making these videos to the ppl who are starting their career on MSBI and hope this helps you guys. I will continue to add the videos to the same page but for now I am adding nearly 10 videos so that you can trigger. Please bookmark this page as the videos will continue to come 🙂

My Best Wishes !!

Video 1

[youtube]https://www.youtube.com/watch?v=ikWEZGWh-1g[/youtube]

Video 2

[youtube]https://www.youtube.com/watch?v=q6XWTuiaRd8[/youtube]

Video 3

[youtube]https://www.youtube.com/watch?v=rPDa04944pw[/youtube]

Video 4

[youtube]https://www.youtube.com/watch?v=v7lJellCpDQ[/youtube]

Video 5

[youtube]https://www.youtube.com/watch?v=axYq6Bek-o8[/youtube]

Video 6

[youtube]https://www.youtube.com/watch?v=58z51evVJIw[/youtube]

Video 7

[youtube]https://www.youtube.com/watch?v=tbgwcEdzsnw[/youtube]

Video 8

[youtube]https://www.youtube.com/watch?v=UOWNzhAj4gI[/youtube]

Video 9

YET TO BE ADDED

Video 10

[youtube]https://www.youtube.com/watch?v=BbvGCD04fKM[/youtube]

Video 11

[youtube]https://www.youtube.com/watch?v=Ml0IjldVJ1Q[/youtube]

Video 12

[youtube]http://www.youtube.com/watch?v=1WvMuc6fXFk[/youtube]

Video 13

[youtube]https://www.youtube.com/watch?v=4swFTekqlQ4[/youtube]

Video 14

[youtube]https://www.youtube.com/watch?v=_Sesm-qKleM[/youtube]

Video 15

[youtube]https://www.youtube.com/watch?v=a0lFSYlXVX8[/youtube]

Video 16

[youtube]http://www.youtube.com/watch?v=A96jzs3IPyY[/youtube]

Video 17

[youtube]http://www.youtube.com/watch?v=bECZty8u_HM[/youtube]

Video 18

[youtube]https://www.youtube.com/watch?v=VRfxmSECGYM[/youtube]

Video 19

[youtube]http://www.youtube.com/watch?v=ozXymSw-IOo[/youtube]

Video 20

[youtube]http://www.youtube.com/watch?v=vE88BQ8krLc[/youtube]

Video 21

[youtube]http://www.youtube.com/watch?v=fXEe_A9esDM[/youtube]

Video 22

[youtube]http://www.youtube.com/watch?v=kY7u9CvYV2c[/youtube]

MORE TO COME …

Hope this helps you !!

Regards,
Roopesh Babu V

Logging in SSIS (Windows Event Log Provider)

By

roopesh.valluru

-

October 11, 2013

402

Friends,

SQL Server Integration Services includes log providers that you can use to implement logging in packages, containers, and tasks. With logging, you can capture run-time information about a package, helping you audit and troubleshoot a package every time it is run. For example, a log can capture the name of the operator who ran the package and the time the package began and finished. You can configure the scope of logging that occurs during a package execution on the Integration Services server. Logs are associated with packages and are configured at the package level. Each task or container in a package can log information to any package log. The tasks and containers in a package can be enabled for logging even if the package itself is not. For example, you can enable logging on an Execute SQL task without enabling logging on the parent package. A package, container, or task can write to multiple logs. You can enable logging on the package only, or you can choose to enable logging on any individual task or container that the package includes. When you add the log to a package, you choose the log provider and the location of the log. The log provider specifies the format for the log data: for example, a SQL Server database or text file.

In this example , I will show you how to enable logging in BIDS during package development, and how to use the SSIS log provider for Windows Event Log. First of all, in BIDS, right click anywhere in the control flow. In the context menu you will click the first option, Logging.

Select Provider type: SSIS log provider for Windows Event Log and click Add.

Now Execute the package . After package is executed, go to Control Panel – > Administrative Tools – > Event Viewer – >Windows Logs – > click on Application – > under Source tab with value “SQLISService” are package logged information.

right click on Control Flow tab and select Log Events. You will notice as shown below.

That’s it guys .. Try it ..

thanks Aparna for the short and simple doc.

Regards,
Roopesh Babu V

Logging in SSIS (XML File Log Provider)

By

roopesh.valluru

-

October 10, 2013

563

Friends,

SQL Server Integration Services includes log providers that you can use to implement logging in packages, containers, and tasks. With logging, you can capture run-time information about a package, helping you audit and troubleshoot a package every time it is run. For example, a log can capture the name of the operator who ran the package and the time the package began and finished.

Let’s start with how to open Logging wizard. There are two places from where you can launch the logging wizard. Firstly, right click on anywhere in the package there will be an option called Logging click on that. Secondly, You can go to the top bar and from where you can select SSIS and inside that you find an option for Logging.

Now select Logging option, it will open the Logging Wizard

Here you can see Containers on the left hand side which the packages ,task and containers that are available and for that you can create a log. Logs are associated with packages and are configured at the package level. Each task or container in a package can log information to any package log. The tasks and containers in a package can be enabled for logging even if the package itself is not. For example, you can enable logging on an Execute SQL task without enabling logging on the parent package. A package, container, or task can write to multiple logs. You can enable logging on the package only, or you can choose to enable logging on any individual task or container that the package includes.

On right hand side you see Provider and Logs means where you want to log and another one is Details means what you want to log. Will see both the tab one by one .Let’s start with Provider and Logs tab and in that Provider Type. It has Five provider type, Here will discuss SSIS log provider for XML Files.

Now Select SSIS log provider for Xml files and select Add.

Create a connection to your Xml file where you want to log. I am creating a new Xml file to save the logs into it by clicking on “New Connection”. I am creating a LogXml.xml file on the desktop. Now select OK

Now, As explained above you can create logs at any level I mean at package level , container level, as well as at task host level. Here is my package.

It has one package and into that it has three task Execute SQL Task ,For Each loop Container and File System Task. I want to create logging at File system Task level. So I select the file system task from the container list and then check the SSIS Log provider for XML Files which I just added.

Now I Select the Details tab on the top at right hand side. It shows that what are the information you want to know and want to store in your log provider. You select all the option or the only those which you want to show. In my case I will check all of them. There is one more option at the bottom called Advanced. In advanced u can see more details.

Now press ok. Now we are good to go. Execute the package and if my package failed it will log all the information in the SQL server database what I provided. So lets Execute.

As you see my Package got failed so there must be a Xml file created at Desktop called LogXml.xml.

So, here your XML file that contains all the log information you needed. That is it guys !! Happie Coding !!

Regards,
Roopesh Babu V

Different Events of EVENT HANDLERS IN SSIS

By

roopesh.valluru

-

October 10, 2013

465

Friends,

Let’s discuss a very little bit about different events available in Event Handlers in SSIS Packages. To turn SSIS package into a trustworthy system which are useful for audit, response appropriately to Error conditions, progress the report and allows instrumentation and keep an eye on respective SSIS packages we need the simplest means of SSIS Event Handlers. SSIS event handlers are very easy to implement and accommodate a great flexibility. As the name suggests, based on certain event, we would like to take some action (handle the event) the way we want. We may want to shoot an email in case of an error or failure in the package. Or we might want to truncate a table once the ETL is completed. you can see all the events in Event Handlers tab in Package as you c an see in below pic.

Here is the brief of all the events to give an idea on WHEN the event will be fired.

OnError: This event is raised by an executable when an error occurs.
OnExecStatusChanged: This event is raised by an executable when its execution status changes.
OnInformation: This event is raised during the validation and execution of an executable to report information. This event conveys information only, no errors or warnings.
OnPostExecute: This event is raised by an executable immediately after it has finished running.
OnPostValidate: This event is raised by an executable when its validation is finished.
OnPreExecute: This event is raised by an executable immediately before it runs.
OnPreValidate: This event is raised by an executable when its validation starts.
OnProgress: This event is raised by an executable when measurable progress is made by the executable.
OnQueryCancel: This event is raised by an executable to determine whether it should stop running.
OnTaskFailed: This event is raised by a task when it fails.
OnVariableValueChanged: This event is raised by an executable when the value of a variable changes. The event is raised by the executable on which the variable is defined.
OnWarning: This event is raised by an executable when a warning occurs.

That is it .. Thanks Giri for the documentation work.

Regards,
Roopesh Babu V

Logging in SSIS (SQL Server Log Provider)

By

roopesh.valluru

-

October 10, 2013

216

Friends,

In this post we are gonna discuss about how to LOG the Package info in Sql Server table. Thanks Swathi for a very good documentation work you have done.

SQL Server Integration Services includes log providers that you can use to implement logging of packages, containers, and tasks. With logging, you can capture run-time information about a package, helping you audit and troubleshoot a package every time it is run. For example, a log can capture the name of the operator who ran the package and the time the package began and finished. Let’s see an example for Logging in SSIS here.

Let’s start with how to open Logging wizard. There are two places from where you can launch the logging wizard. Firstly, right click on anywhere in the package there will be an option called Logging click on that. Secondly, You can go to the top menu and from where you can select SSIS and inside that you find an option for Logging.

Now select Logging option, it will open the Logging Wizard.

Here you can see Containers on the left hand side which the packages ,task and containers that are available and for that you can create a log. Logs are associated with packages and are configured at the package level. Each task or container in a package can log information to any package log. The tasks and containers in a package can be enabled for logging even if the package itself is not. For example, you can enable logging on an Execute SQL task without enabling logging on the parent package. A package, container, or task can write to multiple logs. You can enable logging on the package only, or you can choose to enable logging on any individual task or container that the package includes.

On right hand side you see Provider and Logs means where you want to log and another one is Details means what you want to log. Will see both the tab one by one .Let’s start with Provider and Logs tab and in that Provider Type. It has Five provider type, Here will discuss SSIS log provider for SQL Server.

Now Select SSIS log provider for SQL Server and select Add.

Create a connection to your database where you want to log. Here point to notice is u just need to create a connection no need to create a table inside that, while logging it will create a table called Sysssislog by its own. Here I am creating a connection to my localhost server and selecting my AdventureworkDatabaseDW database.

Now, As explained above you can create logs at any level I mean at package level , container level , as well as at task host level. Here is my package I am using.

It has one package and into that it has three task Execute SQL Task ,For Each loop Container and File System Task. I want to create logging at File system Task level.

So I select the file system task from the container list and then check the SSIS Log provider for SQL server which I just added. Now I Select the Details tab on the top at right hand side. It shows that what are the information you want to know and want to write to your log provider.

You select all the option or the only those which you want to show. In my case I will check all of them. There is one more option at the bottom called Advanced. In advanced u can see more details.

Now select OK and we are good to go. Execute the package and if my package failed it will log all the information in the SQL server database what I provided. So lets Execute.

As you see my Package got failed so there must be a table created in Database which I provided in the connection. Let’s check that in Sql server management Studio. I go to the SSMS and select my database AdventureWorksDW2008R2 and as I told you erlier in this post, a table with the name SYSSSISLOG will be created and let’s check the same.
Select * from sysssislog

You can see it showing all the information about the task that failed and related information.

So, here your table that contains all the log information you needed.

Regards,
Roopesh Babu V

Transfer SQL Server Objects Task in SSIS 2008 R2 With Example

By

roopesh.valluru

-

October 3, 2013

313

Friends,

The Transfer SQL Server Objects task is used to transfer one or more SQL Server objects to a different database, either on the same or another SQL Server instance. This allows you to select different types of objects you want to transfer. You can select tables, views, stored procedures, user defined functions etc. Not only this, you can select a combination of these types of objects to transfer and even select particular objects of a particular object type.

Now let me demonstrate how you can create an SSIS package with the Transfer Database Task.

Go to START -> Microsoft SQL Server 2008 -> SQL Server Business Intelligence Development Studio to launch BIDS.

Then go to File menu -> New -> Project -> Select “Business Intelligence Projects” in the left tree pane -> Select “Integration Services Projects” and name the project as you wish and click OK.

In this new project you will see one package is already added with the name “Package.dtsx“. Drag the “Transfer SQL Server Objects Task” from the Toolbox (which is normally on the left side) to the Control Flow pane. Right click on the task and select Edit… as shown below.

In the “Transfer Sql Server Objects Task Editor”,click on Objects to set the different properties for this task. select Databases on the left and now you are ready to configure this task. Source Connection is the property to specify the connection for the source SQL Server instance, if you have already created a connection manager then you can reuse it here or can create a new one as shown below. This will also need to be done to configure the Destination Connection property as well.

In this scenario I selected source connection as KUSHI_HP (Server Name) and source database as AdventureworksDW database, For destination connection I selected as KUSHI_HP (Server Name) and Destination database as TEST

Source Database is the name of the database from where you are copying the objects and Destination Database is the name of the database to which you are copying the objects to.

Connection
- SourceConnection – the source instance
- SourceDatabase – name of the source database
- DestinationConnection – the destination instance
- DestinationDatabase – name of the new database
Destination
- DropObjectsFirst – Drop selected objects on the target before copy
- IncludeExtendedProperties – While copying operation also include extended properties of SQL objects being copied
- CopyData – While copying tables, transfer the data of the selected tables as well
- ExistingData – whether to append or replace data
- CopySchema – Copy the schema of the objects being copied
- UseCollation – Make sure collation of the columns are appropriately set on copied tables
- IncludeDependentObjects – Include all the objects in copy operation which are dependent on selected objects
Destination Copy Objects
- CopyAllObjects – Do you want to copy all objects from the source database, if set to False, next property ObjectsToCopy will get enabled.
- ObjectsToCopy – With this property you select types of objects you want to copy. You can select all objects of one or more types or select particular objects as you can see in the below image. Depending on the SQL Server version, type of objects selection will vary.
Security
- CopyDatabaseUsers – whether to include users
- CopyDatabaseRoles – whether to include roles
- CopySQLServerLogins – whether to include logins
- CopyObjectLevelPermissions – whether to include object level permissions
Table Options
- CopyIndexes – whether to include indexes
- CopyTriggers – whether to include triggers
- CopyFullTextIndexes – whether to include full text indexes
- CopyAllDRIObjects – whether to include referential integrity objects
- CopyPrimaryKeys – whether to include primary keys
- CopyForeignKeys – whether to include foreign keys

GenerateScriptsInUnicode – whether to create script in Unicode or not

Here we have to select the objects that we need to be migrated. A detail lists of objects are given in the editor as shown below. Assume, you need to copy two tables and two views.

In this scenario I selected two tables to transfer into TEST database.

Now 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.

The person executing the package with this task must have at least browse objects permissions on the source database and on the destination database must have permissions to drop and create objects.

Thanks Archana for a well structured doc.

Regards,
Roopesh Babu V

Transfer Jobs Task in SSIS 2008 R2 with Example

By

roopesh.valluru

-

October 3, 2013

176

Friends,

This task allows to transfer any of the existing SQL Server Agent jobs between SQL Server instances. Just like the other SMO tasks, we can either select to transfer all jobs to synchronize two instances, or use the task to selectively pick which jobs you want to move to another server.

Let’s see how we configure this task. To demonstrate the same, I am creating a JOB in the Sql Server Agent in default instance and move it from there to another instance using “Transfer Jobs Task”.

Open the SSMS and click on SQL Server Agent->Job->New Job.

Under new job we configured the job that will be run on daily basis.

Name: name of the new job

Owner: Job owner (user name)

Category: In this we will select, for which category this task will be fallen. Here I have selected the database maintenance.

Enable: If the Enable checkbox is checked means the task is ready for running.

T- SQL Backup database Script

BACKUP DATABASE [AdventureWorksDW] TO DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\AdventureWorksDW.bak’ WITH NOFORMAT, NOINIT, NAME = N’AdventureWorksDW-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

Step Name: Assign the step name

Type: Which type of job will be schedule, here I am gona selected as TSQL script.

Database: select the database

Command: Under this option we provide the TSQL script

Now we define the schedule date and time.

Name: Schedule job name

Schedule type: It carry out the schedule type like one time, Recurring or start when the SQL server Agent will be start etc. Here I am gona select the one time.

Click on OK and close it now the Job has been configured. We can see in given screenshot also it is showing.

Now open the solution and configure the task.

Right click on task or double click on task the configuration page will be open

Source Connection: Provide the source connection from where the job will be copied to destination server.

Destination server: Provide the destination server here.
Transfer All Job: Put it to False because we gonna transfer only selected job not all the job(which can be taken a lot of time if multiple jobs are presents).

Job List: List out the job from here (which will be copied).

Go to next option i.e.

If Object Exist: Here we have 3 options like Fail task, over right and Skip. I have selected the Skip option means it will be skip the job if it is available.

Now execute the task and see the output

Task is successfully completed, now to see the result again go to SSMS and check the destination server.

In destination server the job Database Backup is created. We can see the history of job after runs, the sample screenshot is here.

That is the steps for creating Transfer Job task in SSIS. Hope you have understood the concept. Try it guys !!

Thank a lot Sushil Mishra for very good documentation work. Keep it up !!

Regards,
Roopesh Babu V

Text Qualifier property in SSIS

By

roopesh.valluru

-

October 2, 2013

2561

Friends,

Let’s assume that we are importing data from a flat file and all the columns have some special characters in it. How to remove those while loading in to destination ? One of the solutions is using the property “Text Qualifier”. Let’s see an example here ..

Here is the sample CSV file as it looks in a text editor. You can see that all of the columns have double quotes around the data even where there is no data. The file is comma delimited, so this should give us enough information to import the data column by column.

To create the package we use a Data Flow Task and then use the Flat File Source as our data flow source.

When setting up the Flat File Connection for the data source we enter the information below, basically just selecting our source file.

If we do a quick preview on the dataset we can see that every column has the double quotes even the columns where there is no data. If you open the text file in Excel the double quotes are automatically stripped, so what needs to be done in SSIS to accomplish this.

On this screen you can see the highlighted area and the entry that is made for the “Text qualifier”. Here we enter in the double quote mark ” and this will allow SSIS to strip the double quotes from all columns.

If we do another preview we can see that the double quotes are now gone and we can move on to the next part of our SSIS package development.

Above this is a simple fix to solve this problem. If you are faced with this issue, hopefully this gives you a quick answer to get your development moving forward. This same technique can be used to strip any other text qualifier data from your files.

Regards,
Roopesh Babu V

SSRS with SQL Detailed Training Sessions (20 Videos)

By

roopesh.valluru

-

September 29, 2013

43

Friends,

This post is very useful to those who wants to learn SSRS. I have covered almost all concepts and please do drop a message if you are happie with the tutorials.

(Link – http://www.4shared.com/get/pcTUO8Nr/ssrs_batch_12_part_10.html)

My Best Wishes !!

Regards,
Roopesh Babu V