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
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;
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;
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.csConfigureServices 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);
}
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);
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;
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);
}
}
}
}
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.