From the category archives:

SSIS

ScriptMain is not CLS-compliant

January 21, 2011

in SSIS

While working with the script component in SSIS, sometimes we may run into the below warning. 

Warning           XX       ‘ScriptMain’ is not CLS-compliant because it derives from ‘UserComponent’, which is not CLS-compliant.

And an error like,

Error XX Validation error.DdataFlowTaskName Script Component [842]: Microsoft.SqlServer.Dts.Pipeline.CannotCreateUserComponentException: Cannot create user component class. Make sure there is one class marked with SSISScriptComponentEntryPointAttribute in your script.     at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.CreateUserComponent()

This basically happens because “UserComponent” that we inherit from is not CLS (Common Language Specification) compliant. As per MSDN “A class or interface is marked as <CLSCompliant(True)> when it derives from or implements a type that is marked as <CLSCompliant(False)> or is not marked.”

 So, this issue can be easily rectified easily by adding the below code near the ‘ScriptMain’ line 

<Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute()> _

<CLSCompliant(False)> _

Public Class ScriptMain

Inherits UserComponent

And also just check the number of input columns being passed and if there are any duplicate columns getting into the script component. This should resolve the issue being faced.

Tagged as: SSIS, SSIS Programming

{ Comments on this entry are closed }

Using Temporary Tables with SSIS

December 3, 2009

in Business Intelligence, SSIS

Many people keep on asking on different SQL Server forums on how to use a temporary table using in an SSIS Package, below mentioned is a mechanism that I have learned and been using for long time for now, below it is…

We are going to take this to achieve this goal, we have to create a temporary table, insert some data into it, and using data flow task which we shall dump this data into a permanent table.
We shall take the below script for this,
Create table ##TempTable(Id int identity(1,1),FName nvarchar(100),LName nvarchar(100))
 
Insert into ##TempTable(Fname,Lname)Values ( ‘John’,‘Smith’)
Insert into ##TempTable(Fname,Lname)Values ( ‘Aaron’,‘James’)
Insert into ##TempTable(Fname,Lname)Values ( ‘Albert’,”)
 
DROP TABLE ##TempTable
To be done on SSIS Front.
1.       Create a new data source
2.       Create a connection manager based on this data source
3.       Select the connection manager created in step to and go to properties, and mark the “RetainSameConnetion” as “TRUE”
4.       Now in the control flow – drag an execute sql task in to designer space.
5.       Copy paste the above SQL statements from the above script into that, except the drop table once.  J
6.       Now select the execute sql task, go to properties and make the “Delay Validation” property under execution as “True”.
Before we create the Data Flow Task, we shall first go to the Management Studio and Create a Global Temporary Table so that we have a basic table to map the columns with
7.       Drag the data flow task into the designer
8.       In this Data Flow task, create and OLEDB Source Connection, and have this select query in place.
select * from ##TempTable
9.       Now create an OLEDB Destination connection with a suitable data source, use the create new table option to create a table for the dump.
That’s it, now right click on the package and say execute, zoom it goes…
Now let’s see how we have to convert the global temporary table to a local temporary table.
10.   Select the execute sql task, and then properties and under expressions we have our sql query, here, at every place replace”##” with “#”.
11.   Now go to the data flow task, and in the properties, custom properties, you will find the sql query in “sqlCommand” property, change it here as well.
That’s it, now we have a package with Local Temporary Table as well.
Horray!!! Reached our Goal….

Tagged as: SSIS

{ Comments on this entry are closed }

Incremental Loads using SQL Queries

November 1, 2009

in Business Intelligence, Developer, SSIS

Incremental Loads using SQL Queries

Problem: Using SSIS we have very easy ways to load a SQL Server Data Mart Incrementally, but how to perform Incremental Data Load when we do not have SSIS with us, in other words using T-SQL Queries.

Solution: To begin with let us have a look at the architecture for loading the data mart, we can get our data from one or more data sources into a staging area. This step should purely depend upon the business and technical needs.
Whenever we want to load data incrementally, we should make sure that we are able to find a unique identifier to distinguish one record from the other. We need to take a look at the source timestamp, primary key, identity key, and unique key columns of the rows that we capture to ensure there is a continuous representation of each unique record in the dimension or a fact table.
 
Loading Fact Tables
In most cases we would be able to find a unique time stamp column or a unique key which is Bill No, Invoice No or a Incident No. Generally here we can go for Delete and Insert method between Stage and Mart Levels, for this we shall first identify the rate of Insertion and Updation of data in the source, and decide the number of days records that we need to get from the source to stage
 
Loading Dimension Tables
Loading dimension would be the trickiest part as we have to make sure all the changes are processed up to the data marts. Suppose, for example if we take a customer dimension, we can have a new customer being added, customers details being updated and in some rare cases customers information being deleted when there is no relevant fact data.
 
The above scenario can be tackled easily depending upon the amount of data by
Should import all the records or last “X” days records depending upon the need and
1.       Doing complete truncate and Insert. à If we want to have History information which is currently not available in transaction tables this method should not be used.
 
2.       Using Except Keyword:  If we have large number of duplicates and handle them on the fly we can go with the Except keyword, using it between the stage and mart, this would give us all the records that are not there in Mart and it would automatically remove duplicates.
 
Insert into Mart_Table
SELECT * FROM Stage_Table
 EXCEPT
SELECT * FROM Mart_Table;
 
 
3.       Doing a delete and Insert à we should delete the records that are available in the stage from mart and Insert all the records from stage to mart. Doing this we will also be having records that are not available in stage and this will also take care of any updating being data at the source level.
 
The above described were a few methods that I have used for loading data incrementally without using SSIS.
Conclusion: we can perform incremental loads using T-SQL when we do not have an access to SSIS.

Tagged as: Incremental Loads, SQL Queries, SSIS

{ Comments on this entry are closed }