ETL from multiple Access databases -SSIS?

Howdy Folks,

I have an ETL project that I want to ask a question about. I want to loop through a folder containing several Access databases, and extract and load each one into a single SQL Server 2005 database. The table layout is the same for all the Access dbs, as well as the SQL db. The number of Access dbs in the folder, their records, and their names will change. Is there a way i can use the SSIS Foreach tool to move through the folder and set the connection string inside the loop?

Thanks for your reply,

Chris

Yes, you can do this.

Use the ForEach File Enumerator to enumerate your list of files. you can use wildcards (e.g. *.mdb)

Store the file path of each file in a variable. You can then use the contents of that variable to dynamically build your connection string using an expression.

Lots of information via Google about this stuff if you want to go hunting or ask here with any pertinent questions.

Regards

-Jamie

|||

Thanks Jamie (alot ) for your suggestion. I tried using the Foreach File Enumerator with the root path for the Access files and a *.mdb wildcard. Then I created a mapped string variable varFileNameto hold the file path. Inside the loop i have my Data Flow Task. In the data source for the task, I was using the variable in an expression as the ConnectionSting property which didn't work. So I added an OLE DB connection string in the expression like "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @.[User::varFileName] but i don't think that's the answer.

I've been googling on this but can't find solutions for multiple .mdb's, most of waht i find is dealing with flat files. Great blogs btw.

|||

Should be able to find all you need here: http://www.connectionstrings.com/

-Jamie

|||

Yep, i went there. Thanks Jamie. My string was ok. Actually what got me running was to set the Foreach Loop Container>Properties>Execution>DelayValidation = True. Works great running the SSIS package on the box with SQL server (as opposed to OLE DB) destinations.

Escaping [characters with text lengths over 4000 characters

In a previous post I mentioned you will get into problems when trying to
update DataSets containing rows with [ characters in ntext or text field
columns. Andrew Conrad (thanks) mentioned that you should escape these, like
so [[]. This will help you, except when the length of the value is over 4000
characters (for ntext) in length. After that the problem of zero affected
rows arises again, despite escaping. How come? How to solve?
Thanks.
AlexThe REPLACE fuction (and other SQL Server string functions) will not operate
on data larger than 8000 bytes -- for an NVARCHAR datatype, that means 4000
characters (2 bytes per character). Can you perform the replace on the
client, before passing the data to SQL Server?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Alex Thissen" <athissen A T killer-apps.nl> wrote in message
news:uNIrcZPFFHA.3284@.TK2MSFTNGP09.phx.gbl...
> In a previous post I mentioned you will get into problems when trying to
> update DataSets containing rows with [ characters in ntext or text field
> columns. Andrew Conrad (thanks) mentioned that you should escape these,
like
> so [[]. This will help you, except when the length of the value is over
4000
> characters (for ntext) in length. After that the problem of zero affected
> rows arises again, despite escaping. How come? How to solve?
> Thanks.
> Alex
>|||Hi Adam,
Thanks for thinking with me on this one. I don't use the REPLACE functions.
Instead, I make sure that the OriginalVersion of my DataRow in the DataSet
has the replaced value. Then the SqlXmlAdapter builds the UPDATE statement
for me, but it uses an optimistic locking scheme by comparing all columns
with the original values.For the (n)text fields the LIKE operator is used,
but as I mentioned this one breaks with values over 8000 bytes and [ chars
in it. I also wrote a (teasing) weblog entry on it, that you can find here:
http://www.alexthissen.nl/weblog/Pe...br />
8c42070.
You might want to read up on it, since this problem is hardly related to
SQLXML. It could be circumvented if there was a possibility to influence the
SQL that is generated. An option to exclude fields from the optimistic
locking would solve it directly.
So, my question remains, given that the LIKE operator breaks for lengths
over 8000 bytes WITH escaped [ characters in it ( as [[] ) in it, how do I
get my DataSets that contain such values to get updated through SQLXML?
Thanks, Alex
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uvUE$$VFFHA.1348@.TK2MSFTNGP14.phx.gbl...
> The REPLACE fuction (and other SQL Server string functions) will not
> operate
> on data larger than 8000 bytes -- for an NVARCHAR datatype, that means
> 4000
> characters (2 bytes per character). Can you perform the replace on the
> client, before passing the data to SQL Server?
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Alex Thissen" <athissen A T killer-apps.nl> wrote in message
> news:uNIrcZPFFHA.3284@.TK2MSFTNGP09.phx.gbl...
> like
> 4000
>sql

Escaping [ characters with text lengths over 4000 characters

In a previous post I mentioned you will get into problems when trying to
update DataSets containing rows with [ characters in ntext or text field
columns. Andrew Conrad (thanks) mentioned that you should escape these, like
so [[]. This will help you, except when the length of the value is over 4000
characters (for ntext) in length. After that the problem of zero affected
rows arises again, despite escaping. How come? How to solve?
Thanks.
Alex
The REPLACE fuction (and other SQL Server string functions) will not operate
on data larger than 8000 bytes -- for an NVARCHAR datatype, that means 4000
characters (2 bytes per character). Can you perform the replace on the
client, before passing the data to SQL Server?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Alex Thissen" <athissen A T killer-apps.nl> wrote in message
news:uNIrcZPFFHA.3284@.TK2MSFTNGP09.phx.gbl...
> In a previous post I mentioned you will get into problems when trying to
> update DataSets containing rows with [ characters in ntext or text field
> columns. Andrew Conrad (thanks) mentioned that you should escape these,
like
> so [[]. This will help you, except when the length of the value is over
4000
> characters (for ntext) in length. After that the problem of zero affected
> rows arises again, despite escaping. How come? How to solve?
> Thanks.
> Alex
>
|||Hi Adam,
Thanks for thinking with me on this one. I don't use the REPLACE functions.
Instead, I make sure that the OriginalVersion of my DataRow in the DataSet
has the replaced value. Then the SqlXmlAdapter builds the UPDATE statement
for me, but it uses an optimistic locking scheme by comparing all columns
with the original values.For the (n)text fields the LIKE operator is used,
but as I mentioned this one breaks with values over 8000 bytes and [ chars
in it. I also wrote a (teasing) weblog entry on it, that you can find here:
http://www.alexthissen.nl/weblog/Per...-8feda8c42070.
You might want to read up on it, since this problem is hardly related to
SQLXML. It could be circumvented if there was a possibility to influence the
SQL that is generated. An option to exclude fields from the optimistic
locking would solve it directly.
So, my question remains, given that the LIKE operator breaks for lengths
over 8000 bytes WITH escaped [ characters in it ( as [[] ) in it, how do I
get my DataSets that contain such values to get updated through SQLXML?
Thanks, Alex
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uvUE$$VFFHA.1348@.TK2MSFTNGP14.phx.gbl...
> The REPLACE fuction (and other SQL Server string functions) will not
> operate
> on data larger than 8000 bytes -- for an NVARCHAR datatype, that means
> 4000
> characters (2 bytes per character). Can you perform the replace on the
> client, before passing the data to SQL Server?
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Alex Thissen" <athissen A T killer-apps.nl> wrote in message
> news:uNIrcZPFFHA.3284@.TK2MSFTNGP09.phx.gbl...
> like
> 4000
>

Error: Subreport could not be shown.

I have a problem with a report containing a number of subreports not running successfully when deployed to the Report Server.

The report runs successfully every time in the VS Development environment, so parameters are all setup correctly and the result is as expected accross the whole report.

No errors or warnings are listed when building or deploying.

All subreports are in the same project as the main report and deployed to the same folder in Report Manager. All the individual subreports run successfully both in VS Studio (Preview Tab) and in the Report Manager portal.

About 50% of the time, the main report runs but shows the error "Subreport could not be shown" for some or all of the subreports.

Running SQL 2005 SP1

Please advise.

Are any of the reports/subreports set to execute on a schedule or are cached?

and do they all have credentials stored?

|||

Thanks for the reply.

None of the reports are cached.|||

Hi,

Can you be experiencing a parameter passing problem? Dates are always a little bit problemetic.

For one of my reports which I use date parameters and the date picker control of the SSRS 2005, the case is just the opposite. It fails on the VS2005 IDE, but runs successfully on the deployed Report Server.

Eralper

http://www.kodyaz.com

|||

Hi,

I don't think this is the issue, because all the subreports are driven by the same 3 parameters from the parent report, 2 of which are using the date picker control and it always works fine in VS. All of the subreports when run individually using the same parameters work fine.

Also, when it returns an error, which is only around half the time, generally the first 1 or 2 of 6 subreports is rendered correctly with the rest showing the error. I have reconciled the data results which are filtered by the parameters (Start & End dates) and the parameters are being correctly applied.

FYI, each subreport takes around 5 minutes to run individually, with the parent report averaging around 15-25 mins.

As a test, I deleted all the reports from the portal, rebuilt the solution and redeployed and everything was working as expected for several runs of the report including a snapshot via a linked report. Unfortunately, the problem has now re-occurred for no apparent reason.

I am begging to suspect resource related / timeout issues or some deloyment related issues but don't really understand why the report would be reliable in VS and not in the portal.

Any help appreciated.

PS: Re your experience, if you haven't already done so, refresh the datasets in VS then try previewing the report.

Error: Subreport could not be shown

I have an infuriating problem which I can't seem to overcome with my first
report containing a sub-report. 1 simple parameter a GUID string is passed
from the parent report to the subreport and all I seem to get in return is
the message 'Error: Subreport could not be shown' with no indication as to
what the problem is or might be. If i view the subreport on its own and
manually enter the parameter the sub report displays fine and as expected
but when the parameter is passed via the parent form it won't play ball.
Thanks in advance for any help - u will be doing me a big favour if you can
help me overcome this.
SimonProbably a type-problem. Are you sure you "send" a string and that the
receiving parameter is also a string?
"Simon Dingley" wrote:
> I have an infuriating problem which I can't seem to overcome with my first
> report containing a sub-report. 1 simple parameter a GUID string is passed
> from the parent report to the subreport and all I seem to get in return is
> the message 'Error: Subreport could not be shown' with no indication as to
> what the problem is or might be. If i view the subreport on its own and
> manually enter the parameter the sub report displays fine and as expected
> but when the parameter is passed via the parent form it won't play ball.
> Thanks in advance for any help - u will be doing me a big favour if you can
> help me overcome this.
> Simon
>
>|||Hi and thanks for the reply,
The parameter passed is a 'field' e.g. =Fields!qst_id.Value which is
actually a GUID from the database. As far as I can see there is no way for
me to specify in the sub-report properties what data type the parameter is.
However, in the sub report itself the parameter data type is defined as
being a string ?
Does this shed any light on things ?
Thanks, Simon
"Antoon" <Antoon@.discussions.microsoft.com> wrote in message
news:4CD7BC66-C0E9-4E79-957E-0C8040C1EA4C@.microsoft.com...
> Probably a type-problem. Are you sure you "send" a string and that the
> receiving parameter is also a string?
> "Simon Dingley" wrote:
> > I have an infuriating problem which I can't seem to overcome with my
first
> > report containing a sub-report. 1 simple parameter a GUID string is
passed
> > from the parent report to the subreport and all I seem to get in return
is
> > the message 'Error: Subreport could not be shown' with no indication as
to
> > what the problem is or might be. If i view the subreport on its own and
> > manually enter the parameter the sub report displays fine and as
expected
> > but when the parameter is passed via the parent form it won't play ball.
> >
> > Thanks in advance for any help - u will be doing me a big favour if you
can
> > help me overcome this.
> >
> > Simon
> >
> >
> >|||Try passing Fields!qst_id.Value.ToString() to the subreport
(I do presume the subreport is within a matrix, tabele, list,... and not "on
its own". In the last case you should use an agregate function fe
first(Fields!qst_id.Value).ToString() )
"Simon Dingley" wrote:
> Hi and thanks for the reply,
> The parameter passed is a 'field' e.g. =Fields!qst_id.Value which is
> actually a GUID from the database. As far as I can see there is no way for
> me to specify in the sub-report properties what data type the parameter is.
> However, in the sub report itself the parameter data type is defined as
> being a string ?
> Does this shed any light on things ?
> Thanks, Simon
> "Antoon" <Antoon@.discussions.microsoft.com> wrote in message
> news:4CD7BC66-C0E9-4E79-957E-0C8040C1EA4C@.microsoft.com...
> > Probably a type-problem. Are you sure you "send" a string and that the
> > receiving parameter is also a string?
> >
> > "Simon Dingley" wrote:
> >
> > > I have an infuriating problem which I can't seem to overcome with my
> first
> > > report containing a sub-report. 1 simple parameter a GUID string is
> passed
> > > from the parent report to the subreport and all I seem to get in return
> is
> > > the message 'Error: Subreport could not be shown' with no indication as
> to
> > > what the problem is or might be. If i view the subreport on its own and
> > > manually enter the parameter the sub report displays fine and as
> expected
> > > but when the parameter is passed via the parent form it won't play ball.
> > >
> > > Thanks in advance for any help - u will be doing me a big favour if you
> can
> > > help me overcome this.
> > >
> > > Simon
> > >
> > >
> > >
>
>|||Antoon - you're a star. That worked a treat.
Many thanks for your help.
Simon
"Antoon" <Antoon@.discussions.microsoft.com> wrote in message
news:283D9BE2-1B26-4066-A3AE-A477868DEB96@.microsoft.com...
> Try passing Fields!qst_id.Value.ToString() to the subreport
> (I do presume the subreport is within a matrix, tabele, list,... and not
"on
> its own". In the last case you should use an agregate function fe
> first(Fields!qst_id.Value).ToString() )
>|||One more quick question if I can. This works great for me when developing
and previewing in Visual Studio .Net but when I deploy the report to the
reporting server I get the subreport could be shown error again ? Do you
know why this is ?
Simon
"Antoon" <Antoon@.discussions.microsoft.com> wrote in message
news:283D9BE2-1B26-4066-A3AE-A477868DEB96@.microsoft.com...
> Try passing Fields!qst_id.Value.ToString() to the subreport
> (I do presume the subreport is within a matrix, tabele, list,... and not
"on
> its own". In the last case you should use an agregate function fe
> first(Fields!qst_id.Value).ToString() )|||Not too worry - after viewing the subreport as a standalone report i
discovered there was a problem connecting through the shared datasource.
Thanks again.
Simon
>One more quick question if I can. This works great for me when developing
>and previewing in Visual Studio .Net but when I deploy the report to the
>reporting server I get the subreport could be shown error again ? Do you
>know why this is ?
>Simon