problem with foreach loop container and excel files

well i am trying to follow the example of msdn help on how to loop through excel files and it doesn't work. the variables have the project scope. what can i do?

What exactly are you trying to do? Can you put the link to the msdn help here please.

Thanks

Sutha

|||

well i have a number of excel files and i want to use them in a loop so as to do calculations and load a table. when i use one file the process is ok, but when i use the steps described in that link

http://msdn2.microsoft.com/en-us/library/ms345182.aspx

i get errors.

thank u in advance

|||

You are not trying to do this on a 64 Bit by any chance?

"You may not be able to connect to as many data sources from a package executing in 64-bit mode as you can from a package executing in 32-bit mode. Some .NET Framework Data Providers and native OLE DB providers may not be available in 64-bit versions. For example, the Microsoft OLE DB Provider for Jet, which connects to Access databases and Excel spreadsheets, is not available in a 64-bit version." (http://msdn2.microsoft.com/en-us/library/ms141766.aspx)

Thanks

Sutha

|||

no an i get the following message

TITLE: Microsoft Visual Studio

Error at Package3 [Connection manager "SourceConnectionExcel"]: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Pilote ISAM introuvable.".

Error at Data Flow Task [Excel Source [1]]: The AcquireConnection method call to the connection manager "SourceConnectionExcel" failed with error code 0xC0202009.

ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)

BUTTONS:

OK

|||

Are you getting this error message on "To loop through Excel files by using the Foreach File enumerator" OR "To loop through Excel tables by using the Foreach ADO.NET Schema Rowset enumerator".

I worked through the first one (To loop through Excel files by using the Foreach File enumerator) and it worked without any issues.

Thanks

Sutha

|||

i worked with To loop through Excel files by using the Foreach File enumerator

I don't know :)

Thanks

|||

well i tryied with the following way? i read a file with the import/export wizard, i followed the instructions of the link, i loaded the files in a separate table and it worked. the problem is with the excel source in the data flow. am i using the wrong compnent. because i want to read directly the files and not to load them in a table. am i not supposed to use "excel source"?

thnxs

|||

I have a working example that I have done can be download from here.

Uses c:\Temp as source directory for Foreach Loop

Thank

Sutha

|||

and how can i use the script in order to use a data flow task in order to use the data in other transformations?

thnxs

|||

All I have done in that example, is just to get the xls files in c:\temp, assign each file name and print the variable to a message box.

you still have to do data flow task.

Thanks

Sutha

Problem with Execute Process Task

Hi,

I have what should be a very simple process.

I have a FOREACHLOOP container, that is set up to loop through files in a directory:

Enumerator: Foreach File Enumerator

Folder: c:\myfolder

Files: *.txt

Retrieve: Name and extension

Variable mappings: User::file_name, Index 0

So far, so good...

Then, inside the FOREACHLOOP, I have an Execute Process Task, which is simply a .bat file that I am running, and passing the name of the file to:

For example: myBat.bat file_name

In the Execute Process Task, I have:

RequireFullFileName: false

Executable: c:\myFile\myBat.bat

WorkingDirectory: c:\myFile

StandardInputVariable: User::file_name

Everything else is set to the defaults.

However, when I execute my container, I get the following error:

Error: 0xC0029151 at Execute Process Task, Execute Process Task: In Executing "c:\myFile\myBat.bat" " " at "c:\myFile", The process exit code was "1", while the the expected was "0".

Is there anything I am obviously doing wrong here at the package level? When I run the .bat file from the command line, it works fine.

Thanks

Even when I remove the Standard Input Variable, and just put file name as the Argument, I still get an error. This is just running the Execute Process Task, not the loop, for one file only.

|||

Ok, I set the SucessValue = 1, instead of 0, and now it's running ok with the single file name argument.

However, when I change it back to the StandardInputVariable with the User::file_name, it doesn't run the batch file correctly, that is the batch file does not work as expected, which makes me wonder what the User::file_name variable looks like?

I set a breakpoint, but how can I see my watch variable?

Thanks!

|||Type User::file_name into the watch window.|||In the execute process task, if the processes uses command line arguments, as in
some_executable.exe arg1
or
some_batch_file.bat arg2

use the Arguments property.

If the process to execute is properly invoked as below, then it is expecting and will use the standard input file stream, not a command line argument, so use the StandardInputVariable property. Note setting the StandardInputVariable to the "User::file_name" is streaming in the name of the file, NOT the contents of the file (as in the echo "x" case below).

echo "x" | some_executable.exe

If the process expects both command line arguments and standard input, as follows:

echo "x" | some_executable.exe arg1

then use both task properties as well.

Problem with displaying workflow?

Hi,

I just recognized a strange displaying problem in one of my SSIS jobs.

I created job which contains a sequence container. Within the sequence container there is one "Execute SQL Task" and one "Foreach Loop Container". Within the "Foreach Loop Container" there are 4 task which are connected with precedence constraints.

Now my problem is that if I load the SSIS job and open the "sequence container" while the "Foreach Loop Container" is already open then the precedence constraint won't be displayed. If I close and open the "Foreach loop container" again then the precedence constraints will be displayed again.

My first assumption was that it might be a problem with the display drivers of the computer ....however the problem appears also on any other computer.

Does anyone know how to solve this display problem without closeing/reopening the container?

Thanks,

StSt

StSt,

You just have to lump it I'm afraid. It happens from time to time.

-Jamie