FullText Ranking: files

Showing posts with label files. Show all posts

Tuesday, March 27, 2012

Foreach loop with XML Source failure

I can't import from XML files using a foreach loop. I load an XML file with a generated XSD. When I map the file to the table it has no errors. If I now go back and change to a different XML file, I get an error:

"Error 1 Validation error. Data Flow Task: DTS.Pipeline: input column "COLUMNNAME" (129) has lineage ID 2115 that was not previously used in the Data Flow task. Package.dtsx 0 0"

This is for testing purposes. When I run the foreach loop it does not work. Ironically, I do the exact same thing in another foreach loop with a completely different XML and it works fine.

Here is the broken XSD:

<?xml version="1.0"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="ComputerStatus">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" maxOccurs="unbounded" name="computer">
<xs:complexType>
<xs:attribute name="GUID" type="xs:string" use="optional" />
<xs:attribute name="WSUSServer" type="xs:string" use="optional" />
<xs:attribute name="WSUSGroup" type="xs:string" use="optional" />
<xs:attribute name="computerName" type="xs:string" use="optional" />
<xs:attribute name="OSBuild" type="xs:unsignedShort" use="optional" />
<xs:attribute name="OSSP" type="xs:unsignedByte" use="optional" />
<xs:attribute name="Model" type="xs:string" use="optional" />
<xs:attribute name="Make" type="xs:string" use="optional" />
<xs:attribute name="BIOS" type="xs:string" use="optional" />
<xs:attribute name="Processor" type="xs:string" use="optional" />
<xs:attribute name="LastReportedStatus" type="xs:string" use="optional" />
<xs:attribute name="LastSyncTime" type="xs:string" use="optional" />
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

Help. Please. What have I done wrong. I imagine there is a flaw in my XML, but I can't pinpoint it.

Here is a sample of the XML file:

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<ComputerStatus>
<computerCount QTY="1" />
<computer GUID="edc2b6a5-5d86-467c-8c89-43fa18ae5921" WSUSServer="WSUS" WSUSGroup="THIS" computerName="COMPUTER" OSBuild="3790" OSSP="1" Model="COMPUTERTYPE" Make="HP" BIOS="1" Processor="x86" LastReportedStatus="10/25/2006 12:00:49 PM" LastSyncTime="10/25/2006 11:57:09 AM" />
</ComputerStatus>

That error says INPUT column, so I doubt its coming from the XML source adapter. An XML source adapter has output columns (and external metadata columns). Are you certain the error is with the source adapter and not some other pipeline component?

As an aside, that XSD and xml will work just fine in without regard the surrounding container. The XSD is not broken so far as use in the SSIS source adapter is concerned, although it does not contain the <computerCount> element.|||

Thank you for the feedback, but I think that I failed to mention that yes, the next thing that I send the XML Source to, whether it be a sort, derived column, an ole db destination, etc... is where the failure shows up.

Take for instance the case where I put the XML Source to an OLE DB Destination. I use a file and set the columns via regular mapping. Then I go back and set the XML Source to another file to be sure it continues to work and I get the error:

Error 1 Validation error. Data Flow Task: DTS.Pipeline: input column "WSUSServer" (5136) has lineage ID 4776 that was not previously used in the Data Flow task. Package.dtsx 0 0

Then I go back into the Ole DB Destination and have it map using Column Names. And everything is okay again. Then go back and switch to the next file and get this error:

Error 1 Validation error. Data Flow Task: DTS.Pipeline: input column "WSUSServer" (5136) has lineage ID 5265 that was not previously used in the Data Flow task. Package.dtsx 0 0

It's a vicious cycle.

An aside, to your aside, I was messing with the XSD and took out the ComputerCount during debug.

Thank you for your help.

sql

ForEach loop with Excel

Hi,

I'm attempting to use the Foreach loop container to loop through the excel files located on a shared network folder. I've set up the Excel file connection manager to include the user variable generated from the container, but I get the below error messages when applying that variable to the connection string in the data flow. I've tried everything but I can't seem to get SSIS to recognize the path of the Excel files. I've tried copying the files to my PC, I tried running the package on the server, etc. The connection works fine if I set it up to point to any of the excel files in the network directory, but not with the Foreach loop connection name.

Any help or suggestions would be greatly appreciated! I've looked everywhere and tried everything but to no avail...

Thanks,

Kevin

TITLE: Microsoft Visual Studio

Error at GDW - RDB LOAD [Connection manager "UK RDB"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D.

Error at Extract UK RDB [UK RDB [1]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "UK RDB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

ADDITIONAL INFORMATION:

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

BUTTONS:

You should give more detail about how you're configuring the connection manager, included any expressions and which properties they're being applied to. Confirming the run-time values of variables through the use of breakpoints would be helpful, too.

You say you're setting the connection string. Isn't there a FileName property?
|||

Thanks!

It's my first time posting on this site so I'll try and do a better job explaining what I'm trying to do.

I have a connection to an Excel file created in my package. Using the ForEach loop I'm attempting to change the connection string on that connection for each excel file located in the directory. I've created a user variable in the ForEach loop that's supposed to be populated with the fully qualified location of each excel file. The data flow component has the error on it before I even execute the package. That component is linked to an OLE DB destination which is a SQL table.

I can send you further detail if you'd like or if I'm missing anything...

Kevin

|||

Kevin wrote:

I have a connection to an Excel file created in my package. Using the ForEach loop I'm attempting to change the connection string on that connection for each excel file located in the directory. I've created a user variable in the ForEach loop that's supposed to be populated with the fully qualified location of each excel file. The data flow component has the error on it before I even execute the package. That component is linked to an OLE DB destination which is a SQL table.

You're saying the OLE DB Destination component is giving you an error? I would expect the error to be on the Excel Source. Usually this is because whatever variables are used in the expression to control Source have not been initialized with default values. The Source needs design-time access to one of the files so it can read the metadata.

Your For Each loop should be placing the fully qualified name of your Excel files into a package-level variable. This package-level variable should have a valid path to an existing file as a default value. You should set up an expression on the Excel connection manager to set the ExcelFilePath property with your variable containing the filename.
|||

The OLE DB Destination component is ok. I've assigned an excel file to the excel source but it gets overwritten because of the ForEach loop variable that was created.

I've done what you explained in the second part of your reply. I set the ConnectionString expression in the Excel file connection to the variable in the ForEach loop.

I used the example in this article but with an excel connection:

http://www.sqlis.com/55.aspx

Thank you,

Kevin

|||

Kevin wrote:

The OLE DB Destination component is ok. I've assigned an excel file to the excel source but it gets overwritten because of the ForEach loop variable that was created.

Correct. The default value is only there for design-time metadata. It will be overwritten at run-time when you actually read the file.

Kevin wrote:

I've done what you explained in the second part of your reply. I set the ConnectionString expression in the Excel file connection to the variable in the ForEach loop.

I think you want the ExcelFilePath property, not the ConnectionString.
|||

I really appreciate your help.

I tried using the ExcelFilePath expression instead and I'm still getting the same error. Also, when I set the excel file in the connection and then go back into it in design mode, the file path is empty.

This is getting pretty frustrating to have this great option but not have it work

Here's the message text again. It's erroring on Package Validation...

TITLE: Package Validation Error

Package Validation Error

ADDITIONAL INFORMATION:

Error at Extract UK RDB [UK RDB [1]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "RDB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Error at Extract UK RDB [DTS.Pipeline]: component "UK RDB" (1) failed validation and returned error code 0xC020801C.

Error at Extract UK RDB [DTS.Pipeline]: One or more component failed validation.

Error at Extract UK RDB: There were errors during task validation.

Error at GDW - RDB LOAD [Connection manager "RDB"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Invalid argument.".

(Microsoft.DataTransformationServices.VsIntegration)

BUTTONS:

|||

I think I may have helped answer my own post with that last reply. There's a setting in the package properties called DelayValidation which I set to True, and now the package runs. I still get the error on the data flow component, but each excel file is loaded. DelayValidation indicates whether the validation of the executable is delayed until run time.

|||

Kevin wrote:

I tried using the ExcelFilePath expression instead and I'm still getting the same error.

I don't know what that error is. Maybe the component got messed up somehow. Try deleting it and creating a new one.

Kevin wrote:

Also, when I set the excel file in the connection and then go back into it in design mode, the file path is empty.

That indicates to me that you don't have a default value in that variable.

Foreach Loop Problem (Bug?)

We discovered that the Foreach loop isn't recalculating the number of files found upon each new loop iteration, so if any new files are placed into the receive directory during the looping, they are not detected/processed. is this a bug or product intention?

Jason,

I would guess that this is by design - and rightly so in my humble opinion.

-Jamie

|||Well comes problems when you have a listener that triggers a foreach loop, but when loop completes and goes back to listening, the files that were placed on directory during loop iteration just sit there until a new file or change occurs.|||

I am pretty sure the documentation does mention this.

You could wrap the for each in another loops until no files are processed.

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?

|||

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?

|||

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

|||

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)

Foreach loop container Foreach File Enumerator sort files

How do you sort files from the Foreach loop container?

I love this component, except I can't get it to sort by filename.

Nice to know how to sort by timestamps also.

Dave

Cylon2005 wrote:

How do you sort files from the Foreach loop container?

I love this component, except I can't get it to sort by filename.

Nice to know how to sort by timestamps also.

Dave

You can't sort the foreach loop container. My experience is that it always *does* sort by name, but I've never trusted it.

One thing you can do is with a few steps. First use your foreach loop container to get the list of files, and then populate a database table. Then, outside of the foreach loop, use an execute SQL task to select from that table using an ORDER BY. Load an object variable with that result set. Then use a second foreach loop to spin through that object variable (ADO.net recordset). From here you can perform the work you desire.|||I was about to suggest the same; puting the file names in a table give you control over the order....|||

Thanks. It sounds like a lot of work for something that should be a property setting.

|||

Cylon2005 wrote:

Thanks. It sounds like a lot of work for something that should be a property setting.

Post your feedback at http://connect.microsoft.com/sqlserver/feedback|||

Cylon2005 wrote:

Thanks. It sounds like a lot of work for something that should be a property setting.

I would agree. Perhaps you could post a suggestion at connect (http://connect.microsoft.com/sqlserver/feedback/)

Your original mail said you wanted to order by filename. By default, this is what it does. I'm a little bit more trusting than Phil as well - I've never seen it do anything different

-Jamie

|||

Rafael Salas wrote:
I was about to suggest the same; puting the file names in a table give you control over the order....

This SQLCLR sproc *might* help you do this:

Replacement for xp_getfiledetails

(http://blogs.conchango.com/jamiethomson/archive/2006/08/24/4400.aspx)

-Jamie

|||

Jamie Thomson wrote:

I would agree. Perhaps you could post a suggestion at connect (http://connect.microsoft.com/sqlserver/feedback/)

Your original mail said you wanted to order by filename. By default, this is what it does. I'm a little bit more trusting than Phil as well - I've never seen it do anything different

-Jamie

The only reason I've never trusted it is because I've never had to. I haven't had a situation yet where I've been required to process the files in order. My requirements to date have been to process all available files in the directory, order irrelevant.

However, the OP is claiming that it doesn't order by filename. I'm curious to know two things, Dave: 1 - What are your filenames? 2 - How have you confirmed that they are not being processed in order?|||

Cylon2005 wrote:

Thanks. It sounds like a lot of work for something that should be a property setting.

Well, as discussed, it isn't. Have you posted anything to Connect?

-Jamie

|||A ascending/descending Sorted Files ForEach enumerator would be a fairly straight-forward extension of the ForEach Directory enumerator included in the set of SQL samples (SqlServerSamples.msi). For example, I added sorting on properties like name (not full path), extension, size, dates, and a regular expression on the file name itself. The included enumerator is for directories, but modifying it to return sorted files was not much of a change.

The included enumerator sample is "%ProgramFiles%\Microsoft SQL Server\90\Samples\Integration Services\Programming Samples\Control Flow\ForEachDirectory Sample", which has both VB.NET and C# versions.

After building the sample, the new enumerator shows up in the For Each container's enumerator list box as "For Each Directory CS", and it has a UI included. Property expressions (on the enumerator itself) may be used as well, as below. The actual enumerated object is an ArrayList of directories. No, this enumerator is not sorted by default, since it calls GetDirectories() on a DirectoryInfo object. Point is, its "easy" (for those with a .NET framework background) to add sorting to it and almost all of it is already built.

Example expressions on enumerator
RootDirectory =>3 "c:\\"
RootDirectorySourceFile => 3 (DirectInput)
EnumerateSubFolders => false

To install the enumerator, modify the project's (in this case C#) post-build event to write the enumerator assembly (which actually contains the enumerator's UI as well) to the Global Assembly Cache and also into the For Each Enumerators directory.

xcopy /y "$(TargetPath)" "%ProgramFiles%\Microsoft SQL Server\90\DTS\ForEachEnumerators"
"$(DevEnvDir)..\..\SDK\v2.0\bin\gacutil.exe" /if "$(TargetPath)"

The second step (gacutil...) is an attempt to write the enumerator to the Global assembly cache. Global assembly cache installs can also be done by copy and paste of the dll (pointed to by $TargetPath) into the "%windir%\assembly" folder.

|||

Wow. Fantastic stuff Jaegd. If I have time I might try and have a play with that later - see if I can get it sorted any differently. Unless you've done it already (which it sounds like you might have done)?

-Jamie

|||

Yes, I have built the sorted files enumerator. The part I'm not sure about is how to build a platform independent installer for SSIS widgets (e.g. an installer that writes to the correct locations on x64 and x86). Installing to the Global assembly cache is no problem.

Perhaps Darren can answer this one or point me in the correct direction. What are the steps necessary to acquire the correct location(s) for a platform indepedent SSIS "widget" install (task,component,enumerator,logger,connection manager). I'm not sure where you can get a platform independent location(s). Registry, environment variable, function call?

|||

SOFTWARE\Microsoft\Microsoft SQL Server\90\DTS\Setup

This registry entry holds the DTS install location for 32-bit. I don't have my 64-bit machine available, so I'm not sure if it is the same for 64-bit. I'll check tomorrow.

|||I've just tested this a little by creating files. Try sort files 1.txt, 2.txt, 21.txt, 3.txt, 33.txt, 31.txt. and msgbox the files in the order they are processed.... Not what you would want or expect for a sort.|||

The files contain date and time stamp.

C200729_82812.XML

C2007210_82812.XML

P200729_82812.XML

P2007210_82812.XML

They are FTP'd over and we decrypt them.

It's be nice if SSIS had the ability to decrypt PGP files too.

Then I wouldn't have to do all this werid batch stuff outside.

Foreach loop container Foreach File Enumerator sort files

How do you sort files from the Foreach loop container?

I love this component, except I can't get it to sort by filename.

Nice to know how to sort by timestamps also.

Dave

Cylon2005 wrote:

How do you sort files from the Foreach loop container?

I love this component, except I can't get it to sort by filename.

Nice to know how to sort by timestamps also.

Dave

You can't sort the foreach loop container. My experience is that it always *does* sort by name, but I've never trusted it.

Thanks. It sounds like a lot of work for something that should be a property setting.

|||

Cylon2005 wrote:

Thanks. It sounds like a lot of work for something that should be a property setting.

Post your feedback at http://connect.microsoft.com/sqlserver/feedback|||

Cylon2005 wrote:

Thanks. It sounds like a lot of work for something that should be a property setting.

I would agree. Perhaps you could post a suggestion at connect (http://connect.microsoft.com/sqlserver/feedback/)

Your original mail said you wanted to order by filename. By default, this is what it does. I'm a little bit more trusting than Phil as well - I've never seen it do anything different

-Jamie

|||

Rafael Salas wrote:
I was about to suggest the same; puting the file names in a table give you control over the order....

This SQLCLR sproc *might* help you do this:

Replacement for xp_getfiledetails

(http://blogs.conchango.com/jamiethomson/archive/2006/08/24/4400.aspx)

-Jamie

|||

Jamie Thomson wrote:

I would agree. Perhaps you could post a suggestion at connect (http://connect.microsoft.com/sqlserver/feedback/)

Your original mail said you wanted to order by filename. By default, this is what it does. I'm a little bit more trusting than Phil as well - I've never seen it do anything different

-Jamie

Cylon2005 wrote:

Thanks. It sounds like a lot of work for something that should be a property setting.

Well, as discussed, it isn't. Have you posted anything to Connect?

-Jamie

Example expressions on enumerator
RootDirectory =>3 "c:\\"
RootDirectorySourceFile => 3 (DirectInput)
EnumerateSubFolders => false

xcopy /y "$(TargetPath)" "%ProgramFiles%\Microsoft SQL Server\90\DTS\ForEachEnumerators"
"$(DevEnvDir)..\..\SDK\v2.0\bin\gacutil.exe" /if "$(TargetPath)"

|||

-Jamie

|||

SOFTWARE\Microsoft\Microsoft SQL Server\90\DTS\Setup

This registry entry holds the DTS install location for 32-bit. I don't have my 64-bit machine available, so I'm not sure if it is the same for 64-bit. I'll check tomorrow.

The files contain date and time stamp.

C200729_82812.XML

C2007210_82812.XML

P200729_82812.XML

P2007210_82812.XML

They are FTP'd over and we decrypt them.

It's be nice if SSIS had the ability to decrypt PGP files too.

Then I wouldn't have to do all this werid batch stuff outside.

Foreach Loop and distributed files

Hi - I'm new to SSIS and am having problems figuring out how to do the following.

I need to load data from flat files into SQLserver 2005 and have created the data flows ok, but my data files are *not* located in a single directory so I cannot use the foreach file enumerator option in the foreach loop container collection. Please correct me if I'm wrong?

My approach has been to execute a SQLcommand to get the filenames from another database table and to use the foreach ADO enumerator option and mapping the returned filenames to a project scoped variable (data type object since it is a rowset).

My problem comes when I edit the properties of the connection manager to try to use that variable for the connectionstring property in the expression editor. I get an error because the datatype of the variable is not supported in an expression.

Can anyone tell me how to correct this or outline another way to solve my problem?

thanks

Brian McLean wrote:
I get an error because the datatype of the variable is not supported in an expression.

Why not? You should be posting the result of the foreach loop into a string variable.|||

But you cannot return a recordset into a string! I tried and the sql execution failed with the following error...

Error: 0xC001F009 at DAOphotLoad: The type of the value being assigned to variable "User::FileList" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

Error: 0xC002F210 at Select Catalog files from HLA DB, Execute SQL Task: Executing the query "Select DAOcat_filename from ImgFileInfo where DAOCat_status like '%Processed%'" failed with the following error: "The type of the value being assigned to variable "User::FileList" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

|||You return a recordset into an object typed variable. Then the foreach loop works on that object variable. Using the variable mappings on the foreach loop, you can store the pieces of data in the object variable in string, int, whatver, variables.|||

Brian McLean wrote:

My approach has been to execute a SQLcommand to get the filenames from another database table and to use the foreach ADO enumerator option and mapping the returned filenames to a project scoped variable (data type object since it is a rowset).

You are in the right track; but you are missing one part; you need to shred the rowset into string variables:

Jamie has a sample package here; pay special attention to Collection and Variable mapping tabs inside of the forEach loop container:

http://blogs.conchango.com/jamiethomson/archive/2005/07/04/SSIS-Nugget_3A00_-Execute-SQL-Task-into-an-object-variable-_2D00_-Shred-it-with-a-Foreach-loop.aspx

Monday, March 26, 2012

ForEach Loop - Testing for when Enumerator is Empty

I have a SSIS package this set to run at a specific time each day. If there are no files for the ForEach tool to work upon...while it doesn't 'fail'...I would like to test for the condition that the enumerator was empty...so that I could send an email message reminding someone to followup and investigate.

What would be the best way to test for that condition?

Cordell,

You could add a counter variable to the package, use a Script Task within the ForEach loop to increment the variable, and then use an expression on a precendence constraint following the loop to decide to mail based on the count variable still being zero.

Here are some helpful links to get you started:
About variables - http://msdn2.microsoft.com/en-us/library/ms141085.aspx
Using variables in Script Tasks - http://msdn2.microsoft.com/en-us/library/ms135941.aspx
Precendence Constraint - http://msdn2.microsoft.com/en-us/library/ms141261.aspx

Cheers,
Patrik

|||

thank you Patrik for researching a solution for my need. I thought this is what I would have to end up doing, but wanted to make sure I was not missing something obvious.

It would be nice in the next major update of SSIS that this condition would be provided as an attribute/event to test for in the ForEach Loop tool.

...cordell...

p.s. Is there a place at MSDN to enter feature requests such as this?

|||

Cordell,

Feedback can be submitted to http://connect.microsoft.com.

Glad I could help,
Patrik

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?

Forcing page breaks

Hello,
Apologies if this is a silly question. I am using Reporting services with
ODBC to convert print image text files into pdb files. Each record/line of
the file has one character code then the data for the line of the report. I
used odbc to describe the file to have 2 columns. The columns are ID and
Data.
Now for the question: I need to force a page break whenever the contents of
the ID column are a '1'. THis sounds like it should be really easy, but I
cannot seem to figure out how to do this.
ThanksI have an example on www.msbicentral.com which allows you to do a page break
after X number of lines. The example (if I remember correctly) uses an
expression for the page break. You could simply change the expression.
Search for Page or Page Break in the Downloads->Reporting Services ->RDL
section
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Stan" <Stan@.discussions.microsoft.com> wrote in message
news:DB2E845D-875B-4770-8C3E-4D0E06ACCFBE@.microsoft.com...
> Hello,
> Apologies if this is a silly question. I am using Reporting services with
> ODBC to convert print image text files into pdb files. Each record/line
> of
> the file has one character code then the data for the line of the report.
> I
> used odbc to describe the file to have 2 columns. The columns are ID and
> Data.
> Now for the question: I need to force a page break whenever the contents
> of
> the ID column are a '1'. THis sounds like it should be really easy, but I
> cannot seem to figure out how to do this.
> Thanks|||Wayne,
Thanks for the tip. Unfortunately, I cannot seem to get it to work. My
data is tabular and not a matrix (tho I have tried both). When ever I add
any group the order of the data gets all fubar(ed) and I cannot seem to stop
it from doing this. The darn reports takes all the ID=1 rows(which is the
first line of each page) and puts them all at the top of the report.
Any more help you can off would be appreciated, but as always the search
continues.
Thanks
Stan
"Wayne Snyder" wrote:
> I have an example on www.msbicentral.com which allows you to do a page break
> after X number of lines. The example (if I remember correctly) uses an
> expression for the page break. You could simply change the expression.
> Search for Page or Page Break in the Downloads->Reporting Services ->RDL
> section
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Stan" <Stan@.discussions.microsoft.com> wrote in message
> news:DB2E845D-875B-4770-8C3E-4D0E06ACCFBE@.microsoft.com...
> > Hello,
> >
> > Apologies if this is a silly question. I am using Reporting services with
> > ODBC to convert print image text files into pdb files. Each record/line
> > of
> > the file has one character code then the data for the line of the report.
> > I
> > used odbc to describe the file to have 2 columns. The columns are ID and
> > Data.
> >
> > Now for the question: I need to force a page break whenever the contents
> > of
> > the ID column are a '1'. THis sounds like it should be really easy, but I
> > cannot seem to figure out how to do this.
> >
> > Thanks
>
>|||I would love to hear a better answer, but if there isn't any other way...
How about using a custom function that acts as a counter that incremented
every time there was a 1? So the data would look something like:
counter ID Data
0 | 0 | xyz
0 | 0 | xyz
0 | 0 | xyz
1 | 1 | xyz
1 | 0 | xyz
1 | 0 | xyz
2 | 1 | xyz
2 | 0 | xyz
2 | 0 | xyz
etc.
Then, you could group by that function and put page breaks after each group.
A simple function could look something like:
Private x As Integer
Public Function addone(ByVal num As Integer) As Integer
x = x + num
Return x
End Function
The function doesn't actually need to be on the report, just use it to
group. Insert a group and for the expression:
=code.addone(Fields!id.value)
Mike G.
"Stan" <Stan@.discussions.microsoft.com> wrote in message
news:191002EE-50DD-4FEA-80DC-70A5D3A95DB4@.microsoft.com...
> Wayne,
> Thanks for the tip. Unfortunately, I cannot seem to get it to work. My
> data is tabular and not a matrix (tho I have tried both). When ever I add
> any group the order of the data gets all fubar(ed) and I cannot seem to
> stop
> it from doing this. The darn reports takes all the ID=1 rows(which is the
> first line of each page) and puts them all at the top of the report.
> Any more help you can off would be appreciated, but as always the search
> continues.
> Thanks
> Stan
>
> "Wayne Snyder" wrote:
>> I have an example on www.msbicentral.com which allows you to do a page
>> break
>> after X number of lines. The example (if I remember correctly) uses an
>> expression for the page break. You could simply change the expression.
>> Search for Page or Page Break in the Downloads->Reporting Services ->RDL
>> section
>> --
>> Wayne Snyder, MCDBA, SQL Server MVP
>> Mariner, Charlotte, NC
>> www.mariner-usa.com
>> (Please respond only to the newsgroups.)
>> I support the Professional Association of SQL Server (PASS) and it's
>> community of SQL Server professionals.
>> www.sqlpass.org
>> "Stan" <Stan@.discussions.microsoft.com> wrote in message
>> news:DB2E845D-875B-4770-8C3E-4D0E06ACCFBE@.microsoft.com...
>> > Hello,
>> >
>> > Apologies if this is a silly question. I am using Reporting services
>> > with
>> > ODBC to convert print image text files into pdb files. Each
>> > record/line
>> > of
>> > the file has one character code then the data for the line of the
>> > report.
>> > I
>> > used odbc to describe the file to have 2 columns. The columns are ID
>> > and
>> > Data.
>> >
>> > Now for the question: I need to force a page break whenever the
>> > contents
>> > of
>> > the ID column are a '1'. THis sounds like it should be really easy,
>> > but I
>> > cannot seem to figure out how to do this.
>> >
>> > Thanks
>>|||Mike,
I was on this track, but could not get it right. You hit the nail right on
the head.
Thanks!!!
Stan
"Mike G." wrote:
> I would love to hear a better answer, but if there isn't any other way...
> How about using a custom function that acts as a counter that incremented
> every time there was a 1? So the data would look something like:
> counter ID Data
> 0 | 0 | xyz
> 0 | 0 | xyz
> 0 | 0 | xyz
> 1 | 1 | xyz
> 1 | 0 | xyz
> 1 | 0 | xyz
> 2 | 1 | xyz
> 2 | 0 | xyz
> 2 | 0 | xyz
> etc.
> Then, you could group by that function and put page breaks after each group.
> A simple function could look something like:
> Private x As Integer
> Public Function addone(ByVal num As Integer) As Integer
> x = x + num
> Return x
> End Function
> The function doesn't actually need to be on the report, just use it to
> group. Insert a group and for the expression:
> =code.addone(Fields!id.value)
> Mike G.
> "Stan" <Stan@.discussions.microsoft.com> wrote in message
> news:191002EE-50DD-4FEA-80DC-70A5D3A95DB4@.microsoft.com...
> > Wayne,
> >
> > Thanks for the tip. Unfortunately, I cannot seem to get it to work. My
> > data is tabular and not a matrix (tho I have tried both). When ever I add
> > any group the order of the data gets all fubar(ed) and I cannot seem to
> > stop
> > it from doing this. The darn reports takes all the ID=1 rows(which is the
> > first line of each page) and puts them all at the top of the report.
> >
> > Any more help you can off would be appreciated, but as always the search
> > continues.
> >
> > Thanks
> >
> > Stan
> >
> >
> >
> > "Wayne Snyder" wrote:
> >
> >> I have an example on www.msbicentral.com which allows you to do a page
> >> break
> >> after X number of lines. The example (if I remember correctly) uses an
> >> expression for the page break. You could simply change the expression.
> >>
> >> Search for Page or Page Break in the Downloads->Reporting Services ->RDL
> >> section
> >>
> >> --
> >> Wayne Snyder, MCDBA, SQL Server MVP
> >> Mariner, Charlotte, NC
> >> www.mariner-usa.com
> >> (Please respond only to the newsgroups.)
> >>
> >> I support the Professional Association of SQL Server (PASS) and it's
> >> community of SQL Server professionals.
> >> www.sqlpass.org
> >>
> >> "Stan" <Stan@.discussions.microsoft.com> wrote in message
> >> news:DB2E845D-875B-4770-8C3E-4D0E06ACCFBE@.microsoft.com...
> >> > Hello,
> >> >
> >> > Apologies if this is a silly question. I am using Reporting services
> >> > with
> >> > ODBC to convert print image text files into pdb files. Each
> >> > record/line
> >> > of
> >> > the file has one character code then the data for the line of the
> >> > report.
> >> > I
> >> > used odbc to describe the file to have 2 columns. The columns are ID
> >> > and
> >> > Data.
> >> >
> >> > Now for the question: I need to force a page break whenever the
> >> > contents
> >> > of
> >> > the ID column are a '1'. THis sounds like it should be really easy,
> >> > but I
> >> > cannot seem to figure out how to do this.
> >> >
> >> > Thanks
> >>
> >>
> >>
>
>

Friday, February 24, 2012

for loop container to process all Excel files

I'm having a problem getting the for loop container to process all excel files in a folder. I set the collection folder to where my .xls files are, and i set a variable in the for loop container to the FileName. I then changed my source connection and added expressions for

ConnectionString:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @.[User::FileName] + ";Extended Properties=" + @.[User::ExtProperties]
(the ExtProperties was necessary to get the double quotes in. )

ServerName:
@.[User::FileName]

It cannot connect.
I used a similar process to loop thru Access .mdb files, but did not set the ServerName and did not need the ExtProperites because the Access connection did not need them, and I got that to work.

Any help would be very much appreciated.Did you get an error message with any useful information?

You should not need to specify "Server." And I'm not clear on the need to use a variables for Extended Properties, since they're probably not changing from one Excel file to another, and no extra quotes are necessary if your string is simply "Extended Properties=Excel 8.0," assuming the default value for HDR and not using IMEX.

You could use a short Script task within your loop to view the connection string resulting from the expression on each iteration.

-Doug|||I used the following expression for Connection String, and was able to successfully loop through all excel files. As Douglas correctly pointed out, you don't need ServerName. Use \ to get your quotes in, if you have to.

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @.[User::Variable] + ";Extended Properties=\"Excel 8.0;HDR=NO\";"
thanks
Ranjeeta|||

I removed the ServerName expression and I changed my connectstring code to be like Ranjeeta's...
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @.[User::FileName] + ";Extended Properties=\"Excel 8.0;HDR=YES\";"
It evaluates correctly, but When I run, I still get the same errors:

Error at LoadStagingTables [Liabilities[1641]]: The AcquireConnection method call to the connection manager "PlanFinancialsInput" failed with error code 0xC0202009.

Error at LoadStagingTables [DTS.Pipeline]: component "Liabilities" (1641) failed validation and returned error code 0xC020801C.

Error at LoadStagingTables [DTS.Pipeline]: One or more component failed validation.

Error at LoadStagingTables: There were errors during task validation.

Error at ProcessFinancials [Connection manager "PlanFinancialsInput"]: An OLE DB error has occurred. Error code 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Invalid argument.".

(Microsoft.DataTransformationServices.VsIntegration)
If I change the connection back to just the first xls file, it works fine, but when I change back to using the connectstring, I get these errors.

Thanks very much for your help.

|||Success!!!!
I got it to work. I created a new package and tested a small .xls file just to see if I could get one to work and it worked. I didn't do anything different. But I still couldn't get my original package to work. So I compared properties at various levels and I found one different. I don't know how it got changed, but it was different. The DelayValidation on my original Data Flow task was set to False, but it was True for my test task. I changed it to True, then deleted my connectstring property and change the source to the excel file, then added the connectstring back again, and voila! it works!|||Were you using the September CTP? I'm running into the same issue and can't seem to get around it as you have.|||

No, the June CTP.
Version 9.00.1187.00

Make sure you have the delay validation property set to true on every data flow task or any task that would use your connection.

|||That did it! We had it set in each task in the data flow, but not the data flow itself. Thanks!|||

just looked at the forum.. you could try the following:

1. Foreach loop to process .xls files in a dir - set variable xlsDoc to filename

2. script to change an Excel Connection Manager to the loaded filename

Try

Dim xlConn As ConnectionManager = Dts.Connections("Excel Connection Manager")

xlConn.Properties("ExcelFilePath").SetValue(xlConn, Dts.Variables("xlsDoc").Value.ToString)

Catch ex As Exception

Dts.TaskResult = Dts.Results.Failure

Exit Sub

End Try

3. Foreach loop to process worksheets (poss load from a list which i'm doing - if you do, then trim the worksheet name from the ADO variable, to remove string padding, or it won't load properly)

4. Call your dataflow which uses the single 'Excel Connection Manager'...

The problem i'm getting now is when a worksheet doesn't exist, trying to trap/cancel the error, i've added handlers to the dataflow task, to the excel connection inside it etc, no joy...

|||

Hi
I am using SqlServer 2005 September CTP version.
I have created a package which uses 'Data Flow task' to read a Excel file,
a 'Script Component' which does some lookups and 'Sql Server Destination' to insert data into Sql server 2005 database.
The package runs sucessfully.Then I deployed the package using dtsinstall.exe,
it validate & deployed successfully,but when I tried to run the
package it gives me following error :

"Error : The AcquireConnection method call to
hte connection manager "Excel Connection
Manager" failed with error code 0xC0202009"

"Error : component "Excel Source" (133) failed
validation and return error code 0xC020801C.

I also tried changing DelayValidation to true
but it dosent work after deployment.

please suggest.
Thanks
Abhishek

|||

Hi,

I trying the same process as you have told but not succesfull.

i am getting the error "Excel Connection Manager Failed".

plz suggest me how to remove it.

thanks

|||

AkChauhan wrote:

Hi,
I trying the same process as you have told but not succesfull.
i am getting the error "Excel Connection Manager Failed".
plz suggest me how to remove it.
thanks

Check this out; I think it has what you need. Otherwise, open a new thread and provide the specific errors you are getting

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=278469&SiteID=1

|||

hi Ranjeeta,

i am having the same issues for looping the excell files. i has used all the settings for the same which you and all others has said but not successful. i am getting some connection manager failed and invalid argument error. Now i dont know what to do for it. plz help.

thanks....

for loop container to process all Excel files

ServerName:
@.[User::FileName]

Any help would be very much appreciated.Did you get an error message with any useful information?

You could use a short Script task within your loop to view the connection string resulting from the expression on each iteration.

-Doug
|||I used the following expression for Connection String, and was able to successfully loop through all excel files. As Douglas correctly pointed out, you don't need ServerName. Use \ to get your quotes in, if you have to.

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @.[User::Variable] + ";Extended Properties=\"Excel 8.0;HDR=NO\";"
thanks
Ranjeeta|||

Error at LoadStagingTables [Liabilities[1641]]: The AcquireConnection method call to the connection manager "PlanFinancialsInput" failed with error code 0xC0202009.

Error at LoadStagingTables [DTS.Pipeline]: component "Liabilities" (1641) failed validation and returned error code 0xC020801C.

Error at LoadStagingTables [DTS.Pipeline]: One or more component failed validation.

Error at LoadStagingTables: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)
If I change the connection back to just the first xls file, it works fine, but when I change back to using the connectstring, I get these errors.

Thanks very much for your help.

|||Success!!!!
I got it to work. I created a new package and tested a small .xls file just to see if I could get one to work and it worked. I didn't do anything different. But I still couldn't get my original package to work. So I compared properties at various levels and I found one different. I don't know how it got changed, but it was different. The DelayValidation on my original Data Flow task was set to False, but it was True for my test task. I changed it to True, then deleted my connectstring property and change the source to the excel file, then added the connectstring back again, and voila! it works!
|||Were you using the September CTP? I'm running into the same issue and can't seem to get around it as you have.|||

No, the June CTP.
Version 9.00.1187.00

Make sure you have the delay validation property set to true on every data flow task or any task that would use your connection.

|||That did it! We had it set in each task in the data flow, but not the data flow itself. Thanks!|||

just looked at the forum.. you could try the following:

1. Foreach loop to process .xls files in a dir - set variable xlsDoc to filename

2. script to change an Excel Connection Manager to the loaded filename

Try

Dim xlConn As ConnectionManager = Dts.Connections("Excel Connection Manager")

xlConn.Properties("ExcelFilePath").SetValue(xlConn, Dts.Variables("xlsDoc").Value.ToString)

Catch ex As Exception

Dts.TaskResult = Dts.Results.Failure

Exit Sub

End Try

3. Foreach loop to process worksheets (poss load from a list which i'm doing - if you do, then trim the worksheet name from the ADO variable, to remove string padding, or it won't load properly)

4. Call your dataflow which uses the single 'Excel Connection Manager'...

The problem i'm getting now is when a worksheet doesn't exist, trying to trap/cancel the error, i've added handlers to the dataflow task, to the excel connection inside it etc, no joy...

|||

"Error : The AcquireConnection method call to
hte connection manager "Excel Connection
Manager" failed with error code 0xC0202009"

"Error : component "Excel Source" (133) failed
validation and return error code 0xC020801C.

I also tried changing DelayValidation to true
but it dosent work after deployment.

please suggest.
Thanks
Abhishek

|||

Hi,

I trying the same process as you have told but not succesfull.

i am getting the error "Excel Connection Manager Failed".

plz suggest me how to remove it.

thanks

|||

AkChauhan wrote:

Hi,

I trying the same process as you have told but not succesfull.

i am getting the error "Excel Connection Manager Failed".

plz suggest me how to remove it.

thanks