SSIS: SSIS stands for SQL Server Integration Services. It is the new data transformation standard for SQL Server 2005 and has replaced the old SQL Server Data Transformation Services.

In this post, I want to show how to update data from different table with condition that the data is Null.

1st Table is Person.Address and 2nd table is Address2

Now I want to update data in column AddressLine2 in Person.Address by using data in Address2 with condition that AddressID is same and AddressLine2 in Person.Address is Null

So 1st we create Data Flow Task in Control Flow tab

Create new Ole DB Connection in Connection manager, and add the flow that looks like below figures

In the OLE DB Source, add the table Person.Address
In the Lookup, Add table Address2. In the column tab at the lookup editor set like this:

So this Lookup transformation is actually add a new column from other table based on addressed.

The Derived Column transformation creates new column or replacing current column values by applying expressions to transformation input columns. An expression can contain any combination of variables, functions, operators, and columns from the transformation input. So from we need this to replace the new column (AddressLine2) from table Address2 into column AddressLine2 in table Person.Address

First we choose Replace ‘OLE DB Source.AddressLine2’ in Derived Column, then we add expression:

The OLE DB Command transformation runs an SQL statement for each row in a data flow. For example, you can run an SQL statement that inserts, updates, or deletes rows in a database table. So in this case, we need this transformation to update current data.

Question mark indicate the parameter. Just remember the sequence of the parameter.
After we fill in the Sql Command, choose Column Mappings Tab and point the Available input columns to the Available Destination Columns based on the parameter that already created.

I think, this is the most effective way to convert csv to dataset. Before this, I already try to use Microsoft.Jet.OLEDB.4.0 but the problem that shows an error message in finding csv file on the web server. So this method using FileStream is the best way.

Sometimes when you working with the large data, you might want to display the data in pivot table / cross tab. Crystal report have a very nice wizard to make our life easier to create cross tab, but not in vb.net

For example you have data table like Table CompanyDetail populated from the SQL Select Query below

company_code

PAY_AMOUNT

tax_period_month

777

3000,0000

05

777

2000,0000

08

777

1000,0000

11

1010000

4000,0000

11

777

5000,0000

11

And you want to display into this order:

company_code

05

08

11

777

3000,0000

2000,0000

6000,0000

1010000

4000,0000

So Here is the step to convert Data Table data into pivot table
Step1. Create this function:

ColumnX = Is the Column that you want to make cross tab (In this example is tax_period_month)
ColumnY = Is the column that will be put in the left column (company_code)
ColumnZ = Is the total value from combining columnX and columnY (PAY_AMOUNT)

Function GetInversedDataTable(ByVal table As DataTable, ByVal columnX As String, ByVal columnY As String, ByVal columnZ As String, ByVal nullValue As String, ByVal sumValues As Boolean) As DataTable
'Create a DataTable to Return
Dim returnTable As New DataTable()
If columnX = "" Then
columnX = table.Columns(0).ColumnName
End If
'Add a Column at the beginning of the table
returnTable.Columns.Add(columnY)
'Read all DISTINCT values from columnX Column in the provided DataTale
Dim columnXValues As New List(Of String)()
For Each dr As DataRow In table.Rows
Dim columnXTemp As String = dr(columnX).ToString()
If Not columnXValues.Contains(columnXTemp) Then
'Read each row value, if it's different from others provided, add to the list of values and creates a new Column with its value.
columnXValues.Add(columnXTemp)
returnTable.Columns.Add(columnXTemp)
End If
Next
'Verify if Y and Z Axis columns re provided
If columnY <> "" AndAlso columnZ <> "" Then
'Read DISTINCT Values for Y Axis Column
Dim columnYValues As New List(Of String)()
For Each dr As DataRow In table.Rows
If Not columnYValues.Contains(dr(columnY).ToString()) Then
columnYValues.Add(dr(columnY).ToString())
End If
Next
'Loop all Column Y Distinct Value
For Each columnYValue As String In columnYValues
'Creates a new Row
Dim drReturn As DataRow = returnTable.NewRow()
drReturn(0) = columnYValue
'foreach column Y value, The rows are selected distincted
Dim rows As DataRow() = table.[Select]((columnY & "='") + columnYValue & "'")
'Read each row to fill the DataTable
For Each dr As DataRow In rows
Dim rowColumnTitle As String = dr(columnX).ToString()
'Read each column to fill the DataTable
For Each dc As DataColumn In returnTable.Columns
If dc.ColumnName = rowColumnTitle Then
'If Sum of Values is True it try to perform a Sum
'If sum is not possible due to value types, the value displayed is the last one read
If sumValues Then
Try
drReturn(rowColumnTitle) = Convert.ToDecimal(drReturn(rowColumnTitle)) + Convert.ToDecimal(dr(columnZ))
Catch
drReturn(rowColumnTitle) = dr(columnZ)
End Try
Else
drReturn(rowColumnTitle) = dr(columnZ)
End If
End If
Next
Next
returnTable.Rows.Add(drReturn)
Next
Else
Throw New Exception("The columns to perform inversion are not provided")
End If
'if a nullValue is provided, fill the datable with it
If nullValue <> "" Then
For Each dr As DataRow In returnTable.Rows
For Each dc As DataColumn In returnTable.Columns
If dr(dc.ColumnName).ToString() = "" Then
dr(dc.ColumnName) = nullValue
End If
Next
Next
End If
Return returnTable
End Function

Step 2: Add gridview to the page and create connection to database to set data table:

This article demonstrates how to use ASP.NET and ADO.NET with Visual Basic .NET to create and to call a Microsoft SQL Server stored procedure with an input parameter and an output parameter.

1 step create store procedure

Create Procedure GetAuthorsByLastName1 (@au_lname varchar(40), @RowCount int output)
as
select * from authors where au_lname like @au_lname;
/* @@ROWCOUNT returns the number of rows that are affected by the last statement. */
select @RowCount=@@ROWCOUNT

Call the stored procedure in asp.net code

Private Function getDataset() As DataSet
Dim DS As DataSet
Dim MyConnection As SqlConnection
Dim MyDataAdapter As SqlDataAdapter
'Create a connection to the SQL Server.
MyConnection = New SqlConnection("server=(local);database=pubs;Trusted_Connection=yes")
'Create a DataAdapter, and then provide the name of the stored procedure.
MyDataAdapter = New SqlDataAdapter("GetAuthorsByLastName", MyConnection)
'Set the command type as StoredProcedure.
MyDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure
'Create and add a parameter to Parameters collection for the stored procedure.
MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@au_lname", _
SqlDbType.VarChar, 40))
'Assign the search value to the parameter.
MyDataAdapter.SelectCommand.Parameters("@au_lname").Value = Trim(txtLastName.Text)
'Create and add an output parameter to Parameters collection.
MyDataAdapter.SelectCommand.Parameters.Add(New SqlParameter("@RowCount", _
SqlDbType.Int, 4))
'Set the direction for the parameter. This parameter returns the Rows returned.
MyDataAdapter.SelectCommand.Parameters("@RowCount").Direction = ParameterDirection.Output
DS = New DataSet() 'Create a new DataSet to hold the records.
MyDataAdapter.Fill(DS, "AuthorsByLastName") 'Fill the DataSet with the rows returned.
'Get the number of rows returned, and then assign it to the variable
Dim rowCount As String
rowCount = MyDataAdapter.SelectCommand.Parameters(1).Value.ToString & " Rows Found!"
MyDataAdapter.Dispose() 'Dispose of the DataAdapter.
MyConnection.Close() 'Close the connection.
Return DS
End Function