How can I force a Next Iteration in a ForEach Loop container?
I am looping through a folder(ForEach Loop Container) looking for a specific File Name ( Child 'Script Task') to evaluate name).
If the current file is not the File Name I need, get the next file, other wise drop down to a Exec Proc task.
Is it possible to force "Next Interation' on the parent container?
Thanks - Covi
Not quite sure what you mean. In what circumstances do you want to 'force teh next iteration'?
-Jamie
sqlI 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>
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.
sqlHi,
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:
OK
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
|||
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.
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:
OK
|||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.
|||I don't know what that error is. Maybe the component got messed up somehow. Try deleting it and creating a new one.
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.
That indicates to me that you don't have a default value in that variable.
Hi,
I want to do the following with a ssis package:
INPUT:
A table contains 2 columns with data i need. column A=Filename and column B=FileContent
PROCESS:
I need to loop through ea record in the table and retrieve columns A and B. Then for ea column i need to write the Content hold in column B into File hold in column A.
I so far found out, that i need a Execute SQL Task in Control Flow querying the table and get columns A and B into 2 variables, plus a 3rd var holding the object. Then the output goes into a Foreach Loop Container. From this point i don't know how to continue. I tried to put a Data Flow Task inside the Foreach Loop, but couldn't find out how i now get the 2 variables to the Data Flow Task and use them to for the file to be written and the content to be placed in the file.
Is there any example similiar to that so i could learn how to start on that?
Thanks
Danny
(Further you can use Import Column transform; in example from here this transform was called File Inserter (in beta release).) - I thought you need insert a file. To export a file you need Export Column transform
|||The Sample you mention is not exactly what i need. That sample loops through a list of files and writes the names of the files back to a table. Then it has a standard Data Flow Task reading the table with the filenames inserted before and do something with it.
What i need is loops through a table, and for each row i need 2 values from the table to work with in the Data Flow Task. One of the values is the filename to be written and the other value is the content to be written in the file.
|||You can do in following way :
1. Let's say you want to put the files in c:\YourFolder, add a data flow task and connection to your table
2. Add a derived column transformation; make a derived column name NewFilePath and in expressions :
"C:\\YourFolder\\+(DT_WSTR,50)ColumnA"
3. Add an Export Column transformation; in Export Column transformation editor set
Extract Column= ColumnB
File Path Column=NewFilePath
so SSIS will get the file from columnB and put in the folder using NewFilePath
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
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
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
sqlI've followed the steps on how to use the ForEach loop container using a Flat Text file and it's working fine. I tried reading the text file and put it in my SQL table. However, I have some DBF files as well and the ForEach loop does not work at all with my DBF files.
Here is my scenario:
A connection manager pointed to C:\DBF_FILES
On the Control Flow: a Foreach loop container with Foreach file enumerator. (checks for IFUL*.DBF files and retrieves the Fully qualified name.
Within the foreach loop container is my DataFlow (import)
Within the dataflow:
OLEDB datasource
data access mode: Table name or View Name variable
Variable nam: username::Filefound
Now from this point on, I'm already having problems, here is the error:
An OLEDB error has occured.
An OLEDB record is available. Source: "Microsoft OLE DB Provider for ODBC Drivers.
Data source name not found and no default driver specified.
Error at data flow task...
There is also some error that say i don't have a destination table specified. Plus the error is on the OLEDB datasource not having a destination table? I don't understand this part.
Please help!
From what I understand of your post in my thread, I think what you want to do is simple. On the Foreach loop set the Folder to C:\DBF_FILES, set Files to *.dbf and select the "Name only" radio box in teh Retrieve file name.
That should set your configured variable in the Variable mapping to the name of the dbf file for each iteration. In your DataFlow set the data access mode to "Table name of view name variable" and the variable to the one you used in the Foreach variable mapping.
|||Oh you should note, that you can only open one type of DBF file with that OLE DB Source. Meaning, all the files will need to have the same schema. If you have files with different content your going to either have to select them and handle the differences before this part. I have a couple ideas but it really depends on what you situation is.
|||I think I see your problem, I actually put something together really quick to test that idea. I'll have to think about it a bit more later.
|||Thanks Dan,
Yup, i only have the same schema for all the DBF files that I'm trying to retrieve. If I test the data flow with an implicit database name, it works fine. It's when I put it to the Foreach loop that I'm having the problem.
When I test the foreach loop with flat file, it works fine, but when I use the OLEDB, that's when it fails, I think I am following everything that needs to be done, it's just that all the samples for foreach loop has flat files for data source, and I expect it to be the same to the other types of data source. Maybe there is a missing link when the data source is not a flat file...
|||Opps, I didn't have my Connection manager pointing to the right directory. What I described does in fact work. I'll try and post up some screen captures of how I have the package setup and perhaps you can see what isn' t configured right on your end.
This is about what I can write-up at the momment.
http://www.infrandom.com/42/Blog/tabid/53/EntryID/2/Default.aspx
|||I've been doing and followed the steps exactly as you have laid out. My problem is on the OLEDB source. When I fil up the: OLEDB Connection Manager, data source mode and the variable name, when I click OK, it gives me the error:
Error at Data Flow task [OLE DB Source(431)] A destination table name has not been provided.
I really really appreciate the screen caps that you have provided. It really helps a lot. However, it is on that OLEDB source that I can't click OK due to the error, although my screen looks exactly like yours already.
Is there perhaps something that's not installed on my machine?
Thanks a lot.
|||Another thing that I'm playing around just to be able to fix this is giving my variable a valid initial value. I am sure that the DBF file exists in the list but the error that I get when I do that is:
Opening a rowset for 'TBLNAME' failed. Check that the object exist in the database.
I tried TBLNAME.DBF, I tried TBLNAME only, but it just can't find it.
I made DBASE IV a version V but it said couldn't find installable ISAM. So I put back the DBASE IV.
On OLEDB Source:
The thing is I made my table access mode as table or view, and I choose TBLNAME from the list, I am fine. The problem is when i make my table access mode as 'table name or view name variable' and on the variable, i select my variable with my initial value as TBLNAME, it just can't find it.
I'm already lost. I gotta get some fresh air.
|||View the variables, if you scroll the pane out, you can set the initial value of the variable in the designer. Give it a valid file name for a file in the directory. (There has to be a file present in the directory design time at least).
|||I got that error when my Foreach loop directory and my Connection Manager directory were not point to the same directory. Once I updated to connection manager to the same directory I was able to run the package. Again, once you set the detault value of the variable just name no extension I think you should have it.
|||Whew!!!
sometimes, a fresh air is really what it takes to get the mind cleared out.
Yup, I give my variable a valid file name. The problem was that only the DBF files were in that directory, not the index files. So I copied the index files as well, and it's now doing it's thing.
I followed everything that you have laid out on your screen caps.
Added a valid value to the variable. Made sure that the name retrieval is set to 'NAME only'. I set it to 'fully qualified path' at first, which contributed to the error. And finally, make sure that both the DBF and CDX files are present.
THANKS A LOT!!!
Really appreciate it.
|||Glad you got it working!
sqlAny help appreciated here...
I'm inserting a CSV file into my SQL Server 2005 Sept CTP which has approx 1700 rows. It contains Companies and Contacts which I split in a data flow and determine if with Fuzzy Lookups whether dupes exist and perform either insert or update (That works great).
Here is my issue:
The database does not use Sequence for the Pkeys and to make matters worse they are prefixed with letters. I have to generate them on my own with a custom function I have written. I need to step through each record of the CSV and assign the Pkey value and upon successfull insert update the counters table.
I cannot seem to figure out how to enumerate through this CSV file? I pull my Pkeys into Variables and thought someone might be able to advise.
Thanks,
David
I assume you are using a Data Flow task to run the import, so could you not use a Script Component to add the new PK column, calling your function?
The Script Component can add a new column, in a similar manner that you may do with the Drived Column Tx for example. You would need to use a script to be able to leverage your existing code I assume. If you could use the Derived Column, that would probably perform better.
Will this work, or have I missed something? Not quite sure what you mean by "update the counters table"?
|||I have added a Derived Column for the Company Primary Key but it generates one primary key for the entire record set from the file (1700 records).Here is the process I'm using.
1.) DataFlow
a.)Flat File Source Adapter (1700 records)
b.)Add Derived Column for PrimaryKey
c.)ConvertData to match input Table.Column datatypes
d.)Need to Assign Primary Key to each record here.
In the control flow I query my database function to give me the next available primaryKey into a variable. I need to perform an update on the "Counters" Table which holds the current max(primarykey) so I can use the function again for the next row.
I can't seem to figure out how to iterate through the recordset where I can update my variable(pkey) for each of the 1700 records.|||
I assume you are using a Data Flow task to run the import, so could you not use a Script Component to add the new PK column, calling your function?
The Script Component can add a new column, in a similar manner that you may do with the Drived Column Tx for example. You would need to use a script to be able to leverage your existing code I assume. If you could use the Derived Column, that would probably perform better.
Will this work, or have I missed something? Not quite sure what you mean by "update the counters table"?
I have added a Derived Column for the Company Primary Key but it generates one primary key for the entire record set from the file (1700 records).
Here is the process I'm using.
1.) DataFlow
a.)Flat File Source Adapter (1700 records)
b.)Add Derived Column for PrimaryKey
c.)ConvertData to match input Table.Column datatypes
d.)Need to Assign Primary Key to each record here.
In the control flow I query my database function to give me the next available primaryKey into a variable. I need to perform an update on the "Counters" Table which holds the current max(primarykey) so I can use the function again for the next row.
I can't seem to figure out how to iterate through the recordset where I can update my variable(pkey) for each of the 1700 records.|||Using the Derived Column, would mean that you assign the PK value as part of the derivation/expression. If you can't, loose this transform.
If you need to call a T-SQL function, then a couple of ideas-
Can you do that in a Lookup, customize the SQL? I haven't tried this in SSIS, but DTS allowed any old SQL to be used.
Or
Use a Script Component. This could open a SQL connection (ADO.Net connection manager), call your function, update your table, do what you want to get your new PK value. Then assign in. The Script component process row method is called once per row, so you should have no problem. The "how to iterate" issue is a non issue when your are doing row by row processing.|||I will work with the Script Component. I'm more DB savvy then Scripting but figured I would have to learn it sooner or later. Thanks for the help. Good to know the best option.
--David
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
|||... 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.
|||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 :)
|||
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...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
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
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.
|||
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|||
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
|||
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
|||
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?|||
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.
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
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.
|||
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|||
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
|||
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
|||
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?|||
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.
In my Control Flow, I execute a data flow that opens a flat file and populates the file into a recordset.
Back to my Control Flow, I have a ForEach container that uses a ForEach ADO Enumerator. Inside the ForEach, I execute an "Execute SQL Task" that updates a table.
This is where I'm confused, while in the ForEach, I also want to call a Data Flow and use the current record (record in my ForEach) and perform several lookup tasks. Unfortunately, I'm now sure how to use the ForEach record as a source in my Data Flow....What am I missing?
Thanks,
Gary
We need to get the column values for each record in the "ForEach ADO Enumerator" in some variable.
Then for the tasks inside the ForEach container - we can use those variables.
Similar example is @. http://www.codeproject.com/useritems/foreachadossis.asp
Thanks,
Loonysan
Click Collection.
On the Collection page, select Foreach File Enumerator."
The drop down only contains
foreach ado enumerator
foreach ado.net schema_rowset.enumerator
foreach from variable.enumerator
foreach nodelist.enumerator
foreach smo.enumerator
How do I get the "foreach file in folder" to appear in the drop down list?
Gary
A quick search of the forum will yield you the answer to your question.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1461435&SiteID=1|||
Mmmn,
I got our technical support team to load the sql server service pack 2 to fix another problem a couple of days ago.
This has not cured this problem (but did fix the other problem I was having).
Funny enough, both problems indicated that certain registry keys could not be read..
I'll get our technical support team to set up a restricted user and run the C# program the knowledgebase article gives.
Gary
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 ...
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*".
Hi,
I have no "Foreach File Enumerator" oprtion in the Enumerator Property of the Foreach Loop Component.
I have this enumerator in the c:\Program Files\Microsoft SQL Server\90\DTS\ForEachEnumerators\ folder.
Also I check it in the GAC - it does not here. I try to execute gacutil.exe -iF ForEachFileEnumerator.dll, but it is failed with "Failure adding assembly to the cache: The module was expected to contain an assembly manifest." Seems it is not managed enumerator.
Please help me.
Also information on how to regeister unmanaged enumerators are welcome!
Did you search the forums?From the thread, http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=608235&SiteID=1, you need to review this KB article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;913817
Also, a user has posted his experience with the same thing: http://mohansmindstorms.spaces.live.com/Blog/cns!69AE1BEA50F1D0E7!203.entrysql
I am trying to use the Foreach File Enumerator and map it to a user variable.
I select a folder in the colletion.
I use *.* for file name.
I left the default value for retrive file name.
I assigned it to my user variable ( data type string)
I am get the following error "The element cannot be found in a collection"
I am sure that I have at lest 10 file in the folder.
Can anyone tell me why I get the error?
I found out what my problem was
I had to assign a value to the variable.
The Foreach File Enumerator did not set the value when i mapped it to the variable.
I had to type in a value for the variable after I created it.
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
|||
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.
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.
|||
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?
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?|||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?
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.
|||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|||
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.|||
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.
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?|||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?
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.
|||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|||
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.|||
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.
