Multi Table Source

I am wondering how I can create an OLE DB Source component that can store a multi-table DataSet object. Is this something that is possible or do I need some custom object to do this? I'm sure I can create a multi-table destination object and create sources for each data table needed however, I need to get the data for 5 tables and do this about 30K times. I'm thinking this approach will perform better.

Here is what I've been trying to get working. (Note there is only one parameter that all the queries use - @.keyName)

SELECT * FROM Table1
WHERE (Key = ?)

SELECT * FROM Table2
WHERE (Key = ?)

SELECT * FROM Table3
WHERE (Key = ?)

SELECT * FROM Table4
WHERE (Key = ?)

SELECT * FROM Table5
WHERE (Key = ?)

TIA

Ian

You can have more than one OLE DB source on a given data flow. From there you can merge/union records as required.|||

Does that mean I should use a separate source for each table then merge them into one DataSet Destination? (Sorry, I'm new to SSIS)

A single procedure/statement returning multiple tables sounds more efficient, is this not possible?

|||

enizin wrote:

Does that mean I should use a separate source for each table then merge them into one DataSet Destination? (Sorry, I'm new to SSIS)

A single procedure/statement returning multiple tables sounds more efficient, is this not possible?

A SQL statement doesn't return a table. It returns a result set. Either write a SQL statement that selects from all of your tables and does the necessary joins or unions and then use that statement in an OLE DB source, or you can use an OLE DB source for each table -- which will have to be merged together to get one "result set."|||

Sorry, I'm used to referring to data tables within ADO.NET DataSets...

In the Management Studio, if I run this set of statements against the AdventureWorks database I can get a "dataset" containing each result set - all of which have different columns.

SELECT * FROM HumanResources.Employee WHERE EmployeeId = ?

SELECT * FROM HumanResources.EmployeeAddress WHERE EmployeeId = ?

SELECT * FROM HumanResources.EmployeeDepartmentHistory WHERE EmployeeId = ?

SELECT * FROM HumanResources.EmployeePayHistory WHERE EmployeeId = ?

It sounds like this wouldn't work in SSIS because one source cannot contain multiple result sets without performing a union as it can only contain one set of columns.

The reason for needing the data like this is I need to add/update/delete rows to/from each of these tables then save them to my destination database. For my purposes it sounds like using the multiple source option will be the best route.

Thanks for your help.

Multi Language

Can anyone give insight on the proper way to store names in a multi
language database? For instance in English we store First, Middle,
Last. In Spanish there could be multiple last names. In Chinese the
whole name is only one name, (fits in one field). Any experience with
something like this?
Peter Cwik
It really depends on the requirements of the application, and whether you
are attempting to separate family name from given name, or preserve name
order for presentation. (In different cultures, the first name is the
family name, whereas in English, the first name is the given name.)
You may have different rules for different localizations, in defining what
is FirstName and LastName, but also have a common FullName field that is
populated in a language-specific or culture-specific manner.
Again, your business requirements will have much to do with the solution.
Cheers,
'(' Jeff A. Stucker
\
Senior Consultant
www.rapidigm.com
"Peter Cwik" <cwik4@.cox.net> wrote in message
news:1135790334.343599.101660@.z14g2000cwz.googlegr oups.com...
> Can anyone give insight on the proper way to store names in a multi
> language database? For instance in English we store First, Middle,
> Last. In Spanish there could be multiple last names. In Chinese the
> whole name is only one name, (fits in one field). Any experience with
> something like this?
> Peter Cwik
>
|||In general, "Family Name" and "Given Name" are less confusing than
"First", "Last", "Christian", "Sur", etc on multi-language forms. A
simple way to organize the columns might be something like this:
NameID(pk), FamilyName, GivenName, Name2, Name3,Name4, NameType --
where FamilyName is the only required name field and NameType is a
foreign key describing the naming convention of that particular person.
So long as the order preference is spelled out in the NameType, I don't
see a reason to distinguish between a middle name, second given name,
or second family name. You can use CASE to distinguish between them in
your select statements:
SELECT CASE NameType
WHEN '3a' THEN GivenName +' '+ Name4 +' '+ FamilyName
WHEN '4b' THEN Name2 +' '+ GivenName +' '+ FamilyName
WHEN '5c' THEN FamilyName +' '+ GivenName
ELSE GivenName+' '+FamilyName END AS FullName
>From Table1
(If you want, you could nest the CASEs so that they check fields for
null values before plugging them in so as to allow people to predict
naming conventions that might apply after marriage, confirmation, etc.)
--L

Multi Language

Can anyone give insight on the proper way to store names in a multi
language database? For instance in English we store First, Middle,
Last. In Spanish there could be multiple last names. In Chinese the
whole name is only one name, (fits in one field). Any experience with
something like this?
Peter CwikIt really depends on the requirements of the application, and whether you
are attempting to separate family name from given name, or preserve name
order for presentation. (In different cultures, the first name is the
family name, whereas in English, the first name is the given name.)
You may have different rules for different localizations, in defining what
is FirstName and LastName, but also have a common FullName field that is
populated in a language-specific or culture-specific manner.
Again, your business requirements will have much to do with the solution.
--
Cheers,
'(' Jeff A. Stucker
\
Senior Consultant
www.rapidigm.com
"Peter Cwik" <cwik4@.cox.net> wrote in message
news:1135790334.343599.101660@.z14g2000cwz.googlegroups.com...
> Can anyone give insight on the proper way to store names in a multi
> language database? For instance in English we store First, Middle,
> Last. In Spanish there could be multiple last names. In Chinese the
> whole name is only one name, (fits in one field). Any experience with
> something like this?
> Peter Cwik
>|||In general, "Family Name" and "Given Name" are less confusing than
"First", "Last", "Christian", "Sur", etc on multi-language forms. A
simple way to organize the columns might be something like this:
NameID(pk), FamilyName, GivenName, Name2, Name3,Name4, NameType --
where FamilyName is the only required name field and NameType is a
foreign key describing the naming convention of that particular person.
So long as the order preference is spelled out in the NameType, I don't
see a reason to distinguish between a middle name, second given name,
or second family name. You can use CASE to distinguish between them in
your select statements:
SELECT CASE NameType
WHEN '3a' THEN GivenName +' '+ Name4 +' '+ FamilyName
WHEN '4b' THEN Name2 +' '+ GivenName +' '+ FamilyName
WHEN '5c' THEN FamilyName +' '+ GivenName
ELSE GivenName+' '+FamilyName END AS FullName
>From Table1
(If you want, you could nest the CASEs so that they check fields for
null values before plugging them in so as to allow people to predict
naming conventions that might apply after marriage, confirmation, etc.)
--L

Multi Language

Can anyone give insight on the proper way to store names in a multi
language database? For instance in English we store First, Middle,
Last. In Spanish there could be multiple last names. In Chinese the
whole name is only one name, (fits in one field). Any experience with
something like this?
Peter CwikIt really depends on the requirements of the application, and whether you
are attempting to separate family name from given name, or preserve name
order for presentation. (In different cultures, the first name is the
family name, whereas in English, the first name is the given name.)
You may have different rules for different localizations, in defining what
is FirstName and LastName, but also have a common FullName field that is
populated in a language-specific or culture-specific manner.
Again, your business requirements will have much to do with the solution.
--
Cheers,
'(' Jeff A. Stucker
\
Senior Consultant
www.rapidigm.com
"Peter Cwik" <cwik4@.cox.net> wrote in message
news:1135790334.343599.101660@.z14g2000cwz.googlegroups.com...
> Can anyone give insight on the proper way to store names in a multi
> language database? For instance in English we store First, Middle,
> Last. In Spanish there could be multiple last names. In Chinese the
> whole name is only one name, (fits in one field). Any experience with
> something like this?
> Peter Cwik
>|||In general, "Family Name" and "Given Name" are less confusing than
"First", "Last", "Christian", "Sur", etc on multi-language forms. A
simple way to organize the columns might be something like this:
NameID(pk), FamilyName, GivenName, Name2, Name3,Name4, NameType --
where FamilyName is the only required name field and NameType is a
foreign key describing the naming convention of that particular person.
So long as the order preference is spelled out in the NameType, I don't
see a reason to distinguish between a middle name, second given name,
or second family name. You can use CASE to distinguish between them in
your select statements:
SELECT CASE NameType
WHEN '3a' THEN GivenName +' '+ Name4 +' '+ FamilyName
WHEN '4b' THEN Name2 +' '+ GivenName +' '+ FamilyName
WHEN '5c' THEN FamilyName +' '+ GivenName
ELSE GivenName+' '+FamilyName END AS FullName
>From Table1
(If you want, you could nest the CASEs so that they check fields for
null values before plugging them in so as to allow people to predict
naming conventions that might apply after marriage, confirmation, etc.)
--L

MSSQL Questions - Images and Frontpage integration

I'm currently a computer networking student, and a semester long project this year requires us to build an online store using IIS6 and MSSQL. I have run into a dead end in this project and cannot find information or documentation that answers my questions.

So as follows.

How do I store an image in the MSSQL database, preferably through a frontpage-created form with upload boxes?


I am working with forms that I create in Frontpage (a requirement of the course) to add entries to the database. Working with just text boxes and text areas I have had much success with this, but I cannot figure out, when using upload boxes, how to direct the file uploaded to be stored in the database instead of a folder on the local machine (I have associated these upload boxes with the image and thumbnail fields on the appropriate table, with no success)

How do I use dropdown boxes to limit the number of selections on a backend form which inserts data into the SQL database but pulls it's selections from the SQL database instead of generating list entries myself.


Currently my attempts have been to insert into the form a database results entry which pulls the data from appropriate tables, lists by name, but uses the associated numerical key to store the data, with no success)

These issues have been driving me crazy and with 4 weeks left before it's due, I'm pulling my hair out trying to figure it out.

Thanks in advance for any help you can give, if I've not been 100% clear with my explanations, please contact me and I will clarify to the best of my ability.

~Ryan F. Bracy "Angel"1. If I find my old code I will share it with you. The thing is pictures are not stored in SQL serve even thou it is possible as binary. It puts strain on a server and works terribly. What usually is done picture is stored on sharable server drive not on local machine and full path to that picture is stored in a database.

2. I need more information on table definitions and code you use.

I think I saw these questions before but you keep changing your nick and I am not sure if it is the same person.

Irina.|||By the way, how did you put picture next to your nick here?
I don't see how to do it... :)|||

Quote:

Originally Posted by iburyak

I think I saw these questions before but you keep changing your nick and I am not sure if it is the same person.
Irina.

This is my first time ever posting here.

and I will try to remember to post the code we're using presently.

Today our professor decided that we need to use all ASP to make the site, as frontpage doesn't have the capabilities to do what he wants us to do...with 3 1/2 weeks left before the project is due, and now he tells us that we have to learn ASP and code our whole site with it. When I suggested ASP from the beginning and was shot down...|||

Quote:

Originally Posted by iburyak

By the way, how did you put picture next to your nick here?
I don't see how to do it... :)

it's under the control panel, under edit avatar...|||I did find code how to convert picture into a binary stream. But I am sure if you'll see complexity of it you will change your mind to use it... :)

Suggest your professor to FTP picture to the server and save it their as I originally suggested on server side and save full path to the picture in a database.

If you still want code I found - send me private message with your direct e-mail.
I am not going to post this code on forum.

Thank you.

P.S. Is it possible that your professor was asking the same thing on forum here? :)|||

Quote:

Originally Posted by Ryan F Bracy

it's under the control panel, under edit avatar...

Thank you... :)