SSIS : Script Component as Transformation

The Script Component is one of the most powerful tools in SSIS. It can be used as a Source, Destination, and Transformation.

In this blog, I will show you how to use the SSIS Script Component as a Transformation with a example. Normally, we can use Script Component to do some task that is not possible for the built-in transformations, or we can use SSIS Script Component as a Transformation to combine the work of multiple transformations in one place.

Example Data

Before we start creating the SSIS Script Component as a Transformation package, Let us see what the data we are going to use. If you want to know what the example doing you can look into like SSIS : Compare two flat files

We have one of big Merge Full Outer Join result and need to compare ‘field'(come from left join) and ‘field(1)’ (come from right join). I am going to using Script Component to do this compare. Below the the merge output screenshot, there are more than hundred field in both left and right join table.

Create Script Component as transformation

To create Script Component as transaformation:

Step 1 : Drag and drop Script Component in the SSIS toolbox to the data flow region. Once you drop the Script component, a new pop up window called Select Script Content Type opened. Then selecting the Transformation option

Step 2 : Double click on the Script component will open the following editor to configure the properties.

Step 3: Within the SSIS Script Component as Transformation Input Columns tab, you can cross-check the input columns.

Step 4: Within the Input and Outputs tab, Go to Output Columns to add output columns.

Step 5 : Within the Script tab, please click on the Edit Script.. button to write the actual C# Script

Once you click on the Edit Script, it will open the main.cs class file to write the C# code. Please write your custom code inside the Input0_ProcessInputRow(Input0Buffer Row) function

Step 6 : Add your custom C# code here. For this example, I am using C# reflection to do comparison. In this way, I don’t need to compare field by field, even if column name changed we don’t need to change the comparison code.

Step 7 : Once you finished editing the Script, Please close the main.cs file. Next, drag the OLE DB Destinations, and join the script component Output Arrow to this new OLE DB Destination

Configure File & Folder Permissions for SQL Server

Since SQL Server 2012 permissions are assigned to the per-service SID for each of its instance. This blog will show how to configure file & folder permission for the SQL Server Database Engine. SQL Server must have permission of the Windows file system to access the file folder where database files are stored. The default SQL server data and backup folder permission are configured during setup. But if your database files saved in a different folder, you might need to follow below steps to setup SQL Server permission to that folder

Configure folder permission for SQL Server instance

  1. Using Windows Explorer, navigate to the file system location where the database files are stored. Right-click the file system folder, and then click Properties.
  2. On the Security tab, click Edit, and then Add.
  3. In the Select Users, Computer, Service Account, or Groups dialog box, click Locations, at the top of the location list, select your computer name, and then click OK.
  4. In the Enter the object names to select box, type the name of instance. If you database use default name then use NT SERVICE\MSSQLSERVER, or for named instance please use NT SERVICE\MSSQL$InstanceName
  5. Click Check Names to validate the entry
  6. In the Group or user names box, select the name added at step 4, and then in the Permissions for box, select the Allow check box for Full control.
  7. Click Apply, and then click OK twice to exit.

SSIS : CS2001 Missing AssemblyAttributes.cs when executing package from the deployed server

Recently, one of my deployed SSIS package keep failing and when I check execution report, the error messages shows:

Error: CS2001 – Source file ‘C:\Windows\TEMP.NETFramework,Version=v4.0.AssemblyAttributes.cs’ could not be found, CSC, 0, 0

As this package was running well at the back-end before and there is not new release, So at the first stage, I thought maybe is dll register issue, as I was using third part dll in the Script Task code. I registered it again. But still now working,I started to check on website. I one clue from below stackoverflow link:

https://stackoverflow.com/questions/35347632/cs2001-missing-assemblyattributes-cs-when-executing-ssis-package-deployed-to-the

I opened folder C:\Windows\Temp and checked the Temp folder Properties->Security, I noticed there is odd account: ‘Account Unknown s-1-15-3-xxxx’.

In the Temp folder Properties-> Security ->Advanced page, ‘disable inheritance’ to remove this ‘Unknown account’ and click ‘Apply’ . Once this unknown account removed, ‘enable inheritance’ again. Issue is fixed now.

SSIS: Debugging Script component break point is not working

Issue 1

I created a SSIS Script Component task (C#4.0 VS2012) and I want to debug the C# code inside the Script component. I set few break points then run with debugging. The break point does not fire at all. I double checked the script editor and package, both shows break point set up successful.

Solution

The reason it might not be triggered is that it may be using the 64 bit runtime. Try running it in the 32 bit runtime. To fix it –

  1. Go to the Solution Explorer
  2. Right click your SSIS project node > Properties
  3. In Configuration Properties > Debugging > Debug Options > Set Run64BitRunTime to False.

Issue 2

Created SSIS Script component task with script languageC# VS2012, but added .net 4.6 dll or used upper C# 4.0 feature then break point cannot triggered as well.

Solution

  1. remove all above 4.0 version feature and dll
  2. or upgrade project to latest version

Use PowerShell to search file content on windows

In this blog, I will show you how to use powershell Select-String cmdlet to string or words from multiple files on Windows quickly?

Select-String is based on lines of text. By default, Select-String finds the first match in each line and, for each match, it displays the file name, line number, and all text in the line containing the match.

Find matches in special files

This command searches all files with the .php file name extension in the current directory

select-string *.php -pattern function

The output displays the lines in those files that include the specified string.

Find a string in subdirectories

This example searches a directory and all of its subdirectories for a specific text string.

Get-ChildItem -Path C:\inetpub\wwwroot\wordpress -Include *.php -Recurse | Select-String -Pattern ‘function posted_on’

Get-ChildItem uses the Path parameter to specify folder, The Include parameter indicate the file type will be searched The Recurse parameter includes the subdirectories. The objects are sent down the pipeline to Select-String.

Select-String uses Pattern parameter and specifies the string ‘ function posted_on ‘

Find a pattern match with wildcard

Select-String -Path ” C:\inetpub\wwwroot\wordpress\*.php” -Pattern ‘posted_on*’

IIS Granting folder permissions to Website

Whenever a new website created, IIS will create an application pool that has the same name as website. For example, if you create an website with the name “fictionlib,” an application pool with the name ” fictionlib ” is created. Each application pool has a spcial a security identifier in Windows, system files and folders can be secured by using this identity. However, the identity is not a real user account and will not show up as a user in the Windows User Management Console.

To config, website folder permission, you need to follow below steps:

  1. Open Windows Explorer
  2. Select Website folder
  3. Right click the directory and select Properties
  4. Select the Security tab
  5. Click the Edit button and then Add button
  6. Click the Locations button and make sure that you select your computer.
  7. Enter IIS AppPool\ (eg: IIS AppPool\website name) in the Enter the object names to select: text box
  8. Click the Check Names button and click OK.
  9. Check Modify under the Allow column, and click OK, and OK.

SSIS: String Variables in Derived Columns

I was recently tried to add a string variable as new derived columns, but got a very tricky SSIS run time error:

truncation error occurred on the specified object of the specified component

Then I checked Microsoft SSIS document and found below comments about Length’

Screenshot of SSIS

But there is not warning or error at all in in the design view. When I check the properties of derived column, I found the type is Unicode  String and and Size always 0.

If adding data to a new column, the Derived Column TransformationEditor dialog box automatically evaluates the expression and sets the column length for string data. The value of this column is read-only.

Which means, by default SSIS will retrieve the data size from variable definition. The string variable content I was using always blank, that’s why size is 0 by default.

Solution

For pass this issue, I know there are two options:

  1. we can using Derived Column ‘Advanced Editor ‘ to manually changed the new derived column type and length to correct one
  2. Assign default value to string variable, make the content is not blank.

.Net Core Connection String

In ASP.NET Core the configuration system is very flexible, and the connection string could be stored in appsettings.json. In this blog, this blog shows asp.net core how to use the connection string stored in appsettings.json.

Config appsettings.json file

To define the connection strings in appsettings.json it is important to specify it in the right section of the JSON structure. Below snippet shows how to store connection string in appsettings.json file

{
“Logging”: {
“LogLevel”: {
“Default”: “Warning”
}
},
“AllowedHosts”: “*”,
“ConnectionStrings”: {
“Dev”: “Server=myServer;Database=dev;Trusted_Connection=True;”,
“Live”: “Server=myServer;Database=live;Trusted_Connection=True;”
}
}

Read connection string from appsettings.json file

For Entity Framework Core, we can use the Configuration API to pass the connection string to the DbContext in the Startup class:

public void ConfigureServices(IServiceCollection services) {
if (CurrentEnvironment.IsDevelopment())
{
services.AddDbContext(options => { options.UseSqlServer(Configuration.GetConnectionString(“Dev”)); });
}
else {
services.AddDbContext(options => { options.UseSqlServer(Configuration.GetConnectionString(“Live”)); });
}
}

Not Entity Framework

If you are not an Entity Framework Core fan, then you can create a interface with property to store the connection string.

public interface IDBInfo
{
string DbConnectionString { get; }
}

In the Startup class we can use AddScoped method to read data from json file to interface:

public void ConfigureServices(IServiceCollection services) {
if (CurrentEnvironment.IsDevelopment())
{
services.AddScoped(x => new DBInfo(Configuration.GetConnectionString(“Dev”)));
}
else {
services.AddScoped(x => new DBInfo(Configuration.GetConnectionString(“live”)));
}
}

.NET Core with Dapper

When you searching .NET Core database via Google, all results in the first five page are EF Core. But I am a Dapper fans, I thought I should introduce about .NET Core and Dapper.

Add Connection String to appsettings.json

.Net Core can use appsettings.json to store database connection string. Open the file and add a ConnectionString section to the file. You can maintain multiple connection strings in this section if you have more than one connection string.

“ConnectionStrings”: {
“Dev”: “Server=WIN-TAUP5KNN93J\SQLEXPRESS;Database=test;User Id=sa;Password=sa;”,
“Test”: “Server=WIN-TAUP5KNN93J\SQLEXPRESS;Database=live;User Id=sa;Password=sa;”
}

Create generic interface and class to pass the connection string between service

Because the heart of .NET Core is DI. we are going to use DI to pass the connection strings between different classes and services.

public interface IDBInfo
{
string GetConnectionString();
}
public class DBInfo : IDBInfo
{
private string _dbString;
public DBInfo(string connString)
{
_dbString = connString;
}
public string GetConnectionString()
{
return _dbString;
}
}

DI passing connection string to DBinfo class

Using below code to get connection string from setting file and pass to DBInfo class.

services.AddTransient(x=> new DBInfo(Configuration.GetConnectionString(“Dev”)));

Add Nuget Package

Open you database access project and add below two Nuget packages:

Install System.Data.SqlClient for using SqlConnection

Install Dapper

Using Dapper

To make things simple, I am only show Dapper related code at below. Dapper has number of extension methods. For detail Dapper API, you can get from Dapper Gehub project page. In below code shows how to pass connection string from contractor to service and fetch the data from table.

public class UserService : IUserService
{
#region Fields
private readonly IDBInfo _db;
#endregion
public UserService(IDBInfo dbInfo) {
_db = dbInfo;
}
public List GetUserList()
{
using (var con = new SqlConnection(_db.GetConnectionString()))
{
var users = con.Query(
“SELECT [User_Id],[PC_Name],[PC_IP],[First_Name],[Last_Name],[Password],[Is_Manager] FROM [tb_users]”
)
.ToList();
return users;
}
}
}

SQL SERVER : Login Failed. The Login is From an Untrusted Domain and Cannot be Used with Windows Authentication

Recently, I try to setup a new SQL SERVER on AWS. When I try to connect to server via private IP. I got below SQL Login error:

Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication. [CLIENT: 172.31.23.96]
SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The operating system error code indicates the cause of failure. The logon attempt failed [CLIENT: 172.31.23.96]

WORKAROUND/SOLUTION

Loopback check can be removed by adding a registry entry as follows:

  • Edit the registry using regedit. (Start –> Run > Regedit )
  • Navigate to: HKLM\System\CurrentControlSet\Control\LSA
  • Add a DWORD value called “DisableLoopbackCheck”
  • Set this value to 1

Refer : https://blogs.msdn.microsoft.com/dataaccesstechnologies/2012/12/19/error-message-login-failed-the-login-is-from-an-untrusted-domain-and-cannot-be-used-with-windows-authentication/