SSIS Catalog Migrate

Install ‘ SSIS Catalog Migration Wizard ‘ from Visual Studio Marketplace

Once Installation is done, we can find ‘SSIS Cataloger’ under ‘Tools’ menu

Choose Source

Choose Target

Choose SSIS Catalog folders to Migrate

Migration is done

SSIS Converting date to YYYYMMDD

SSIS Expression format date to YYYYMMDD

Mostly, when we create an auto file creation SSIS package we want to have timestamp in file name. To achieve this purpose, we can simply use below SSIS Expression to produce file name with current timestamp.

“D:\\OutPut\\Transactions_”
+ (DT_STR, 4, 1252) DATEPART(“yyyy” , GETDATE())
+ RIGHT(“0” + (DT_STR, 2, 1252) DATEPART(“mm” , GETDATE()), 2)
+ RIGHT(“0” + (DT_STR, 2, 1252) DATEPART(“dd” , GETDATE()), 2)
+ “.csv”

SSIS Expression format YYYYMMDD to Date

(DT_DATE)(SUBSTRING(@[yourDateAsStr],1,4)
+ “-” + SUBSTRING(@[yourDateAsStr],5,2)
+ “-” + SUBSTRING(@[yourDateAsStr],7,2))

Set SSIS Expression to format File Connection Manager Property

SSIS : Script component error – Output0Buffer does not exist

Before we start to talk about the error, let me introduce a Script component editor property SynchronousInputID first. In Microsoft document, described SynchronousInputID as below:

The SynchronousInputID property has a non-zero value only in transformations with synchronous outputs. If the value of this property is zero, it means that the output is asynchronous. For a synchronous output, where rows are passed through to the selected output or outputs without adding any new rows, this property should contain the ID of the component’s input.

Which means if output data only one line, then the output columns are just part of the Input Columns, and there is no need for Output0Buffer. If your output data has many rows, then you need to create new Output0Buffer.

In SSIS, SynchronousInputID default value is zero. If you created a new Script component and have change this default value, but in C# script you are try to use Output0Buffer.AddRow(); to create multiple rows output, then you will get error message:

Output0Buffer does not exist

If you got this error, just follow below steps to change the SynchronousInputID value to non.

clicking on the Script Transformation Editor -> clicking the catagory Inputs and Outputs -> clicking on Output 0 -> changing SynchronousInputId to None in the right hand panel

SSIS: Extract and import JSON file to SQL Server via Script Component as Transformation

Currently there are lots of JSON format stored data. It’s important to be able to read the JSON data stored in files, load the data into SQL Server, and analyze it. This blog describes how to import JSON files into SQL Server via SSIS Script Component as Transformation.

I have JSON format file looks like below

{“id”:”037-002″,”accountAlias”:”Primary”,”homeCurrency”:”USD”,”marginUsed”:”0.0548″}
{“id”:”055-001″,”accountAlias”:”Primary”,”homeCurrency”:”USD”,”marginUsed”:”10.000″}

need import this format json file to database table, table schema looks like below

CREATE TABLE import_raw_accounts(
id varchar(60) NULL,
account_alias varchar(60) NULL,
home_currency varchar(60) NULL,
division varchar(3) NULL,
userid varchar(60) NULL,
marginUsed varchar(60) NULL
)

Create Script Component as Source

Step 1 : Drag and drop Script Component in the SSIS toolbox to the data flow region. Once you drop the Script component, a new pop up window called Select Script Content Type opened. Then selecting the Source option

Step 2: Within the Input and Outputs tab, Go to Output Columns to add output columns.

Step 3 : Within the Script tab, please click on the Edit Script.. button to write the actual C# Script

Once you click on the Edit Script, it will open the main.cs class file to write the C# code. Please write your custom code inside the CreateNewOutputRows function

Step 4 : Before start extract Json data, we need to create a new class for imported JSON object, which will be the same JSON data structure.

public class Accounts
{
public string Id { get; set; }
public string accountAlias { get; set; }
public string homeCurrency { get; set; }
public string division { get; set; }
public string userid { get; set; }
public string marginUsed { get; set; }
}

Step 5: Add JSON deserialize code to method CreateNewOutputRows

using (StreamReader r = new StreamReader(Variables.JsonFileName))
{
while (!r.EndOfStream)
{
string json = r.ReadLine();
var item = JsonConvert.DeserializeObject(json);
if (item != null)
{
OutputAccountsBuffer.AddRow();
OutputAccountsBuffer.id = item.Id;
OutputAccountsBuffer.accountalias = item.accountAlias;
OutputAccountsBuffer.homecurrency = item.homeCurrency;
OutputAccountsBuffer.marginUsed = item.marginUsed;
var acc = item.Id.Split(‘-‘);
if (acc.Length >= 1)
{
OutputAccountsBuffer.division = acc[1];
OutputAccountsBuffer.userid = acc[2];
}
}
}
}

Final Step: add destination source and mapping output data to table import_raw_accounts.

SSIS Expression Task

According to Microsoft document

The Expression Task creates and evaluates expressions that set variable values at runtime, using the Expression Builder

It’s a pretty useful task which can help you avoid having to write any .net code. In this blog, I will you show you how to use Expression Task to do conditional check.

I have one SSIS package need to export data to flat file. But need to determine if the exported row count is grater than zero or not. If it’s grater than zero then I need to update the row count to database table. If not then need to delete empty file.

I am using Expression Task to do this. The expression in my Expression Task looks like this.

Using conditional logic to compare the row_count then determine which action need to run.

Left side Precedence Constraint Editor

Right side Precedence Constraint Editor

SSIS : Script Component as Transformation

The Script Component is one of the most powerful tools in SSIS. It can be used as a Source, Destination, and Transformation.

In this blog, I will show you how to use the SSIS Script Component as a Transformation with a example. Normally, we can use Script Component to do some task that is not possible for the built-in transformations, or we can use SSIS Script Component as a Transformation to combine the work of multiple transformations in one place.

Example Data

Before we start creating the SSIS Script Component as a Transformation package, Let us see what the data we are going to use. If you want to know what the example doing you can look into like SSIS : Compare two flat files

We have one of big Merge Full Outer Join result and need to compare ‘field'(come from left join) and ‘field(1)’ (come from right join). I am going to using Script Component to do this compare. Below the the merge output screenshot, there are more than hundred field in both left and right join table.

Create Script Component as transformation

To create Script Component as transaformation:

Step 1 : Drag and drop Script Component in the SSIS toolbox to the data flow region. Once you drop the Script component, a new pop up window called Select Script Content Type opened. Then selecting the Transformation option

Step 2 : Double click on the Script component will open the following editor to configure the properties.

Step 3: Within the SSIS Script Component as Transformation Input Columns tab, you can cross-check the input columns.

Step 4: Within the Input and Outputs tab, Go to Output Columns to add output columns.

Step 5 : Within the Script tab, please click on the Edit Script.. button to write the actual C# Script

Once you click on the Edit Script, it will open the main.cs class file to write the C# code. Please write your custom code inside the Input0_ProcessInputRow(Input0Buffer Row) function

Step 6 : Add your custom C# code here. For this example, I am using C# reflection to do comparison. In this way, I don’t need to compare field by field, even if column name changed we don’t need to change the comparison code.

Step 7 : Once you finished editing the Script, Please close the main.cs file. Next, drag the OLE DB Destinations, and join the script component Output Arrow to this new OLE DB Destination

SSIS : CS2001 Missing AssemblyAttributes.cs when executing package from the deployed server

Recently, one of my deployed SSIS package keep failing and when I check execution report, the error messages shows:

Error: CS2001 – Source file ‘C:\Windows\TEMP.NETFramework,Version=v4.0.AssemblyAttributes.cs’ could not be found, CSC, 0, 0

As this package was running well at the back-end before and there is not new release, So at the first stage, I thought maybe is dll register issue, as I was using third part dll in the Script Task code. I registered it again. But still now working,I started to check on website. I one clue from below stackoverflow link:

https://stackoverflow.com/questions/35347632/cs2001-missing-assemblyattributes-cs-when-executing-ssis-package-deployed-to-the

I opened folder C:\Windows\Temp and checked the Temp folder Properties->Security, I noticed there is odd account: ‘Account Unknown s-1-15-3-xxxx’.

In the Temp folder Properties-> Security ->Advanced page, ‘disable inheritance’ to remove this ‘Unknown account’ and click ‘Apply’ . Once this unknown account removed, ‘enable inheritance’ again. Issue is fixed now.

SSIS: Debugging Script component break point is not working

Issue 1

I created a SSIS Script Component task (C#4.0 VS2012) and I want to debug the C# code inside the Script component. I set few break points then run with debugging. The break point does not fire at all. I double checked the script editor and package, both shows break point set up successful.

Solution

The reason it might not be triggered is that it may be using the 64 bit runtime. Try running it in the 32 bit runtime. To fix it –

  1. Go to the Solution Explorer
  2. Right click your SSIS project node > Properties
  3. In Configuration Properties > Debugging > Debug Options > Set Run64BitRunTime to False.

Issue 2

Created SSIS Script component task with script languageC# VS2012, but added .net 4.6 dll or used upper C# 4.0 feature then break point cannot triggered as well.

Solution

  1. remove all above 4.0 version feature and dll
  2. or upgrade project to latest version

SSIS: String Variables in Derived Columns

I was recently tried to add a string variable as new derived columns, but got a very tricky SSIS run time error:

truncation error occurred on the specified object of the specified component

Then I checked Microsoft SSIS document and found below comments about Length’

Screenshot of SSIS

But there is not warning or error at all in in the design view. When I check the properties of derived column, I found the type is Unicode  String and and Size always 0.

If adding data to a new column, the Derived Column TransformationEditor dialog box automatically evaluates the expression and sets the column length for string data. The value of this column is read-only.

Which means, by default SSIS will retrieve the data size from variable definition. The string variable content I was using always blank, that’s why size is 0 by default.

Solution

For pass this issue, I know there are two options:

  1. we can using Derived Column ‘Advanced Editor ‘ to manually changed the new derived column type and length to correct one
  2. Assign default value to string variable, make the content is not blank.

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.