iT Synergy Blogs

Growing Innovation - Soluciones a problemas reales

  • Facebook
  • Instagram
  • LinkedIn
  • Phone
  • Twitter
  • YouTube

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

April 28, 2014 By Ana María Orozco Zuluaga 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.

Filed Under: CRM OptionSet mapping component, Dynamics, ETL, KingswaySoft, Picklist, SSIS

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

January 30, 2014 By Bernardo Enrique Cardales Acuña 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

Filed Under: .NET, SSIS, WCF

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

January 23, 2014 By Ana María Orozco Zuluaga 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.

Filed Under: Foreach loop container, Integration Services, SSIS Tagged With: ConnectionString, Excel Connection Manager, Excel Connection Manager with expressions, SSIS

Team


Marco
Antonio Hernández

Jaime
Alonso Páez

Luis
Carlos Bernal

Ana
María Orozco

Juan
Camilo Zapata

Diana
Díaz Grijalba

Carlos
Alberto Rueda

Sonia
Elizabeth Soriano

Alexandra
Bravo Restrepo

Bernardo
Enrique Cardales

Juan
Alberto Vélez

Diana
Paola Padilla

Jhon
Jairo Rodriguez

Jesús
Javier Hernández

Alejandro
Garcia Forero

Gustavo
Adolfo Echeverry

Yully
Arias Castillo

Carlos
Andrés Vélez

Brayan
Ruiz

Jimmy
Quejada Meneses

Natalia
Zartha Suárez

Josué
Leonardo Bohórquez

Oscar
Alberto Urrea

Odahir
Rolando Salcedo

Carolina
Torres Rodríguez

Juan
Mauricio García

Mario
Andrés Cortés

Eric
Yovanny Martinez

Tag Cloud

.NET (9) 940px (1) Analysis Services mdx (1) An attempt was made to load a program with an incorrect format. (1) ASP.NET MVC (1) Azure (3) Backup (1) BAM (7) BAM API (1) BAMTraceException (2) BI (3) BizTalk (24) Business Intelligence (6) C# (2) caracteristicas de publicacion (2) Content Editor (3) ESB (15) ESB Toolkit (3) General (4) habilitar caracteristicas (3) indexes (2) Integration Services (2) Master Page (3) MDX (2) MSE (11) net.tcp (2) Office 365 (2) Oracle (2) Performance Point (2) Public Website (2) Receive Location (2) SDK (2) Servicio Web (2) Sharepoint 2010 (2) SharePoint 2013 (4) SharePoint Online (2) SOA (8) Soap Fault (2) Sort Months MDX (2) SQL Server (2) Visual (2) Visual Studio 2010 (2) WCF (19) Windows (3) Windows 8 (17)

Categories

  • .NET (33)
  • Analysis Services (1)
  • ASP.NET MVC (2)
  • Azure (7)
  • BAM (9)
  • BAM PrimaryImport (3)
  • BigData (1)
  • BizTalk (77)
  • BizTalk 2010 configurations (57)
  • BizTalk Application (60)
  • BizTalk Services (13)
  • Business Intelligence (4)
  • Cloud (3)
  • CMD (1)
  • CodeSmith – NetTiers (2)
  • CommandPrompt (1)
  • CRM OptionSet mapping component (1)
  • Desarrollo de software (6)
  • develop (6)
  • developers (3)
  • DropBox (1)
  • Dynamics (1)
  • Enterprise Architect (1)
  • Entity Framework (1)
  • Errores BizTalk (2)
  • ESB (27)
  • ETL (1)
  • Event Viewer (1)
  • Excel Services (1)
  • Foreach loop container (1)
  • General (4)
  • Gerencia de Proyectos (2)
  • Google (1)
  • Grouped Slices (1)
  • Human Talent (1)
  • IIS (4)
  • Integración (6)
  • Integration Services (3)
  • KingswaySoft (1)
  • Lync (1)
  • MSE (13)
  • Office 365 (2)
  • Oracle Data Adapter (2)
  • Performance Point (4)
  • Picklist (1)
  • Pivot Table (1)
  • Procesos (1)
  • Pruebas (1)
  • Public Website (2)
  • Reports (1)
  • SCRUM (1)
  • SDK (2)
  • SEO (1)
  • Servicios (2)
  • Sharepoint (9)
  • SharePoint 2010 (10)
  • SharePoint 2013 (4)
  • SharePoint Online (2)
  • SharpBox (1)
  • Shortcuts (1)
  • Sin categoría (1)
  • SOA (50)
  • SQL (5)
  • SQL Server (3)
  • SQL Server Management Studio (1)
  • SSIS (3)
  • SSL (1)
  • SSO (1)
  • Tracking Profile Editor (2)
  • Twitter (1)
  • Uncategorized (1)
  • Virtual Network (2)
  • Visual Studio 11 (1)
  • Visual Studio 2010 (2)
  • Visual Studio Online (1)
  • VMware (2)
  • WCF (24)
  • Web (1)
  • Web Api (1)
  • Windows (5)
  • Windows 8 (11)
  • Windows Azure (2)
  • Windows Live Write (1)
  • Windows Phone (7)
  • Windows Phone 8 (1)
  • Windows Scheduler (1)
  • windows8 (2)
  • WindowsRT (3)
  • WP7 SDK (1)

Manage

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org