SSIS – Error in Script Component

Posted on December 24, 2017 by Maniraj KV

May God bless you with JOY, PEACE and LOVE on this Christmas

Wish you all Merry Christmas

Friends,

I am so happy to connect with you all through blogging. In this Christmas time, I would like to share my work experience on the recent data migration project.

Core logic required was

Replacing comma separated GUID values in the column with the corresponding name for the GUID from the system

I should not use tables need to manipulate using flat files.

I did it as two-step:

Split the values, frame it like a table, do the join with reference table to get the name of the GUID and store it in a flat file.
Then convert into comma separated values as source.

Just like this below figure

Untitled

for second step, i got a snippet from google search. It really reduced my development time for this work. Here is the link

Prototype is ready. While implementing the similar transformation, I just copied the data flow task and done the required changes.

While executing the whole package, faced two error (where the DFT-Data Flow Task pasted)

Error 1:

The value is too large to fit in the column data area of the buffer

Normally, this error comes if the source column length is greater than the destination column. I am sure in my project maintained the same.

I don’t have any clue, what’s wrong? Refreshed the component, multiple time build the script component project. No use..

Finally got the solution..Delete the script component and Create it 😀

Lesson learnt: DON’T COPY PASTE THE SCRIPT COMPONENT. Time wasting on development is better than debugging the error

Error 2:

The code in the above link will not handle empty rows. So I got error like this.

There is no current row in the buffer. A row may need to be added using the AddRow method.

Added code for validate the empty rows. Problem resolved.

That’s all from my end.

If you have any suggestions on this topic, please do share your thoughts.

Feel free to reach out to me If you need any help on data migration project / consulting.

Expertise in SSIS, Talend (ETL tool), Dynamics 365/CRM adapters – Kingswaysoft, Cozyroc, Scribe, OneTapAdapter.

Data Migration, SSIS

SSIS – {Package inserts Bit data type column ‘1’ as ‘-1’ and ‘0’ as ‘0’ in Int data type column}

Posted on February 3, 2017 by Maniraj KV

Quick Tip:

This issue occurs while we moving data from Bit column (Source) to Integer column(Target). If we look the metadata of the columns in SSIS package, It shows DT_BOOL datatype. Means that all the Bit type fields are considered as Boolean data type in package.

I have seen several solution from forums as Convert the source column into Int and change the data type in the output column properties. These type of change is not proper or not a better approach. Because maintenance perspective is tough. we can’t remember where we changed and what we changed.

Tip: Best is to make the data type similar to source.

This tips shows that don’t think too much..

download

Data Migration, Dynamics 365, Dynamics CRM, SSIS

SSIS – {The column cannot be processed because more than one code page (1256 and 1252) are specified for it}

Posted on February 3, 2017 by Maniraj KV

Second post in 2017.. hoping for more posts in upcoming days.

Recently, I faced this issue while migrating records from Source database to Staging database.

Package background: I have developed the package in development server and deployed in migration server.

Analysis: I just checked the code page of the error column in dev server. It’s 1252. Was thinking how come 1256 ? .

1951885
Continue reading “SSIS – {The column cannot be processed because more than one code page (1256 and 1252) are specified for it}” →

Data Migration, Dynamics 365, Dynamics CRM, SSIS

{SSIS – Special characters getting appended while migrating records from CSV to SQL table}

Posted on January 18, 2017 by Maniraj KV

After long back..

Hope everyone had wonderful New year and Pongal celebrations. Hoping for the next year (2018) Pongal celebrations with full fledged government support for conducting JALLIKATTU in Tamil Nadu.

Lets discuss an important tip for data migration.

Recently I faced an issue in moving records from CSV file to Dynamics CRM 2016.

Scenario: In my data file, it has Arabic letters. So i set the properties of CSV file in UTF-8 format. After i moved the data to the table, checked out the Arabic field column. It has some special characters are appended. If i am moving using Excel file, it’s not appending any special characters.

Resolution: After searched various forums , I have found that It’s a data type issue. If we check the arabic values column and its data type, it shows as DT_STR. But it supposed to be DT_WSTR. So changing the data type of the required columns to DT_WSTR data type will help to overcome this issue.

Hope will help somebody in future.

Happy sharing !!

Data Migration, Dynamics 365, Dynamics CRM, SSIS

{User settings Id not found} – Dynamics CRM

Posted on December 23, 2016 by Maniraj KV

Recently, I faced an issue in migrating user from Non CRM application to On-Premise Dynamics CRM.

“User settings Id={00000000-0000-0000-000000000000} is not available”

I was thinking related to the user settings. Usually we don’t required to pass anything related to user settings. Then why why?? ? ? I just passed the username (domainname\username) and business unit reference to the service.

I was asking our expert goooooogle.. Wats the solution for this. None of the answer s related to me. Started revisiting my approach.. errrrrrrrrrr…

We were synced the existing Active Directory in AD configured server. I thought that while we supply domainname\username as available in AD, it will auto populate the first name and last name and other available fields. So I did not pass the values to the request.

Voila… Got it. If we were supplying the username in User creation form in CRM, it will do the auto population work. Since we were setting the values though service it will not auto populate for first name and last name. Hope you got it. Yesss we need to pass the name too..

Minimum values required for creating user in CRM are

Username as per AD
First name
Last name
Business unit reference

Hope this tip might help some body.

Keep Sharing your thoughts about this post and blog!!

” ADVANCE MERRY CHRISTMAS TO YOU AND YOUR FAMILY “

Data Migration, Dynamics CRM, SSIS

{Integration Services Deployment wizard – AppCrash}

Posted on December 22, 2016 by Maniraj KV

Hello Everyone,

Hope everyone doing good and preparing for long vacation 😉 Advance Merry Christmas and New year wishes to all.

Let’s discuss one of the issue faced in data migration…

Earlier i faced an issue in deploying my SSIS package and shared the experience and how to overcome that in the below link.

Unable to create the type with the name ‘CRM’ (Microsoft.SqlServer.ManagedDTS)

Today I faced an another issue while deploying my packages in Pre-Production server. It’s something strange and new to me.

“isdeploymentwizard.exe is Crashes every time and unable to deploy the project”

I have checked everything that’s used in the packages like

Third Party component installed properly or not
Changing Project to run in 32 bit or 64 bit run time
I have used BDD also. I have checked that whether BDD is installed or not

Still I faced the same issue. Then i checked the event viewer and found something useful.

“Faulting application name: isdeploymentwizard.exe, version: 12.0.2000.8, time stamp: 0x5306c9c6

Faulting module name: dts.DLL, version: 2014.120.2000.8, time stamp: 0x5306c90b

Exception code: 0xc0000005

Fault offset: 0x0006f806

Faulting process id: 0x198

Faulting application start time: 0x01d25c4e8748942d

Faulting application path: C:\Program Files (x86)\Microsoft SQL Server\120\DTS\Binn\isdeploymentwizard.exe

Faulting module path: C:\Program Files (x86)\Microsoft SQL Server\120\DTS\Binn\dts.DLL

Report Id: 2f82e5ec-c842-11e6-80ca-005056af2141

Faulting package full name:

Faulting package-relative application ID: ”

Highlighted line remind me that I have used SQL connections in my package. So possibly something related to SQL versions. Then I checked that SQL version which was used for developed are “SQL server 12.0.5203″. But the deployed SQL version is “SQL server 12.0.2000“. It is lower than the developed version. To verify that just replaced the “dts.Dll” of higher version in deployed SQL server. Now am able to deploy it.

“So make sure that while deploying in different server, SQL version should be same as developed version.”

Do share your thoughts!! Keep Sharing !!

Data Migration, Dynamics 365, Dynamics CRM, SSIS

Data Migration SSIS Tips – 1

Posted on December 20, 2016 by Maniraj KV

Recently we were migrating records from Source system to staging database and then to Dynamics CRM 2016 using SSIS.

Few issues are encountered while we migrating the large number of records.

Script component:

Some time we might require to use the script components because some conversion is not possible/not easy with SQL. In this case, number of columns which you processing will affect performance of the package.

How to improve ?

Avoid using the script component as much as possible

We can utilise the BDD – Balanced Data Distributor. It helps to parallelize the data flow. if you new to this, here is the link for more information (BDD – Balanced Data Distributor)

Try to use synchronised transformation. It’s better compared to Asynchronous. Ideally it’s up to the requirement. for better understanding, here is the link (Synchronous vs Asynchronous)

Derived column:

The number of columns in derived columns also affects the performance of the package.

Then how to ?

Try not to use the derived columns as much as possible

If there is a need, we have to use. then use multiple “Derived columns” component for manipulation. Instead of doing all the manipulation in single “Derived column”. Significantly it will improve the performance

Data Conversion:

This component in SSIS which will help to convert the data type of column to another compatible data type. It’s just drag and drop and easy to use. when you really care about the performance, then here we go. It does maintains the old column as well as converted column too.

Then how to ?

Try to do the conversion at source itself. It’s best as package don’t need to maintain the duplicate of it.

If the conversion is not possible at source, do the manipulation at staging.

Always, i prefer to have the manipulation in Stored Procedure. It really faster and it will not impact the performance as much as others.

Do share your knowledge on these..

Keep Sharing …

Data Migration, SSIS

SSIS – Binary code for the script is not found

Posted on October 30, 2016 by Maniraj KV

Last Friday, I was into an issue. My package is working fine in development server. but its not working in the deployed server.

untitled

Constantly getting the same error. Even after the multiple Build/Rebuild/Clean on the Script component and Project 🙁

Finally Spark came up.. whether the Script Component project build is giving new build file every time or giving the pre-compiled version of it.???

Yes you got it.. I too thought the same…

index

Then how to clear the cache in Script component ?

Easiest way is that make some changes in code, Build it. ITS WORKED FOR ME. Hope will for you too.

Share with me, if you got some other way to do..

Happy blogging !!!

Data Migration, SSIS

Things to consider “Package part” for your implementation

Posted on October 26, 2016 by Maniraj KV

What’s best we got ?

We can create a template which will be reused forever
Do a change in one place, it will reflect wherever it’s referred
Reduces developer time in finding the copy paste issues LoL
Able to pass parameters to and fro
Best work for the control flow component which will be independent of the previous steps ex. Package audit
Defined connection managers in package parts will be carried out wherever it’s used. I guess it’s best thing. So many times I have faced this issue during copy paste. Hope this issue will not repeat if we use package parts

What to be considered ?

The configured package parts variables in your package will be overridden , whenever you modifying / refreshing the package parts
Best works for the control flow executables which will be independent of the previous steps ex. Package audit
You can debug package part from the package by keeping breakpoints in package parts
Variables in package parts are Task Scoped. So you have to think on how to pass values to it
Using expression editor in package parts variable, we can pass values
Passing object variables to package parts is not possible because expression editor will not support object variables

This feature is available from SQL server 2016. Since this is first release we can expect more in upcoming releases.

Try it out and share with your thoughts .

Happy blogging . . .

Data Migration, SSIS

SSIS – Flat File Package Logger – Part 2

Posted on October 23, 2016 by Maniraj KV

In my previous post, I have explained how to create reusable SSIS Template in SSIS packages. If you haven’t read, I strongly recommend to go through SSIS – Flat File Package Logger – Part 1

Today’s post is all about

Package parts variables and its scope
How to use SSIS reusable templates in actual SSIS packages ?
How to pass values to the package parts ?

Package parts variables and its scope

Would you like to develop your package part more dynamic / configurable?

Go for using “variables” in the package parts. It will act like input and output parameter. Continue reading “SSIS – Flat File Package Logger – Part 2” →