Forecasting in analysis tab cannot view

Hi I installed the add-in for excel data mining but when i try to select a table, in the tab of analyze, dont have any properties. please help me.

carlos of southamerica

Did you select a cell within a table?

Do you see "Table Tools" above the ribbon?

Do you see "Analyze" and "Design" ribbons? (they are usually the last 2 ribbons)

If all answers are yes, what do you see in the Analyze ribbon?

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 over Excel files seems 'fragile'

All,

I have a package that loops over ~60 Excel files in a directory. Each
file has three named ranges in it, which I import into different
tables. Sometimes the package runs without a hitch, sometimes it
chokes. But it is intermittent.

If I pull the control flow components out of the foreach loop and
point the Excel connection manager to the specific Excel file that has
caused the package to choke, I get a message in the dataflow component
pointing to the named range that "the metadata of the following output
columns does not match the metadata of the external columns......Do
you want to replace the metadata of the output columns with the
metadata of the external columns?" When I choose 'yes', then the
file will be loaded. then I can put the control flow components back
into the foreach loop and the file will run again, successfully, along
with some more, until it chokes again....

So, first of all, does anyone have any insight into this? Sometimes,
somedays, these files will load with no problems. These exact files;
I am having to reload constantly... Other times, like today, it is a
battle.

Otherwise, is there a way to get Integration Svcs to handle the
metadata issue on the fly?

Any ideas, resources, references, war stories, or good clean jokes
would be appreciated,
Kathryn

Metadata cannot change... Do you have changing metadata in your Excel documents, or does SSIS just think it is changing?|||

Phil,

Thanks for the quick reply. It seems that SSIS thinks the metadata is changing..

As far as I can tell, the problem is caused when a field in the file does/does not have a hyphen in it. For example, some files give us EIN with a hyphen and some don't. The package will chug along until it gets an EIN with a hyphen, then it will choke. I will pull the control flow components out of the foreach, point the excel source at the file that's causing it to choke, then i will answer yes to the metadata warning. Then I'll put the control flow components back into the foreach and it will chug along until it gets to a file WITH a hyphen in the EIN, when it will choke again....

All fields are defined to be strings. I even put a Data Conversion component after the Excel Source component to strip out hyphens, but the data flow doesn't get to the Data Conversion; it chokes on the Excel Source.

Kathryn

|||

Hey Kathryn,

Try this... I don't know if it'll work or if you've already tried this, but try to process the erroneous file first (if possible) in the loop. I don't know if you can control that or not. Here's what I'm thinking. I think that SSIS looks at the first file, sees that FieldA1 is a numeric, and sets the metadata to numeric for that field. When you encounter a text value for that same field in a subsequent file, it bombs. So I'm wondering if you can process a file first that contains the text value of that field, for example. Then it'll think that field is a text field and process it the same for the rest? It's just a thought!

Rebecca

|||Maybe setting IMEX=1 in the excel connection string is the answer here as well.|||

Phil,

Thanks for the suggestion. Unfortunately, it didn't work, though it seems that that should be the answer....

Kathryn

|||

I'm very surprised that IMEX=1 did not work, since forcing everything to be loaded as a string should avoid the issue with the mixed data types that you otherwise have in your EIN column (numeric values when there's no dash, string values when there is one).

The only potential issue that comes to mind is the difference between string and memo fields, for which there must be at least 1 row with a memo value in the rows sampled by the driver for the driver to recognize that column as a memo column.

Let's remember that Excel has no column metadata. The driver can only guess.

-Doug

sql

Forcing types in Excel connection?

I am using Crystal Reports 9 to build a report that involves connections to both an Excel spreadsheet and an Oracle database. The problem is, the Oracle database contains an ID field stored as text (although the data is currently strictly numeric). The same ID field appears in the excel spreadsheet. I am trying to join the spreadsheet to the database. My problem is that, since ID field in the spreadsheet contains only numeric data, Crystal Reports imports the field as a numeric field then expects it match to the Oracle database's text ID field! Obviously, this creates a type error and the report fails.

My question is: is there any way to force Crystal Reports to regard a field in Excel as text rather than numeric data? Changing the cell attribute to text in Excel didn't seem to work; Crystal still saw numbers in the field and assumed the data was numeric.

This is an urgent matter so any help at all would be appreciated. Thanks!When joining the tables convert oracles table's id to numericsql

Force to date format MMDDYY

My sql :

SELECT....., date_ of_ shipment,.....

FROM......

WHERE.....

ORDER BY....

After that it will export to excel.

My problem: Some times I get a column of mixing date format MMDDYY and number.?

I have to format the whole excel column to get all date format.

My question: How can I write sql so it force the the result to be date format MMDDYY in excel? Where this clause in sql (where clause ?)

Thanks
Daniel

Code Snippet

select convert(varchar(8), getdate(), 1)

OR

select replace(convert(varchar(8), getdate(), 1), '/', '')

Depending on whether you want the slash separators or not.

Use code 101 instead of 1 in the Convert if you want 4 digit years.

|||

If my column name is date_of _shipment

Select convert (varchar(8), date_of_shipment,1) ?

Thnks

Daniel

|||

Right.

It will also lose it's name, so you may want to re-alias it.

Code Snippet

Select convert (varchar(8), date_of_shipment,1) AS date_of_shipment

|||

Thanks a millions. Look like it work great for what ever my excel column format.

Daniel

|||

I may need hour: min: second too

How about MMDDYYYYHHMMSS ?

Thanks
Daniel

Force Excel Column type when exporting from SSRS

Hi all,

I have a tricky behavior here. I have a column in my report which contains alphanumeric codes. When I have a code like 17E001 and I export the report to Excel, excel kindly shows that alphanumeric code to 1+E7 and the value of the column is changed to 1700 which is defintly not what I want.

So I was wondering if there is any way to force the column types when exporting from SSRS?

Sbastien.

By the way if there is a way to force all columns to be formated as Text that will do for me as the excel reports are only used to process data using SSIS.

Forbid Excel access for users

It seems that people can use Excel to browse the Cube, thats all good and fun. But we do not like for all the users to be able to go through the cube using excel (only a select group). The problem is that i have to create roles for the users to set their security for the report server. So that they can open the reports i've created. But if i'm not mistaking by granting users access to the cube to be able to use my reports I automaticly give them access through Excel ?!

Is there a way to give people access to the cube via report server and block their access for Excel ?

Please help me you experts

Hello! It is possible to block the access from an Excel client by using a dedicated SSAS2005 role for reporting services.

You create a single windows account for SSRS2005 execution of SSAS2005 reports, add that account to a cube role and stop using windows integrated security.

In this way it is only this dedicated SSRS2005 account that have access to the cube.

I assume that you do not use any detailed security in the cubes like dimension restrictions and cell security.

HTH

Thomas Ivarsson

|||

That would be a nice solutions but its not possible in my case because i need to set security for users with restructions as you also posted below, i have like 30 different roles allowed groups of people to access certain dimensions. It would be much easier if there was an option in the roles to allow or disallow Excel, but i guess there is no such thing ? So basicly i'm stuck ?|||

In SQL Server you have application roles but I have never heard about that in SSAS2005.

One way can be to remove the OLEDB for OLAP 9 from each Excel client if your users are on the same LAN.

I can not see any other solution than the ones outside of SSAS2005.

Regards

Thomas Ivarsson

|||

We're dealing with a similar issue. One approach is to define a users dimension with a many-to-many relation to a security measure group. The linkages between the security measure and the various dimension members would allow you to redefine all your security roles as follows:

Everyone who should have direct access to the cube for Excel, ProClarity etc, would have a role attached to his particular username in the user dimension.

THen the Reporting Services reports would be run under the privileged account. The RS queries would have to specify the username perhaps a hidden parameter you can fill from either a data driven subscription or from an extension to the web browser.

Its' complicated. It's not all that efficient (many-to-many dimensions are not wonderful). It requires either a data driven subscription or a custom web viewer control to fill the parameter. But I think it will work.

|||

Darn so there is no "easy" solution

I suggest Microsoft brings out a servicepack 3 with application roles in it

For MS Development Team - Subscription Issues

Hi,
I set up a standard subscription and am rendering the report to excel or
pdf.
After recieving the email, I open the excel or pdf report and it displays a
bunch of
garbage. SQL 2000 Reporting Services SP1 is installed. Our email system is
Novell GroupWise 6.5. Can you tell me if this is a bug?
One other item. When we include the link in the email, the link itself is
placed on two lines. The top line is a hyperlink and the bottom isn't.
Meaning, you can't click on it. We have to paste the top line in into the
address link and then go back and copy the bottom line and paste it after the
first. Is this a bug or is there a fix to this?
Thank you for your help!Please look to the following for a fix to the corrupted attachment:
http://support.microsoft.com/default.aspx?scid=kb;[LN];872774
As for the hyperlink, are you using Outlook Express? I have seen OE
incorrectly handle the link before.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"clutch" <clutch@.discussions.microsoft.com> wrote in message
news:94A32CAE-BA18-4A6A-BA7E-DA30B90DD95F@.microsoft.com...
> Hi,
> I set up a standard subscription and am rendering the report to excel or
> pdf.
> After recieving the email, I open the excel or pdf report and it displays
> a
> bunch of
> garbage. SQL 2000 Reporting Services SP1 is installed. Our email system is
> Novell GroupWise 6.5. Can you tell me if this is a bug?
> One other item. When we include the link in the email, the link itself is
> placed on two lines. The top line is a hyperlink and the bottom isn't.
> Meaning, you can't click on it. We have to paste the top line in into the
> address link and then go back and copy the bottom line and paste it after
> the
> first. Is this a bug or is there a fix to this?
> Thank you for your help!|||We are using Novell GroupWise 6.5.
"Daniel Reib [MSFT]" wrote:
> Please look to the following for a fix to the corrupted attachment:
> http://support.microsoft.com/default.aspx?scid=kb;[LN];872774
> As for the hyperlink, are you using Outlook Express? I have seen OE
> incorrectly handle the link before.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "clutch" <clutch@.discussions.microsoft.com> wrote in message
> news:94A32CAE-BA18-4A6A-BA7E-DA30B90DD95F@.microsoft.com...
> > Hi,
> >
> > I set up a standard subscription and am rendering the report to excel or
> > pdf.
> > After recieving the email, I open the excel or pdf report and it displays
> > a
> > bunch of
> > garbage. SQL 2000 Reporting Services SP1 is installed. Our email system is
> > Novell GroupWise 6.5. Can you tell me if this is a bug?
> >
> > One other item. When we include the link in the email, the link itself is
> > placed on two lines. The top line is a hyperlink and the bottom isn't.
> > Meaning, you can't click on it. We have to paste the top line in into the
> > address link and then go back and copy the bottom line and paste it after
> > the
> > first. Is this a bug or is there a fix to this?
> >
> > Thank you for your help!
>
>|||Thank you for your response, however, the workarounds are not an option for
our business. Our business requirements are to be able to render in any
available format via email using Reporting Services. Is this a bug and do I
need to contact support?
"Daniel Reib [MSFT]" wrote:
> Please look to the following for a fix to the corrupted attachment:
> http://support.microsoft.com/default.aspx?scid=kb;[LN];872774
> As for the hyperlink, are you using Outlook Express? I have seen OE
> incorrectly handle the link before.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "clutch" <clutch@.discussions.microsoft.com> wrote in message
> news:94A32CAE-BA18-4A6A-BA7E-DA30B90DD95F@.microsoft.com...
> > Hi,
> >
> > I set up a standard subscription and am rendering the report to excel or
> > pdf.
> > After recieving the email, I open the excel or pdf report and it displays
> > a
> > bunch of
> > garbage. SQL 2000 Reporting Services SP1 is installed. Our email system is
> > Novell GroupWise 6.5. Can you tell me if this is a bug?
> >
> > One other item. When we include the link in the email, the link itself is
> > placed on two lines. The top line is a hyperlink and the bottom isn't.
> > Meaning, you can't click on it. We have to paste the top line in into the
> > address link and then go back and copy the bottom line and paste it after
> > the
> > first. Is this a bug or is there a fix to this?
> >
> > Thank you for your help!
>
>|||The link I sent you fixes an encoding issue in the MHTML generated by RS.
It is not specific to Lotus. If you are having issues viewing attachments
this fix is the most likely cause.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"clutch" <clutch@.discussions.microsoft.com> wrote in message
news:EB624D1B-D741-4F40-B869-F6D36E2A47D3@.microsoft.com...
> Thank you for your response, however, the workarounds are not an option
> for
> our business. Our business requirements are to be able to render in any
> available format via email using Reporting Services. Is this a bug and do
> I
> need to contact support?
> "Daniel Reib [MSFT]" wrote:
>> Please look to the following for a fix to the corrupted attachment:
>> http://support.microsoft.com/default.aspx?scid=kb;[LN];872774
>> As for the hyperlink, are you using Outlook Express? I have seen OE
>> incorrectly handle the link before.
>> --
>> -Daniel
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "clutch" <clutch@.discussions.microsoft.com> wrote in message
>> news:94A32CAE-BA18-4A6A-BA7E-DA30B90DD95F@.microsoft.com...
>> > Hi,
>> >
>> > I set up a standard subscription and am rendering the report to excel
>> > or
>> > pdf.
>> > After recieving the email, I open the excel or pdf report and it
>> > displays
>> > a
>> > bunch of
>> > garbage. SQL 2000 Reporting Services SP1 is installed. Our email system
>> > is
>> > Novell GroupWise 6.5. Can you tell me if this is a bug?
>> >
>> > One other item. When we include the link in the email, the link itself
>> > is
>> > placed on two lines. The top line is a hyperlink and the bottom isn't.
>> > Meaning, you can't click on it. We have to paste the top line in into
>> > the
>> > address link and then go back and copy the bottom line and paste it
>> > after
>> > the
>> > first. Is this a bug or is there a fix to this?
>> >
>> > Thank you for your help!
>>|||Thanks Mr. Reib. This did work. However, I was wondering if you had any
other ideas on the hyperlink question I had. When you include the hyperlink
in an subscription, only part of the hyperlink is a hyperlink, the rest of
the address is just text.
Thanks Again!
"Daniel Reib [MSFT]" wrote:
> The link I sent you fixes an encoding issue in the MHTML generated by RS.
> It is not specific to Lotus. If you are having issues viewing attachments
> this fix is the most likely cause.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "clutch" <clutch@.discussions.microsoft.com> wrote in message
> news:EB624D1B-D741-4F40-B869-F6D36E2A47D3@.microsoft.com...
> > Thank you for your response, however, the workarounds are not an option
> > for
> > our business. Our business requirements are to be able to render in any
> > available format via email using Reporting Services. Is this a bug and do
> > I
> > need to contact support?
> >
> > "Daniel Reib [MSFT]" wrote:
> >
> >> Please look to the following for a fix to the corrupted attachment:
> >>
> >> http://support.microsoft.com/default.aspx?scid=kb;[LN];872774
> >>
> >> As for the hyperlink, are you using Outlook Express? I have seen OE
> >> incorrectly handle the link before.
> >>
> >> --
> >> -Daniel
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >> "clutch" <clutch@.discussions.microsoft.com> wrote in message
> >> news:94A32CAE-BA18-4A6A-BA7E-DA30B90DD95F@.microsoft.com...
> >> > Hi,
> >> >
> >> > I set up a standard subscription and am rendering the report to excel
> >> > or
> >> > pdf.
> >> > After recieving the email, I open the excel or pdf report and it
> >> > displays
> >> > a
> >> > bunch of
> >> > garbage. SQL 2000 Reporting Services SP1 is installed. Our email system
> >> > is
> >> > Novell GroupWise 6.5. Can you tell me if this is a bug?
> >> >
> >> > One other item. When we include the link in the email, the link itself
> >> > is
> >> > placed on two lines. The top line is a hyperlink and the bottom isn't.
> >> > Meaning, you can't click on it. We have to paste the top line in into
> >> > the
> >> > address link and then go back and copy the bottom line and paste it
> >> > after
> >> > the
> >> > first. Is this a bug or is there a fix to this?
> >> >
> >> > Thank you for your help!
> >>
> >>
> >>
>
>|||The work-around in the Microsoft tech note (sending just a link to the
report, etc.) does get around the problem in some cases, but
unfortunately it is impossible to control what users select when they
create a subscription.
I want to make sure that people were aware of a third-party product,
Proposion Report Adapter for Microsoft Reporting Services and Lotus
Notes/Domino, that fixes this problem and a lot more. It not only
allows you to deliver reports via NATIVE NOTES MAIL, it also allows you
to use Notes/Domino as data sources for reports and/or allows you to
automatically deposit scheduled reports into Notes databases.) See
http://www.proposion.com/ReportAdapter.

For MS Development Team - SP2

Hello,
I am experiencing an issue with exporting to Excel - on some reports the
last row is missing. Is there a hot fix for this and/or is this addressed in
SP2 for Reporting Services?
TIA.
--
Chris HanleyMost likely:
http://www.ReportingServicesFAQ.com/ow.asp?SP2ForRS%3F
chanley54 wrote:
> Hello,
> I am experiencing an issue with exporting to Excel - on some reports the
> last row is missing. Is there a hot fix for this and/or is this addressed in
> SP2 for Reporting Services?
> TIA.

for loop container to process all Excel files

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

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

ServerName:
@.[User::FileName]

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

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

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

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

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

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

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

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

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

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

Error at LoadStagingTables: There were errors during task validation.

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

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

Thanks very much for your help.

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

No, the June CTP.
Version 9.00.1187.00

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

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

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

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

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

Try

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

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

Catch ex As Exception

Dts.TaskResult = Dts.Results.Failure

Exit Sub

End Try

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

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

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

|||

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

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

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

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

please suggest.
Thanks
Abhishek

|||

Hi,

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

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

plz suggest me how to remove it.

thanks

|||

AkChauhan wrote:

Hi,

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

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

plz suggest me how to remove it.

thanks

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

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

|||

hi Ranjeeta,

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

thanks....

for loop container to process all Excel files

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

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

ServerName:
@.[User::FileName]

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

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

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

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

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

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

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

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

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

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

Error at LoadStagingTables: There were errors during task validation.

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

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

Thanks very much for your help.

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

No, the June CTP.
Version 9.00.1187.00

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

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

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

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

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

Try

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

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

Catch ex As Exception

Dts.TaskResult = Dts.Results.Failure

Exit Sub

End Try

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

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

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

|||

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

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

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

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

please suggest.
Thanks
Abhishek

|||

Hi,

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

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

plz suggest me how to remove it.

thanks

|||

AkChauhan wrote:

Hi,

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

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

plz suggest me how to remove it.

thanks

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

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

|||

hi Ranjeeta,

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

thanks....

for loop container to process all Excel files

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

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

ServerName:
@.[User::FileName]

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

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

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

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

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

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

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

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

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

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

Error at LoadStagingTables: There were errors during task validation.

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

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

Thanks very much for your help.

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

No, the June CTP.
Version 9.00.1187.00

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

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

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

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

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

Try

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

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

Catch ex As Exception

Dts.TaskResult = Dts.Results.Failure

Exit Sub

End Try

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

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

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

|||

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

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

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

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

please suggest.
Thanks
Abhishek

|||

Hi,

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

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

plz suggest me how to remove it.

thanks

|||

AkChauhan wrote:

Hi,

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

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

plz suggest me how to remove it.

thanks

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

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

|||

hi Ranjeeta,

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

thanks....