SSIS Connections cannot be changed

This a quite common issue you would come across, especially if you want to change the Data Source  of a SSIS Package and check some functionality against a different data source. Issue is, how many times you change it from Connection Manager (ex. Server Name), it jumps back to the previous value.

Reason behind this behavior is these details are associated with Parameters and sometimes it may be not present in the view.

You can right click the Project.params and view the code in such cases as shown below;

Now inspect the code;

You will realize that previous details (ex. Server) are set as shown above and they doesn't get change when you change the Connection Manager from UI. So you need to change this file with new details. This fix the issue.

Handle comma separated string stored in a cell in SQL table

This is a challenging thing to do in SQL queries, though one single task that needs to be done in most of the instances. In other words, our challenge is to loop through the values stored in single cell.
My Sample string: Mango, Orange, Pine Apple, Avocado (Separator can be anything)

1) PARSENAME

This is my first approach. PARSENAME is a special function we can use to retrieve item by item in string of items, but separator should be just “.” (i.e. dot). In fact, in my code I am first replacing my separator (which is “;”) to a dot.

Anyway, biggest drawback with this is, PARSNAME seems to be working only up to 4 items.

DECLARE @FruitStr VARCHAR(500)
DECLARE @NumberOfItems int
DECLARE @Count int

SET @FruitStr = 'Mango;Orange;Pine Apple; Avocado'

SET @FruitStr = Replace(@FruitStr,';','.')
SET @NumberOfItems = (len(@FruitStr) - len(Replace(@FruitStr,'.','')))+1

SET @Count = 1

WHILE @Count <= @NumberOfItems
    BEGIN
       PRINT PARSENAME(@FruitStr,@Count) --this is the single item
       SET @Count = @Count + 1;
    END;

2) For any number of items

This method works for any number of items. What I do here is identifying first item within a loop (by searching first separator) and looping through removing the item we already read.

DECLARE @FruitStr VARCHAR(500)
DECLARE @FruitItem VARCHAR(50)
SET @FruitStr = 'Mango;Orange;Pine Apple; Avocado'
 
SET @FruitStr = @FruitStr + ';'

WHILE CHARINDEX(';',@FruitStr,1) != 0
BEGIN
    SET @FruitItem = SUBSTRING(@FruitStr,1,CHARINDEX(';',@FruitStr,1))
    PRINT LEFT(@FruitItem,LEN(@FruitItem)-1) --this is the single item
    SET @FruitStr = SUBSTRING(@FruitStr,CHARINDEX(';',@FruitStr,1)+1,LEN(@FruitStr))
END

Hope this is helpful.

One practical usage I came through;

Once I needed this solution in Data migration for a CRM where source file for activities came with comma separated lists for Email addresses for Email activities or attendees of the Appointments. This way I managed to create JSON objects which were then injected through KingswaySoft Adaptor.

Delete duplicates and keep only the latest record

This SQL code could be helpful since it’s a general requirement to remove duplicates while keeping latest record.

Considered example, we have a primary key called Id and Account Number is the field we are concentrating on.

Though this code is a loop, this works quickly since loop goes only for records with duplicates.

DECLARE @AccountNumber uniqueidentifier
DECLARE @ID VARCHAR(50)

DECLARE _cursor1 CURSOR for
 SELECT A1.AccountNumber
 FROM Tbl_Account as A1
 WHERE A1.AccountNumber is not null
 GROUP BY A1.AccountNumber
 HAVING (SELECT COUNT(A2.AccountNumber) FROM  Tbl_Account A2 WHERE A2.AccountNumber = A1.AccountNumber)>1

OPEN _cursor1

FETCH NEXT FROM _cursor1 INTO @AccountNumber
WHILE (@@FETCH_STATUS = 0)
BEGIN

    SET @ID = NULL

    SELECT TOP 1 @ID = Id FROM Tbl_Account WHERE AccountNumber = @AccountNumber ORDER BY [ModifiedOn] DESC
    
    DELETE FROM Tbl_Account WHERE AccountNumber = @AccountNumber AND Id <> @ID
 

FETCH NEXT FROM _cursor1 INTO @AccountNumber
END      
CLOSE _cursor1

DEALLOCATE _cursor1

Enjoy!

SQL to Fetchxml converter

This is a cool tool.

As we all are from on-premise CRM arena, one of the obvious challenges we have now is doing the same complex SSRS reports in online CRMs. In fact, challenge is writing fetchxml queries in the same way we did in t-sql queries. This is a cool free online tool by Kingswaysoft. This converts your t-sql queries to fetch.

 

Note: Please read the help page before using it.

Run SSRS report against specific CRM record

When we write SSRS reports to Dynamics CRM, it may need to write a report against opened record. For example, order report against selected account. Consider my example for custom entity called office. (Schema name: new_office).

Our usual select statement which returns all the offices;

SELECT
new_name as OfficeName,
new_code as OfficeCode
Filterednew_office

Special query to return the current record specific data;

SELECT    
CRMAF_Filterednew_office.new_name as OfficeName, 
CRMAF_Filterednew_office.new_code as OfficeCode
FROM  Filterednew_office as CRMAF_Filterednew_office

Prefix “CRMAF_Filtered” seems to be doing the filtering. What you really have to do is do your query this way and configure the report so that you can run from opened form of selected entity type. (In my example its Office) Please make sure you select below option so that report is ready to be run from opened record.

By the way, if the query I need to write is complex I read the Guid of current record to a variable so that I am free to use it for any other complex queries without any issue as below;

DECLARE @OfficeId uniqueidentifier

SELECT @OfficeId =CRMAF_Filterednew_office.new_officeid
FROM  Filterednew_office as CRMAF_Filterednew_office

Query to check duplicate field values

Sometimes you need to check if we got duplicate values for a given field. This is a script, we can use to check them.

Suppose I have new_RegNumber field in Account Entity which needs to be unique. So below query will show me if there are any ducplicates by mistake.

SELECT A1.new_RegNumber
FROM Account as A1
WHERE A1.new_RegNumber is not null
GROUP BY A1.new_RegNumber
HAVING (SELECT COUNT(A2.new_RegNumber) FROM  Account A2 WHERE A2.new_RegNumber = A1.new_RegNumber)>1

Hope this is useful.

Concerns in passing a parameter to a sub report

In most cases we need to pass parameters to sub reports in SSRS. If parameter is not available in certain scenarios it can raise issues. I think it’s always good to do the queries in below manner when it comes to datasets in sub report.

In my example I am passing a company name as a parameter which is not mandatory one. Typically my data set returns a Brand name.  In this case, if parameter is null, relevant data fields in the sub report will show a label called “Brand not available”.

IF (@companyName is NOT NULL)
BEGIN

    SELECT new_brandname as BrandName
    FROM ....
    WHERE new_companyName = @companyName  

END

ELSE

BEGIN

   SELECT 'Brand Name not Available' as BrandName

END

A simple trick.

Issue of Select into Temp table with NULL fields

Sometimes when we create Temporary tables we might need to create some null fields. Idea is to update them later.

Consider this script which seems correct.

SELECT 'JOHN' as Name, NULL as Company into #temp

Now we will try to populate the null field with some data, probably with char data.

UPDATE #temp
SET Company = 'ABC and Company'

We are getting this error.

If you check the error message carefully, you might notice it’s a converting error. Actual problem is null field is considered to be an int, unless it’s properly defined with a type.

So correct statement to create the temp table is like this;

SELECT 'JOHN' as Name, Cast(null as varchar(50)) as Company into #temp

Convert Guid to a String in SQL

Probably it’s just a simple thing, but I had to play a little bit to figure out this... so I thought of posting…

If you want to convert a Guid to a String in your query this is how you do it.

Id =CONVERT(VARCHAR(36),opportunityid)

Practical usage comes in SSRS reports; where you can’t play around with Guids within the report surface, in fact you need to get it as a string. I faced this when I needed to have a link to real record from the report.