Multiple flat files to multiple tables

Hi,

I have searched but not found quite the best way to look at this so far..

I have an application that outputs data to several text files (up to 30). These have commonality by an object name, but then contain completely different column data.

In DTS I had each of the source text file connections going to one OLE DB connection and then individual transform data tasks pointing to the one OLE DB connection.

Looking at SSIS, it would appear that I would need to have one source and one destination for each of these and therefore 30 parallel data flows?

Just wondering if there is a neater way of doing this?

It is a regular data import that happens a few times a day - the text files are named the same as the SQL tables - ie app_userdata.txt goes to app_userdata table.

Hope that explains ok and thanks in advance.

Mike

Since each source file and each destination table has its own schema, I don't know of any better way than what you describe.

Personally I would rather see 30 packages, each with one data flow, rather than one package with 30 data flows, but that's largely a personal preference, not a hard and fast rule or anything...

|||Thanks for the response - hadn't considered a package for each so will take a look at that|||The advantage that I see in the "one package per destination table" approach is that it makes ongoing maintenance much simpler. For smaller projects it seems like overkill, but it serves me well...

Multiple columns full text search doesn't seems to work properly

Hi all !
I have a full text search index on a table with several fields
indexed.
I tried the following query :
SELECT MyIndexedTable.MyIndexedTableID
MyIndexedTable.Title,
KEY_TBL.RANK RANK_Total
FROM
MyIndexedTable
INNER JOIN
CONTAINSTABLE(MyIndexedTable,*,'"sport" AND "news"') AS KEY_TBL
ORDER BY KEY_TBL.RANK DESC
There are some records that are not returned by the query but they
have the words "sport" and "news" in their fields.
Any idea what could be happening?
Thanks in advance.
Xavi
This should work. Note that sports and news will have to be in the same
column to get a hit form this row.
"Xavi" <xaspas@.gmail.com> wrote in message
news:1177326871.889457.41300@.e65g2000hsc.googlegro ups.com...
> Hi all !
> I have a full text search index on a table with several fields
> indexed.
> I tried the following query :
> SELECT MyIndexedTable.MyIndexedTableID
> MyIndexedTable.Title,
> KEY_TBL.RANK RANK_Total
> FROM
> MyIndexedTable
> INNER JOIN
> CONTAINSTABLE(MyIndexedTable,*,'"sport" AND "news"') AS KEY_TBL
> ORDER BY KEY_TBL.RANK DESC
> There are some records that are not returned by the query but they
> have the words "sport" and "news" in their fields.
> Any idea what could be happening?
> Thanks in advance.
> Xavi
>
|||Exactly but I'd like to know how to make it work when words are
located in different fields.
I have read about creating an extra Text (or nText) field with all the
content in it and then create an index based on that field, I also
have seen something using unions and multiple querys on the index. I
wonder if there's any better approach and which one will give better
perfomance.
Thank you for your answer!
Xavi
On 23 abr, 14:31, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> This should work. Note that sports and news will have to be in the same
> column to get a hit form this row.
> "Xavi" <xas...@.gmail.com> wrote in message
> news:1177326871.889457.41300@.e65g2000hsc.googlegro ups.com...
>
>
|||The concatenated column offers the best performance.
"Xavi" <xaspas@.gmail.com> wrote in message
news:1177335970.294430.289990@.p77g2000hsh.googlegr oups.com...
> Exactly but I'd like to know how to make it work when words are
> located in different fields.
> I have read about creating an extra Text (or nText) field with all the
> content in it and then create an index based on that field, I also
> have seen something using unions and multiple querys on the index. I
> wonder if there's any better approach and which one will give better
> perfomance.
> Thank you for your answer!
> Xavi
> On 23 abr, 14:31, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
>

Multiple column full text search

Is there a way to specify which column weights more when doing full text
search..
For example, I have three column, filename, description and data. I did
full text index on all three column, but want to list the hits in filename
first, is there a way to do it sql 2005?
--Xin Chen
your might want to check out some of these postings for examples of how
to do this.
http://groups-beta.google.com/groups...ff&qt_s=Search
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

multiple blob

A table has a text field which provides content on a web page.
If the web content was to be regionalised, what would be the pros/cons of
having a text field for each region in the same record?
Thanks Soc.The main downside IMHO would be a total loss of scalability. Why not just us
e
the ntext datatype? That way you can just keep all in one column. Look up us
e
of unicode data types in Books Online.
ML|||How would you keep them all in one column? The ntext datatype only allows
unicode characters, not special regionlization (unless I am about to learn
something :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"ML" <ML@.discussions.microsoft.com> wrote in message
news:73A4CA11-1D30-4C0E-92D1-A72187B07E62@.microsoft.com...
> The main downside IMHO would be a total loss of scalability. Why not just
> use
> the ntext datatype? That way you can just keep all in one column. Look up
> use
> of unicode data types in Books Online.
>
> ML|||That's not exactly what I meant, but hey, why not?
ItemID : LanguageID : Text
Or did you mean all of them in one row? Now, that even I can't imagine. :)
No, wait... I can! If all they're inside an XML. But that's just silly, righ
t?
E.g. ItemID : XML
Item Text
BookTitle <values>
<value language="english">A summer night's
dream</value>
<value language="Deutsch">Ein sommer Nachts
Traum</value>
</values>
ML|||I didn't figure it was really what you meant. But you are right, you could
use XML, depending on the actual needs.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"ML" <ML@.discussions.microsoft.com> wrote in message
news:AE81E10E-DBEF-4FD7-B5E6-38C9AA2CF27A@.microsoft.com...
> That's not exactly what I meant, but hey, why not?
> ItemID : LanguageID : Text
> Or did you mean all of them in one row? Now, that even I can't imagine. :)
> No, wait... I can! If all they're inside an XML. But that's just silly,
> right?
> E.g. ItemID : XML
> Item Text
> BookTitle <values>
> <value language="english">A summer night's
> dream</value>
> <value language="Deutsch">Ein sommer Nachts
> Traum</value>
> </values>
>
> ML|||On Fri, 9 Sep 2005 16:12:16 +0100, soc wrote:

>A table has a text field which provides content on a web page.
>If the web content was to be regionalised, what would be the pros/cons of
>having a text field for each region in the same record?
>Thanks Soc.
>
Hi Soc,
Pros: none that I can think of.
Cons: not normalised, not scalable.
Better to have an extra table, with foreign key to the main table,
language code plus this foreign key as the primary key and the
translated contents as the third column.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks all.
I agree with the separate table option...
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:4804i1pd5an6r5jl1m42rttos800j7sapt@.
4ax.com...
> On Fri, 9 Sep 2005 16:12:16 +0100, soc wrote:
>
> Hi Soc,
> Pros: none that I can think of.
> Cons: not normalised, not scalable.
> Better to have an extra table, with foreign key to the main table,
> language code plus this foreign key as the primary key and the
> translated contents as the third column.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Multipe text file import to single table

All,
I need to import the contents of 1000+ text files into a single table
in SQL Server 7.0. The files are plain ascii, fields seperated by
"/".
Does anyone have ideas of how this can be achieved?
Regards,
A.Multiple BULK INSERT commands?
"Adrian Smith" <adriancsi@.aol.com> wrote in message
news:eb79b8a3.0311181012.76211750@.posting.google.com...
> All,
> I need to import the contents of 1000+ text files into a single table
> in SQL Server 7.0. The files are plain ascii, fields seperated by
> "/".
> Does anyone have ideas of how this can be achieved?
> Regards,
> A.|||Do they all have exactly the same format?
If so it may be easier to concatenate them into one single file... From a
DOS prompt you could use:
TYPE *.TXT > BIGFILE.TXT
(Assuming they all have .TXT extension and that they're the only files in
the folder with .TXT extension, of course)
From there you could then use either BULK COPY or BCP.
If for some reason you don't want to or can't concatenate them, you could
write a batch file that would call BCP, replacing the filename parameter in
the batch file with %1, then call the batch file for each file using (again
from a DOS prompt):
FOR %i IN (DIR *.TXT) DO INSERT.BAT %i
Or you could write the entire thing in a SQL script using a cursor and a
call to xp_cmdshell to get a list of the files... IMO the DOS methods are a
lot easier.
"Adrian Smith" <adriancsi@.aol.com> wrote in message
news:eb79b8a3.0311181012.76211750@.posting.google.com...
> All,
> I need to import the contents of 1000+ text files into a single table
> in SQL Server 7.0. The files are plain ascii, fields seperated by
> "/".
> Does anyone have ideas of how this can be achieved?
> Regards,
> A.

Multipart email

Hello,

Does anyone know of a solution for sending multipart email messages from SQL server? In particular I wish to send emails with both text and html versions embedded. I've no problem sending either text or html, but most solutions seem to give only those options, not multipart.

thanks!

/david

Moving thread to Tools forum. This is a question for the new Database Mail feature if you are on SQL Server 2005.

Multi-page report

I am new to Crystal Reports, and I'm trying to create a multipage report. The first page is populated at the top and bottom with text and data fields. The middle portion is one large text box (with border) which could contain one or more pages of data.

If the text box goes beyond what will fit on the first page, I want it to generate additional pages. The fields on the bottom of the first page should be repeated on the additional pages. Also the one-line title of the first page should be repeated at the top of the additional pages, with the words "(- contined)" added to it.

Finally, the there is a last page that has text and data fields at the top and bottom (different than the others), and one empty text box with border filling up the middle.

As for the first (and additional) pages, the large text box has a border, which should fill up all available space (central 2/3 of first page, most of additional page) in each page, regardless of how much text is in it.

I have made the top portion of the first page the report header, the title line of the additional pages in the page header (suppressed on first and last page), the footer of the first and additional pages the page footer (suppressed on last page), and the last page the report footer. However, when I preview the report, I only get the first page.

Also, there is one report parameter that is input. When I preview the page, I want it to prompt me for a value, not just insert one automatically.I've played around with it a bit, but I'm down to two problems:

I have a page footer which takes up about two inches at the bottom of the page, but is suppressed on the last page. I have a report footer which is to take up an entire page (actually, I have it less than that). When I preview the report, the page footer appears on the bottom of the report footer page, forcing the bottom few items onto a next page. The report footer would fit entirely on the page if the page footer did not appear at the bottom.

Also, I have it prompting me to type in a parameter value, but the selection is not pulling up the record from the database that matches that parameter. All the fields are blank.