SSIS : Use SSIS Environment variable to maintain multiple server connection string

In the real project, we need to maintain lots of environments for different purpose, like testing server, staging server and production server . How can we deploy SSIS project to different server and run directly without any code change.

I am going to use DB Connection string as an example in this blog to show you how can we connect to different database without code change after project deployed.

  1. Add new Connection Managers
  2. Right click the new created connection manager then select ‘Parameterize…’ from popup list

3. change property to ‘ConnectionString’, select ‘Create new parameter’ in Parameterize window and enter the parameter name

4. Once you saved this configuration then you can see your saved parameter in Project Parameters windows

5. In Management Studio, expand the Integration Services Catalogs> SSISDB node in Object Explorer, and locate the Environments folder of the project for which you want to create an environment. Type a name for the environment and optionally a description, and then click OK.

6. Right-click the new environment and then click Properties to open properties window.

7. On the Variables page, do the following to add a variable:
Enter the variable name
Select the Type for the variable.
Enter the Value for the variable

8. Click OK to save your changes to the environment properties.

9. Under the SSISDB node in Object Explorer, expand the Projects folder, right-click the project, and then click Configure.

10. On the References page, click Add to add an environment, and then click OK to save the reference to the environment.

11. Right-click the project again, and then click Configure. In the Parameters tab on the Parameters page, click the browse button next to the Value field. Click Use environment variable, and then select the environment variable you created.

12. Click OK twice to save your changes.

SSIS: Foreach Loop Container

The Foreach Loop container defines a repeating control flow in a package. It is similar to Foreach looping in programming languages. In a package, looping is enabled by using a Foreach enumerator. The Foreach Loop container repeats the control flow for each member of a specified enumerator.

This procedure describes how to configure a Foreach Loop container, including property expressions at the enumerator and container levels.

  1. In SQL Server Data Tools (SSDT), open the Integration Services project that contains the package you want.
  2. Click the Control Flow tab and double-click the Foreach Loop.
  3. In the Foreach Loop Editor dialog box, click General and, optionally, modify the name and description of the Foreach Loop.
  4. Click Collection and select an enumerator type from the Enumerator list.
  5. Specify an enumerator and set enumerator options
  6. Optionally, click the browse button (…) in the Expressions text box on the Collection page to create expressions that update property values. 
  7. Optionally, click Variable Mappings to map object properties to the collection value, and then do the following things:
    a. In the Variables list, select a variable or click <New Variable> to create a new variable.
    b. If you add a new variable, set the variable properties in the Add Variable dialog box and click OK.
    c. If you use the For Each Item enumerator, you can update the index value in the Index list. Note, The index value indicates which column in the item to map to the variable. Only the For Each Item enumerator can use an index value other than 0.

SSIS: How to debug Foreach Loop Container Variable

In today’s blog post, I am going to show you how to debug ForEach Loop Container variable.

Let’s say you fetched a data list from table and want to loop this list to do other tasks item by item. But you want to debug the item value before run other task.

First, Create two variables for Foreach Loop Container:

In the control flow of an SSIS package, add an Execute SQL Task and open the editor. Set the ‘Result Set‘ property to Full result set. Create an OLE DB connection to the database. Add the following T-SQL statement:

SELECT URL as BookListURL,Id as BookCategoryId,PreviousProcessedPageNumber as BookPreviousProcessedPageNumber
FROM URL_BookList

Click the ‘Result Set‘ option to the left. Set the Result Name to 0 and for the Variable Name, select User::BookListURLs. This variable will hold the results returned by the query. Click OK to close the editor.

Next,  add a ForEach Loop container and connect the Execute SQL  task to it. Open the editor and click Collection. In the enumerator drop down menu, select ForEach ADO Enumerator. In the ADO Object Source Variable drop down menu, select BookListURLs and for the Enumeration Mode, select Rows in the first table.

Set up Variable Mappings then click OK to close the editor:

To debug looping item value, you need to add a Breakpoint:

Now, execute the package. The package will stop and a watch window should open at the bottom of the screen. If it doesn’t, click Debug > Windows > Watch 1. In the watch window, add the names of the variables you want to monitor. Keep in mind that the variable names are case sensitive here.

Powershell script sending email (Gmail)

I have a simple powershell project to do file copy and sync task. Can I get email notification if task has issue, like error message or copied file name. The answer is yes, we can us Net.Mail.SmtpClient to send out email in powershell.

Below is the script:

function status_notification{
param( [string]$body,[string] $subject ,[string] $emailTo)
$email =”automated@youremail.com”
$psCred = New-Object System.Net.NetworkCredential(“$email”, ” your email password “);
$smtpServer = “smtp.gmail.com”
$msg = new-object Net.Mail.MailMessage
$smtp = new-object Net.Mail.SmtpClient($smtpServer,587)
$smtp.EnableSsl = $true
$msg.IsBodyHTML = $true
$msg.From = “$email”
$msg.To.Add(“$ emailTo”)
$msg.Subject = “$subject”
$msg.Body = “$body”
$smtp.Credentials = $psCred
$smtp.Send($msg)
}

SQL Server Date and Time styles

One of the most frequently question I asked is which date format style number I should to use. Here’s a summary of different date and time style matrix that we can used in T-SQL as part of the CONVERT function.

Matrix of Date and Time style

Style numberInput/Output Format
100 mon dd yyyy hh:miAM (or PM)
1011 = mm/dd/yy
101 = mm/dd/yyyy
1022 = yy.mm.dd
102 = yyyy.mm.dd
1033 = dd/mm/yy
103 = dd/mm/yyyy
1044 = dd.mm.yy
104 = dd.mm.yyyy
1055 = dd-mm-yy
105 = dd-mm-yyyy
1066 = dd mon yy
106 = dd mon yyyy
1077 = Mon dd, yy
107 = Mon dd, yyyy
108hh:mi:ss
109 mon dd yyyy hh:mi:ss:mmmAM (or PM)
11010 = mm-dd-yy
110 = mm-dd-yyyy
11111 = yy/mm/dd
111 = yyyy/mm/dd
11212 = yymmdd
112 = yyyymmdd
113 dd mon yyyy hh:mi:ss:mmm (24h)
114hh:mi:ss:mmm (24h)
 120 yyyy-mm-dd hh:mi:ss (24h)
 121 yyyy-mm-dd hh:mi:ss.mmm (24h)
22 mm/dd/yy hh:mi:ss AM (or PM)
23yyyy-mm-dd
126yyyy-mm-ddThh:mi:ss.mmm (no spaces)
127yyyy-mm-ddThh:mi:ss.mmmZ (no spaces)
130dd mon yyyy hh:mi:ss:mmmAM
131dd/mm/yyyy hh:mi:ss:mmmAM

Sample convert string to datetime

select convert(datetime, ‘2015/12/31 13:58:03’,111)

select convert(datetime, ‘2012-11-07T18:26:20.096Z’,127)

How to compare SQL Server Table schemas

We can use sp_help to display all table schema

sp_help import_OAN_clients

Will get below result:

But how we can compare two tables schema quickly via T-SQL? We can use INFORMATION_SCHEMA.COLUMNS to compare two tables schema easily, but this way cannot compare table cross database or instance. In this blog I will show how to compare it with internal function sys.dm_exec_describe_first_result_set

Create Demo Tables

CREATE TABLE demo1(
	[User_ID] [nvarchar](50) NULL,
	[FirstName] [nvarchar](50) NULL,
	[LastName] [nvarchar](100) NULL,
	[BirthDate] datetime NULL,
	[Citizenship] [nvarchar](100) NULL,
	[Country] [nvarchar](20) NULL
) 
CREATE TABLE demo2(
	[User_ID] [nvarchar](50) NULL,
	[FirstName] [nvarchar](100) NULL,
	[LastName] [nvarchar](100) NULL,
	[BirthDate] [nvarchar](30) NULL,
	[Citizenship] [nvarchar](100) NULL,
	[Country] [nvarchar](100) NULL
) 

How sys.dm_exec_describe_first_result_set is working

If you want to know all detail specs of sys.dm_exec_describe_first_result_set, you can go to microsoft developer community

before we start compare two tables, let’s see what kind of information the function can get

select * from sys.dm_exec_describe_first_result_set ( N’SELECT * FROM dbo.demo1′, NULL, 1)

We can see the returned schema as below:

Doing Comparisons

To do two tables comparison, we just need to simply doing OUTER JOIN between the two function results. (To do comparisons between to database or instance, please use linked servers and full part name to address the table. like product.dbo.demo2)

select a.column_ordinal,b.column_ordinal,a.name,b.name ,a.is_nullable,b.is_nullable ,a.system_type_name,b.system_type_name,a.max_length,b.max_length,a.precision,b.precision,a.scale,b.scale
from sys.dm_exec_describe_first_result_set ( N’SELECT * FROM dbo.demo1′, NULL, 1) as a
full outer join sys.dm_exec_describe_first_result_set ( N’select * from dbo.demo2′, NULL, 1) as b
on a.name = b.name

We can see the comparison result between to tables now.

SSIS: Execute SSIS Package from a SQL Server Stored Procedure

I have a Integration Services Project:SSIS_UK_Projects contains the package LEI_Lookup.dtsx. But how I can run it from Stored Procedure?

1. calls catalog.create_execution to create an instance of execution for the LEI_Lookup.dtsx package.

EXEC [SSISDB].[catalog].[create_execution]
@package_name=N’LEI_Lookup.dtsx’,
@execution_id=@execution_id OUTPUT,
@folder_name=N’UK’,
@project_name=N’SSIS_UK_Projects’,
@use32bitruntime=False,
@reference_id=@env_reference_id

2 calls catalog.set_execution_parameter_value to set values for the parameter, and LOGGING_LEVEL parameters.

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

EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=30, @parameter_name=N’Client_code’, @parameter_value=@parameter1
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=30, @parameter_name=N’Source_table’, @parameter_value=N’processing_hold’

3. calls catalog.start_execution to start an instance of execution.

EXEC [SSISDB].[catalog].[start_execution] @execution_id

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"?>

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

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}"/>
</rule>