Looping through several Excel data sources in SSIS

I am attempting to use the foreach loop structure in an SSIS package to
loop through however many Excel files are placed in a directory and
then perform an import operation into a SQL table on each of these
files sequentially. The closest model for this that I was able to find
in the MS tutorial used a flat file source rather than Excel. That
involved adding a new expression to the Connection Manager that set the
connection string to the current filename, as provided by the foreach
component. That works just fine, but when I attempt to apply the same
method to an Excel source, rather than a flat file source, I cannot get
it to work. I see the following error associated with the Excel source
on the Data Flow page: "Validation error. Data Flow Task: Excel Source
[1]: The AcquireConnection method call to the connection manager "Excel
Connection Manager 1" failed with error code 0xC020200." I think that
it's just a matter of getting the right expression, and I thought that
perhaps I should be constructing an expression for ExcelFilePath rather
than the Connection String, but I have fiddled with it for hours and
haven't come up with something that will be accepted. Has anybody out
there been able to do this, or can perhaps refer me to some
documentation that contains an example of what I am trying to do?
Thanks for any help you can give.David,

The April 2006 update of SQL Server 2005 Books Online contains a
new topic titled "How to: Loop through Excel Files and Tables", at
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/a5393c1a-cc37-491a-a260-7aad84dbff68.htm
or online at
http://msdn2.microsoft.com/en-us/library/ms345182.aspx

Why don't you see if that helps, and if not, let us know exactly
what point in that article something goes wrong.

The download page for BOL, for local installation, is
http://www.microsoft.com/downloads/...&DisplayLang=en

Steve Kass
Drew University

davidz wrote:
> I am attempting to use the foreach loop structure in an SSIS package to
> loop through however many Excel files are placed in a directory and
> then perform an import operation into a SQL table on each of these
> files sequentially. The closest model for this that I was able to find
> in the MS tutorial used a flat file source rather than Excel. That
> involved adding a new expression to the Connection Manager that set the
> connection string to the current filename, as provided by the foreach
> component. That works just fine, but when I attempt to apply the same
> method to an Excel source, rather than a flat file source, I cannot get
> it to work. I see the following error associated with the Excel source
> on the Data Flow page: "Validation error. Data Flow Task: Excel Source
> [1]: The AcquireConnection method call to the connection manager "Excel
> Connection Manager 1" failed with error code 0xC020200." I think that
> it's just a matter of getting the right expression, and I thought that
> perhaps I should be constructing an expression for ExcelFilePath rather
> than the Connection String, but I have fiddled with it for hours and
> haven't come up with something that will be accepted. Has anybody out
> there been able to do this, or can perhaps refer me to some
> documentation that contains an example of what I am trying to do?
> Thanks for any help you can give.

LOOKUP component does not have AlwaysUseDefaultCodePage property

Is there any reason for this?
It doesn't make sense to me. After all, we're hitting OLE DB sources with it - if the OLE DB Source component has this property, why not LOOKUP component?

-JamieHi Jamie,

The problem here is that this particular property was added very late to the OLEDB source, furthermore, the lookup component handles its sql statement quite differently from the oledb source so it wasn't trivial to add it (not that it was trivial for the oledb source either its just that the oledb source was written in a way that was considerably more straightforward to add it than for the lookup).

HTH,
Matt|||OK Matt, thanks.

I've raised something via the product feedback centre about this for the future.

-Jamie

Looking to add MySQL as a datasource for reports.

I am upgrading my RS server to a windows 2003 server and want to use the
MySQL.dll for .NET.
I know that I am able to add custom data sources to the report designer but
have yet to figure it out.
Can someone point me in the right direction with some very good
documentation?
Thanks
KevinDocumentation on the config files:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_extend_dataproc_8iqq.asp
This should get you started:
* First, you have to make sure the MySQL data provider is installed
correctly, so that the report designer in VS.NET and report server inside
ASP.NET are able to load the dlls of the data provider at runtime. Note:
there could be file system permissions issues which prevent ASP.NET from
accessing the MySQL data provider dlls. You might want to try to copy the
dlls directly into the
report designer and report server installation directories.
* close all VS.NET sessions before modifying rsReportDesigner.config
* backup the existing rsReportDesigner.config and rsReportServer.config
* read the MySQL documentation to find out the type name for the Connection
object which implements IDbConnection. You will need this information when
changing the config files.
* Add these entries to rsReportDesigner.config in the Data section:
<Extension Name="MySQL" Type="***MySql Connection ClassTypeName***,
***AssemblyName***"/>
... and in the Designer section:
<Extension Name="MySQL"
Type="Microsoft.ReportDesigner.Design.GenericQueryDesigner,Microsoft.Reporti
ngServices.Designer"/>
* Stop the ReportServer Windows service
* Add this entry to rsReportServer.config in the Data section:
<Extension Name="MySQL" Type="***MySql Connection ClassTypeName***,
***AssemblyName***"/>
* Restart ReportServer Windows service
* Reset IIS/ASP.NET by running iisreset from the command line
--
Robert M. Bruckner
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kevin" <k@.r.com> wrote in message
news:OY1LjSz$EHA.2880@.TK2MSFTNGP14.phx.gbl...
> I am upgrading my RS server to a windows 2003 server and want to use the
> MySQL.dll for .NET.
> I know that I am able to add custom data sources to the report designer
but
> have yet to figure it out.
> Can someone point me in the right direction with some very good
> documentation?
> Thanks
> Kevin
>|||Thanks.
It works great.
I am running into a problem with my defined functions. I built a functions
library and have been using it until now so I know it is configured
correctly. I am attempting to convert my reports over but they all use
functions that I have built and I am getting errors returned from the Report
Designer. "Error in SQL Syntax."
Since the MySQL library does work through the report designer. Should the
functions ability also not work as it did with the ODBC driver? I am not
sure of any settings that I might need to check.
Thanks
Kevin
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:%23nvQTnz$EHA.2568@.TK2MSFTNGP11.phx.gbl...
> Documentation on the config files:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_extend_dataproc_8iqq.asp
>
> This should get you started:
> * First, you have to make sure the MySQL data provider is installed
> correctly, so that the report designer in VS.NET and report server inside
> ASP.NET are able to load the dlls of the data provider at runtime. Note:
> there could be file system permissions issues which prevent ASP.NET from
> accessing the MySQL data provider dlls. You might want to try to copy the
> dlls directly into the
> report designer and report server installation directories.
> * close all VS.NET sessions before modifying rsReportDesigner.config
> * backup the existing rsReportDesigner.config and rsReportServer.config
> * read the MySQL documentation to find out the type name for the
> Connection
> object which implements IDbConnection. You will need this information when
> changing the config files.
> * Add these entries to rsReportDesigner.config in the Data section:
> <Extension Name="MySQL" Type="***MySql Connection ClassTypeName***,
> ***AssemblyName***"/>
> ... and in the Designer section:
> <Extension Name="MySQL"
> Type="Microsoft.ReportDesigner.Design.GenericQueryDesigner,Microsoft.Reporti
> ngServices.Designer"/>
> * Stop the ReportServer Windows service
> * Add this entry to rsReportServer.config in the Data section:
> <Extension Name="MySQL" Type="***MySql Connection ClassTypeName***,
> ***AssemblyName***"/>
> * Restart ReportServer Windows service
> * Reset IIS/ASP.NET by running iisreset from the command line
> --
> Robert M. Bruckner
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Kevin" <k@.r.com> wrote in message
> news:OY1LjSz$EHA.2880@.TK2MSFTNGP14.phx.gbl...
>> I am upgrading my RS server to a windows 2003 server and want to use the
>> MySQL.dll for .NET.
>> I know that I am able to add custom data sources to the report designer
> but
>> have yet to figure it out.
>> Can someone point me in the right direction with some very good
>> documentation?
>> Thanks
>> Kevin
>>
>
>

looking into rdl file

Some business analysts would like to reverse engineer the ssrs .rdl files by looking into the field mappings and data sources. Do they have to install the client version of visual studio or there is an easier way?

TIA..

You can open the RDL files in Notepad and see that they are written in XML. You can find the query run by looking for the <CommandText> tag, and each field is listed under a <ReportItems> tag. The data source information is also listed in there, under the <ConnectionProperties> tag.

Hope this helps.

Jarret