iT Synergy Blogs

Growing Innovation - Soluciones a problemas reales

Copyright © 2025 · iT Synergy·

Error working with CRM OptionSet mapping component – “Microsoft.SqlServer.Dts.Pipeline.DoesNotFitBufferException: The value is too large to fit in the column data area of the buffer.”
Error working with CRM OptionSet mapping component – “Microsoft.SqlServer.Dts.Pipeline.DoesNotFitBufferException: The value is too large to fit in the column data area of the buffer.” avatar

By Leave a Comment

When we work with SSIS to get data from a source and load then into CRM Dynamics, we have some option which allow us to manage the particular CRM data type like Picklist. In this case we were working with the components from KingswaySoft to performance this particular task.

Normally we use the shape: Dynamics CRM OptionSet Mapping to map the values from source and the values we have to assign for each value, See the pictures bellow:

In the sheet general, we have to specify the input column we want to assign, the CRM entity ante the picklist field which contains the option.

 

 

In some cases, the values we are using to mapping are too large. In that case when you execute the ETL you get the next error: “Microsoft.SqlServer.Dts.Pipeline.DoesNotFitBufferException: The value is too large to fit in the column data area of the buffer”.

To solution this error: right click the OptionSet Mapping component and choose “Show Advanced Editor”. In the sheet: Input and Output Properties page select output and find the field you are mapping as CRM OptionSet field, and increase its Length property. Be sure you are writing a correct length for each field you are working with.

Consumir servicio WCF desde un paquete SSIS
Consumir servicio WCF desde un paquete SSIS avatar

By Leave a Comment

Para este ejemplo se utilizará SSIS 2010 para la creación del paquete y Visual Studio 2012 para la creación del servicio WCF. El servicio WCF tendrá una operación que realizará las operaciones matemáticas básicas (suma, resta, multiplicación y división) y el paquete llamará el servicio e insertará el resultado en una tabla de base de datos.

Servicio WCF

1. Abrir VS2012 y crear un proyecto de tipo WCF Service Application.

image

2. Crear una operación de la siguiente manera.

image

3. Ir a la implementación del servicio y escribir un codigo como el siguiente.

image

4. Probamos el servicio.

image

Paquete SSIS

1. Crear un proyecto de tipo Integration Services

image

2. Crear un data flow Task y dentro crear un Script Component como origen de datos

image

3. Abrir el Script Component, seleccionar el item Input and Outputs y añadir columnas con los valores que se van utilizar para crear registro en la tabla.

image

6.  En el Script Component Edit Script, luego en la ventana que aparece; agregar la referencia del servicio.

image.

image

7. Abrir el archivo de configuración que se generó y verificar el binding del servicio.

image

8. Agregar la referencia System.ServiceModel

image

9. Crear un codigo como este y luego compilar. Tener en cuenta el binding generado para este caso fué BasicHttpBinding.

image

10. Crear un destino de datos de tipo ADO NET Destination y conectar el Script Component con el ADO NET destination.

image

11.  Crear la conexión a la base de datos y crear una nueva tabla.

image

image

12. Clic en el item Mappings y luego OK.

image

12. Ir al Data Flow Task, luego clic derecho y ejecutar la tarea.

image

image

13. Se hacen cinco pruebas y estos son los resultados:

image

Integration Services – Configuring Foreach Loop Container for excel files
Integration Services – Configuring Foreach Loop Container for excel files avatar

By Leave a Comment

Sometimes we have many excel files that contain the data we have to work with. When those excel file have the same structure, I mean, the same numbers of columns, same name of columns, same file´s extension, etc, and the only thing which is different is a partial part of the file name, and all of them are in the same folder, the option that SSIS offers us is a shape that help us to repeat task in a package.

In this example I am going to show how to configure the Foreach loop container and the connection manager to achieve our goal.

So, in my case I have 4 excel files. The structure is the same: 3 columns named: A, B and C and these columns contain numbers.

 

These files are located in the same folder named: Exceles, in that way all have the same folder path.

 

The first thing we have to do, in the order I am describing here in the following:

  • Create a variable with data type: String. In the field value you can write by default a path for one excel file.

 

Then, create an Excel Connection Manager, and in the properties select true in Delay validation. This is going to help us when we have to use the variable that is going to have the file path for each file in the folder, and the connection is going to work without any problem.

 

Now, we select in the connection manager properties windows: Expressions, and select the points ….

In the Property Field select: ConnectionString.

 

In the field, for this case I am using this provider and this variable:

“Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” + @[User::CurrentExcelPath] + “;Extended Properties=\”Excel 12.0 XML;HDR=YES\””.

When you hit the Evaluate Expression, notice that the Evaluated value shows the entire file path that we assigned to the variable created before.

 

Then Ok, and the fx expression appears in the connection manager that indicates that we are using a parameter or expression for that connection string.

 

Test the package and make sure that all the data in the excel file now are in the destination you have selected.