July 15, 2008 at 6:55 pm
Hi all,
I am attempting to do what I think is a very simple operation. I would like to loop through a folder looking for Excel files. With each file I open and output the rows to a flat file. Everything works except the looping.
I have a Foreach Loop Container and I have placed the Data Flow Task inside of it as well as pointed to it (not sure the term) with the green arrow from the Foreach.
I have done the simple stuff, and have attempted to create variabes and expressions. But to no avail.
The results are a wonderful text file with the contents of only one of the two file data.
One thought is that it is overwriting the file each loop. But for the life of me I cannot figure this out.
Thanks to all.
\e
July 15, 2008 at 7:04 pm
Hi all.
Well it was overwriting the file but that was not the problem.
The Foreach loop appears to not be foreaching.
\e
July 16, 2008 at 1:26 pm
I found the answer. I will post the steps when I have the time.
Eric
July 16, 2008 at 8:42 pm
Hi ,,
Can you post yr steps so i can easily solve my probs for future..
thanks
July 18, 2008 at 7:32 am
I will do that this weekend. Started to write them up and got about 75% complete and then I got pulled off.
\e
July 20, 2008 at 9:05 am
Sorry for the delay. It is because I am having a problem recreating the package on my system.
I have a package (built on another system) running perfectly on my system. However, When I created a new project and attempted to duplicate the process it failes.
I have had simpular issues as this post but possible not related.
http://www.sqlservercentral.com/Forums/Topic537243-148-1.aspx
The problem is with the Excel Source Connection and the Expression I am using. (Again I am duplicating a running package) It seems that I cannot select a .xls file and have an ExcelFilePath Expression defined at the same time. If I create an Excel connection, select a file everything works fine. I add the ExcelFilePath Expression and variable and the filename and path are removed from the connection string. The variable is located in the ForeachLoop container and selected using the expressions dialog box.
I can kind of see why that would happen but I have a running project that has that same combination. So I am really confussed. I have adding things in different order, etc. to no avail.
I will continue to look at it as time permits. If anyone has insight I would gretly appreciate it.
The steps to do this are 95% complete once I figure out why I have a problem.
Eric
created one and selected a file, etc., looked at the fields and
July 21, 2008 at 12:57 am
It's not entirely clear from your post but I think the problem you are having is that you need to ignore the ExcelFilePath property and concentrate on the Connection String property. It would help to understand the issue if you could post some screen shots.
Below are step by step instructions...
Note: Delete any existing Excel connection manager first!
1.Create a Variable (scoped to Package Level) called FilePath with the data Type = “String” and the Value = . Make sure it ends in a \ backslash
2.Add a ForEach Container with properties as follows:
a.Collection
i.Select File Enumerator as the Enumerator
ii.Select the source folder
iii.Amend the Files to read *.xlsx
iv.Retrieve Fully Qualified file
b.Variable Mappings
i.Under Variable select
1.Under Container, select ForEach Loop Container
2.Name = CurrentFileName
3.Drop a Data Flow task into the ForEach container
a.Open the Data Flow task
b.Drop an Excel File Source onto the data flow
i.Open the Excel Source task
ii.Connection Manager
1.Next to the OLEDB Connection Manager, select New
a.Nagivate to and select one of the Excel files in question
b.Tick or untick “First Row has column names” as required
c.Hit OK
2.Data Access Mode = Table or View
3.Select the Excel Sheet from the dropdown
iii.Columns
If “First Row has column names” was not ticked then you will need to update the column names as required by typing in the fields
iv.Hit OK
c.Drop a Flat File Destination onto the data flow
d.Connect the green success path arrow from the Source to the destination
e.Open the Flat File Destination task
i.Connection Manager
1.Next to the Flat File Connection Manager, select New
a.Select the desired format
b.General
i.Type in the destination folder and file name
ii.Select the locale and code page
iii.Type in the desired Text Qualifier eg double quote marks etc if you want to use them
c.Columns
i.Change the Row and Column delimiters if required
d.Hit OK
2.Tick or untick “Overwrite data in the file” as required
ii.Mappings
These should default correctly, but check them and make any changes as required
iii.Hit Ok
f.Right-click the Excel Source task and select Properties
i.Set the ValidateExternalMetadata property to FALSE
g.Under the Connection Managers section
i.Right click the “Excel Connection Manager” and select Properties
1.Set the DelayValidation property to TRUE
2.Copy the value of the ConnectionString property
3.On the Expressions property, select the ellipsis button next to the blank value field
a.Select ConnectionString property from the property field dropdown in the Expressions Editor.
b.Select the ellipsis button next to the blank value of the ConnectionString
c.Paste the value that you previous copied into the Expression field
d.Add a double-quote mark at the beginning and end of the expression
e.Find the Data Source clause inside the expression and replace everything between the = and the ; with 2 double-quote marks and 2 ampersands like this: “ & &”
f.Between the 2 ampersands, type the following: @[User:CurrentFileName]
ii.Right click the “Flat File Connection Manager” and select Properties
1.Set the DelayValidation property to TRUE
2.On the Expressions property, select the ellipsis button next to the blank value field
a.Select the Connection String property from the property field dropdown in the Expressions Editor.
b.Select the ellipsis button next to the blank Connection String value
c.Enter the expression as follows: replace(@[User:CurrentFileName],”.xlsx”,”.csv”)
presuming of course you want them in the same folder...
Kristen Hodges
b. http://bi-tch.blogspot.com
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply