Parameterize ADO.NET Source in Data Flow

Parameterizing OLEDB as source in Data Flow is easier. It’s not that straight using ADO.NET.

In SSIS you can’t parametrize ADO.NET source. You have to use a workaround.

Luckily, there are few workarounds. One would be creating Script Component that acts like source and code it. However, one can’t always easily convert the existing resource into script, especially when he lacks ADO.NET programming knowledge.

There is another workaround, and that would be creating the SQL Query before the ADO.NET Source takes action. However, when you open ADO.NET source, you will notice that Data access mode doesn’t allow variable input. So, how do you proceed?

You want to dynamically set the SQL expression of the ADO.NET source, so you have to tell your data flow task to configure the SSIS ADO.NET source component by using Expression.

To make the long story short (or not-quite-so-short :), do this:

  • in your package, enter your data flow task with source/destination components
  • click anywhere on the background, to have Task properties shown in Property panel
  • in Property panel find Expressions property, that can configure various data source/destination properties, and open it using ellipsis button (…)
  • under Property, select SQL Command property of your source (e.g. [ADO.NET source].[SqlCommand]) to add one row
  • click ellipsis button for the row to open Expression Builder
  • build your dynamic query in the Expression Builder

The last step could be somewhat cumbersome for date/datetime parameter. However, here’s the example, for your convenience:

"SELECT * FROM YOUR_SOURCE_TABLE WHERE your_date_column = '" + 
  (DT_WSTR,4)YEAR(@[User::VAR_CONTAINING_DATE]) + "-" +
  (DT_WSTR,2)MONTH(@[User::VAR_CONTAINING_DATE]) + "-" +
  (DT_WSTR,2)DAY(@[User::VAR_CONTAINING_DATE]) + "'"

Here is a reference for using OLEDB as source;

Store and Read Objects from Session in ASP.NET Core

Here you go;

In your Startup.cs, under the Configure method, add the following line:

public void Configure(IApplicationBuilder app, IHostingEnvironment env)
{
app.UseSession();
}

And under the ConfigureServices method, add the following line:

public void ConfigureServices(IServiceCollection services)
{
  //Added for session state
  services.AddDistributedMemoryCache();

  services.AddSession(options =>
  {
  options.IdleTimeout = TimeSpan.FromMinutes(10);               
  });
}

The simple method to store and read data from session is;

# In a class;
Session.SetString("Email", "foo.com"); //store data
Session.GetString("Email"); //read data

# In Razor pages
@using Microsoft.AspNetCore.Http; 
string CustomerEmail = String.Empty;
@if (Context.Session.GetString("Email") != null)
 {
     CustomerEmail = Context.Session.GetString("Email").ToString();
 }
}

# You can use it in Razor markup 
<ul class="navbar-nav mr-auto float-right">
   <li class="nav-item">
      <span class="navbar-text text-light">Hello @CustomerEmail</span>
   </li>

</ul>

In order to store complex objects in your session in .NET Core, follow the following steps:

Create a model class of your object type (in your case EmployeeDetails):

public class EmployeeDetails
{
    public string EmployeeId { get; set; }
    public string DesignationId { get; set; }
}

Then create a SessionExtension helper to set and retrieve your complex object as JSON:

public static class SessionExtensions
{
  public static void SetObjectAsJson(this ISession session, string key, object value)
   {
     session.SetString(key, JsonConvert.SerializeObject(value));
   }

   public static T GetObjectFromJson<T>(this ISession session, string key)
   {
     var value = session.GetString(key);
     return value == null ? default(T) : JsonConvert.DeserializeObject<T>(value);
   }
}

Then finally set the complex object in your session as:

var employee = new EmployeeDetails();
employee.EmployeeId = "1";
employee.DesignationId = "2";

HttpContext.Session.SetObjectAsJson("EmployeeDetails", employee);

To retrieve your complex object in your session:

var employeeDetails = HttpContext.Session.GetObjectFromJson<EmployeeDetails>("EmployeeDetails");
int employeeID = Convert.ToInt32(employeeDetails.EmployeeId);
int designationID= Convert.ToInt32(employeeDetails.DesignationId);

My session was not working. it turns out that it relates to GDPR issues. Three fixes;

FIX-I

The first fix is to provide user a login page and let him accept cookies policy.

If you don’t provide a login page and still want to configure sessions in ASP.NET Core, you need these two fixes in Startup.cs file;

Fix-II Startup.cs file ConfigureServices method. Add this line;

 services.AddSession(options =>
 {
    options.Cookie.IsEssential = true;
 });

Fix-III Startup.cs file Configure method, comment this line;

//app.UseCookiePolicy();

The most common fix is FIX-I. For testing I choose Fix-II and session started working.

Sources

https://andrewlock.net/session-state-gdpr-and-non-essential-cookies/

Dataset and Data Tables (a refresher)

Dataset is an in-memory representation of a database relationship. Data Tables are individual tables that can be joined with Data relation objects.

Let’s go through an example of a tenant and his/her maintenance request;

Create a Dataset;

//create dataset
DataSet serviceRequest = new DataSet();

Create tenant table

//create tenant table
DataTable tenant = new DataTable();
tenant.Clear();
//add columns
tenant.TableName = "tenant";
tenant.Columns.Add("tenantID");
tenant.Columns.Add("Name");
tenant.Columns.Add("AptNumber");
//add rows
DataRow rowT = tenant.NewRow();
rowT["tenantID"] = "A1";
rowT["Name"] = "khan";
rowT["AptNumber"] = "1";
tenant.Rows.Add(rowT);

//Add this table to data set
serviceRequest.Tables.Add(tenant);

Create request table

//create request table
DataTable request = new DataTable();
request.Clear();
//add columns
request.TableName = "request";
request.Columns.Add("requestID");
request.Columns.Add("tenantID");
request.Columns.Add("description");
//add rows
DataRow rowR = request.NewRow();
rowR["requestID"] = "1";
rowR["tenantID"] = "A1";
rowR["description"] = "air conditioner does not work";
request.Rows.Add(rowR);
//add this table to data set
serviceRequest.Tables.Add(request);

Create relationship

//create relationship
DataRelation relation;
DataColumn tenantColumn = serviceRequest.Tables["tenant"].Columns["tenantID"];
DataColumn requestColumn = serviceRequest.Tables["request"].Columns["tenantID"];
relation = new DataRelation("relation", tenantColumn, requestColumn);
//assign relation
serviceRequest.Relations.Add(relation);

Create LINQ query to read data;

//simple LINQ query
var maintenanceData = (from x in serviceRequest.Tables["tenant"].AsEnumerable()
join y in serviceRequest.Tables["request"].AsEnumerable()
on x.Field<string>("tenantID") equals y.Field<string>("tenantID")
select new
   {
       TenantID = x.Field<string>("tenantID"),
       RequestID = y.Field<string>("requestID"),
       Description = y.Field<string>("description")
   }).ToList();

Here is the output of this LINQ query;

If we are going to add a new column in request table, for example AllowToEnterApt. We can use foreach loop to update the values in this column;

serviceRequest.Tables["request"].Columns.Add("AllowToEnterApt");
foreach (DataRow row in serviceRequest.Tables["request"].Rows)
{
     row["AllowToEnterApt"] = 1;
}

Remove single quote from strings

I would like to remove single quote from beginning and end of this string;

string tabName = "'i# milestones$'";
string outputTabName = tabName.Trim(new char[]{(char)39});

Where (char)39 represents ', and the .Trim() will remove the first and last ' from the string; You can try like this as well:

string outputTabName = tabName.Trim('\'');

Get File Extension using T-SQL

The easies way to do this using SQL Server 2017 and up;

DECLARE @FileName NVARCHAR(255) = N'Need to get extension of this file.xlsx';
--PRINT @FileName
SELECT 
	@FileName ExcelFileName,
	--check there is a '.' in ExcelFileName
	CASE WHEN @FileName LIKE '%.%' THEN
		REVERSE(left(REVERSE(@FileName), CHARINDEX('.', REVERSE(@FileName)) - 1))
	ELSE ''
	END ExcelFileExtension
WHERE 1=1

Earlier versions can use this approach;

DECLARE @FileName NVARCHAR(255) = N'Need to get extension of this file.xlsx';
--PRINT @FileName
SELECT 
	@FileName ExcelFileName,
	CASE 
         WHEN @FileName LIKE '%.%' THEN 
			RIGHT(@FileName, LEN(@FileName) - CHARINDEX('.', @FileName)) 
         ELSE '' 
       END ExcelFileExtension 
WHERE 1=1