How to install an SSL certificate on Microsoft IIS 10

Before install the SSL certificate, you need to know which kind of certificate you have.

If you certificate file is .cer file then just simply install it via IIS manager. But if have .key file and .crt file then you need to convert the certificate to .pfx file.

Installing certificate via IIS manager

  • go to the Start menu, choose Administrative Tools and select Internet Information Services (IIS) Manager. Otherwise, access it via Win+R >> inetmgr >> OK.
  • Click on the required server name and go to the Server Certificates option in the center menu.

Press the Complete Certificate Request button in the Actions right-side section.

Following the wizard to finish the import step by step. Once imported certificate is shown in the list of Server Certificates. Then you can assign the certificate to the website.

  • pand the Sites menu and choose the website you want to assign the certificate to. After that, click on the Bindings option in the Actions section.
  • In the Site Bindings window, click Add.
  • In the Add Site Binding window, choose the following parameters:

Type – https;

IP address – All Unassigned, or your IP address;

Port – 443;

SSL certificate – friendly name of the imported certificate.

After all details are selected, click OK button.

  • The new binding has been successfully created.

If the site already has https enabled, and if you want to update the SSL certificate, you will need to choose the Edit button in binding for port 443, select a friendly name for the new certificate from the dropdown list and click OK to apply the changes.

Converting to PFX file

If you have the private key in PEM format (.key file), you need to generate the certificate in PKCS#12 format (.pfx).

Use this tool to generate the certificate in PKCS#12. Use your certificate with .crt extension, CA bundle with .ca-bundle extension and the saved key with .key extension.

If there’s an OpenSSL client installed on the server, you can create PFX file out of a certificate in PEM format (.pem, .crt, .cer) or PKCS#7/P7B format (.p7b, .p7c) and the private key using the following commands.

PEM (.pem, .crt, .cer) to PFX

openssl pkcs12 -export -out certificate.pfx -inkey privatekey.key -in certificate.crt -certfile more.crt

*where “more.crt” is the name of the CA Bundle file

SSIS:Truncation may occur due to inserting data from data flow column

I have an SSIS package with a Data Flow that import data from Flat File Source to ADO NET Destination. Output column type of Flat File Source was configured correctly. Input Columns type and External Columns type in ADO NET Destination is automatically generated. But I got below warning messages:

Warning: 0x802092A7 at DFT_import_json, ADO NET Destination Transaction [62]: Truncation may occur due to inserting data from data flow column “reduced_trade_units” with a length of 100 to database column “reduced_trade_units” with a length of 50.

I see the column length of External Columns in ADO NET Destination Input is different with Input Columns length. I manually changed the length to the same as Input Columns length in the Advanced Editor for ADO NET Destination. But It reverted to wrong length again once I closed and open it again. How can I solve it?

After long investigate, I find out it’s related with Destination property setting. By default ADO NET Destination property ValidateExternalMetadata‘s value is true and it will automatically update the external columns type. Need change this property value to false if we wan to manually set external columns type.

How to Setup WordPress Custom Permalinks on Windows IIS

This blog will show how to config WordPress Custom URL Structure under Windows and IIS is how to handle URL Rewrites

Log in to the Admin section of your WordPress

Click on Settings -> Permalinks

Choose URL structure or enter a custom structure

Save below web.config file into WordPress root folder can allow permalinks (or “pretty URLs”) on Windows IIS.

<?xml version="1.0" encoding="UTF-8"?>

        <rule name="wordpress" stopProcessing="true">
          <match url="." />
            <add input="{REQUEST_FILENAME}" matchType="IsFile" negate="true"/>
            <add input="{REQUEST_FILENAME}" matchType="IsDirectory" negate="true"/>
        <action type="Rewrite" url="/index.php"/>

Once web.config file created, ‘Pretty URL’ will working properly, but if you have images or other kind of static files in the website, you need to add another rewrite rule to stop above rewrite:

<rule name="Redirect Image to HTTP" stopProcessing="true">
<match url=".*\.(gif|jpg|jpeg|png|css|js)$" ignoreCase="true"/>
<action type="Rewrite" url="{R:0}"/>

SSIS: Execute SQL Task get Stored Procedure return values

In some project we need to execution stored procedure in SSIS Execute SQL Task and want to capture the stored procedure’s return value. Can we do this in SSIS Execute SQL Task? How can implement it in SSIS?

The answer is yes. The Execute SQL Task configuration is depend on your stored procedure parameter setting and database connection type. For output parameter and stored procedure return value, the SSIS parameter config is different For OLE DB connection and ADO.NET connection config are different as well. In below example, I am using OLE DB connection.

Firstly, we need to create one stored procedure with both output parameter and return value:

create procedure sp_check_import_status @client_code varchar(10),@currenty_day varchar(8),
@output int output
declare @result int
select @result = count(1) from transfer_history
where [email protected]_code and day_transactions >= @currenty_day
set @output = @result
if @result = 1
return @result
return 0

Secondly, need to config SSIS Execute SQL Task

Double click Execute SQL Task to open task editor. In the editor window, set ResultSet to None and SQLSourceType to Direct Input, input following statement to SQLStatement

exec ? = sp_import_oan_check_import_status ?,?,? output

Switch to the Parameter Mapping window, click Add to add the variables/parameters created in SSIS project and map the parameters with the stored procedure result as follows:

Note that the ParameterName values are numeric. You need to set the name starting from 0 according to the parameters occurrence sequence in the SQLStatement.

SQL SERVER BCP UTF8 import and export, support UTF8 after SQL SERVER 2014SP2

The bulk copy program utility (bcp) is a very powerful bulk copy tool between database and flat file. Normally used for large number of data import and export. But if you sql server version is older then 2014 SP2, unlucklly you cannot process UTF8 data even if there is a code page parameter -C , but it doesn’t support UTF8. Refer to Microsoft community document

code_pageSpecific code page number; for example, 850.

** Important ** SQL Server does not support code page 65001 (UTF-8 encoding).

If you want to process UTF8, then must need to upgrade SQL Server to version 2014 SP2 or later. Once you installed the upgrade, then you can use BCP to import or export UTF8 as below:

To import UTF-8 data to SQL Server, use the BCP utility and run the following bcp t_users in “D:\test.csv” -c -C 65001

To export UTF-8 data to SQL Server, use the BCP utility and run the following bcp t_users out ” D:\test.csv ” -c -C 65001

SSIS: Foreach File Enumerator variable folder path and file filter

When we use SSIS to import files to Database, there is a very comm issues for most of user. How we can dynamically set the folder path and file filters for Foreach File Enumerator in a Foreach Loop. There are lots of website mentioned how to import different type files to database, how to loop folders and most of those website are hard code folder path and file filters. But for one of my project, I need to dynamically loop the folders and pick up file dynamically per parameter.

It is quite easy to implement the feature if you know the property Directory of the Foreach File Enumerator. Now let us look at the steps:

1. Create two SSIS package parameters CSVFileFilter and FolderPathTest2.

Create two variables CSVFileLists and CSVFileName

2. Create a Foreach Loop Container task to the Control Flow panel, double click it to pop up the property window.

3. Switch to the Collection tab, choose the Enumerator as “Foreach File Enumerator”, expand Expressions and add two properties “Directory” for folder path and “FileSpec” for filtering specific types of files. Specify “@[$Package::FolderPathTest2]” to Directory and ” @[$Package::CSVFileFilter]” to FileSpec.

4. Mapping the variable

5. Click OK to finished Foreach Loop Container configuration

6. Add a Data Flow Task in the Foreach Loop Container

7. Add a Flat File Source inside the Data Flow Task,configure Flat File Source

8. Assign variable @[User::CSVFileName] ‘Flat File Connection Manager’ ConnectionString property

9. Add ADO NET Destination then we can dynamically import file to table now.

SQL performance tuning: variable in where condition cause performance issue

I am seeing a hug performance difference between two quires that are almost identical.

Query 1:

declare @type varchar(10) = ‘OR-‘
select * from client_details t1, Orders t2
where t1.client_code = @type +t2.client_code

Query 2:

select * from client_details t1, Orders t2
where t1.client_code = ‘OR-‘+t2.client_code

client_details has around 400 thousands records, Orders table has around 200 thousands.

Query 1 take long then 5 hours to processing, but Query 2 is finished within few minutes.

I did lots of search and testing, it seems SQL SERVER optimizer issue.

Please the difference of Literal Values and local variable from Kendra Little’s Blog:

Solution to fix my issue, I created an additional column called type and then use the value from this new column:

select * from client_details t1, Orders t2
where t1.client_code = t2.OrderType +t2.client_code

SSIS: ScriptMain.CreateNewOutputRows error after copying script components

In my SSIS project, there are lots of packages used the similar code and logic. So I just copied the component and changed the things that need to be changed. Then I got a very weird error :

at ScriptMain.CreateNewOutputRows()
at UserComponent.PrimeOutput(Int32 Outputs, Int32[] OutputIDs, PipelineBuffer[] Buffers, OutputNameMap OutputMap)
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers)

After lots of search and investigation, I realized it’s SSIS component copy issue.

I followed Stefan’s steps and try to fix it. But it’s not working for mine. I recreated a new script component from the scratch.

There is ticket in Visual Studio Developer community as well, the last update at 06/02/2020 mentioned it’s a VS bug and still working on it.

SSIS: Reduce SSISDB database size

The SSISDB database is automatically created when we create SSIS catalog. All the SSIS projects, packages and execution history are saved in this database. So if you have a package that run every few minutes you could see the database size is growing exponentially. I deployed one project at AWS EC2 server with limited disk storage size (100GB SSD), the package was automatically executed every 3 minutes by SQL Agent Job. After one weeks later, I noticed the disk size almost full. After investigation, I notice the size of SSISDB is very big already. How can I control SSISDB to a minimum size?

SSIS created a maintenance job already when we create the catalog, it’s SIS Server Operation Records Maintenance.

Looking the job steps, you will notice it calls a stored procedure internal.cleanup_server_retention_window, in this stored procedure you will see it is using catalog.catalog_properties to do database maintain.

Now is solution is easy, I just need to update RETENTION_WINDOW property of catalog_properties .

exec catalog.configure_catalog RETENTION_WINDOW , 1

After this change, then just waiting for SSIS Maintenance Job to do database cleaning task.

If you don’t want to do this via T-SQL. You can right click on your catalog then click Property to change settings from Catalog Properties window:

After you reduced Database size, you maybe will notice the log file size is going up now. For the log file, if it’s not very critical system, you can used DBCC SHRINKFILE to shrink files directly:

–use below script to find ssisdb log file id
–SELECT file_id, name FROM sys.database_files;
— Truncate the log by changing the database recovery model to SIMPLE.
— Shrink the truncated log file to 1 MB.
— Reset the database recovery model.

Best practice to run SSIS Package within store-procedure (T-SQL)

Once SSIS packages deployed to production server, there are several way to execute it. Execute it by SQL Job or performing it from SQL script.

For most of case in my project need to execute SSIS package from SQL procedure. There are few challenges come up when executing SSIS package from SQL procedure.

By default start_execution is async function. The SQL procedure move to the following script once SSIS Package is called. But some of case we need to the data which processed in SSIS Package . In this case, asynchronous execution should be used.

The syntax to add SYNCHRONIZED parameter is shown below:

EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type = 50, @parameter_name = N’SYNCHRONIZED’, @parameter_value = 1

start_execution will return success message even if SSIS Package is failed. For most of case, the whole store procedure should be stopped once SSIS package execution failed. The workaround solution to solve this issue, we need to get package execution status flag from SSISDB then raise error. Code show as below:

IF 7 <> (SELECT [status] FROM [SSISDB].[catalog].[executions] WHERE execution_id = @execution_id)
RAISERROR(‘Json file import exception, please contact support team’, 16, 1)