iT Synergy Blogs

Growing Innovation - Soluciones a problemas reales

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

Copyright © 2025 · iT Synergy·

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

‘Query timeout expired’ when trying to display a preview in a OLE DB Source Editor
‘Query timeout expired’ when trying to display a preview in a OLE DB Source Editor avatar

December 1, 2011 By Juan Alberto Vélez Casadiego Leave a Comment

When we try to preview the 200 rows in a OLE DB Source Editor of Integration Services, maybe we can find this message error.

This happens when the query take more than 30 seconds to preview the data. By the moment, there is no option to increment the time to wait in Integration Services. In this case, press OK and continue working normally.

But if we want to view the data in SQL Server Management Studio, and see the same error. You need to increment the time of waiting in Tools/Options…/Designers, In the right panel we can see the option ‘Transaction time-out after’.. Change the seconds between 1-65535 and press OK.

We can see that the value of the seconds is 30 by default.

That’s all for today. See you!

Filed Under: Integration Services, SQL Server Management Studio Tagged With: Integration Services, OLE DB Source Editor, Query timeout expired

The AcquireConnection method call to the connection manager “ExcelConnection” failed with error code 0xC00F9304
The AcquireConnection method call to the connection manager “ExcelConnection” failed with error code 0xC00F9304 avatar

September 14, 2011 By Jaime Alonso Páez torres Leave a Comment

 

Problema

Dentro de un paquete de SQL Server Integration Server, tengo una conexión a un archivo excel, el problema esta que al tratar de ejecutar el paquete me genera el siguiente error:

Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "ExcelConnection" failed with error code 0xC00F9304.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Solución

Cambiar la propiedad del proyecto ETL,

Run 64BitRuntime = false

Filed Under: Integration Services Tagged With: Integration Services

Team


Marco
Antonio Hernández

Jaime
Alonso Páez

Luis
Carlos Bernal

Ana
María Orozco

Juan
Camilo Zapata

Sonia
Elizabeth Soriano

Diana
Díaz Grijalba

Carlos
Alberto Rueda

Bernardo
Enrique Cardales

Alexandra
Bravo Restrepo

Juan
Alberto Vélez

Diana
Paola Padilla

Jhon
Jairo Rodriguez

Brayan
Ruiz

Jesús
Javier Hernández

Alejandro
Garcia Forero

Gustavo
Adolfo Echeverry

Yully
Arias Castillo

Carlos
Andrés Vélez

Oscar
Alberto Urrea

Odahir
Rolando Salcedo

Jimmy
Quejada Meneses

Natalia
Zartha Suárez

Josué
Leonardo Bohórquez

Mario
Andrés Cortés

Eric
Yovanny Martinez

Carolina
Torres Rodríguez

Juan
Mauricio García

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