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

FOREACH Loop Container: Continue ?

With a ForEach container, configured to loop through files in a directory, if I have a problem with a file.. can I direct the loop to skip on to the next file?
I'm processing structured files, first record of each is some header info, body records are in the middle, and then the last record is a trailer containg a checksum
So, for each file in the directory, I split the records into three raw files, one for header rec(s) , one for body recs and one for trailer recs. (based on line numbers and using a conditional split to direct the records)
Then I start by processing the header recs in a dataflow.. if all goes well there I move on to the next dataflow to process the body recs from the DataRecs raw file.. etc...
I would like to do some validation at each processing step.. if a header rec fails validation say... then I'd like to just stop processing that file and move onto the next file...
Now, I don't see my validation throwing an exception... so its more that I'd decide (maybe using an Audit ) that the header doesn't pass validation.. then I'd like to put a record in an error table (with info about filename, source etc, not just content of the current data row)
But not sure what approach to take on this...
If there is an appropriate section in BOL please point me at it...
Thanks
PJ

OK, so a record could fail validation but won't necassarily cause an error, is that right?

If you can find a way of populating a boolean variable stating whether the validation was successful or not then you could put a conditional precedence constraint beween data-flow1 and data-flow2 to determine whether data-flow2 should execute or not.

-Jamie

|||

... and if data-flow2 shouldn't execute then the ForEach will just loop around?

PJ

|||

Ok... so have to figure out how to access global variables in a script task....

OR...

...could do a conditional split.. which carries out the validation checking...and has one output connected to an OLE DB Destination to store a valid header record.. the other output directed to a RowCount...

If the row count >0 then we have a validation failure.. so could set the global variable there... is there a way to send a row count to a package variable?

PJ

|||

PJFINTRAX wrote:

... and if data-flow2 shouldn't execute then the ForEach will just loop around?

PJ

Absolutely! (Unless you have any other OnSuccess precedence constraints from data-flow1). Once all work is done for that iteration then it'll go onto the next iteration.

|||

PJFINTRAX wrote:

a way to send a row count to a package variable?

PJ

Err yeah. That's exactly what it does (and the only thing it does). i.e. Store the rowcount in a variable :)

|||

Jamie Thomson wrote:

PJFINTRAX wrote:

a way to send a row count to a package variable?

PJ

Err yeah. That's exactly what it does (and the only thing it does). i.e. Store the rowcount in a variable :)

DOH! Well, we all know I'm only making this up as I go along :^)

Thanks

PJ

|||

sigh.. this is like pulling teeth... now another problem has reared its ugly head...

when i use the SQL Task to retrieve the ID of the FileHeader record (that I just inserted) into a package variable... I get a datatype error.. and can't figure out which bloomin data type to use...(for the variable)

In my DB table the ID is a bigint....

what on earth is the equivalent in SSIS datatypes.... I have literally tried em all.. only one that works is Object !!!

So, SQL Task is executing SQL query as follows "select MAX(ID) as ID from fileheader"

Table definition is :

CREATE TABLE [dbo].[FileHeader](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[Source] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FileName] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LoadDate] [datetime] NULL CONSTRAINT [DF_FileHeader_LoadDate] DEFAULT (getdate())
) ON [PRIMARY]

So what should my variable type be if its to hold the result of the query?

PJ....

|||

Ahh what they hell.. I'll just use String.. that works....

|||Guys, I have the bigint problem again, can't just use a string this time...
has anyone any idea what the SSIS datatype equivalent of a bigint is at all?
Thanks
PJ
|||Is this just so obvious that nobody is bothering to answer?
|||

Probably cos they don't know. And I'm afraid I include myself in that. I don't have a SSIS instance to hand but as and when I do I'll try and take a look OK.

-Jamie

|||

Thanks Jamie,

but I have been through every single datatype .. and if I try to wedge a bigint into anything other than a string I get an error...

I would have thought an INT64 would work.. but no...

the bigint is an identity column in a table in my db... I'm a result set (which includes it) into an object and using that as a recordset using a foreach.. and I need to compare the ID column numerically.. to decide which flow to go down (ie If FiletypeID >12 and FileTypeID > 4 )

i suppose since the presedence contstraint is an expression I could try casting to a int64 maybe...

bit awkward tho.... would have thought this would all be easier... (like most SSIS things, heh)

PJ

ForEach File Enumerator extension bug?

I set up a basic ForEach enumerator loop and specified files of type *.sql.

In the directory, I had some files I needed to keep but didn't want the package to touch, so I changed the extension to *.sqlo. Much to my dismay, the ForEach loop picked up those files. (Though it did skip the *.xml and *.bat and a few other types...)

Sounds like a bug to me.

That is expected wildcard behaviour. At least, it is what I would expect. To verify I wasn't 'shrooming, I checked out: http://msdn2.microsoft.com/en-us/library/wz42302f.aspx where I found this to put my mind at ease ...

Note

When using the asterisk wildcard character in a searchPattern, such as "*.txt", the matching behavior when the extension is exactly three characters long is different than when the extension is more or less than three characters long. A searchPattern with a file extension of exactly three characters returns files having an extension of three or more characters, where the first three characters match the file extension specified in the searchPattern. A searchPattern with a file extension of one, two, or more than three characters returns only files having extensions of exactly that length that match the file extension specified in the searchPattern. When using the question mark wildcard character, this method returns only files that match the specified file extension. For example, given two files, "file1.txt" and "file1.txtother", in a directory, a search pattern of "file?.txt" returns just the first file, while a search pattern of "file*.txt" returns both files.

Donald

|||

I believe you (after looking at that link). But that is just bizarre and, to me, nonsensical. Especially if one continues to read. This *only* applies to a 3-character extension search pattern - nothing else.

Why oh why would they do such a stupid thing?

This means that there is direct no way to specify give me only the three character file extension files that I've told you to give me.

Were they shrooming when they came up with this?

If I wanted the other extensions I'd ask for "*.txt*".

ForEach file enumeration with bulk insert problem

OK, a new package, with a Foreach container enumerating CSV files in a directory.

I create the container pointing it at the directory and retrieving the fully qualified name, and create a variable (called 'CSVFiles') with a package scope, but no value.

Inside the container is a bulk insert task. The destination db/table is set, and the input flat file connection manager for the CSV files is defined with the connection string set to the variable created above.

As it iterates through the files, the variable is correctly set to the next file in the directory (I put a message box in the stream to display the file name/variable). It resembles 'C:\temp\Location1.csv'.

But when it gets to the bulk insert, I get this error message:

[Bulk Insert Task] Error: The specified connection "CSVFiles" is either not valid, or points to an invalid object. To continue, specify a valid connection.

What's going on here? Can I not use a bulk insert task in the container? Or some other parameter needs to be set?

SQL Server 9.00.3159

You have to use a file connection instead of the variable.

HTH.

|||

thanks...I was typing a bit too fast on my first post.

The variable for the ForEach container is called 'CSVFN' and the connection manager name is 'CSVFiles'. In the properties for the connection manager, I changed the 'connectionstring' to equal the variable (@.[User::CSVFN]).

And on a related note, how do I use that variable in a T-SQL script in an Execute SQL task in the container (I get a syntax error about the variable not being defined)? I would like to insert the name of the file (from the variable) into a table for auditing purposes.

thx

|||

Kevin6 wrote:

thanks...I was typing a bit too fast on my first post.

The variable for the ForEach container is called 'CSVFN' and the connection manager name is 'CSVFiles'. In the properties for the connection manager, I changed the 'connectionstring' to equal the variable (@.[User::CSVFN]).

And on a related note, how do I use that variable in a T-SQL script in an Execute SQL task in the container (I get a syntax error about the variable not being defined)? I would like to insert the name of the file (from the variable) into a table for auditing purposes.

thx

You'd have to put the variable in the expression editor for the property "ConnectionString." Right click on the connection manager object and select properties. Scroll down to find "Expressions." Click the ellipsis and select ConnectionString. There is where you put the variable name.

Re: Execute SQL Task
Make sure that the variable is of package-level scope and that the Execute SQL Task can see it.
Then build a SQL statement like this:
insert into auditTable values (1,"Testing", ?)

Then click on the parameter mapping tab, click Add, and select the variable in the variable name column. Then use zero (0) for the parameter name. Change the data type to "VARCHAR". If you have another parameter, do the same, but use a one (1) in the parameter name box.

Foreach file

I loop through a list of files in a directory and one of the tasks in the container is to validate the xml file against dtd if this succeeds i process the file and the lforeachloop gets the next file. If validation fails i then delete that file,the problem i am having is that whe the xml task fails it stops the whole for each container even though the failparentonfailure is set to false.

How can i get the foreach container to continue to the next file after the xmltask fails (validation) and the file is succesfully deleted?

PS is their away of posting the DTSx screenshot as this might explain it better?

Try setting the xml task's ForceExecutionResults property to Success.|||

If the xml task(valid) is success i extract the nodes if it fails(invalid) i delete the file. If i force the success it will fail down the line when it tries to process an invalid xml file. i.e Get file --> if valid Process File/If invalid delete file --> Get next File.

Below the result of next task if a force success

[XML Task] Error: An error occurred with the following error message: "Data at the root level is invalid. Line 1, position 1.".

|||Set MaximumErrorCount (package properties) to some high number, perhaps 999 or something like that.

The default is 1, so even though you've told it not to fail the parent, the package still sees one error and then stops.|||

I dont want to tamper with the package error setting as some items exit on failure correctly. What i want is in my loop conatiner if the validate xml fails i delete the invalid file and get the next file. The scope of the failrwe is within the foreach loop and the only step i want it to continue looping through files is when i detect an invalid file and successfuly delete it.

I think it would be easier to understand what i am trying to do if i could post the screen shot of the control flow.

|||

MikeTomkies wrote:

I think it would be easier to understand what i am trying to do if i could post the screen shot of the control flow.

You can send me the screen shot via my posted e-mail on my profile. I can then host it and post it here.|||Did you get my screenshot, Anybody else able to help?

ForEach Config validation errors

I have a Config file for a Data Flow that is utilizing a Configuration file. I have specified in the Configuration file to capture the Directory and FileSpec for the ForEach Enumeration.

When I open the package, I get the following error:

Warning 1 Warning loading ISOTypeLoss.dtsx: The package path referenced an object that cannot be found: "\Package\Foreach TC03 File.ForEachEnumerator.Properties[Directory]". This occurs when an attempt is made to resolve a package path to an object that cannot be found. i:\visual studio 2005\projects\claimtran\isotypeloss\ISOTypeLoss.dtsx 1 1

Warning 2 Warning loading ISOTypeLoss.dtsx: The package path referenced an object that cannot be found: "\Package\Foreach TC03 File.ForEachEnumerator.Properties[FileSpec]". This occurs when an attempt is made to resolve a package path to an object that cannot be found. i:\visual studio 2005\projects\claimtran\isotypeloss\ISOTypeLoss.dtsx 1 1

I have verified that these two entries are indeed in the Config file and they do point to a valid location for the Directory and File Specification. The package runs fine.

Thanks.

More information:

When I am selecting these two fields during the Configuration file wizard, the final screen that shows the summary of activity displays the following info under the Properties section:

Properties:
<Unknown>.Properties[FileSpec]
<Unknown>.Properties[Directory]
\Package.Connections[LoggingOutput].Properties[ConnectionString]
\Package.Connections[DSSStaging].Properties[ServerName]
\Package.Connections[DSS].Properties[ServerName]
\Package.Connections[Corp1Staging].Properties[ServerName]

The FileSpec and Directory properties are under the ForEach.EnumeratorProperties section.

Is there a known issue pertaining to this?

|||What build are you running?|||

KirkHaselden wrote:

What build are you running?

June CTP|||UPDATE:

We deployed the dtsx to the file system on the server, made changes to the FileSpec parameter in the config file and deployed it, then executed the package pointing to the Config file.

The package still looked for the file as specified in the dtsx XML (package), regardless of what was specified in the config.

To confirm, we renamed the input file to match the FileSpec parameter in the package and it ran successfully, finding and processing the file.

The ForEach parsing for the Enumeration objects appears to have a bug. Can someone let me know if this is a known bug and if it is going to be fixed for the next CTP release?

Thanks.|||

We attempted to recreate the Config file uinder the September CTP and are having the same problems. The Config wizard

Here is what is displayed at the bottom of the dtsConfig file:

File name:
\\MSSQLYukon\etlconfig\ISOTypeLoss.dtsConfig

Properties:
\Package.Connections[ISOTypeLossLog].Properties[ConnectionString]
\Package.Connections[DSSStaging].Properties[ServerName]
\Package.Connections[DSSStaging].Properties[InitialCatalog]
\Package.Connections[DSSStaging].Properties[ConnectionString]
<Unknown>.Properties[FileSpec]
<Unknown>.Properties[Directory]

We still get the error in the Error Window:

Warning 1 Warning loading ISOTypeLoss.dtsx: The package path referenced an object that cannot be found: "\Package\Foreach TC03 File.ForEachEnumerator.Properties[Directory]". This occurs when an attempt is made to resolve a package path to an object that cannot be found. i:\visual studio 2005\projects\claimtran\isotypeloss\ISOTypeLoss.dtsx 1 1

|||You will get this error if your config file contains references to names that don't exist elsewhere in the package. (In this case, it sounds like your config file contains references to "Foreach TC03 File", which doesn't exist in your package.) When you recreated the config file, did you actually delete the previous config file from the file system?|||

Cim Ryan wrote:

You will get this error if your config file contains references to names that don't exist elsewhere in the package. (In this case, it sounds like your config file contains references to "Foreach TC03 File", which doesn't exist in your package.) When you recreated the config file, did you actually delete the previous config file from the file system?

Absolutely. The errors I have highlighted above were copied and pasted from the confirmation screen of completing the Package Configuration Wizard and BEFORE the file is written.

The wizard itself does not recognize the setting before the file is written.

This is a HUGE issue if we are to deploy Yukon! |||

I've only been able to reproduce the error you're encountering by manually editing the config file and corrupting the name of the ForEach Loop. If you'll remove from your package all but the tasks necessary to reproduce the error (make a backup copy first, obviously), I'll take a look at the pared down package if you post it here.

|||

Cim Ryan wrote:

I've only been able to reproduce the error you're encountering by manually editing the config file and corrupting the name of the ForEach Loop. If you'll remove from your package all but the tasks necessary to reproduce the error (make a backup copy first, obviously), I'll take a look at the pared down package if you post it here.

Where in the Wizard should I be selecting the Directory and FileSpec properties? I select them under the <PackageName>/Executables/<ForEachContainerName>/Executables/EnumeratorProperties entry. In there I select "Directory" and "FileSpec".

I tried to post the XML but the Forum logged an error. Can I send the XML to you via email or post an attachment somewhere?|||Yeah, that sounds like the correct place to be specifying the properties. Go ahead and email me the package and config files.|||By the way, these are not errors, they are warnings which are non-critical. There is a property on packages called "SuppressConfigurationWarnings" that will hide these messages. They are there to simply inform you that the configuration is attempting to configure an object that is not there. So you can either remedy the situation or ignore it.
Configuration warnings should not fail the package.
K|||

KirkHaselden wrote:

By the way, these are not errors, they are warnings which are non-critical. There is a property on packages called "SuppressConfigurationWarnings" that will hide these messages. They are there to simply inform you that the configuration is attempting to configure an object that is not there. So you can either remedy the situation or ignore it.
Configuration warnings should not fail the package.
K

Kirk:

The problem is that we need to change settings in the config file to migrate the packages through Model, QA and to Production. As I posted above, I can change the settings to point to a different Directory but because the package cannot configure the setting it always uses the setting that was set when the package was designed, which is the Test directory. The only way to change this is to open the package, change the attributes during design and save them. But then it points to the Production directory, etc.

Changing the SuppressconfiurationWarnings setting will indeed hide the warning but it will not change the setting at execution time - which is the reason for having these properties in the config file in the first place.|||

Cim Ryan wrote:

Yeah, that sounds like the correct place to be specifying the properties. Go ahead and email me the package and config files.

Done. As stated, I am running September CTP.|||

I wasn't able to reproduce this failure, and Raul can no longer do so, either.

ForEach Config validation errors

I have a Config file for a Data Flow that is utilizing a Configuration file. I have specified in the Configuration file to capture the Directory and FileSpec for the ForEach Enumeration.

When I open the package, I get the following error:

Warning 1 Warning loading ISOTypeLoss.dtsx: The package path referenced an object that cannot be found: "\Package\Foreach TC03 File.ForEachEnumerator.Properties[Directory]". This occurs when an attempt is made to resolve a package path to an object that cannot be found. i:\visual studio 2005\projects\claimtran\isotypeloss\ISOTypeLoss.dtsx 1 1

Warning 2 Warning loading ISOTypeLoss.dtsx: The package path referenced an object that cannot be found: "\Package\Foreach TC03 File.ForEachEnumerator.Properties[FileSpec]". This occurs when an attempt is made to resolve a package path to an object that cannot be found. i:\visual studio 2005\projects\claimtran\isotypeloss\ISOTypeLoss.dtsx 1 1

I have verified that these two entries are indeed in the Config file and they do point to a valid location for the Directory and File Specification. The package runs fine.

Thanks.

More information:

When I am selecting these two fields during the Configuration file wizard, the final screen that shows the summary of activity displays the following info under the Properties section:

Properties:
<Unknown>.Properties[FileSpec]
<Unknown>.Properties[Directory]
\Package.Connections[LoggingOutput].Properties[ConnectionString]
\Package.Connections[DSSStaging].Properties[ServerName]
\Package.Connections[DSS].Properties[ServerName]
\Package.Connections[Corp1Staging].Properties[ServerName]

The FileSpec and Directory properties are under the ForEach.EnumeratorProperties section.

Is there a known issue pertaining to this?

|||What build are you running?|||

KirkHaselden wrote:

What build are you running?

June CTP|||UPDATE:

We deployed the dtsx to the file system on the server, made changes to the FileSpec parameter in the config file and deployed it, then executed the package pointing to the Config file.

The package still looked for the file as specified in the dtsx XML (package), regardless of what was specified in the config.

To confirm, we renamed the input file to match the FileSpec parameter in the package and it ran successfully, finding and processing the file.

The ForEach parsing for the Enumeration objects appears to have a bug. Can someone let me know if this is a known bug and if it is going to be fixed for the next CTP release?

Thanks.|||

We attempted to recreate the Config file uinder the September CTP and are having the same problems. The Config wizard

Here is what is displayed at the bottom of the dtsConfig file:

File name:
\\MSSQLYukon\etlconfig\ISOTypeLoss.dtsConfig

Properties:
\Package.Connections[ISOTypeLossLog].Properties[ConnectionString]
\Package.Connections[DSSStaging].Properties[ServerName]
\Package.Connections[DSSStaging].Properties[InitialCatalog]
\Package.Connections[DSSStaging].Properties[ConnectionString]
<Unknown>.Properties[FileSpec]
<Unknown>.Properties[Directory]

We still get the error in the Error Window:

Warning 1 Warning loading ISOTypeLoss.dtsx: The package path referenced an object that cannot be found: "\Package\Foreach TC03 File.ForEachEnumerator.Properties[Directory]". This occurs when an attempt is made to resolve a package path to an object that cannot be found. i:\visual studio 2005\projects\claimtran\isotypeloss\ISOTypeLoss.dtsx 1 1

|||You will get this error if your config file contains references to names that don't exist elsewhere in the package. (In this case, it sounds like your config file contains references to "Foreach TC03 File", which doesn't exist in your package.) When you recreated the config file, did you actually delete the previous config file from the file system?|||

Cim Ryan wrote:

You will get this error if your config file contains references to names that don't exist elsewhere in the package. (In this case, it sounds like your config file contains references to "Foreach TC03 File", which doesn't exist in your package.) When you recreated the config file, did you actually delete the previous config file from the file system?

Absolutely. The errors I have highlighted above were copied and pasted from the confirmation screen of completing the Package Configuration Wizard and BEFORE the file is written.

The wizard itself does not recognize the setting before the file is written.

This is a HUGE issue if we are to deploy Yukon! |||

I've only been able to reproduce the error you're encountering by manually editing the config file and corrupting the name of the ForEach Loop. If you'll remove from your package all but the tasks necessary to reproduce the error (make a backup copy first, obviously), I'll take a look at the pared down package if you post it here.

|||

Cim Ryan wrote:

I've only been able to reproduce the error you're encountering by manually editing the config file and corrupting the name of the ForEach Loop. If you'll remove from your package all but the tasks necessary to reproduce the error (make a backup copy first, obviously), I'll take a look at the pared down package if you post it here.

Where in the Wizard should I be selecting the Directory and FileSpec properties? I select them under the <PackageName>/Executables/<ForEachContainerName>/Executables/EnumeratorProperties entry. In there I select "Directory" and "FileSpec".

I tried to post the XML but the Forum logged an error. Can I send the XML to you via email or post an attachment somewhere?|||Yeah, that sounds like the correct place to be specifying the properties. Go ahead and email me the package and config files.|||By the way, these are not errors, they are warnings which are non-critical. There is a property on packages called "SuppressConfigurationWarnings" that will hide these messages. They are there to simply inform you that the configuration is attempting to configure an object that is not there. So you can either remedy the situation or ignore it.
Configuration warnings should not fail the package.
K|||

KirkHaselden wrote:

By the way, these are not errors, they are warnings which are non-critical. There is a property on packages called "SuppressConfigurationWarnings" that will hide these messages. They are there to simply inform you that the configuration is attempting to configure an object that is not there. So you can either remedy the situation or ignore it.
Configuration warnings should not fail the package.
K

Kirk:

The problem is that we need to change settings in the config file to migrate the packages through Model, QA and to Production. As I posted above, I can change the settings to point to a different Directory but because the package cannot configure the setting it always uses the setting that was set when the package was designed, which is the Test directory. The only way to change this is to open the package, change the attributes during design and save them. But then it points to the Production directory, etc.

Changing the SuppressconfiurationWarnings setting will indeed hide the warning but it will not change the setting at execution time - which is the reason for having these properties in the config file in the first place.|||

Cim Ryan wrote:

Yeah, that sounds like the correct place to be specifying the properties. Go ahead and email me the package and config files.

Done. As stated, I am running September CTP.|||

I wasn't able to reproduce this failure, and Raul can no longer do so, either.

FOR XML in web page- OK what's next

(1) I need to select records from a SQL database and create a XML document which I then need to write to a users directory. I am using SQL express with VWD 2005. I located the FOR XML and can execute in VWD's SQL graphical tool. I haven't tried in a Web Form yet but I assume it will work ok. But then how do I write the xml results from the SQL query to local user's directory?

(2) I can execute the FOR XML in the SQL graphical tool but how do i Connect to the DB in the Web Form? Use data.sqlClient.Connection and use .SQLcommand to perform the SQL query? If so, then what?

Following is an example I found that shows use of a NameSpace

WITH XMLNAMESPACES (DEFAULT 'urn:example.com/doc'

, 'urn:example.com/customer' as "c"

, 'urn:example.com/order' as"o"

)

SELECT CustomerID as "@.ID",

(SELECT OrderID as "@.OrderID"

from Orders

where Customers.CustomerID=Orders.CustomerID

FOR XML PATH('o:Order'), TYPE

) as "c:Orders",

CompanyName as "c:CompanyName",

ContactTitle as "c:ContactName/@.ContactTitle",

ContactName as "c:ContactName/text()",

PostalCode as "c:Address/@.ZIP",

Address as "c:Address/c:Street",

City as "c:Address/c:City"

FROM Customers

FOR XML PATH('c:Customer'), ROOT('doc')

My research turns up nothing on the above. Is FOR SQL the best way to go? I see that the SQLXML is not available in SQL server express 2005.

Thanks for any help.

Pauley

Once you have the data in a dataset you can write the XML to a file(froma datatable as well if you want) Link is here;

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

To do this you do not need to use for xml or even the XML-DT on the server.

For Each Loop Script

hi friends,

i have choosen the for each loop in that i was selected the all jpgs(d:\images ) directory

when we get the *.jpgs

in the for each loop i am getting the all jpgs (one by one in a local variable fname)

i have choosen the script task in the for each loop

now when we displaying the jpgs through the message box in script

msgbox(fname)

i am getting the full name with path like

"d:\iimages\aaa.jpg"

I WANT TO BE

===============================

FNAME FILE PATH

===============================

aa.JPG "d:\iimages\aaa.jpg"

===============================

?

i need the file name only

regards,

koti

A .Net question really, see the System.IO.Path.GetFilename method which can be used in your VB.Net code.

For Each Directory

How can I modify the for each loop to go through all the folders under parent folder?

ParentFolder

SubFolder1

SubFolder2

SubFolder3

Maybe add another foreach loop?|||Click the transverse subfolders checkbox?|||

Crispin wrote:

Click the transverse subfolders checkbox?

Yes. Using the Foreach File Enumerator, you have this option.... That should work for the OP.|||Transverse Subfolders is the way to go. Just make sure you specify the parent folder in the foreach loop|||

I actually tried that already and it does not give me the result I need. If there are no files present in the sub folder nothing happens.

ParentFolder

SubFolder1

SubFolder2

SubFolder3

What I am trying to do is delete all subfolders over 90 days old in the parent folder but first I want to write the folder name to a txt file/email/db which ever is decided later on.

The thing is, the name of the subfolders can change and the subfolder may not always contain a file. It maybe empty sometimes. When I use Traverse subfolders it looks in the subfolders for files. If there are files in those subfolders the package works fine. But when no files are present nothing happens.

|||If there are no files in any of the folders, then it will return nothing as it's a File Enumerator.

The only way would be to have a script task beforehand and check the folders there. If they are empty, delete them.

Have a look forVB.net examples ongoogle for listing directories.|||One of the SSIS custom component samples Microsoft provides is a Directory enumerator. I'd take a look at that for this purpose.

For Each Directory

How can I modify the for each loop to go through all the folders under parent folder?

ParentFolder

SubFolder1

SubFolder2

SubFolder3

Maybe add another foreach loop?|||Click the transverse subfolders checkbox?|||

Crispin wrote:

Click the transverse subfolders checkbox?

Yes. Using the Foreach File Enumerator, you have this option.... That should work for the OP.|||Transverse Subfolders is the way to go. Just make sure you specify the parent folder in the foreach loop|||

I actually tried that already and it does not give me the result I need. If there are no files present in the sub folder nothing happens.

ParentFolder

SubFolder1

SubFolder2

SubFolder3

What I am trying to do is delete all subfolders over 90 days old in the parent folder but first I want to write the folder name to a txt file/email/db which ever is decided later on.

The thing is, the name of the subfolders can change and the subfolder may not always contain a file. It maybe empty sometimes. When I use Traverse subfolders it looks in the subfolders for files. If there are files in those subfolders the package works fine. But when no files are present nothing happens.

|||If there are no files in any of the folders, then it will return nothing as it's a File Enumerator.

The only way would be to have a script task beforehand and check the folders there. If they are empty, delete them.

Have a look for VB.net examples on google for listing directories.|||One of the SSIS custom component samples Microsoft provides is a Directory enumerator. I'd take a look at that for this purpose.

For each container - performance problem?

I have a for each container configured to iterate over files in a directory (around 2600 files). The for each container contains only one data flow task. The data flow task is very simple. Only 3 steps. Read from file using flat file source (ragged right), format column values (eg. converting YYYYMMDD into yyyy/mm/dd and TRIM spaces - 13 such conversions) using derived column transform and inserting them into a table (23 columns - mostly nvarchar, few numerics and few dates) using Ole Db Destination. The table has one non-unique clustered index on nvarchar(18) column. Each of the 2600 file has at an average 750000 rows.

It seems to me that memory deallocation doesn't happen after each iteration of the loop (I might be wrong). So after few iterations, sqlservr.exe is hogging all available memory (2GB in this case) whereas my dual processors are running at 3-6 % of capacity. As a result, to process less than 5 million rows, it is taking 6 hours.

I am surprised that for such a trivial operation, performance should degrade this much.

One thing that is suspicious is that the files are residing on a USB 2 external drive.
Also, the database files are on the same drive. But, still... why first few iterations zap through like missiles while the later ones seem to be competing with snails?

Any insight?

TIA,
NiteshDo you use transactions? If yes, I suspect the cause of the problem that all files are submitted as part of single transaction - so the Sql Server has to keep all the data before committing the transactions.

I would rather avoid transactions unless really needed and use SQL Destination for bulk insert operation which performs better than generic OleDb Destination.|||Try loading to a raw file. You can append to the raw file each time around the loop. Then, simply insert the whole of the raw file contents at the same time.

-Jamie|||

Michael Entin SSIS wrote:

Do you use transactions? ...

No, I am not using Transactions.

Michael Entin SSIS wrote:

...use SQL Destination for bulk insert operation which performs better than generic OleDb Destination.

If I remember correctly, in CTP15/16 SQL Destination was not an option for remote operations. I am not sure what holds true for nov RTM. I will give it a shot.

thanks,
Nitesh|||

Jamie Thomson wrote:

Try loading to a raw file. You can append to the raw file each time around the loop. Then, simply insert the whole of the raw file contents at the same time.

-Jamie

Interesting idea Jamie.
However, this will create a huge file (say with 1.5 billion rows). I am not sure when I try to load that, how it will impact memory etc.

thanks,
Nitesh|||

Nitesh Ambastha wrote:

Jamie Thomson wrote:

Try loading to a raw file. You can append to the raw file each time around the loop. Then, simply insert the whole of the raw file contents at the same time.

-Jamie

Interesting idea Jamie.
However, this will create a huge file (say with 1.5 billion rows). I am not sure when I try to load that, how it will impact memory etc.

thanks,
Nitesh

There's only one way to find out :)

I'll be brave and say it should be able to handle it. Remember, all it does is swap buffers in and out of memory - once the contents of a buffer is inserted into the target the buffer "disappears".

You may have to play with buffer sizes etc... to get optimal performance but that sounds like an interesting piece of work if you ask me (perhaps that's just me! )

Let us know how it goes anyway. You should definately try it.

-Jamie|||

Nitesh Ambastha wrote:

If I remember correctly, in CTP15/16 SQL Destination was not an option for remote operations. I am not sure what holds true for nov RTM. I will give it a shot.

thanks,
Nitesh

Correct. SQL Server Destination can only be used when the target is local.

-Jamie