Importing textfiles in to text fields how ?

Hello,
I have text files.
(Less than a hundred files, sizes between 3 K and 150 K)
I would like to import these files into the database, first
in a table called : Long_text_table.
Each text file goes into only one field.
The id and label fields will be assigned by hand with the
correct values, so that the long text can be moved to
the correct field in the destination table with an sql statement.
How can I get the texts in to this Long_text_table ?
(Using the standard SQL-server tools).
ben brugman
The import table will be something like :
CREATE TABLE [dbo].[Long_text_table] (
[id1] [int] IDENTITY (1, 1) NOT NULL ,
[id2] [int] NULL ,
[id3] [int] NULL ,
[label1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[label2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[longtext] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
You can do it with DTS as outlined in
http://www.sqldts.com/?246
Set the field and row delimiter of the Import text file to a combination of
characters that you are sure do not appear in the text file and the whole
file will be treated as one field.
You can just type over the list with delimiters in the DTS designer, you are
not limited to {CR}{LF} etc.
Jacco Schalkwijk
SQL Server MVP
"ben brugman" <ben@.niethier.nl> wrote in message
news:eCvSwC8pEHA.3252@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I have text files.
> (Less than a hundred files, sizes between 3 K and 150 K)
> I would like to import these files into the database, first
> in a table called : Long_text_table.
> Each text file goes into only one field.
> The id and label fields will be assigned by hand with the
> correct values, so that the long text can be moved to
> the correct field in the destination table with an sql statement.
> How can I get the texts in to this Long_text_table ?
> (Using the standard SQL-server tools).
> ben brugman
>
> The import table will be something like :
> CREATE TABLE [dbo].[Long_text_table] (
> [id1] [int] IDENTITY (1, 1) NOT NULL ,
> [id2] [int] NULL ,
> [id3] [int] NULL ,
> [label1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [label2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [longtext] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
>
|||Thanks for your attention,
I'll have a look into this. (On the first glance this is not familiar to
me).
For importing files in a single field I have found
the DTS readfile transformation. I tried this with one file
and it works.
Next week I'll try to integrate both methods.
(If I understand the example then).
Otherwise I have to type the filenames by hand one at the
time. (Not a huge problem).
thanks again
ben
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:ubR5bS8pEHA.2456@.TK2MSFTNGP10.phx.gbl...
> You can do it with DTS as outlined in
> http://www.sqldts.com/?246
> Set the field and row delimiter of the Import text file to a combination
of
> characters that you are sure do not appear in the text file and the whole
> file will be treated as one field.
> You can just type over the list with delimiters in the DTS designer, you
are
> not limited to {CR}{LF} etc.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:eCvSwC8pEHA.3252@.TK2MSFTNGP14.phx.gbl...
>

Importing textfiles in to text fields how ?

Hello,
I have text files.
(Less than a hundred files, sizes between 3 K and 150 K)
I would like to import these files into the database, first
in a table called : Long_text_table.
Each text file goes into only one field.
The id and label fields will be assigned by hand with the
correct values, so that the long text can be moved to
the correct field in the destination table with an sql statement.
How can I get the texts in to this Long_text_table ?
(Using the standard SQL-server tools).
ben brugman
The import table will be something like :
CREATE TABLE [dbo].[Long_text_table] (
[id1] [int] IDENTITY (1, 1) NOT NULL ,
[id2] [int] NULL ,
[id3] [int] NULL ,
[label1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[label2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[longtext] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GOYou can do it with DTS as outlined in
http://www.sqldts.com/?246
Set the field and row delimiter of the Import text file to a combination of
characters that you are sure do not appear in the text file and the whole
file will be treated as one field.
You can just type over the list with delimiters in the DTS designer, you are
not limited to {CR}{LF} etc.
--
Jacco Schalkwijk
SQL Server MVP
"ben brugman" <ben@.niethier.nl> wrote in message
news:eCvSwC8pEHA.3252@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I have text files.
> (Less than a hundred files, sizes between 3 K and 150 K)
> I would like to import these files into the database, first
> in a table called : Long_text_table.
> Each text file goes into only one field.
> The id and label fields will be assigned by hand with the
> correct values, so that the long text can be moved to
> the correct field in the destination table with an sql statement.
> How can I get the texts in to this Long_text_table ?
> (Using the standard SQL-server tools).
> ben brugman
>
> The import table will be something like :
> CREATE TABLE [dbo].[Long_text_table] (
> [id1] [int] IDENTITY (1, 1) NOT NULL ,
> [id2] [int] NULL ,
> [id3] [int] NULL ,
> [label1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [label2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [longtext] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
>|||Thanks for your attention,
I'll have a look into this. (On the first glance this is not familiar to
me).
For importing files in a single field I have found
the DTS readfile transformation. I tried this with one file
and it works.
Next week I'll try to integrate both methods.
(If I understand the example then).
Otherwise I have to type the filenames by hand one at the
time. (Not a huge problem).
thanks again
ben
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:ubR5bS8pEHA.2456@.TK2MSFTNGP10.phx.gbl...
> You can do it with DTS as outlined in
> http://www.sqldts.com/?246
> Set the field and row delimiter of the Import text file to a combination
of
> characters that you are sure do not appear in the text file and the whole
> file will be treated as one field.
> You can just type over the list with delimiters in the DTS designer, you
are
> not limited to {CR}{LF} etc.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:eCvSwC8pEHA.3252@.TK2MSFTNGP14.phx.gbl...
> > Hello,
> >
> > I have text files.
> > (Less than a hundred files, sizes between 3 K and 150 K)
> >
> > I would like to import these files into the database, first
> > in a table called : Long_text_table.
> >
> > Each text file goes into only one field.
> > The id and label fields will be assigned by hand with the
> > correct values, so that the long text can be moved to
> > the correct field in the destination table with an sql statement.
> >
> > How can I get the texts in to this Long_text_table ?
> > (Using the standard SQL-server tools).
> >
> > ben brugman
> >
> >
> > The import table will be something like :
> >
> > CREATE TABLE [dbo].[Long_text_table] (
> > [id1] [int] IDENTITY (1, 1) NOT NULL ,
> > [id2] [int] NULL ,
> > [id3] [int] NULL ,
> > [label1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [label2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [longtext] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> > ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> > GO
> >
> >
>

Importing multiple flat files to multiple tables in SSIS

I have a couple of hundred flat files to import into database tables using SSIS.

The files can be divided into groups by the format they use. I understand that I could import each group of files that have a common format at the same time using a Foreach Loop Container.

However, the example for the Foreach Loop Container has multiple files all being imported into the same database table. In my case, each file needs to be imported into a different database table.

Is it possible to import each set of files with the same format into different tables in a simple loop? I can't see a way to make a Data Flow Destination item accept its table name dynamically, which seems to prevent me doing this.

I suppose I could make a different Data Flow Destination item for each file, in the Data Flow. Would that be a reasonable solution, or is there a simpler solution, or should I just resign myself to making a separate Data Flow for every single file?

In an OLEDB destination you can select to use a "Table or view name from variable" -in this way the destination can be dynamic.

|||Thanks, I missed that.|||

Any suggestions on the best way to assign the destination table name variable?

I imagine could do it in a Script item in the Foreach Loop before the Data Flow item is executed, but is there somewhere to do it more in-line, like building it into the Data Flow item's properties somehow?

|||

Use some logic to derive the table name based on the format, which I guess must be derivable from the filename?

You have the filename, so how do you expect to transform this into a table name. I would expect an expression to be used somewhere, the tablename variable for example, but you may need to refer to some logic table. an Execute SQL Task inside the loop could query a SQL table that gave you the destination table from a filename. The Exec SQL Task result could then be assigned to the tablename variable.

Importing multiple files to SQL

I have over three hundred text files that I need to import to SQL
Server.
Each is in the exact same format.
I want to import tham as seperate tables.
Is there any way to do it in one process?

Regards,
CiarnYou can use DTS:
http://www.sqldts.com/default.aspx?246

If all the files are the same format then why not import them to a
single table with an extra column to identify the source file? That
should be much more convenient than creating 300 separate tables.

--
David Portas
SQL Server MVP
--|||I use the above method, but if you want seperate tables:

You could add in dynamic properties and some scripts to the workflow of
the DTS. A script task sets a source filename dynamic property. A
create table task uses the dynamic property as the table name. Then
import into the new table from the file(again using the dynamic
properties to set the source filename and destination table name).

Using only one table will make some things much easier, and other
things harder. Consider that with multiple tables you will have to
either have a copy of all your queries for each table, or have a stored
procedure that allows the table name to be specified as a parameter. I
think it would be easier to put all the data in one table and have a
sproc that takes the code for the special column as one of the
filtering criteria. I think the syntax for specifying the criteria for
that column is no harder than specifying a table name.|||Ok, I've copied SQLDTS.com Loop Import and Archive (246).dts from
http://www.sqldts.com/default.aspx?246

I don't really understand the intricacies of how it works, but
presume that I need to change the source folders it looks at.
How do I get it to import all the .txt files in say
D:\Documents and Settings\CiaranHudson\My Documents and all its sub
directories?|||Ok, I've copied SQLDTS.com Loop Import and Archive (246).dts from
http://www.sqldts.com/default.aspx?246

I don't really understand the intricacies of how it works, but
presume that I need to change the source folders it looks at.
How do I get it to import all the .txt files in say
D:\Documents and Settings\CiaranHudson\My Documents and all its sub
directories?|||Ok, I've copied SQLDTS.com Loop Import and Archive (246).dts from
http://www.sqldts.com/default.aspx?246

I don't really understand the intricacies of how it works, but
presume that I need to change the source folders it looks at.
How do I get it to import all the .txt files in say
D:\Documents and Settings\CiaranHudson\My Documents and all its sub
directories?

Importing existing DTS packages.

There doesn't seem to be a specific group covering legacy subsystems so I thought I would post this in here.

I have several hundred of these to import that are going to be left to run as they are until such time somebody has the time to migrate them to SSIS.

I have to say I'm not look forward to doing them one at a time using the brain dead UI (it folds up folders behind itself causing another two mouse click between each import). Is there a way of automating this import?

Thanks.

DTS packages are stored in exactly the same location in 2005 as they were in 2000, after all it is just a backward compatibility feature. If you upgraded a system there would be no change, If you are migrating as part of the "upgrade" then just copy the contents of msdb..sysdtspackages.

This may explain more about DTS storage

Transferring DTS Packages
(http://www.sqldts.com/default.aspx?204)

|||

Thanks for the guidance.

I actually found a utility by following your link above. The tool is called DTSbackup2000. It's more than a backup tool as it also transfers DTS packages between servers, including SQL Server 2005 targets.

Here is a direct link to the download: http://www.sqldts.com/download/DTSBackup2000.msi

It has worked for me and saved me a lot of clicky, clicky with the UI!