Dynamic Data

Thanks for reading this post.
We have an application that can import data from various sources
containined in various formats. This data is to be stored in one table!
How do we handle this scenario? The only way I could think of is to
have a generic table with columns called columnName, stringValue and
numericValue and store every field in each row of the input data as a
row in the generic table.
Is this good design or is there is a better to handle the problem
please.
Thanks...Hi,John
When you say > We have an application that can import data from various
sources
> containined in various formats? do you mean that you get the data from
> TEXT files ,Access Database ,Excel documents?
"John Smith" <postmaster@.sumanthcp.plus.com> wrote in message
news:1138098782.505092.292110@.g44g2000cwa.googlegroups.com...
> Thanks for reading this post.
> We have an application that can import data from various sources
> containined in various formats. This data is to be stored in one table!
> How do we handle this scenario? The only way I could think of is to
> have a generic table with columns called columnName, stringValue and
> numericValue and store every field in each row of the input data as a
> row in the generic table.
> Is this good design or is there is a better to handle the problem
> please.
>
> Thanks...
>|||Hi Uri,
Yes, we get data in different physical formats but the data we get can
have diferent schema/columns. We import everything into SQLSERVER and
use the imported data for reporting. If we recoginse some columns then
they will be used to produce some hash totals. I hope I have given you
the picture.
Thanks|||John Smith wrote:
> Thanks for reading this post.
> We have an application that can import data from various sources
> containined in various formats. This data is to be stored in one table!
> How do we handle this scenario? The only way I could think of is to
> have a generic table with columns called columnName, stringValue and
> numericValue and store every field in each row of the input data as a
> row in the generic table.
> Is this good design or is there is a better to handle the problem
> please.
>
> Thanks...
The external format shouldn't determine your database design. Design
the correct logical data model first. Then worry about how to get the
data into it.
Since you have given us zero information about the data you are
modelling I can't help you with the design. Anyway, newsgroups are not
the place to solve design problems. Good design requires more knowledge
about your business and requirements than we can reasonably expect to
learn through an online discussion.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||John Smith (postmaster@.sumanthcp.plus.com) writes:
> Thanks for reading this post.
> We have an application that can import data from various sources
> containined in various formats. This data is to be stored in one table!
> How do we handle this scenario? The only way I could think of is to
> have a generic table with columns called columnName, stringValue and
> numericValue and store every field in each row of the input data as a
> row in the generic table.
> Is this good design or is there is a better to handle the problem
> please.
Whether this a good design or not depends on what you will use the data
for. Without further knowledge, I would not put much faith in this
solution though. And you did mention reporting - that reporting will
not be any fun.
Probably you need to conduct further analysis of the data import, so you
can define more tables.
As for stringValue, numericValue etc, you may want to investigate the
type sql_variant, that can host any other datatype in SQL Server, save
text, ntext and image.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||The real question is - what are you doing with this data. When you have the
answer to that, you'll have the fundamentals of your logical model. Which
still has little to do with actually creating tables.
So, please tell us what this data is for (on your end) and we can guide you
to a solution. I can't imagine anyone just coming up with a solution based o
n
your post.
ML
http://milambda.blogspot.com/|||Look up various ETL tools for this kind of problem. They will do the
data scrubbing and format conversions. I would take a look at
Sunopsis.
numericValue and store every field [sic] in each row of the input data
as a row in the generic table. <<
There is no such animal as a "generic table" in a properly designed
RDBMS. Tables model one and only kind of entity and all the attributes
are clearly defined. To be is to be something in particular; to be
nothing in particular or everything in general is to be nothing at all
-- Aristotle.
The design flaw you have re-discovered is called EAV and you can
Google it.|||Thanks to all for your answers,
In fact we are trying to develop a tool which is capable of ELT and
that's why we need a central table to hold input data temporarily
before transforming it to create files/other databases.
The full picture is as follows: Our application will recieve
files/databases from external sources. The data we receive will have
some known attributes like for example - Quantity, Name, Cost etc. but
there may be more information (very likely) and we will not know what
those extra columns would be. We are planning to create two tables -
Core and Extra. Core will contain all the columns we know before hand
and will be used to run queries/reports (like checking hash values etc)
and Extra table which will be a EAV table used only during tranforming
data to other formats (formats are dynamic - users define them).
Is there an alternative elegant way of solving our problem or do we
have to live with the EAV flaw?|||John Smith wrote:
> Thanks to all for your answers,
> In fact we are trying to develop a tool which is capable of ELT and
> that's why we need a central table to hold input data temporarily
> before transforming it to create files/other databases.
> The full picture is as follows: Our application will recieve
> files/databases from external sources. The data we receive will have
> some known attributes like for example - Quantity, Name, Cost etc. but
> there may be more information (very likely) and we will not know what
> those extra columns would be. We are planning to create two tables -
> Core and Extra. Core will contain all the columns we know before hand
> and will be used to run queries/reports (like checking hash values etc)
> and Extra table which will be a EAV table used only during tranforming
> data to other formats (formats are dynamic - users define them).
> Is there an alternative elegant way of solving our problem or do we
> have to live with the EAV flaw?
I would echo Joe's suggestion that you consider off-the-shelf data
integration tools. Yours is precisely the type of application where
those packages have benefits over and above hand-coding your own
transformations. Specifically, you have changing metadata and so your
transformations won't be static. Therefore having a separate metadata
repository can work to your advantage because the tool will usually
take away some of the pain of generating the changing transformations.
As regards EAV I'd say that it won't sove your problem. There is no
substitute for developing properly normalized schema and EAV isn't
flexible enough to represent all non-relational data sources. How will
you model a XML hierarchy with EAV for example? Again, integration
tools can help because they will automate or semi-automate the process
of deriving a relational schema from the source. Also, bear in mind
that many of those tools expose their own API sothey are potential
still extensible with your own code.
Microsoft SQL Server Integration Services (SSIS)
www.microsoft.com/sql/technologies/...on/default.mspx
www.sqlis.com
Microsoft Data Transformation Services (DTS - the predecessor of SSIS)
www.sqldts.com
Other integration software
www-306.ibm.com/software/data/integration/dis/
www.abinitio.com
www.datamirror.com
www.datawatch.com
www.embarcadero.com/products/dtstudio/index.html
www.informatica.com
www.pervasive.com/solutions/
www.microsoft.com/biztalk/default.mspx
Enterprise Integration Patterns
www.enterpriseintegrationpatterns.com
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

dynamic connection string

hi all,

i've been reading through the forum on this subject but couldn't find the solution. i'm using RS2005.

it's mentioned that dynamic connection string is not applicable for shared data source. is there a workaround for this?

thanks!

Dynamic connection strings cannot be used with shared data sources.

Other suggestions can be found in this related thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=16395&SiteID=1

-- Robert

|||

Thanks Robert for your reply.

however, do methods for RS2000 apply for RS2005? i've never used RS2000 before and i'm not sure where to start either.

Thanks!

|||

Yes, everything mentioned for RS 2000 works for RS 2005 the same way.
RS 2005 offers expression-based connection strings in addition.

-- Robert

|||

I wrote an application using VS2005 that has a generic report viewer and rdlc files stored in a database with the stored procedures necessary to run each report.

Then I took the report, replaced the dataset stored in the rdlc file programmatically with the generic dataset which accepts the result of the stored procedure and use it to populate the report.

It wasn't that straight forward, but now it works dynamically and I have an application now which I can add a new record to my database storing my rdlc files and stored procedure names and the list of reports that drop down in my application is completely dynamic, including parameters required appearing in a datagrid with controls for calendars and drop-down lists.

Just an idea of how you might get that dynamic dataset.

Dymamic connection strings in SSIS...

I was reading through the following post regarding dynamic connection strings:

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=812814&SiteID=17

In this posting they talk about setting up a configuration file to setup the connection string, but I am not sure how to apply that connection string to my connection manager. When I go into my configuration file, I can see the connection string, and when I go into the advanced properties of the connection manager, under the "Named ConnectionString\FileName" property, I set the property value to the name of my configuration file, but when I test the connection I get an error that the connection string property has not been initialized.

Can someone please assist me with identifying what I am doing incorrectly?

Thanks in advance for the assistance!

Craig

Hi Craig,

It sounds like you might be missing a step. In the post you linked, they are setting a variable through a configuration file, and then using that variable in an expression to set the connection's ConnectionString. Did you setup the expression properly?

Jaime's blog has an entry on how to set expressions.

Hope that helps!

~Matt

|||

Hi Matt,

Thanks for the reply. I checked out the link that you sent me. It looks like he is setting the data source connection string for a flat file dynamically... When I go into the Connection Manager for the Ole Db, there is no expression property in which to set up. There is, however, a File Name property under the "Named ConnectionString" group, which I put in the value of <PATH>\SSIS_Config.dtsConfig.

I am trying to set the connection string for the destination SQL table dynamically (we basically have 3 environments - Development, Staging, and Production). When the packages are pushed to the staging environment, we want to be able to change the connection string in the configutation file and have all packages use the configured connection string.

Please let me know if you have any further questions.

Thanks!

Craig

|||

It's a little confusing, but the expressions don't show up in the connection manager editor window you get when you double click on the connection name - you have to view the connection in the "Properties" window. Right click on you connection and select Properties, or select the connection when the properties window is in view.

I'm not too familiar with the "Named ConnectionString" property group, but I think you can accomplish what you're trying to do by setting expressions for the InitialCatalog property (and maybe ServerName if your host changes based on the staging environment).

Post if you have trouble finding it, or it's not working for you.. I can try to post some screen shots.

~Matt

|||

Matt,

Thanks again for the reply! I did find the expressions for the Ole Db connection manager (just as you said, not by double-clicking, but by clicking on the connection manager, and expanding the properties window). :-)

So, here's my issue... I created a global variable as a string data type, and set it's value to the path of the config file. I then opened the expressions window, and selected the ConnectionString property, and set it's value to @.[User::Config_ConnectionString]. When I evaluate the expression, i get the path of the config file.

Am I missing another step? If you don't mind putting some screen shots together, you can email me directly at craigster1976@.msn.con, so you don't have to worry about trying to post them here. I think i'm pretty close - just missing one or two steps.

Thanks for all of your help, and your speedy responses!

Craig

|||

Hi Craig,

The variable should be the actual value, not the path to the configuration file.

You should take a look at this article - Easy Package Configuration. It appears they are doing what you're trying to accomplish without using expressions. Expressions are more useful when you're building up connection strings programmatically. If you're setting the full server / initial catalog values in your configuration, you might want to take this approach instead.

~Matt

|||

Good morning from snowy Colorado!

I am kind of confused... :-( I have set up my configuration file, which contains my connection string (the link in your last posting gave instructions on how to set up the confguration file, and that worked fine). Now, I need to retrieve the value from the config file, and set the connection string for my connection mamager, and I am not sure how to accomplish this... I have tried several variations... I tried setting up an environment variable, pointing it to my Xml config file, but I cannot find any documentation on how to pull the value out, and use that value in my package. I have seen a lot of postings where the config file is used to programmatically pull the value from the config file, then set a variable in the package through code, but we would rather not do that... All of our packages are going to be running through scheduled jobs, and we don't want to write a program that kicks off the package. However, rather than manually changing the connection string every time we move from Dev to Prod, we want this value to come from the config file - we would just change that config file value for the new environment that we will be running in. I have read through alomost all of Jamie's blog's regarding SSIS, and haven't found one that addresses what we are trying to accomplish. It seems that there is a plethera of information on setting up the config file, but not pulling values from it! Anyway... If you can provide any further insight, I would be most appreciative.

Thanks, agian, for the help and the time!

Craig

|||

If you set up a configuration file then you don't have to do anything in your package other than tell your package to use it (which you do via the SSIS menu in BIDS).

You do not have to do anything explicit in your package to make a value from the referenced configuration file appear for a particular property (i.e. "pulling from it" as you term it). If you have a configuration set up in that configuration file for a particular property then it just happens as a matter of course.

-Jamie

|||

Jamie...

Well, now that I feel like a complete moron! To test, what I did was set up the configuration file to point to our development DB server. I then setup the connection manager to point to my localhost. When I exected the package (just as you said) "it just happened as a matter of course"... All the records were pumped into the development DB, just as they were supposed to.

On a side note, I have really enjoyed your blogs, and have learned a ton from them, so thanks for putting that information into your blogs for us to learn from! Just as a suggestion, maybe put together a blog regarding the use of the configuration files so people like me don't spin our wheels for a day trying to figure something out so remedial!

Thanks, again! Have a nice day!

Craig

|||

Craigster wrote:

Jamie...

Well, now that I feel like a complete moron! To test, what I did was set up the configuration file to point to our development DB server. I then setup the connection manager to point to my localhost. When I exected the package (just as you said) "it just happened as a matter of course"... All the records were pumped into the development DB, just as they were supposed to.

OK cool. Good to know. Glad you got it working.

Craigster wrote:

On a side note, I have really enjoyed your blogs, and have learned a ton from them, so thanks for putting that information into your blogs for us to learn from!

My pleasure.. Its good to know that they're appreciated, believe me.

Craigster wrote:

Just as a suggestion, maybe put together a blog regarding the use of the configuration files so people like me don't spin our wheels for a day trying to figure something out so remedial!

All suggestions readily accepted. I'll put it on my "things to do" list.

cheers

-Jamie