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:

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

|||

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:

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.

|||

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 Issue

Here is what I am attempting to get accomplished. I have an SSIS package that contains a Foreach loop container. This container executes a number of SQL tasks in order: SQL Task 1, SQL Task 2, SQL Task 3.

if the SQL task 1 succeeds it should flow on to SQL Task 2 and 3. This works fine when the SQL tasks do not fail...

In the event of any SQL Task failing control should flow to a send mail task to alert about the failure. Next the Foreach loop container should go to the next enumeration in the Foreach loop container and start the next new SQL task 1. So far I have been able to get the control to flow to the send mail task when a SQL Task fails. What does not work is when one SQL Task fails the entire Foreach loop fails and does not move to the next enumeration. It should only fail the package and move on.

Any help would be appreciated....

Please check the FailPackageOnFailure and FailParentOnFailure properties of ForeachLoop Container as well as Execute SQL Task Object. if any of them is defined as true then set it to false.

If this will not help you let me know.

|||

I have checked these properties and i have both of them set to 'False'... Still Fails...

Any other suggestons?

|||I suppose you could ForceExectionResult = Success|||

Hi Steve,

Here is the Solution:

1. For Foreach Loop container set "ForceExecutionResult" to "Success" so that this container never failes on execution.
2. For precedence constraint of all your tasks in Foreach loop container set the "Value" property as "Completion" so that next SQL task get executed only on COMPLETION of previous SQL task and not SUCCESS of previous one.
3. Set the "FailParentOnFailure", "FailPackageOnFailure" property to "False" for all SQL Task in container. Set "ForceExecutionResult" property to "None" for all SQL Task in container.
4. I am sure you are using "Failure" precedence constraint to send mail task from SQL Tasks.

I created a test package to try this scenario and it works :)

Thanks
Mohit

for loop expression

I'm trying to run a data flow task inside of a for loop container.

First I was attempting to set the forloop expressions using variables that I was setting by running execute sql tasks. I ran into so many problems there, I decide to try this an easier way.

So I've created 3 variables, all Int32. One is called counter, one is called inc, and one is called max. The values are set to: counter=1, inc=2, max=100.
I then need to set the Expressions for the for loop. SO I open the properties window, and click on Expressions on the left hand side.

I've set the following:
AssignExpression : @.counter = @.counter + @.inc
EvalExpression : @.counter < @.max
InitExpression : @.counter = 1

To prove to myself that the counter variable was not incrementing, I created a Script Task, and had it show a MsgBox with the latest counter value. It's looping, but the counter variable just stays set to 1.

What is the trick? Once I get this, then I can set these variables to other variables.

Thanks in advance.
-Lori

Are you sure @.inc is 2 in the scope of the for loop? Maybe check that it is not zero in the msg box.

Mark

|||I think I was doing something stupid like that when all the variables were ints.

Now I've moved to the next step, which is all the variables need to be strings. This is because both the @.max and the initial @.counter will be set based on values I pull from the database. When getting these values they have to come out as strings, otherwise the query fails.

@.max is going to be set to the max id of a table in mysql.
@.counter (initially) will be set to the max id of a table in sql server.
The goal is to pull all rows from the mysql table to the sql server table between @.counter and @.max. I'm doing this in a forloop so as to lessen the number of rows that the job tries to pull from mysql at any given time.

Once again, I've stepped back and am just trying to get the looping to work with strings.
Here are my expressions:
Currently the looping is not working:
Assign: @.counter =(DT_WSTR,50)((DT_U18)(@.counter) + (DT_U18)@.inc))
When running in debug mode, the counter is not incrementing. I've check that @.inc is not 0, by having it displayed to me in a msgbox.

Having to manipulate everything as strings to ints to strings again is extremely frustrating.

Thanks for help,
-Lori|||

I'm not sure I understand why the variables need to be strings in the for loop container. YOu get the values once, before the ForLoop begins, correct? Could you cast them at that time, or make integer copies for the ForLoop to simplify the expressions?

Thanks
Mark

|||Hmmm. That was fancy. Wrote this big long response on how to achieve this, and the forum says "can't post for unknown reason", and I lost the whole response. Let's try again.

I used to work with DTS. It was extremely straightforward to get max id from a table, set it to a parameter and then use that parameter in the pull command. SSIS is not so straightfoward. It took me about 3 full days to figure how to do what I just mentioned in SSIS. Then luckily I wrote a howto for myself and colleagues because I couldn't recall how to do it a month later. What I learned in those three days was that when getting the max id from a table, you have to convert it to a string because that's the only way to not have data loss when using max(id).

For others, here's the trick from mysql and sql server.
mysql (using ADO.net/odbc provider): select cast(max(id) as char) as max from <table>
sql server(using OLE DB provider): select convert(nvarchar(20), max(id)) as max from <table>
When setting the result set:
mysql: ResultSetName: 0, Variable name: <whatever>
sqlServer: ResultSetName: max, variable name: <whatever2>

Then to create the sql command, you had to click on DataFlow Task, Expressions, and set up an Expression for the sqlcommand. We had to do this, so that we could use the max string. If it's an integer, you don't have to do this.
"select * from <table> where id > " + @.[User::max]

So after finally figuring all that out, I now try to create a forloop. Since I kept fighting with integers vs. strings, I thought maybe it's just best to have every variable be a string. I don't think that this is that crazy of an assumption after all the **** I had to deal with.

But it turns out that setting @.counter as a string just doesn't work. Either the looping doesn't work or the loop won't end. Depends on lucky you get. I finally figured this out though.
Create your @.counter and @.inc variables with the Package as the scope and as Int64.

Then do the following for the forloop expressions:
InitExpression: @.counter = (DT_I8)@.mssql_max
EvalExpression: @.counter < (DT_I8) @.max
AssignExpression: @.counter = @.counter + @.inc

Then for the sqlcommand in the dataflow task, do the following:
"select * from <table> where id > " + (DT_WSTR, 50) @.counter + " and id <= " + (DT_WSTR, 50) ( @.counter+ @.inc).
Convert @.counter to a string, and (@.counter + @.inc) to a string. This is working very well.

I hope this helps someone else out as there didn't seem to be any examples of this out there.
Good luck to everyone in SSIS land.|||

After fighting the same problem for several hours I found this post that finally helped me on the right track...

However for if you like me use the mysql-odbc connector the index to the parameter seems to be starting on 1.

mysql: ResultSetName: 0, Variable name: <whatever>

Should then needs be changed to:

mysql: ResultSetName: 1, Variable name: <whatever>

/Albert

for loop expression

I'm trying to run a data flow task inside of a for loop container.

First I was attempting to set the forloop expressions using variables that I was setting by running execute sql tasks. I ran into so many problems there, I decide to try this an easier way.

So I've created 3 variables, all Int32. One is called counter, one is called inc, and one is called max. The values are set to: counter=1, inc=2, max=100.
I then need to set the Expressions for the for loop. SO I open the properties window, and click on Expressions on the left hand side.

I've set the following:
AssignExpression : @.counter = @.counter + @.inc
EvalExpression : @.counter < @.max
InitExpression : @.counter = 1

To prove to myself that the counter variable was not incrementing, I created a Script Task, and had it show a MsgBox with the latest counter value. It's looping, but the counter variable just stays set to 1.

What is the trick? Once I get this, then I can set these variables to other variables.

Thanks in advance.
-Lori

Are you sure @.inc is 2 in the scope of the for loop? Maybe check that it is not zero in the msg box.

Mark

|||I think I was doing something stupid like that when all the variables were ints.

Now I've moved to the next step, which is all the variables need to be strings. This is because both the @.max and the initial @.counter will be set based on values I pull from the database. When getting these values they have to come out as strings, otherwise the query fails.

@.max is going to be set to the max id of a table in mysql.
@.counter (initially) will be set to the max id of a table in sql server.
The goal is to pull all rows from the mysql table to the sql server table between @.counter and @.max. I'm doing this in a forloop so as to lessen the number of rows that the job tries to pull from mysql at any given time.

Once again, I've stepped back and am just trying to get the looping to work with strings.
Here are my expressions:
Currently the looping is not working:
Assign: @.counter =(DT_WSTR,50)((DT_U18)(@.counter) + (DT_U18)@.inc))
When running in debug mode, the counter is not incrementing. I've check that @.inc is not 0, by having it displayed to me in a msgbox.

Having to manipulate everything as strings to ints to strings again is extremely frustrating.

Thanks for help,
-Lori|||

I'm not sure I understand why the variables need to be strings in the for loop container. YOu get the values once, before the ForLoop begins, correct? Could you cast them at that time, or make integer copies for the ForLoop to simplify the expressions?

Thanks
Mark

|||Hmmm. That was fancy. Wrote this big long response on how to achieve this, and the forum says "can't post for unknown reason", and I lost the whole response. Let's try again.

I used to work with DTS. It was extremely straightforward to get max id from a table, set it to a parameter and then use that parameter in the pull command. SSIS is not so straightfoward. It took me about 3 full days to figure how to do what I just mentioned in SSIS. Then luckily I wrote a howto for myself and colleagues because I couldn't recall how to do it a month later. What I learned in those three days was that when getting the max id from a table, you have to convert it to a string because that's the only way to not have data loss when using max(id).

For others, here's the trick from mysql and sql server.
mysql (using ADO.net/odbc provider): select cast(max(id) as char) as max from <table>
sql server(using OLE DB provider): select convert(nvarchar(20), max(id)) as max from <table>
When setting the result set:
mysql: ResultSetName: 0, Variable name: <whatever>
sqlServer: ResultSetName: max, variable name: <whatever2>

Then to create the sql command, you had to click on DataFlow Task, Expressions, and set up an Expression for the sqlcommand. We had to do this, so that we could use the max string. If it's an integer, you don't have to do this.
"select * from <table> where id > " + @.[User::max]

So after finally figuring all that out, I now try to create a forloop. Since I kept fighting with integers vs. strings, I thought maybe it's just best to have every variable be a string. I don't think that this is that crazy of an assumption after all the **** I had to deal with.

But it turns out that setting @.counter as a string just doesn't work. Either the looping doesn't work or the loop won't end. Depends on lucky you get. I finally figured this out though.
Create your @.counter and @.inc variables with the Package as the scope and as Int64.

Then do the following for the forloop expressions:
InitExpression: @.counter = (DT_I8)@.mssql_max
EvalExpression: @.counter < (DT_I8) @.max
AssignExpression: @.counter = @.counter + @.inc

Then for the sqlcommand in the dataflow task, do the following:
"select * from <table> where id > " + (DT_WSTR, 50) @.counter + " and id <= " + (DT_WSTR, 50) ( @.counter+ @.inc).
Convert @.counter to a string, and (@.counter + @.inc) to a string. This is working very well.

I hope this helps someone else out as there didn't seem to be any examples of this out there.
Good luck to everyone in SSIS land.|||

After fighting the same problem for several hours I found this post that finally helped me on the right track...

However for if you like me use the mysql-odbc connector the index to the parameter seems to be starting on 1.

mysql: ResultSetName: 0, Variable name: <whatever>

Should then needs be changed to:

mysql: ResultSetName: 1, Variable name: <whatever>

/Albert