SSIS: Converting a string to byte-array

This is a short how-to convert a string to byte-array without any encoding (byte-by-byte) in SQL Server Integration Services (SSIS).

I would like to save this string “I love you” as BLOB. Let’s assume our Output column name is fileContent.

Row.filecontent.AddBlobData(GetBytes("I love you"));

AND

byte[] GetBytes(string str)
{
    byte[] bytes = new byte[str.Length * sizeof(char)];
    System.Buffer.BlockCopy(str.ToCharArray(), 0, bytes, 0, bytes.Length);
    return bytes;
}

Reference

https://stackoverflow.com/questions/35703025/how-do-i-convert-a-string-to-a-blobcolumn-in-ssis/46837584

https://stackoverflow.com/questions/472906/how-do-i-get-a-consistent-byte-representation-of-strings-in-c-sharp-without-manu/10380166#10380166

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.

Load Bootstrap tabs dynamically

Need Bootstrap tabs, but with dynamic content? Use this JS to fetch the content before showing the tab. Works for in-content links as well.

//the reason for the odd-looking selector is to listen for the click event
// on links that don't even exist yet - i.e. are loaded from the server.
$('#tabs').on('click','.tablink,#prodTabs a',function (e) {
    e.preventDefault();
    var url = $(this).attr("data-url");

    if (typeof url !== "undefined") {
        var pane = $(this), href = this.hash;

        // ajax load from data-url
        $(href).load(url,function(result){      
            pane.tab('show');
        });
    } else {
        $(this).tab('show');
    }
});

Read more here