Foreach loop over Excel files seems 'fragile'

All,

I have a package that loops over ~60 Excel files in a directory. Each
file has three named ranges in it, which I import into different
tables. Sometimes the package runs without a hitch, sometimes it
chokes. But it is intermittent.

If I pull the control flow components out of the foreach loop and
point the Excel connection manager to the specific Excel file that has
caused the package to choke, I get a message in the dataflow component
pointing to the named range that "the metadata of the following output
columns does not match the metadata of the external columns......Do
you want to replace the metadata of the output columns with the
metadata of the external columns?" When I choose 'yes', then the
file will be loaded. then I can put the control flow components back
into the foreach loop and the file will run again, successfully, along
with some more, until it chokes again....

So, first of all, does anyone have any insight into this? Sometimes,
somedays, these files will load with no problems. These exact files;
I am having to reload constantly... Other times, like today, it is a
battle.

Otherwise, is there a way to get Integration Svcs to handle the
metadata issue on the fly?

Any ideas, resources, references, war stories, or good clean jokes
would be appreciated,
Kathryn

Metadata cannot change... Do you have changing metadata in your Excel documents, or does SSIS just think it is changing?|||

Phil,

Thanks for the quick reply. It seems that SSIS thinks the metadata is changing..

As far as I can tell, the problem is caused when a field in the file does/does not have a hyphen in it. For example, some files give us EIN with a hyphen and some don't. The package will chug along until it gets an EIN with a hyphen, then it will choke. I will pull the control flow components out of the foreach, point the excel source at the file that's causing it to choke, then i will answer yes to the metadata warning. Then I'll put the control flow components back into the foreach and it will chug along until it gets to a file WITH a hyphen in the EIN, when it will choke again....

All fields are defined to be strings. I even put a Data Conversion component after the Excel Source component to strip out hyphens, but the data flow doesn't get to the Data Conversion; it chokes on the Excel Source.

Kathryn

|||

Hey Kathryn,

Try this... I don't know if it'll work or if you've already tried this, but try to process the erroneous file first (if possible) in the loop. I don't know if you can control that or not. Here's what I'm thinking. I think that SSIS looks at the first file, sees that FieldA1 is a numeric, and sets the metadata to numeric for that field. When you encounter a text value for that same field in a subsequent file, it bombs. So I'm wondering if you can process a file first that contains the text value of that field, for example. Then it'll think that field is a text field and process it the same for the rest? It's just a thought!

Rebecca

|||Maybe setting IMEX=1 in the excel connection string is the answer here as well.|||

Phil,

Thanks for the suggestion. Unfortunately, it didn't work, though it seems that that should be the answer....

Kathryn

|||

I'm very surprised that IMEX=1 did not work, since forcing everything to be loaded as a string should avoid the issue with the mixed data types that you otherwise have in your EIN column (numeric values when there's no dash, string values when there is one).

The only potential issue that comes to mind is the difference between string and memo fields, for which there must be at least 1 row with a memo value in the rows sampled by the driver for the driver to recognize that column as a memo column.

Let's remember that Excel has no column metadata. The driver can only guess.

-Doug

sql

for loops?

Hi all.

Im trying to create a Stored Procedure that inserts multiple rows. But I can't get it to work.

Here's how I would like it to work

for test in (select myid from tblPlayers)
begin
Insert into tblMatches VALUES(test.myid, 2)
end

but obviously it does not work. Any ideas?Insert tblMatches
Select myid, 2 FROM tblPlayers

For loops in Tsql

Can somebody please tell me how can i write a tsql statement in sql server 2000.

Same time how can i get the last digit of a inteager variable through tsql .What i want is to write 'right(intVariable,4) which is in vb .I want that in sql server 2000

Thank you

The best way to write TSql is to use Query Analyzer.

To get the last digit of an integer variable try: Right( Convert(varchar(10), intVariable ), 1 ) That will turn the int into a string, then get the last digit.

Since your subject has For Loop, but you haven't asked about For Loops, I'll just throw it in. TSql has While() instead of For. Declare a variable for your counter, use the While, and be sure to increment the counter.

Declare @.counter int

Set @.counter = 1

While @.counter < 10

Begin

Do something significant

Set @.counter = @.counter + 1

End

|||

Hi rohanfernando, I moved your question over to the Transact-SQL forum. The folks over here should be able to help you with your question.

Cheers,

JJustice [MSFT]

|||The easiest way to get the last digit of an integer is to get the 'mod 10' of it. Like this:

select 234 % 10 --gives 4
select 12098523 % 10 --gives 3

Rob

For Each Loop Failure when some values in recordset are NULL

I have a for each loop container that is performing various tasks as it loops through a record set. Some of the values from the recordset are NULL at times and this causes the FELC to fail because it is unable to map the variables that have NULL values.

Is there something I can set to have it accept the NULLS or something I can change about the variables themselves. This is valid data that still needs to be processed. There are other attributes that have data in the record.

Here are samples of my error messages:

Error: ForEach Variable Mapping number 19 to variable "User::varTransactionReference" cannot be applied.

Error: ForEach Variable Mapping number 20 to variable "User::varFlowStartDate" cannot be applied.

Error: ForEach Variable Mapping number 26 to variable "User::varCancelFee" cannot be applied.

Thanks in advance.

SK

SK1000,

The For Each control is weak. There are much better controls

that you can use to process records. Here is 2

web pages that discuss different techniques using different

controls.

This is my web site
http://www.mathgv.com/sql2005docs/SSISTransformScriptETL.htm

This Jamie

Thompson's cool blog.

http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

Thanks,

Greg Van

Mullem

|||

Thanks, Greg. I will look at both options.

SK