Using class library in SSIS Projects without GAC installation

I don’t want to add custom assembly in GAC. Here is my research.

There is no direct way to do that from integration services packages, because the only workaround is using CurrentDomain_AssemblyResolve function – loading an assembly from a location instead of installing it into GAC.

There are 3 ways for using custom dll’s on deployment:

  • Assigning DLL’s to the GAC
  • Using the AssemblyResolve Function
  • Copy all Dll’s to the sql server DTS Assemblies folder (example for SQL Server 2008: C:\Program Files\Microsoft SQL Server\100\DTS\Binn) and to the .Net framework assemblies folder.

There could be an interesting workaround by using a Web Service instead of direct dll, so you can create a web service which contains the methods you are using and add a Web reference instead of a Local assembly

Here is an example.

This might be your starting point.

Using custom DLL in script task

Resources

https://docs.microsoft.com/en-us/answers/questions/92003/unable-to-reference-custom-class-library-in-ssis-s.html

https://www.codeproject.com/Articles/895028/Refering-dlls-in-SSIS-Script-Task

https://www.dotnetspider.com/resources/45645-How-to-use-custom-DLL-in-SSIS-Package.aspx

AutoMapper in ASP.NET Core

How to keep a separation between domain models and view models and let them exchange data in an easier and simple way? We write code that allows us to map domain model into view model. As we add more views and domain models, we end up writing more mappers. We write mappers to map domain transfer objects from database layer into domain objects.

This practice is repetitive. AutoMapper solve this problem. It’s a convention-based object-to-object mappers.

We are going to use these NuGet packages for ASP.NET Core 2.1;

AutoMapper.Extensions.Microsoft.DependencyInjection		v3.1.0
Microsoft.Extensions.DependencyInjection.Abstractions		v3.1.0

For ASP.NET Core V2.1, we will need at least V3..0.1 of AutoMapper.Extensions.Microsoft.DependencyInjection. This package will install AutoMapper package automatically.

Configure AutoMapper in Startup.cs class under ConfigureServices method;

//Auto mapper configuration
services.AddAutoMapper();
services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_1);

The above single line works fine but If we want to explicit in configuration, the alternative is;

var mapperConfig = new MapperConfiguration(mc =>
{
    mc.AddProfile(new MappingProfile());
});
IMapper mapper = mapperConfig.CreateMapper();
services.AddSingleton(mapper);
services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_1);

AutoMapper in Action

Create a user model in Model folder.

public class User
{
  public User(int id, string firstName, string lastName, string emailAddress)
  {
            Id = id;
            FirstName = firstName;
            LastName = lastName;
            EmailAddress = emailAddress;
  }

        public int Id { get; }
        public string FirstName { get; }
        public string LastName { get; }
        public string EmailAddress { get; }
}

Create a view model in Model folder that will be used to display User data.

public class UserViewModel

{
        public int Id { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string EmailAddress { get; set; }
 }

We need to tell AutoMapper to map from User Model to User View Model. For that we will use Profile. Profiles in AutoMapper are a way of organizing mapping collections. To create Profile, We create a new class and inherit from Profile. This class will hold mapping configuration of new classes.

public class MappingProfile : Profile
{
        public MappingProfile()
        {
            CreateMap<User, UserViewModel>();
            CreateMap<UserViewModel, User>();
        }
}

The same profile can be created like this;

public class MappingProfile : Profile
{
        public MappingProfile()
        {
            CreateMap<User, UserViewModel>().ReverseMap();
        }
}

We now have a MappingProfile class that creates the mapping between our User Model and User ViewModel. But how does AutoMapper know about our UserProfile class? Well, towards the start of this example we added this line of code to our ConfigureServices method in Startup.cs:

services.AddAutoMapper();

When our application starts up and adds AutoMapper, AutoMapper will scan our assembly and look for classes that inherit from Profile, then load their mapping configurations. I also have an alternative explicit implementation in startup class if you prefer.

Let’s create a new UserController in the Controllers folder and inject the IMapper interface into the constructor:

public class UserController : Controller
{
        private readonly IMapper _mapper;
        public UserController(IMapper mapper)
        {
            _mapper = mapper;
        }

        public IActionResult Index()
        {
            return View();
        }
}

As with Profiles, by calling AddAutoMapper in our Startup.cs ConfigureServices method, it’s taken care of registering IMapper for us. In Index Action method, let’s create a User object and use IMapper interface to call the Map method:

We give the Map method the type we want to map to and the object we would like to map from:

public IActionResult Index()
{
            var user = new User(1, "Shahzad", "Khan", "shahzad@msn.com");
            UserViewModel viewModel = _mapper.Map<UserViewModel>(user);
            return View(viewModel);
}

Finally create the view;

@model UserViewModel

@{
    ViewData["Title"] = "Index";
}

<h2>Index</h2>

<div>
    <h4>UserViewModel</h4>
    <hr />
    <dl class="dl-horizontal">
        <dt>
            @Html.DisplayNameFor(model => model.Id)
        </dt>
        <dd>
            @Html.DisplayFor(model => model.Id)
        </dd>
        <dt>
            @Html.DisplayNameFor(model => model.FirstName)
        </dt>
        <dd>
            @Html.DisplayFor(model => model.FirstName)
        </dd>
        <dt>
            @Html.DisplayNameFor(model => model.LastName)
        </dt>
        <dd>
            @Html.DisplayFor(model => model.LastName)
        </dd>
        <dt>
            @Html.DisplayNameFor(model => model.EmailAddress)
        </dt>
        <dd>
            @Html.DisplayFor(model => model.EmailAddress)
        </dd>
    </dl>
</div>
<div>
    @Html.ActionLink("Edit", "Edit", new { /* id = Model.PrimaryKey */ }) |
    <a asp-action="Index">Back to List</a>
</div>

Here is the page;

We just scratched the surface of what AutoMapper has to offer in terms of mapping objects from one to another.

Summary

First, you need both a source and destination type to work with. The destination type’s design can be influenced by the layer in which it lives, but AutoMapper works best as long as the names of the members match up to the source type’s members. If you have a source member called “FirstName”, this will automatically be mapped to a destination member with the name “FirstName”. AutoMapper also supports Flattening, which can get rid of all those pesky null reference exceptions you might encounter along the way.

Once you have your types, and a reference to AutoMapper, you can create a map for the two types.

CreateMap<User, UserViewModel>().ReverseMap();

The type on the left is the source type, and the type on the right is the destination type. To perform a mapping, use the Map method.

var userEntity = await _unitOfWork.GetAllUsersAsync();
List<UserViewModel> vm = Mapper.Map<List<UserViewModel>>(userEntity.Result);

References

https://docs.automapper.org/en/stable/Getting-started.html

https://automapper.org/

https://jimmybogard.com/

https://stackoverflow.com/questions/40275195/how-to-set-up-automapper-in-asp-net-core

https://stackoverflow.com/questions/50411188/trying-to-add-automapper-to-asp-net-core-2

https://stackoverflow.com/questions/13479208/automapper-auto-map-collection-property-for-a-dto-object/13499361

https://stackoverflow.com/questions/52218340/automapper-projectto-adds-tolist-into-child-properties

https://code-maze.com/automapper-net-core/

SSIS: Use Sql connection inside script component

If the requirement is to run dynamic query on each row, you might get inside Script comoponent. Here is how you can grab SQL connection and run your queries;

Your connection is defined here on script component;

Edit Script…
public class ScriptMain : UserComponent
{
    IDTSConnectionManager100 connMgr;
    SqlConnection sqlConn;
    SqlDataReader sqlReader;

    public override void AcquireConnections(object Transaction)
    {
        connMgr = this.Connections.MyConn;
        sqlConn = (SqlConnection)connMgr.AcquireConnection(null);
    }

    public override void ReleaseConnections()
    {
        //base.ReleaseConnections();
        connMgr.ReleaseConnection(sqlConn);
    }

    public override void PreExecute()
    {
        base.PreExecute();
        string KeyValue = Variables.vFileOutputString;

        String vSqlStatement = "SELECT SqlStatement FROM [DataHub].[vw_LastStepForRglData] WHERE ADNumber = '" + KeyValue + "' "; 
        SqlCommand cmd = new SqlCommand(vSqlStatement, sqlConn);
        sqlReader = cmd.ExecuteReader();
    }

    public override void PostExecute()
    {
        base.PostExecute();
        sqlReader.Close();
    }

    public override void CreateNewOutputRows()
    {
        while (sqlReader.Read())
        {
            {
                DynamicSqlBuffer.AddRow();
                DynamicSqlBuffer.SqlStatement = sqlReader.GetString(0);
            }
        }
    }
}

Hope this will help.

Download Excel Files

Recently i had to download binary format data using ASP.NET Core, Console Core and SSIS applications;

ASP.NET Core application;

Here is a work around for ASP.NET Core Where we are getting back File Result;

[HttpGet]
public IActionResult GetExcelData2(Guid? documentId)
{
            var sql = "SELECT TOP(1) FileName, ContentType, FileStreamCol FROM [dbo].[STG_Document] WHERE 1 = 1 AND DocumentId = @documentId";

            byte[] fileContent = null;
            string mimeType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            string fileName = "C:\\Business\\myfile.xlsx";

            using (var connection = new SqlConnection(AppConnectionString.Value.PMBImportsConn))
            {
                connection.Open();
                var result = connection.Query(sql, new { DocumentId = documentId });
                result.AsList().ForEach(x =>
                {
                    fileContent = x.FileStreamCol;
                    mimeType = x.ContentType;
                    fileName = x.FileName;
                });

                return File(new MemoryStream(fileContent, 0, fileContent.Length - 1), mimeType, fileName);
            }
}

I am subtracting 1 element from byte array to fix a problem I encountered in a VB6 application. Otherwise, you don’t need to subtract this element.

 return File(new MemoryStream(fileContent, 0, fileContent.Length), mimeType, fileName);

This is how we call it from view;

<a asp-controller="Project" asp-action="GetExcelData2" asp-route-documentId="@p.DocumentId">Download</a>

If everything is correct, the download starts automatically.

C# Console application;

Here is how this will work in C# Console application;

internal void DownloadExcelFiles()
{
            // Create folder to save downloaded files
            string myFolderPath = @"C:\WorkToDo\";
                        Directory.CreateDirectory(myFolderPath + "\\FilesDownloaded");

            //run sql query to grab image files
            var sql = "SELECT DocumentId AS DocumentId, ContentType, FileName, FileStreamCol  FROM dbo.STG_Document WHERE 1 = 1";

            byte[] fileContent = null;
            string mimeType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            string fileName = "";

            using (var connection = new SqlConnection(DB2SqlConnectionString))
            {
                connection.Open();
                var result = connection.Query(sql);
                result.AsList().ForEach(x =>
                {
                    string fileExtension = "xlsx";
                    switch (x.ContentType)
                    {
                        case "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet":
                        case "application/vnd.ms-excel":
                        case "application/msexcel":
                            fileExtension = "xlsx";
                            break;
                        case "application/vnd.ms-excel.sheet.macroEnabled.12":
                            fileExtension = "xlsm";
                            break;
                        default:
                            break;
                    }
                    string fileToWrite = $"{myFolderPath}\\FilesDownloaded\\{x.DocumentId}.{fileExtension}";
                    fileContent = x.FileStreamCol;
                    mimeType = x.ContentType;
                    fileName = x.FileName;

                    FileStream fileStream = File.OpenWrite(fileToWrite);
                    fileStream.Write(fileContent, 0, fileContent.Length - 1);
                    fileStream.Flush();
                    fileStream.Close();
                });
                Console.WriteLine("Download complete..");
            }           
}

I am subtracting 1 element from byte array to fix a problem I encountered in a VB6 application. Otherwise, you don’t need to subtract this element.

FileStream fileStream = File.OpenWrite(fileToWrite);
fileStream.Write(fileContent, 0, fileContent.Length);

I ma using Dapper and SqlClient libraries ASP.NET Core and Console application to access data from Database.

SSIS application;

Here is how this will work in SQL Server Integration services;

ADO.NET Source

SELECT DocumentId, [FileName], [ContentType], [FileStreamCol]
FROM [dbo].[Document]
WHERE 1=1

SC-Transform data

This is a SSIS Transformation script component. This prepares the data that will be used later on in Export Column task;

Here is the code block;

private byte[] fileContent = null;
private string fileExtension = String.Empty;

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
        //determine content type
        switch (Row.ContentType)
        {
            case "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet":
            case "application/vnd.ms-excel":
            case "application/msexcel":
                fileExtension = "xlsx";
                break;
            case "application/vnd.ms-excel.sheet.macroEnabled.12":
                fileExtension = "xlsm";
                break;
            default:
                break;
        }
Row.fileContentName = String.Concat(Row.DocumentId, ".", fileExtension);
//A VB6 application saves excel files as BLOB objects. For download it subtracts one element from 
//byte array. Following same dance here
        var blobLength = Convert.ToInt32(Row.FileStreamCol.Length);
        fileContent = Row.FileStreamCol.GetBlobData(0, blobLength - 1);
        Row.fileContent.AddBlobData(fileContent);
}

Derived column transform;

Export column settings;

Checking Force Truncate overwrites extracted files.

Hope this will help.