Table variable using in SSIS packages

When you need it and how to use it?

As you may know table variable is similar to temp table in Microsoft SQL, however; table variable generally uses fewer resources. I am going to explain when table variable could be useful at the time of SSIS package development.

Scenario 1: When you need to store data like array and need to use it later in your SSIS package

For example, below scenario I only had to insert/update data where the periods exist in the source data and then making next operation depend on the value exist or not.

DECLARE @periodACD TABLE (periodACD char(2))

INSERT INTO @periodACD

SELECT Distinct [PERIOD]

FROM [ETL].[SourceTable];


SELECT CASE WHEN periodACD='4' THEN 'Insert or update for April'  END /*You can use any transformation or calculation at THEN clause.*/
FROM @periodACD WHERE periodACD='4'


Scenario 2: Keep insert or updated row count for logging purposes while you use SQL Merge

For example, you are using merge to update the dimension and want to catch the number of rows you insert, you can use table variable to store the data for merge.

DECLARE @RowCount TABLE (InsertedID INT) --Declare Table variable

MERGE  [Dim].[Table] AS Target
 USING    [Staging].[Data] as Source
    ON Target.SourceID = Source.SourceID --BusinessKey
WHEN MATCHED

AND (ISNULL(Source.SourceName,'')<>ISNULL(target.[BrandDesc],'')
OR ISNULL(Source.SourceDate,0)<>ISNULL(Target.SourceDate,0)
        OR ISNULL(Source.SourceBit,'')<>ISNULL(Target.SourceBit,'')
   
THEN
   UPDATE SET Target.SourceName = Source.SourceName,
  Target.SourceDate = Source.SourceDate,
  Target.SourceBit = Source.SourceBit

OUTPUT inserted.SourceID 'InsertedID' INTO @RowCount; --output IDs for rowcount

SELECT COUNT(*)
FROM @RowCount

How to deploy SSIS package to Sql Server Integration Services Catalogs (SSISDB)?

Though there are different methods to deploy SSIS package, however; this blog post will cover how to deploy SSIS package to SSISDB. So far found, deploying to SSISDB is considered as best practice. SSISDB is introduced with SQL server 2012. The advantages you can have by using SSISDB as follows:
1) Easy to deploy from one environment to other
2) Report facilities in the SSISDB, so you can see execution time, error or whatever happend to your packages.
3) SSISDB is protected so your connection string and sensitive data is safe.

As per MSDN, "The SSISDB catalog is the central point for working with Integration Services (SSIS) projects that you’ve deployed to the Integration Services server. For example, you set project and package parameters, configure environments to specify runtime values for packages, execute and troubleshoot packages, and manage Integration Services server operations.The objects that are stored in the SSISDB catalog include projects, packages, parameters, environments, and operational history."

Let's divide the topic into two section 1) Creating Catalog 2) Deploy package to the catalog

Section 1: Creating catalog

step 1: Log into you Sql Server management studio and you will find the folder "Integration Services Catalogs", please create catalog as like below  fig 1:

fig 1: Create Catalog

Step 2: Create folder under Catalog:
Right click the newly created catalog 'SSISDB' and then create new folder with the different layers (e.g. staging, edw layer, data mart layer etc.)

fig 2: Folder under catalogue

After you create folder you will find  two new folders automatically created under the folder. It means if you create a folder called 'DSA' then you will find two folders  'Projects' and 'Environments' as like below Fig 2A

Fig 2A: folder under SSISDB

Section 2: Deploy package to the catalog

The below steps will assist you to deploy the package to the catalog.

Step 1: Build the solution and I am sure you all know how to build solution ;)

fig 3: build the solution

Step 2: Locate the .ispac file

fig 4: .ispac file

Step 3: Follow the wizard to deploy

Double click the .ispac file and then you will find below wizard:

fig 5: 1st page of the wizard

Choose the option project deployment and browse the .ispac file that you want to deploy.

fig 6: project deployment

Now you reach at the destination wizard where please  provide the destination server name (SQL server name where you will deploy the package) and then browse the path. When you click the browse for path you will find folder list from SSSIDB catalog (the folder you created at the section 1, step 2 of this post)

fig 7: choose destination path

So your package deployment is done to the SSISDB catalog.

SSISDB catalog has the opportunity to monitor the packages, you can create report from it. please find the below figure to generate the report:

fig 8: SSISDB catalog report

SSIS Error: Cannot convert unicode and non-unicode string data types

At the time of data migration or at any situation you may need to add new column and assign fix value to the column. After you have added the column and put the static/fixed value, then you may receiving the error: "Cannot convert unicode and non-unicode string data types"

 Let's make an example; business delivered excel file with all data from legacy system which is already closed; and  you need to add the old data to your data warehouse. Loading data from excel file to a staging table;you built the package as like below figure 1, where you have added data flow component 'Excel file source' and you have 'derived column' to add new column and assign a hard coded value.

After you design it found the red error like below that you may hate.

fig 1: SSIS package migrated data

Well, let's look it closer and you can see the error saying  as like below fig 2: "Cannot convert unicode and non-unicode string data types". But why we see this error?

Fig 2: Unicode and non Unicode miss matched

We made a new column by using derived column component called 'Status'  and we set 'Completed' for the column Status which is extracting from the excel since all extracted data are old and transactions are completed so we set status as completed. However, the static string 'Completed' set as data type Unicode String [DT_WSTR] in SSIS which has issue with the target table column 'Status' data type. The  staging table has staus column as varchar which cause the issue here.

Fig 3: Data Type issue

If you look at the above figure 3, where string "Completed" is set under the Expression so DataType has been picked as [DT_WSTR] which is Nvarchar means Unicode. However, the staging table we have created in the database, the particular column is defined as Varchar which is DT_STR for SSIS means non Unicode. So it's clearly difference between [DT_WSTR] and [DT_STR] in SSIS.

How to solve it?

To solve the miss match of Unicode and Non Unicode issue, we need to convert unicode data type to Non Unicode. So here goes the conversion expression :(DT_STR,17,1252) "Completed". "Completed" string is considered as Non Unicode/Varchar data type. 

Fig 4: Solve issue with data conversion

If we explain the expression (from Fig 4),  where (DT_STR,17,1252) -> (DataType, length of the data, Code page), Code page 1252 means it's windows Latin 1 (ANSI).

After you add the above expression the error will disappear and you can run the package to load the data.

A newly developed job in SAS DI Studio: Why target table not found after Checked In?

When you start working with new tool you will find the tool is buggy (you assume so.. but actually most of the cases it’s you who don’t know all features of the tool).  However, a few cases tool have it’s known issue or can’t report the error message properly.

If you are new to SAS DI, I am sure you will find this post interesting.

Let’s start with a ETL job that which get data from an external file and loading into Teradata table.

       Fig 1: An example job that get data from external file and load into Teradata table

When job is completed we do checked in and so that other developer can review it and can deployed to Production.

Check In Window Look like below:

                                        Fig 2:  Check In Window

After clicking Check In you will find below three steps to do check In the job.

Fig 3: Title and description at while check in.

  Fig 4: Verify the objects that you are checking in

Fig 5: Summary of Check In Wizard

After completing the check In when you tried to open the job you will find below error:

Fig 6: Missing Objects

By looking at the information message you can't identify what has changes until you look and observe your job is missing two objects one is external source file and other is target Terdata table. 

But you have checked in the job, why then those objects are missing??

Well, only by checking  the job you can't expect all objects will be check in automatically. You must need to do check In source file and target table then you will find the job as it should be.

Tips: It's always good idea to do check In even if the job is not completed and check In all the objects related to job.

ETL Job via SAS DI Studio:Why Precode and Postcode properties of a job is disabled?

If you are an ETL developer and working with the tool called SAS DI Studio. A bad day in the morning could be started like; you are not able to insert code in the Precode and Postcode box of your job properties. The check box for Precode and Postcode is disabled. It's maybe nonsense, you never seen this kind of error before, right??!! Precode and Postcode of the job property look like below:

                       Fig 1: Precode and Postcode of the Job Property is disabled.

What Can go wrong?

If you look at the below picture, you will find three different type of code generation mode.

                         Fig 2: Three different type of Code generation mode

Generally Code generation mode 'Automatic' is selected by default.

                                        Fig 3: By default 'automatic' is selected.

If we want to look at the user defined code then usually we select 'All user Written' which is look like below:

                                    Fig 4: Code generation mode 'All user written' selected.

By mistakenly, if you choose 'all user written' and click 'yes' (see below image) then code generation mode will be changed and saved.

                                Fig 5:Saving code generation mode 'All user written'.

At this situation, if you click on properties and want to insert Precode and Postcode for the job, you will not be able add Precode and Postcode for the job. The option is disable for the job.

How you can Enable Precode and Poscode?

To enable Precode and Postcode we need to change code generation mode from 'All user written' to 'Automatic' and save it.

Then you will be able to add Precode and Postcode in the job and property screen will look like below:

                                        Fig 6: Enabled Precode and Postcode