Import Prs

Hey guys,

I was curious how other people create several 100 SQL objects on a
new database. Example: I have each procedure in its own text file (IE
100 .sql files), so I just check them out of source control, then run
a perl script that just dumps them into one txt file. Then I paste
that mess into Query Analyzer.

So to reiterate my question, how do other people get sql objects
into a database. Obviously I'd do an object copy if they resided in
some other database.

While my solution works, there is always a better way. Thanks for
your suggestions.

- CptI don't know Perl but I assume you could execute OSQL from your script.
Another method is to invoke a Windows FOR command in a command prompt
window to execute OSQL. For example:

CD C:\SQLScripts
FOR %v in (*.sql) DO OSQL -i "%v" -o "%v.out" -E

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"CptVorpal" <cptvorpal@.hotmail.com> wrote in message
news:76cffbe0.0310081530.6c7a034b@.posting.google.c om...
> Hey guys,
> I was curious how other people create several 100 SQL objects on a
> new database. Example: I have each procedure in its own text file (IE
> 100 .sql files), so I just check them out of source control, then run
> a perl script that just dumps them into one txt file. Then I paste
> that mess into Query Analyzer.
> So to reiterate my question, how do other people get sql objects
> into a database. Obviously I'd do an object copy if they resided in
> some other database.
> While my solution works, there is always a better way. Thanks for
> your suggestions.
> - Cpt|||cptvorpal@.hotmail.com (CptVorpal) wrote in message news:<76cffbe0.0310081530.6c7a034b@.posting.google.com>...
> Hey guys,
> I was curious how other people create several 100 SQL objects on a
> new database. Example: I have each procedure in its own text file (IE
> 100 .sql files), so I just check them out of source control, then run
> a perl script that just dumps them into one txt file. Then I paste
> that mess into Query Analyzer.
> So to reiterate my question, how do other people get sql objects
> into a database. Obviously I'd do an object copy if they resided in
> some other database.
> While my solution works, there is always a better way. Thanks for
> your suggestions.
> - Cpt

You could start by looking at using OSQL.EXE to run your .sql scripts
from the command line - it should be straightforward to wrap that in a
script of some sort which gets the list of scripts, then executes them
one by one. You can trap the output from OSQL and use it for error
handling as well.

Simon|||[posted and mailed]

CptVorpal (cptvorpal@.hotmail.com) writes:
> I was curious how other people create several 100 SQL objects on a
> new database. Example: I have each procedure in its own text file (IE
> 100 .sql files), so I just check them out of source control, then run
> a perl script that just dumps them into one txt file. Then I paste
> that mess into Query Analyzer.
> So to reiterate my question, how do other people get sql objects
> into a database. Obviously I'd do an object copy if they resided in
> some other database.
> While my solution works, there is always a better way. Thanks for
> your suggestions.

The suggestion from Dan and Simon to use OSQL is a good one. I'll
add that you can invoke OSQL for each file. This could be help to
track any errors.

For a faster execution you could look into to connect to the database
from Perl using some interface. (There is a short overview on my
web site at: http://www.algonet.se/~sommar/mssql...ternatives.html.

And if you want a ton of bells and whistles, you can look at
http://www.abaris.se/abaperls/. This is the load tool that we use
in our shop, and as the name indicates it's all Perl. You could
say that I started where you are now, and this is what I have seven
years later. :-)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Import problem with varchar(max) field

I'm trying to import some Assessor data from a text file into a table and the field for Legal Description (column 2 in the source text file) in the table is of data type varchar(max) because some of the data goes over the 8K size. I get an error on the first row of importing that refers to column 2 (see 'Initial errors' below). I read the related post and changed the size of input column 2 to 8000 and got this error. Finally I set the size of the of input column 2 to 4000 and it ran. So I'm thinking there is a limit on the size of varchar data that can be imported. Just want to clarify what that limit is and how I might go about importing this data.

Thanks, John

Error with input column 2 set to size of 8000:

Setting Destination Connection (Error)

Messages

Error 0xc0204016: DTS.Pipeline: The "output column "Column 2" (388)" has a length that is not valid. The length must be between 0 and 4000.
(SQL Server Import and Export Wizard)

Exception from HRESULT: 0xC0204016 (Microsoft.SqlServer.DTSPipelineWrap)

Initial errors:

Executing (Error)

Messages

Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "Column 2" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
(SQL Server Import and Export Wizard)

Error 0xc020902a: Data Flow Task: The "output column "Column 2" (18)" failed because truncation occurred, and the truncation row disposition on "output column "Column 2" (18)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)

Error 0xc0202092: Data Flow Task: An error occurred while processing file "\\Scux00\assrdumps\SQLServerDB\exportsql.txt" on data row 1.
(SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task: The PrimeOutput method on component "Source - exportsql_txt" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited with error code 0xC0047038.
(SQL Server Import and Export Wizard)

Error 0xc0047039: Data Flow Task: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0047039.
(SQL Server Import and Export Wizard)

There is a limit on the size of unicode character strings at 4000. Non-unicode character strings have a limit of 8000. Ensure that you don't have type conversions between varchar and nvarchar.|||

I'm trying the SQL Server Import and Export Wizard. I've set the DataType to most of the fields as 'string[DT_STR]' and the Unicode box is unchecked, but when I look at column mappings after clicking 'Edit Mappings', all the columns say they're type nvarchar on the screen and it doesn't appear to be something I can change. If I highlight a column on that screen it shows the setting that I set in the Advanced screen for the text file, ie .. Column 2 string[DT_STR](4000). Since I'm getting the limit of 4k it must be because of this. I'm not sure how to modify it. I'll save the package as a file and open it up in BID Studio ... john

import of data in text file How ?

Hello group,
Each month we want to import data from an ascii file,
the records are of a fixed length (some spare space is added to fill them
up)
there are 12 different types of records (variants)
Each type has a different (but within the type always the same) build.
Each record starts with a type indication. Then the records follows.
One type can have 2 fields the other type can have 15 fields.
Records following eachother can have relations, so a type two record
belongs to the type 1 record before the type 2 record.
Example with 3 types
1 AAAAAABB
2 CCCDDDDDDEEEEFFGGHH
2 CCCDDDDDDEEEEFFGGHH
3 JJJJJJKKKKKKKKKLLMMMMMMNOPPPPQQQQ
1 AAAAAABB
2 CCCDDDDDDEEEEFFGGHH
The letters are indications for the different fields.
Any suggestions how to solve this ?
In our propriety system this was solved by reading the record
into a pascal record with all twelf variants and then writing it to the
propriety database. We could reuse the technique of the program,
but can not reuse the program.
And we still have to split up each record into fields.
Our propriety database just accepted the 'binairy' data as is.
ben brugmanBen,
You can reimplement your method of reading a record at a time and inserting
the row into the proper destination by using a DTS package and doing some
scripting for each row.
How I tend to do things like this is:
BULK INSERT the file to a work table
CREATE TABLE BulkInsertWork
(RowID INT IDENTITY,
RowText NVARCHAR(1000))
TRUNCATE TABLE BulkInsertWork
BULK INSERT MyDB.dbo.BulkInsertWork FROM 'c:\MyMonthlyFile.txt' ...
Then I write a stored procedure that parses out the specific rows, such as:
INSERT INTO Table1 (ColumnA, ColumnB)
SELECT SUBSTRING(RowText,3,6), SUBSTRING(9,2)
FROM BulkInsertWork
etc. for each type
FWIW
Russell Fields
http:/www.sqlpass.org/events/seattle03
2003 PASS Community Summit - Seattle
- The largest user-even dedicated to SQL Server! Register TODAY!
"ben brugman" <ben@.niethier.nl> wrote in message
news:bob7k7$4dj$1@.reader11.wxs.nl...
> Hello group,
> Each month we want to import data from an ascii file,
> the records are of a fixed length (some spare space is added to fill them
> up)
> there are 12 different types of records (variants)
> Each type has a different (but within the type always the same) build.
> Each record starts with a type indication. Then the records follows.
> One type can have 2 fields the other type can have 15 fields.
> Records following eachother can have relations, so a type two record
> belongs to the type 1 record before the type 2 record.
> Example with 3 types
> 1 AAAAAABB
> 2 CCCDDDDDDEEEEFFGGHH
> 2 CCCDDDDDDEEEEFFGGHH
> 3 JJJJJJKKKKKKKKKLLMMMMMMNOPPPPQQQQ
> 1 AAAAAABB
> 2 CCCDDDDDDEEEEFFGGHH
> The letters are indications for the different fields.
> Any suggestions how to solve this ?
> In our propriety system this was solved by reading the record
> into a pascal record with all twelf variants and then writing it to the
> propriety database. We could reuse the technique of the program,
> but can not reuse the program.
> And we still have to split up each record into fields.
> Our propriety database just accepted the 'binairy' data as is.
> ben brugman
>|||I immeadiatly went ahead with your suggested method.
I created a .txt file from the example in the mail.
I changed the nvarchar in the table to varchar.
When inserting from the .txt file I get the following errors :
Server: Msg 4832, Level 16, State 1, Line 1
Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any
information about the error.
The statement has been terminated.
Added :
WITH (
DATAFILETYPE = 'char',
ROWTERMINATOR = '\n'
)
But stil the same errors.
(Lastrow and rowsperbatch did not bring a solution either).
Please advise.
Also played around a bit with DTS, but the problem is that I can do an
import and then
a selection. Or that I can do a split up of the line into different fields.
But I can not do them both.
(Or I have to split up every line in every possible way and after selection
throwing away
the not desired results. Make a table for each type and insert every row in
every table and
then trowing away the types which do not belong in the table. I think this
is pretty ugly).
Ben brugman
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:uf4ZQm7oDHA.1072@.TK2MSFTNGP09.phx.gbl...
> Ben,
> You can reimplement your method of reading a record at a time and
inserting
> the row into the proper destination by using a DTS package and doing some
> scripting for each row.
> How I tend to do things like this is:
> BULK INSERT the file to a work table
> CREATE TABLE BulkInsertWork
> (RowID INT IDENTITY,
> RowText NVARCHAR(1000))
> TRUNCATE TABLE BulkInsertWork
> BULK INSERT MyDB.dbo.BulkInsertWork FROM 'c:\MyMonthlyFile.txt' ...
> Then I write a stored procedure that parses out the specific rows, such
as:
> INSERT INTO Table1 (ColumnA, ColumnB)
> SELECT SUBSTRING(RowText,3,6), SUBSTRING(9,2)
> FROM BulkInsertWork
> etc. for each type
> FWIW
> Russell Fields
> http:/www.sqlpass.org/events/seattle03
> 2003 PASS Community Summit - Seattle
> - The largest user-even dedicated to SQL Server! Register TODAY!
>
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:bob7k7$4dj$1@.reader11.wxs.nl...
> > Hello group,
> >
> > Each month we want to import data from an ascii file,
> > the records are of a fixed length (some spare space is added to fill
them
> > up)
> > there are 12 different types of records (variants)
> > Each type has a different (but within the type always the same) build.
> > Each record starts with a type indication. Then the records follows.
> > One type can have 2 fields the other type can have 15 fields.
> >
> > Records following eachother can have relations, so a type two record
> > belongs to the type 1 record before the type 2 record.
> >
> > Example with 3 types
> >
> > 1 AAAAAABB
> > 2 CCCDDDDDDEEEEFFGGHH
> > 2 CCCDDDDDDEEEEFFGGHH
> > 3 JJJJJJKKKKKKKKKLLMMMMMMNOPPPPQQQQ
> > 1 AAAAAABB
> > 2 CCCDDDDDDEEEEFFGGHH
> >
> > The letters are indications for the different fields.
> >
> > Any suggestions how to solve this ?
> >
> > In our propriety system this was solved by reading the record
> > into a pascal record with all twelf variants and then writing it to the
> > propriety database. We could reuse the technique of the program,
> > but can not reuse the program.
> > And we still have to split up each record into fields.
> > Our propriety database just accepted the 'binairy' data as is.
> >
> > ben brugman
> >
> >
>|||Ben,
A guess: The last row of the text file does not have the '\n'. If that is
so, append the character.
Less likely and more obscure: Are you on SQL Server 2000? If you are on
7.0, KB article 324122 has obscure problems that it is unlikely apply,
but...
http://support.microsoft.com/default.aspx?scid=kb;en-us;324122#appliesto
http://support.microsoft.com/default.aspx?scid=kb;EN-US;272292
http://support.microsoft.com/default.aspx?scid=kb;EN-US;197043
All error numbers that are in the range of 7300 to 7399 indicate a problem
with the provider, in this case STREAM.
Russell Fields
http://www.sqlpass.org/events/seattle03
2003 PASS Community Summit - Seattle
- The largest user-event dedicated to SQL Server! Register TODAY!
"ben brugman" <ben@.niethier.nl> wrote in message
news:eumzJHGpDHA.2188@.TK2MSFTNGP11.phx.gbl...
> I immeadiatly went ahead with your suggested method.
> I created a .txt file from the example in the mail.
> I changed the nvarchar in the table to varchar.
> When inserting from the .txt file I get the following errors :
> Server: Msg 4832, Level 16, State 1, Line 1
> Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'STREAM' reported an error. The provider did not give any
> information about the error.
> The statement has been terminated.
>
> Added :
> WITH (
> DATAFILETYPE = 'char',
> ROWTERMINATOR = '\n'
> )
> But stil the same errors.
> (Lastrow and rowsperbatch did not bring a solution either).
> Please advise.
> Also played around a bit with DTS, but the problem is that I can do an
> import and then
> a selection. Or that I can do a split up of the line into different
fields.
> But I can not do them both.
> (Or I have to split up every line in every possible way and after
selection
> throwing away
> the not desired results. Make a table for each type and insert every row
in
> every table and
> then trowing away the types which do not belong in the table. I think this
> is pretty ugly).
> Ben brugman
>
>
>
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:uf4ZQm7oDHA.1072@.TK2MSFTNGP09.phx.gbl...
> > Ben,
> >
> > You can reimplement your method of reading a record at a time and
> inserting
> > the row into the proper destination by using a DTS package and doing
some
> > scripting for each row.
> >
> > How I tend to do things like this is:
> > BULK INSERT the file to a work table
> >
> > CREATE TABLE BulkInsertWork
> > (RowID INT IDENTITY,
> > RowText NVARCHAR(1000))
> >
> > TRUNCATE TABLE BulkInsertWork
> >
> > BULK INSERT MyDB.dbo.BulkInsertWork FROM 'c:\MyMonthlyFile.txt' ...
> >
> > Then I write a stored procedure that parses out the specific rows, such
> as:
> >
> > INSERT INTO Table1 (ColumnA, ColumnB)
> > SELECT SUBSTRING(RowText,3,6), SUBSTRING(9,2)
> > FROM BulkInsertWork
> >
> > etc. for each type
> >
> > FWIW
> > Russell Fields
> > http:/www.sqlpass.org/events/seattle03
> > 2003 PASS Community Summit - Seattle
> > - The largest user-even dedicated to SQL Server! Register TODAY!
> >
> >
> >
> > "ben brugman" <ben@.niethier.nl> wrote in message
> > news:bob7k7$4dj$1@.reader11.wxs.nl...
> > > Hello group,
> > >
> > > Each month we want to import data from an ascii file,
> > > the records are of a fixed length (some spare space is added to fill
> them
> > > up)
> > > there are 12 different types of records (variants)
> > > Each type has a different (but within the type always the same) build.
> > > Each record starts with a type indication. Then the records follows.
> > > One type can have 2 fields the other type can have 15 fields.
> > >
> > > Records following eachother can have relations, so a type two record
> > > belongs to the type 1 record before the type 2 record.
> > >
> > > Example with 3 types
> > >
> > > 1 AAAAAABB
> > > 2 CCCDDDDDDEEEEFFGGHH
> > > 2 CCCDDDDDDEEEEFFGGHH
> > > 3 JJJJJJKKKKKKKKKLLMMMMMMNOPPPPQQQQ
> > > 1 AAAAAABB
> > > 2 CCCDDDDDDEEEEFFGGHH
> > >
> > > The letters are indications for the different fields.
> > >
> > > Any suggestions how to solve this ?
> > >
> > > In our propriety system this was solved by reading the record
> > > into a pascal record with all twelf variants and then writing it to
the
> > > propriety database. We could reuse the technique of the program,
> > > but can not reuse the program.
> > > And we still have to split up each record into fields.
> > > Our propriety database just accepted the 'binairy' data as is.
> > >
> > > ben brugman
> > >
> > >
> >
> >
>|||After loads of trying, reading the KB file, still getting the same Error
message.
In the end we deleted the identity column from "BulkInsertWork" and now the
insert works, but offcourse we have lost the order of the table.
Also did try some DTS work, but DTS is not very flexible when it comes to
variant records in a text file.
Thanks for your time,
and if you have suggestions to bring the identity column back,
I would appreciate that.
ben brugman
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:uqOboZIpDHA.2404@.TK2MSFTNGP12.phx.gbl...
> Ben,
> A guess: The last row of the text file does not have the '\n'. If that
is
> so, append the character.
> Less likely and more obscure: Are you on SQL Server 2000? If you are on
> 7.0, KB article 324122 has obscure problems that it is unlikely apply,
> but...
> http://support.microsoft.com/default.aspx?scid=kb;en-us;324122#appliesto
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;272292
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;197043
> All error numbers that are in the range of 7300 to 7399 indicate a problem
> with the provider, in this case STREAM.
> Russell Fields
> http://www.sqlpass.org/events/seattle03
> 2003 PASS Community Summit - Seattle
> - The largest user-event dedicated to SQL Server! Register TODAY!
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:eumzJHGpDHA.2188@.TK2MSFTNGP11.phx.gbl...
> > I immeadiatly went ahead with your suggested method.
> > I created a .txt file from the example in the mail.
> > I changed the nvarchar in the table to varchar.
> >
> > When inserting from the .txt file I get the following errors :
> >
> > Server: Msg 4832, Level 16, State 1, Line 1
> > Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.
> > Server: Msg 7399, Level 16, State 1, Line 1
> > OLE DB provider 'STREAM' reported an error. The provider did not give
any
> > information about the error.
> > The statement has been terminated.
> >
> >
> > Added :
> > WITH (
> > DATAFILETYPE = 'char',
> > ROWTERMINATOR = '\n'
> > )
> >
> > But stil the same errors.
> > (Lastrow and rowsperbatch did not bring a solution either).
> >
> > Please advise.
> >
> > Also played around a bit with DTS, but the problem is that I can do an
> > import and then
> > a selection. Or that I can do a split up of the line into different
> fields.
> > But I can not do them both.
> > (Or I have to split up every line in every possible way and after
> selection
> > throwing away
> > the not desired results. Make a table for each type and insert every row
> in
> > every table and
> > then trowing away the types which do not belong in the table. I think
this
> > is pretty ugly).
> >
> > Ben brugman
> >
> >
> >
> >
> >
> >
> >
> > "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> > news:uf4ZQm7oDHA.1072@.TK2MSFTNGP09.phx.gbl...
> > > Ben,
> > >
> > > You can reimplement your method of reading a record at a time and
> > inserting
> > > the row into the proper destination by using a DTS package and doing
> some
> > > scripting for each row.
> > >
> > > How I tend to do things like this is:
> > > BULK INSERT the file to a work table
> > >
> > > CREATE TABLE BulkInsertWork
> > > (RowID INT IDENTITY,
> > > RowText NVARCHAR(1000))
> > >
> > > TRUNCATE TABLE BulkInsertWork
> > >
> > > BULK INSERT MyDB.dbo.BulkInsertWork FROM 'c:\MyMonthlyFile.txt' ...
> > >
> > > Then I write a stored procedure that parses out the specific rows,
such
> > as:
> > >
> > > INSERT INTO Table1 (ColumnA, ColumnB)
> > > SELECT SUBSTRING(RowText,3,6), SUBSTRING(9,2)
> > > FROM BulkInsertWork
> > >
> > > etc. for each type
> > >
> > > FWIW
> > > Russell Fields
> > > http:/www.sqlpass.org/events/seattle03
> > > 2003 PASS Community Summit - Seattle
> > > - The largest user-even dedicated to SQL Server! Register TODAY!
> > >
> > >
> > >
> > > "ben brugman" <ben@.niethier.nl> wrote in message
> > > news:bob7k7$4dj$1@.reader11.wxs.nl...
> > > > Hello group,
> > > >
> > > > Each month we want to import data from an ascii file,
> > > > the records are of a fixed length (some spare space is added to fill
> > them
> > > > up)
> > > > there are 12 different types of records (variants)
> > > > Each type has a different (but within the type always the same)
build.
> > > > Each record starts with a type indication. Then the records follows.
> > > > One type can have 2 fields the other type can have 15 fields.
> > > >
> > > > Records following eachother can have relations, so a type two record
> > > > belongs to the type 1 record before the type 2 record.
> > > >
> > > > Example with 3 types
> > > >
> > > > 1 AAAAAABB
> > > > 2 CCCDDDDDDEEEEFFGGHH
> > > > 2 CCCDDDDDDEEEEFFGGHH
> > > > 3 JJJJJJKKKKKKKKKLLMMMMMMNOPPPPQQQQ
> > > > 1 AAAAAABB
> > > > 2 CCCDDDDDDEEEEFFGGHH
> > > >
> > > > The letters are indications for the different fields.
> > > >
> > > > Any suggestions how to solve this ?
> > > >
> > > > In our propriety system this was solved by reading the record
> > > > into a pascal record with all twelf variants and then writing it to
> the
> > > > propriety database. We could reuse the technique of the program,
> > > > but can not reuse the program.
> > > > And we still have to split up each record into fields.
> > > > Our propriety database just accepted the 'binairy' data as is.
> > > >
> > > > ben brugman
> > > >
> > > >
> > >
> > >
> >
> >
>|||Ben,
Yes, I am sorry that I glossed this in my earlier quick explanation. (I
thought about it during the night, don't you know.)
You need a format file to tell bulk insert to skip over the Identity column.
Something like:
8.0
2
1 SQLCHAR 0 0 "" 1 ID SQL_Latin1_General_Cp437_BIN
2 SQLCHAR 0 1000 "\r\n" 2 Text SQL_Latin1_General_Cp437_BIN
Russell Fields
http://www.sqlpass.org/events/seattle03
2003 PASS Community Summit - Seattle
- The largest user-event dedicated to SQL Server! Register TODAY!
"ben brugman" <ben@.niethier.nl> wrote in message
news:OiAr$FTpDHA.3844@.tk2msftngp13.phx.gbl...
> After loads of trying, reading the KB file, still getting the same Error
> message.
> In the end we deleted the identity column from "BulkInsertWork" and now
the
> insert works, but offcourse we have lost the order of the table.
> Also did try some DTS work, but DTS is not very flexible when it comes to
> variant records in a text file.
> Thanks for your time,
> and if you have suggestions to bring the identity column back,
> I would appreciate that.
> ben brugman
>
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:uqOboZIpDHA.2404@.TK2MSFTNGP12.phx.gbl...
> > Ben,
> >
> > A guess: The last row of the text file does not have the '\n'. If that
> is
> > so, append the character.
> >
> > Less likely and more obscure: Are you on SQL Server 2000? If you are
on
> > 7.0, KB article 324122 has obscure problems that it is unlikely apply,
> > but...
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;324122#appliesto
> > http://support.microsoft.com/default.aspx?scid=kb;EN-US;272292
> > http://support.microsoft.com/default.aspx?scid=kb;EN-US;197043
> >
> > All error numbers that are in the range of 7300 to 7399 indicate a
problem
> > with the provider, in this case STREAM.
> >
> > Russell Fields
> > http://www.sqlpass.org/events/seattle03
> > 2003 PASS Community Summit - Seattle
> > - The largest user-event dedicated to SQL Server! Register TODAY!
> >
> > "ben brugman" <ben@.niethier.nl> wrote in message
> > news:eumzJHGpDHA.2188@.TK2MSFTNGP11.phx.gbl...
> > > I immeadiatly went ahead with your suggested method.
> > > I created a .txt file from the example in the mail.
> > > I changed the nvarchar in the table to varchar.
> > >
> > > When inserting from the .txt file I get the following errors :
> > >
> > > Server: Msg 4832, Level 16, State 1, Line 1
> > > Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.
> > > Server: Msg 7399, Level 16, State 1, Line 1
> > > OLE DB provider 'STREAM' reported an error. The provider did not give
> any
> > > information about the error.
> > > The statement has been terminated.
> > >
> > >
> > > Added :
> > > WITH (
> > > DATAFILETYPE = 'char',
> > > ROWTERMINATOR = '\n'
> > > )
> > >
> > > But stil the same errors.
> > > (Lastrow and rowsperbatch did not bring a solution either).
> > >
> > > Please advise.
> > >
> > > Also played around a bit with DTS, but the problem is that I can do an
> > > import and then
> > > a selection. Or that I can do a split up of the line into different
> > fields.
> > > But I can not do them both.
> > > (Or I have to split up every line in every possible way and after
> > selection
> > > throwing away
> > > the not desired results. Make a table for each type and insert every
row
> > in
> > > every table and
> > > then trowing away the types which do not belong in the table. I think
> this
> > > is pretty ugly).
> > >
> > > Ben brugman
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> > > news:uf4ZQm7oDHA.1072@.TK2MSFTNGP09.phx.gbl...
> > > > Ben,
> > > >
> > > > You can reimplement your method of reading a record at a time and
> > > inserting
> > > > the row into the proper destination by using a DTS package and doing
> > some
> > > > scripting for each row.
> > > >
> > > > How I tend to do things like this is:
> > > > BULK INSERT the file to a work table
> > > >
> > > > CREATE TABLE BulkInsertWork
> > > > (RowID INT IDENTITY,
> > > > RowText NVARCHAR(1000))
> > > >
> > > > TRUNCATE TABLE BulkInsertWork
> > > >
> > > > BULK INSERT MyDB.dbo.BulkInsertWork FROM 'c:\MyMonthlyFile.txt' ...
> > > >
> > > > Then I write a stored procedure that parses out the specific rows,
> such
> > > as:
> > > >
> > > > INSERT INTO Table1 (ColumnA, ColumnB)
> > > > SELECT SUBSTRING(RowText,3,6), SUBSTRING(9,2)
> > > > FROM BulkInsertWork
> > > >
> > > > etc. for each type
> > > >
> > > > FWIW
> > > > Russell Fields
> > > > http:/www.sqlpass.org/events/seattle03
> > > > 2003 PASS Community Summit - Seattle
> > > > - The largest user-even dedicated to SQL Server! Register TODAY!
> > > >
> > > >
> > > >
> > > > "ben brugman" <ben@.niethier.nl> wrote in message
> > > > news:bob7k7$4dj$1@.reader11.wxs.nl...
> > > > > Hello group,
> > > > >
> > > > > Each month we want to import data from an ascii file,
> > > > > the records are of a fixed length (some spare space is added to
fill
> > > them
> > > > > up)
> > > > > there are 12 different types of records (variants)
> > > > > Each type has a different (but within the type always the same)
> build.
> > > > > Each record starts with a type indication. Then the records
follows.
> > > > > One type can have 2 fields the other type can have 15 fields.
> > > > >
> > > > > Records following eachother can have relations, so a type two
record
> > > > > belongs to the type 1 record before the type 2 record.
> > > > >
> > > > > Example with 3 types
> > > > >
> > > > > 1 AAAAAABB
> > > > > 2 CCCDDDDDDEEEEFFGGHH
> > > > > 2 CCCDDDDDDEEEEFFGGHH
> > > > > 3 JJJJJJKKKKKKKKKLLMMMMMMNOPPPPQQQQ
> > > > > 1 AAAAAABB
> > > > > 2 CCCDDDDDDEEEEFFGGHH
> > > > >
> > > > > The letters are indications for the different fields.
> > > > >
> > > > > Any suggestions how to solve this ?
> > > > >
> > > > > In our propriety system this was solved by reading the record
> > > > > into a pascal record with all twelf variants and then writing it
to
> > the
> > > > > propriety database. We could reuse the technique of the program,
> > > > > but can not reuse the program.
> > > > > And we still have to split up each record into fields.
> > > > > Our propriety database just accepted the 'binairy' data as is.
> > > > >
> > > > > ben brugman
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||I'll try that on monday.
At the moment I have done away with the identity column,
I am not sure if I will need it, because it seems that the only dependency
in order of the records is the dependency on the very first record, which
only exists once.
I am using your method of
"SUBSTRING(RowText,3,6), SUBSTRING(RowText,9,2)"
But because there are 153 columns in total, I do not want to type this out.
So I am in the process of writing scripts to generate this code.
Up to know I have managed to get three tables done, but the script
still needs some 'hand editing' for each table.
Using constructs like
Select 'INSERT INTO' + @.@.tablename+ '('
Select 'SUBSTRING(Rowtext,
'+convert(varchar(3),offset)+','+convert(varchar(3),length)+'),'
From metatable where table_name = @.@.tablename and length <> 0
Select 'From Bulkinsertwork'
Or something similar, then I cut and past this into another QA window,
clean it up and execute it.
Some things still to be solved are
The extra comma behind the last SUBSTRING line.
The extra lines generated (with the number of rows affectted and headers).
So I might go and do this in a stringvariabele and try to get the last comma
out.
Haven't decided jet if I am going to deliver the end script or enough tools
to build
the script.
Still some problems with referential constraints and spaces which have to be
converted to
<NULLS>. (After or during ? inserting ?).
Chopping all problems up and going from intermediate result to intermadiate
result makes
the code less complex.
And some problems with the content of the columns coming from other lines.
(The first line contains a sort of number which should be included in almost
all records).
Probably use temporary tables resolve all problems and then copy the content
to the
production tables.
Thanks for you help.
ben brugman
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:ucvlkTTpDHA.1632@.TK2MSFTNGP10.phx.gbl...
> Ben,
> Yes, I am sorry that I glossed this in my earlier quick explanation. (I
> thought about it during the night, don't you know.)
> You need a format file to tell bulk insert to skip over the Identity
column.
> Something like:
> 8.0
> 2
> 1 SQLCHAR 0 0 "" 1 ID SQL_Latin1_General_Cp437_BIN
> 2 SQLCHAR 0 1000 "\r\n" 2 Text SQL_Latin1_General_Cp437_BIN
>
> Russell Fields
> http://www.sqlpass.org/events/seattle03
> 2003 PASS Community Summit - Seattle
> - The largest user-event dedicated to SQL Server! Register TODAY!
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:OiAr$FTpDHA.3844@.tk2msftngp13.phx.gbl...
> > After loads of trying, reading the KB file, still getting the same Error
> > message.
> >
> > In the end we deleted the identity column from "BulkInsertWork" and now
> the
> > insert works, but offcourse we have lost the order of the table.
> >
> > Also did try some DTS work, but DTS is not very flexible when it comes
to
> > variant records in a text file.
> >
> > Thanks for your time,
> > and if you have suggestions to bring the identity column back,
> > I would appreciate that.
> >
> > ben brugman
> >
> >
> > "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> > news:uqOboZIpDHA.2404@.TK2MSFTNGP12.phx.gbl...
> > > Ben,
> > >
> > > A guess: The last row of the text file does not have the '\n'. If
that
> > is
> > > so, append the character.
> > >
> > > Less likely and more obscure: Are you on SQL Server 2000? If you are
> on
> > > 7.0, KB article 324122 has obscure problems that it is unlikely apply,
> > > but...
> > >
http://support.microsoft.com/default.aspx?scid=kb;en-us;324122#appliesto
> > > http://support.microsoft.com/default.aspx?scid=kb;EN-US;272292
> > > http://support.microsoft.com/default.aspx?scid=kb;EN-US;197043
> > >
> > > All error numbers that are in the range of 7300 to 7399 indicate a
> problem
> > > with the provider, in this case STREAM.
> > >
> > > Russell Fields
> > > http://www.sqlpass.org/events/seattle03
> > > 2003 PASS Community Summit - Seattle
> > > - The largest user-event dedicated to SQL Server! Register TODAY!
> > >
> > > "ben brugman" <ben@.niethier.nl> wrote in message
> > > news:eumzJHGpDHA.2188@.TK2MSFTNGP11.phx.gbl...
> > > > I immeadiatly went ahead with your suggested method.
> > > > I created a .txt file from the example in the mail.
> > > > I changed the nvarchar in the table to varchar.
> > > >
> > > > When inserting from the .txt file I get the following errors :
> > > >
> > > > Server: Msg 4832, Level 16, State 1, Line 1
> > > > Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.
> > > > Server: Msg 7399, Level 16, State 1, Line 1
> > > > OLE DB provider 'STREAM' reported an error. The provider did not
give
> > any
> > > > information about the error.
> > > > The statement has been terminated.
> > > >
> > > >
> > > > Added :
> > > > WITH (
> > > > DATAFILETYPE = 'char',
> > > > ROWTERMINATOR = '\n'
> > > > )
> > > >
> > > > But stil the same errors.
> > > > (Lastrow and rowsperbatch did not bring a solution either).
> > > >
> > > > Please advise.
> > > >
> > > > Also played around a bit with DTS, but the problem is that I can do
an
> > > > import and then
> > > > a selection. Or that I can do a split up of the line into different
> > > fields.
> > > > But I can not do them both.
> > > > (Or I have to split up every line in every possible way and after
> > > selection
> > > > throwing away
> > > > the not desired results. Make a table for each type and insert every
> row
> > > in
> > > > every table and
> > > > then trowing away the types which do not belong in the table. I
think
> > this
> > > > is pretty ugly).
> > > >
> > > > Ben brugman
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> > > > news:uf4ZQm7oDHA.1072@.TK2MSFTNGP09.phx.gbl...
> > > > > Ben,
> > > > >
> > > > > You can reimplement your method of reading a record at a time and
> > > > inserting
> > > > > the row into the proper destination by using a DTS package and
doing
> > > some
> > > > > scripting for each row.
> > > > >
> > > > > How I tend to do things like this is:
> > > > > BULK INSERT the file to a work table
> > > > >
> > > > > CREATE TABLE BulkInsertWork
> > > > > (RowID INT IDENTITY,
> > > > > RowText NVARCHAR(1000))
> > > > >
> > > > > TRUNCATE TABLE BulkInsertWork
> > > > >
> > > > > BULK INSERT MyDB.dbo.BulkInsertWork FROM 'c:\MyMonthlyFile.txt'
...
> > > > >
> > > > > Then I write a stored procedure that parses out the specific rows,
> > such
> > > > as:
> > > > >
> > > > > INSERT INTO Table1 (ColumnA, ColumnB)
> > > > > SELECT SUBSTRING(RowText,3,6), SUBSTRING(9,2)
> > > > > FROM BulkInsertWork
> > > > >
> > > > > etc. for each type
> > > > >
> > > > > FWIW
> > > > > Russell Fields
> > > > > http:/www.sqlpass.org/events/seattle03
> > > > > 2003 PASS Community Summit - Seattle
> > > > > - The largest user-even dedicated to SQL Server! Register TODAY!
> > > > >
> > > > >
> > > > >
> > > > > "ben brugman" <ben@.niethier.nl> wrote in message
> > > > > news:bob7k7$4dj$1@.reader11.wxs.nl...
> > > > > > Hello group,
> > > > > >
> > > > > > Each month we want to import data from an ascii file,
> > > > > > the records are of a fixed length (some spare space is added to
> fill
> > > > them
> > > > > > up)
> > > > > > there are 12 different types of records (variants)
> > > > > > Each type has a different (but within the type always the same)
> > build.
> > > > > > Each record starts with a type indication. Then the records
> follows.
> > > > > > One type can have 2 fields the other type can have 15 fields.
> > > > > >
> > > > > > Records following eachother can have relations, so a type two
> record
> > > > > > belongs to the type 1 record before the type 2 record.
> > > > > >
> > > > > > Example with 3 types
> > > > > >
> > > > > > 1 AAAAAABB
> > > > > > 2 CCCDDDDDDEEEEFFGGHH
> > > > > > 2 CCCDDDDDDEEEEFFGGHH
> > > > > > 3 JJJJJJKKKKKKKKKLLMMMMMMNOPPPPQQQQ
> > > > > > 1 AAAAAABB
> > > > > > 2 CCCDDDDDDEEEEFFGGHH
> > > > > >
> > > > > > The letters are indications for the different fields.
> > > > > >
> > > > > > Any suggestions how to solve this ?
> > > > > >
> > > > > > In our propriety system this was solved by reading the record
> > > > > > into a pascal record with all twelf variants and then writing it
> to
> > > the
> > > > > > propriety database. We could reuse the technique of the program,
> > > > > > but can not reuse the program.
> > > > > > And we still have to split up each record into fields.
> > > > > > Our propriety database just accepted the 'binairy' data as is.
> > > > > >
> > > > > > ben brugman
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

Import Multi-Row Record Text File with DTS

I have a text file I need to import into a SQL Server table. Each record spans several lines. Does anyone have a vbscript routine that wil go thru this text file and put each record on one row? Once the records are one row, DTS will easily handle the import. Or is there a better way?

Sample multi-row records:

WRLDWYXCDS1 ALT101 APR04 21:30:24 6879 FAIL ALT
HOST 00 0 08 00 DN 3073477171 1st CYCLE
TEST TYPE CKTTST DIAGNOSTIC RESULT BIC/EBS LC TRBL:PTRN 000E S= 1 R= 0
ACTION REQUIRED Replace Card CARD TYPE 6X21AC

WRLDWYXCDS1 ALT101 APR04 22:31:37 7672 FAIL ALT
HOST 00 0 08 00 DN 3073477171 1st CYCLE
TEST TYPE CKTTST DIAGNOSTIC RESULT BIC/EBS LC TRBL:PTRN 000E S= 1 R= 0
ACTION REQUIRED Replace Card CARD TYPE 6X21ACDTS questions don't get a lot of answers on this forum. Perhaps most developers feel as I do, that DTS is a kluge solution of several different poorly integrated technologies. If you are lucky, my posting this reply will shoot your post back to the top of the list queue and somebody proficient in DTS will give you an answer.

Otherwise, I use DTS only for what it is best at; transfering and importing raw data. I avoid using DTS for transforming data, prefering instead to load the data into staging tables in my database and then run stored procedures to transfer it to the main database schema. I think one of the big advantages of this is that you separate the import process from the transform process, allowing you to flag and retain records that fail your business rules.|||Originally posted by eoffshore
I have a text file I need to import into a SQL Server table. Each record spans several lines. Does anyone have a vbscript routine that wil go thru this text file and put each record on one row? Once the records are one row, DTS will easily handle the import. Or is there a better way?

Sample multi-row records:

WRLDWYXCDS1 ALT101 APR04 21:30:24 6879 FAIL ALT
HOST 00 0 08 00 DN 3073477171 1st CYCLE
TEST TYPE CKTTST DIAGNOSTIC RESULT BIC/EBS LC TRBL:PTRN 000E S= 1 R= 0
ACTION REQUIRED Replace Card CARD TYPE 6X21AC

WRLDWYXCDS1 ALT101 APR04 22:31:37 7672 FAIL ALT
HOST 00 0 08 00 DN 3073477171 1st CYCLE
TEST TYPE CKTTST DIAGNOSTIC RESULT BIC/EBS LC TRBL:PTRN 000E S= 1 R= 0
ACTION REQUIRED Replace Card CARD TYPE 6X21AC

You don't need VB for this. It would be faster to create a staging table with one column. You want the column to be a char so you don't lose your fixed length. Have a select statement push it into a second staging table with char, but big enough for all four rows.

Then, you can either use a final parsing statement to divide it up, or export to a fixed length text file. It should be quicker to do it this way in sets then to parse through in VB where you'll have to parse through each line and length section.|||In your case it looks like you have records with five elements, where each element is a separate line in your raw data.

One solution to this is to import each record into a staging table as a single string of characters. A possible layout for your staging table would be:

RecordID (Identity)
GroupID (Int)
RecordType (Int)
RecordString (Varchar(500))
ImportErrors (Varchar(100))

As the data is imported into the staging table a RecordID is created to maintain the order.

Next you run a query(s) against your table to identify the RecordType for each record. For instance, maybe all the type 2 records start with 'HOST'

Lastly, group the records like this:

Update StagingTable
set GroupID = SubTable.GroupID
from StagingTable
inner join
(select StagingTable.RecordID, Min(GroupRecords.RecordID) GroupID
from StagingTable
inner join StagingTable GroupRecords
on StagingTable.RecordID >= GroupRecords.RecordID
and GroupRecords.RecordType = 1) SubTable
on StagingTable.RecordID = SubTable.RecordID

Now you have identified your imported records and their relationships to eachother, and you can load them into your schema. It's not neat or pretty, but it works. For each step you can flag problems by appending an error message to the ImportErrors field.

Import multiple txt files

I have 8GB of text files which are basically log files from the past few years.
There is 24 text files per directory which are labeled for every day (so they are not all in 1 folder).
It would make reading them much easier if I could import them to SQL but I only seem to be able to import 1 at a time? (with the wizards :eek: )

Surely there is a way to mass import without all the costly applications that google searches give me?
cheers :PUse a DTS package implementing looping constructs with ActiveX scripts to loop through each folder and import the data contained in each file. There are some basic examples of how to do this on SQLDTS.com

Regards
Lempster|||If you are using SQL Server 2005 then you can use Integration service.
SSIS provides number of features to perform variety of tasks. You can use for loop container to import multiple text files at a time.|||I would use bcp and a cmd shell script for this.|||I would use bcp and a cmd shell script for this.

I would probably wrap a DTSRun in a cmd shell script (with a for/do) and pass in whatever needed input variables (date, basefilename, etc.), I just wish cmd had good "native" equivalents of grep and sed that exist in UNIX. I've used type | find, type | findstr but it gets a bit clumsy.|||Lost of ideas for me to look at here hehe, thanks a lot

import multiple text files?

Hello,

I am kind of new to Sql Server 2005.

I figured out how to use the import data wizard to import a delimited text file.

But I need to find a way to import many delimited text files at once.

does anybody know if this can be done in Sql Server 2005? and how?
thanks in advance,Hi,

you should probably use integration services for that. SSIS has a special task for that which will loop though a directory and inmport all text files which fit into the filter specified before.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Import multiple text files into Single table

How to import multiple text files (residing in single folder) into SQL Server table? I know how to import single file but not sure how multiple files could be loaded? Pls. guide.

Thanks,

HShah

I am assuming that thefiles all have the same structure.

There are a number of options availableto you. See here:

Processing data from multiple files all at once
(http://blogs.conchango.com/jamiethomson/archive/2006/10/14/SSIS_3A00_-Processing-data-from-multiple-files-all-at-once.aspx)

-Jamie

sql

Import Logins from text or spreadsheet

Is there a way to create logins from a text file or
spreadsheet? I have a long list of users. They will be
Sql Server Authenicated. I need to
1. add login
2. set psw to generic value
3. set default database
4. add them to user defined role
Thanks,
Brian
> 1. add login
> 2. set psw to generic value
> 3. set default database
4. add user to database
5. add them to user defined role
The required script template:
EXEC sp_addlogin 'SomeLogin', 'SomePassword', 'SomeDatabase'
USE MyDatabase
EXEC sp_adduser 'SomeLogin'
EXEC sp_addrolemember 'SomeRole', 'SomeLogin'
Let's assume your Excel spreadsheet has 3 columns: Login, DefaultDatabase
and Role. One method is to generate the needed script using SQL and
OPENROWSET. The query below will generate a script for each row in the
spreadsheet. You can then copy/paste the results into a Query Analyzer
window and execute. The spreadsheet file needs be accessible by the SQL
Server service.
SELECT
'EXEC sp_addlogin ''' +
Login +
''', ''SomePassword'', ''' +
DefaultDatabase + '''
USE ' + DefaultDatabase + '
EXEC sp_adduser ''' +
Login + '''
EXEC sp_addrolemember ''' +
Role +
''', ''' +
Login +
''''
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE=c:\temp\Logins.xls',
'Select * from [Sheet1$]')
Hope this helps.
Dan Guzman
SQL Server MVP
"Brian" <bgroves@.medibase.com> wrote in message
news:2db5001c46b6b$7f8d35d0$a601280a@.phx.gbl...
> Is there a way to create logins from a text file or
> spreadsheet? I have a long list of users. They will be
> Sql Server Authenicated. I need to
> 1. add login
> 2. set psw to generic value
> 3. set default database
> 4. add them to user defined role
> Thanks,
> Brian
|||Possibly less prone to error is:
CREATE PROCEDURE Process (
@.SomeLogin sysname,
@.SomePassword nvarchar(200) collate Latin1_General_CS_AS,
@.SomeDatabase sysname
) as
DECLARE @.SQL nvarchar(4000)
set @.SQL = '
EXEC sp_addlogin $L$, $P$, $D$
USE MyDatabase
EXEC sp_adduser $L$
EXEC sp_addrolemember $R$, $L$
'
select
replace(replace(replace(replace(@.sql,
'$L$',quotename(Login,'''')),
'$P$',quotename(Password,'''')),
'$D$',quotename(Database)),
'$R$',role)
from openrowset ...
It could go wrong if any of the Excel fields contains one of
the $x$ codes replaced later.
Creating a 4th column in Excel is also a solution:
="EXEC sp_addlogin '"& A1 & "', ... and so on
Steve Kass
Drew University
Dan Guzman wrote:

>4. add user to database
>5. add them to user defined role
>The required script template:
>EXEC sp_addlogin 'SomeLogin', 'SomePassword', 'SomeDatabase'
>USE MyDatabase
>EXEC sp_adduser 'SomeLogin'
>EXEC sp_addrolemember 'SomeRole', 'SomeLogin'
>Let's assume your Excel spreadsheet has 3 columns: Login, DefaultDatabase
>and Role. One method is to generate the needed script using SQL and
>OPENROWSET. The query below will generate a script for each row in the
>spreadsheet. You can then copy/paste the results into a Query Analyzer
>window and execute. The spreadsheet file needs be accessible by the SQL
>Server service.
>SELECT
>'EXEC sp_addlogin ''' +
> Login +
> ''', ''SomePassword'', ''' +
> DefaultDatabase + '''
>USE ' + DefaultDatabase + '
>EXEC sp_adduser ''' +
> Login + '''
>EXEC sp_addrolemember ''' +
> Role +
> ''', ''' +
> Login +
> ''''
>FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> 'Excel 8.0;DATABASE=c:\temp\Logins.xls',
> 'Select * from [Sheet1$]')
>
>
|||Good suggestion, Steve. FWIW, I usually the token names like the following
when using this technique but that's just a personal preference.
set @.SQL = '
EXEC sp_addlogin $(Login), $(Password), $(Database)
USE $(Database)
EXEC sp_adduser $(Login)
EXEC sp_addrolemember $(Role), $(Login)
'
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23okGIu7aEHA.3892@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Possibly less prone to error is:
> CREATE PROCEDURE Process (
> @.SomeLogin sysname,
> @.SomePassword nvarchar(200) collate Latin1_General_CS_AS,
> @.SomeDatabase sysname
> ) as
> DECLARE @.SQL nvarchar(4000)
> set @.SQL = '
> EXEC sp_addlogin $L$, $P$, $D$
> USE MyDatabase
> EXEC sp_adduser $L$
> EXEC sp_addrolemember $R$, $L$
> '
> select
> replace(replace(replace(replace(@.sql,
> '$L$',quotename(Login,'''')),
> '$P$',quotename(Password,'''')),
> '$D$',quotename(Database)),
> '$R$',role)
> from openrowset ...
> It could go wrong if any of the Excel fields contains one of
> the $x$ codes replaced later.
> Creating a 4th column in Excel is also a solution:
> ="EXEC sp_addlogin '"& A1 & "', ... and so on
> Steve Kass
> Drew University
>
> Dan Guzman wrote:

Import Logins from text or spreadsheet

Is there a way to create logins from a text file or
spreadsheet? I have a long list of users. They will be
Sql Server Authenicated. I need to
1. add login
2. set psw to generic value
3. set default database
4. add them to user defined role
Thanks,
Brian> 1. add login
> 2. set psw to generic value
> 3. set default database
4. add user to database
5. add them to user defined role
The required script template:
EXEC sp_addlogin 'SomeLogin', 'SomePassword', 'SomeDatabase'
USE MyDatabase
EXEC sp_adduser 'SomeLogin'
EXEC sp_addrolemember 'SomeRole', 'SomeLogin'
Let's assume your Excel spreadsheet has 3 columns: Login, DefaultDatabase
and Role. One method is to generate the needed script using SQL and
OPENROWSET. The query below will generate a script for each row in the
spreadsheet. You can then copy/paste the results into a Query Analyzer
window and execute. The spreadsheet file needs be accessible by the SQL
Server service.
SELECT
'EXEC sp_addlogin ''' +
Login +
''', ''SomePassword'', ''' +
DefaultDatabase + '''
USE ' + DefaultDatabase + '
EXEC sp_adduser ''' +
Login + '''
EXEC sp_addrolemember ''' +
Role +
''', ''' +
Login +
''''
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE=c:\temp\Logins.xls',
'Select * from [Sheet1$]')
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Brian" <bgroves@.medibase.com> wrote in message
news:2db5001c46b6b$7f8d35d0$a601280a@.phx.gbl...
> Is there a way to create logins from a text file or
> spreadsheet? I have a long list of users. They will be
> Sql Server Authenicated. I need to
> 1. add login
> 2. set psw to generic value
> 3. set default database
> 4. add them to user defined role
> Thanks,
> Brian|||Possibly less prone to error is:
CREATE PROCEDURE Process (
@.SomeLogin sysname,
@.SomePassword nvarchar(200) collate Latin1_General_CS_AS,
@.SomeDatabase sysname
) as
DECLARE @.SQL nvarchar(4000)
set @.SQL = '
EXEC sp_addlogin $L$, $P$, $D$
USE MyDatabase
EXEC sp_adduser $L$
EXEC sp_addrolemember $R$, $L$
'
select
replace(replace(replace(replace(@.sql,
'$L$',quotename(Login,'''')),
'$P$',quotename(Password,'''')),
'$D$',quotename(Database)),
'$R$',role)
from openrowset ...
It could go wrong if any of the Excel fields contains one of
the $x$ codes replaced later.
Creating a 4th column in Excel is also a solution:
="EXEC sp_addlogin '"& A1 & "', ... and so on
Steve Kass
Drew University
Dan Guzman wrote:
>>1. add login
>>2. set psw to generic value
>>3. set default database
>>
>4. add user to database
>5. add them to user defined role
>The required script template:
>EXEC sp_addlogin 'SomeLogin', 'SomePassword', 'SomeDatabase'
>USE MyDatabase
>EXEC sp_adduser 'SomeLogin'
>EXEC sp_addrolemember 'SomeRole', 'SomeLogin'
>Let's assume your Excel spreadsheet has 3 columns: Login, DefaultDatabase
>and Role. One method is to generate the needed script using SQL and
>OPENROWSET. The query below will generate a script for each row in the
>spreadsheet. You can then copy/paste the results into a Query Analyzer
>window and execute. The spreadsheet file needs be accessible by the SQL
>Server service.
>SELECT
>'EXEC sp_addlogin ''' +
> Login +
> ''', ''SomePassword'', ''' +
> DefaultDatabase + '''
>USE ' + DefaultDatabase + '
>EXEC sp_adduser ''' +
> Login + '''
>EXEC sp_addrolemember ''' +
> Role +
> ''', ''' +
> Login +
> ''''
>FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> 'Excel 8.0;DATABASE=c:\temp\Logins.xls',
> 'Select * from [Sheet1$]')
>
>|||Good suggestion, Steve. FWIW, I usually the token names like the following
when using this technique but that's just a personal preference.
set @.SQL = '
EXEC sp_addlogin $(Login), $(Password), $(Database)
USE $(Database)
EXEC sp_adduser $(Login)
EXEC sp_addrolemember $(Role), $(Login)
'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23okGIu7aEHA.3892@.TK2MSFTNGP10.phx.gbl...
> Possibly less prone to error is:
> CREATE PROCEDURE Process (
> @.SomeLogin sysname,
> @.SomePassword nvarchar(200) collate Latin1_General_CS_AS,
> @.SomeDatabase sysname
> ) as
> DECLARE @.SQL nvarchar(4000)
> set @.SQL = '
> EXEC sp_addlogin $L$, $P$, $D$
> USE MyDatabase
> EXEC sp_adduser $L$
> EXEC sp_addrolemember $R$, $L$
> '
> select
> replace(replace(replace(replace(@.sql,
> '$L$',quotename(Login,'''')),
> '$P$',quotename(Password,'''')),
> '$D$',quotename(Database)),
> '$R$',role)
> from openrowset ...
> It could go wrong if any of the Excel fields contains one of
> the $x$ codes replaced later.
> Creating a 4th column in Excel is also a solution:
> ="EXEC sp_addlogin '"& A1 & "', ... and so on
> Steve Kass
> Drew University
>
> Dan Guzman wrote:
> >>1. add login
> >>2. set psw to generic value
> >>3. set default database
> >>
> >>
> >
> >4. add user to database
> >5. add them to user defined role
> >
> >The required script template:
> >
> >EXEC sp_addlogin 'SomeLogin', 'SomePassword', 'SomeDatabase'
> >USE MyDatabase
> >EXEC sp_adduser 'SomeLogin'
> >EXEC sp_addrolemember 'SomeRole', 'SomeLogin'
> >
> >Let's assume your Excel spreadsheet has 3 columns: Login, DefaultDatabase
> >and Role. One method is to generate the needed script using SQL and
> >OPENROWSET. The query below will generate a script for each row in the
> >spreadsheet. You can then copy/paste the results into a Query Analyzer
> >window and execute. The spreadsheet file needs be accessible by the SQL
> >Server service.
> >
> >SELECT
> >'EXEC sp_addlogin ''' +
> > Login +
> > ''', ''SomePassword'', ''' +
> > DefaultDatabase + '''
> >USE ' + DefaultDatabase + '
> >EXEC sp_adduser ''' +
> > Login + '''
> >EXEC sp_addrolemember ''' +
> > Role +
> > ''', ''' +
> > Login +
> > ''''
> >FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> > 'Excel 8.0;DATABASE=c:\temp\Logins.xls',
> > 'Select * from [Sheet1$]')
> >
> >
> >

Import Logins from text or spreadsheet

Is there a way to create logins from a text file or
spreadsheet? I have a long list of users. They will be
Sql Server Authenicated. I need to
1. add login
2. set psw to generic value
3. set default database
4. add them to user defined role
Thanks,
Brian> 1. add login
> 2. set psw to generic value
> 3. set default database
4. add user to database
5. add them to user defined role
The required script template:
EXEC sp_addlogin 'SomeLogin', 'SomePassword', 'SomeDatabase'
USE MyDatabase
EXEC sp_adduser 'SomeLogin'
EXEC sp_addrolemember 'SomeRole', 'SomeLogin'
Let's assume your Excel spreadsheet has 3 columns: Login, DefaultDatabase
and Role. One method is to generate the needed script using SQL and
OPENROWSET. The query below will generate a script for each row in the
spreadsheet. You can then copy/paste the results into a Query Analyzer
window and execute. The spreadsheet file needs be accessible by the SQL
Server service.
SELECT
'EXEC sp_addlogin ''' +
Login +
''', ''SomePassword'', ''' +
DefaultDatabase + '''
USE ' + DefaultDatabase + '
EXEC sp_adduser ''' +
Login + '''
EXEC sp_addrolemember ''' +
Role +
''', ''' +
Login +
''''
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE=c:\temp\Logins.xls',
'Select * from [Sheet1$]')
Hope this helps.
Dan Guzman
SQL Server MVP
"Brian" <bgroves@.medibase.com> wrote in message
news:2db5001c46b6b$7f8d35d0$a601280a@.phx
.gbl...
> Is there a way to create logins from a text file or
> spreadsheet? I have a long list of users. They will be
> Sql Server Authenicated. I need to
> 1. add login
> 2. set psw to generic value
> 3. set default database
> 4. add them to user defined role
> Thanks,
> Brian|||Possibly less prone to error is:
CREATE PROCEDURE Process (
@.SomeLogin sysname,
@.SomePassword nvarchar(200) collate Latin1_General_CS_AS,
@.SomeDatabase sysname
) as
DECLARE @.SQL nvarchar(4000)
set @.SQL = '
EXEC sp_addlogin $L$, $P$, $D$
USE MyDatabase
EXEC sp_adduser $L$
EXEC sp_addrolemember $R$, $L$
'
select
replace(replace(replace(replace(@.sql,
'$L$',quotename(Login,'''')),
'$P$',quotename(Password,'''')),
'$D$',quotename(Database)),
'$R$',role)
from openrowset ...
It could go wrong if any of the Excel fields contains one of
the $x$ codes replaced later.
Creating a 4th column in Excel is also a solution:
="EXEC sp_addlogin '"& A1 & "', ... and so on
Steve Kass
Drew University
Dan Guzman wrote:

>4. add user to database
>5. add them to user defined role
>The required script template:
>EXEC sp_addlogin 'SomeLogin', 'SomePassword', 'SomeDatabase'
>USE MyDatabase
>EXEC sp_adduser 'SomeLogin'
>EXEC sp_addrolemember 'SomeRole', 'SomeLogin'
>Let's assume your Excel spreadsheet has 3 columns: Login, DefaultDatabase
>and Role. One method is to generate the needed script using SQL and
>OPENROWSET. The query below will generate a script for each row in the
>spreadsheet. You can then copy/paste the results into a Query Analyzer
>window and execute. The spreadsheet file needs be accessible by the SQL
>Server service.
>SELECT
>'EXEC sp_addlogin ''' +
> Login +
> ''', ''SomePassword'', ''' +
> DefaultDatabase + '''
>USE ' + DefaultDatabase + '
>EXEC sp_adduser ''' +
> Login + '''
>EXEC sp_addrolemember ''' +
> Role +
> ''', ''' +
> Login +
> ''''
>FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> 'Excel 8.0;DATABASE=c:\temp\Logins.xls',
> 'Select * from [Sheet1$]')
>
>|||Good suggestion, Steve. FWIW, I usually the token names like the following
when using this technique but that's just a personal preference.
set @.SQL = '
EXEC sp_addlogin $(Login), $(Password), $(Database)
USE $(Database)
EXEC sp_adduser $(Login)
EXEC sp_addrolemember $(Role), $(Login)
'
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23okGIu7aEHA.3892@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Possibly less prone to error is:
> CREATE PROCEDURE Process (
> @.SomeLogin sysname,
> @.SomePassword nvarchar(200) collate Latin1_General_CS_AS,
> @.SomeDatabase sysname
> ) as
> DECLARE @.SQL nvarchar(4000)
> set @.SQL = '
> EXEC sp_addlogin $L$, $P$, $D$
> USE MyDatabase
> EXEC sp_adduser $L$
> EXEC sp_addrolemember $R$, $L$
> '
> select
> replace(replace(replace(replace(@.sql,
> '$L$',quotename(Login,'''')),
> '$P$',quotename(Password,'''')),
> '$D$',quotename(Database)),
> '$R$',role)
> from openrowset ...
> It could go wrong if any of the Excel fields contains one of
> the $x$ codes replaced later.
> Creating a 4th column in Excel is also a solution:
> ="EXEC sp_addlogin '"& A1 & "', ... and so on
> Steve Kass
> Drew University
>
> Dan Guzman wrote:
>

Import Large Record Fixed Length Text File

I am trying to import a large record (about 13k per record) fixed length file into SQL Server. I have built a format file and would like to now import the data. My understanding is that SQL Server limits record sizes to 1024, so I should only select the columns I really need in the format file. Can I perfrom the import without having to create the table ahead of time. I may change the columns that I include an I don't want to have to keep changing the table as well as the format file.

Will the bcp or bulk insert utlility create a table based on the format file or do I just have to tuff it out and make changes in both the format file and the table and be sure that they match?SQL server only supports bcp / bulk inserts into existing tables ...|||Thanks for the info|||What version of sql are you using|||The only reason I ask is that sql 2k supports 8K records.

BTW. You could always use DTS. Which will allow you to choose which columns you import and create a table ( please if you use this don't accept the default of varchar(255) Ugh!! )|||Please post the structure of the data you are importing - data type/size ... And maybe even attach a sample of the data. You have various options like text/ntext/image when importing into sql server to help with 8k boundary - but this is highly dependent on your data structure. You can play with dts and go to transformations to change the data types.sql

Import Human-Readable text file into SQL Server 2000

Hello,

I am receiving a text file that is produced from a mainframe that is
out of my control. I am attempting to find a (hopefully clean) way to
import it into a SQL Server database in an automated fashion. I am
not really concerned about how many tables it requires or what the
schema looks like as long as the data remains related and ends up in
its respective fields (I will probably use scratch tables for this).

The data is given to me in a format that is meant to be printed out
and read by human eyes (in a text file). The format looks something
like this:

Begin File:
------------------------
1234 1234 1234 1234 XYZ Company 01/01/2003
......More stuff related to XYZ company for a couple of lines ......
......(this stuff can easily be parsed by position)......

MCARD VISA AMEX DISC
------------------------
TOTAL 11111.11 4444.44 5555.55 30.01
TRANS FEE .20 .20 .15 .15
TRANS AMOUNT 2222.22 888.89 833.33 4.50
DISC .0165 .0165 .0365 .0355
------------------------

ANOTHER HEADER

.........More stuff related to XYZ Company............

End File:

Well, this isn't the exact format, but just an example. The point is
that all of the data in each column is related and should end up in
the same record which is related to the parent record of XYZ Company
(or all in a single record in a single table if that is the closest I
can get).

Also, the rows are not always present. For example, if TRANS FEE
doesn't apply to anything in the row, then the entire row will
collapse and TRANS AMOUNT would be the next line after TOTAL.

I was looking at the bcp utility and dts, but dts doesn't seem to have
the performance capabilities (or reliability for that matter) I am
looking for. Bcp seems like it might work if there is some advanced
formatting commands that I can't find in the documentation - Anyone?

The best I can come up with is to use a high level language such as C#
or VB.NET to parse the text file into another text file that is comma
delimited, and then use the bcp utility (or bulk insert) to import it
into SQL Server where I can then use TSQL to manipulate it how I want.
I am trying to eliminate the high level language parse and just go
straight from file to database. Does anybody know an easier route?

TIAI would probably still go with the high level language. You have multiple
pieces of information in different formats relating to the same bigger
object. It looks as though you have a fixed length part at the top and then
break into a pivot table in the middle. I don't know any format file that
is going to decipher that for you.

--
--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Sven" <sstorhaug@.webuniverse.net> wrote in message
news:67ca584a.0309302103.1eb4b10e@.posting.google.c om...
> Hello,
> I am receiving a text file that is produced from a mainframe that is
> out of my control. I am attempting to find a (hopefully clean) way to
> import it into a SQL Server database in an automated fashion. I am
> not really concerned about how many tables it requires or what the
> schema looks like as long as the data remains related and ends up in
> its respective fields (I will probably use scratch tables for this).
> The data is given to me in a format that is meant to be printed out
> and read by human eyes (in a text file). The format looks something
> like this:
>
> Begin File:
> -----------------------
--
> 1234 1234 1234 1234 XYZ Company 01/01/2003
> .....More stuff related to XYZ company for a couple of lines ......
> .....(this stuff can easily be parsed by position)......
> MCARD VISA AMEX DISC
> -----------------------
--
> TOTAL 11111.11 4444.44 5555.55 30.01
> TRANS FEE .20 .20 .15 .15
> TRANS AMOUNT 2222.22 888.89 833.33 4.50
> DISC .0165 .0165 .0365 .0355
> -----------------------
--
> ANOTHER HEADER
> .........More stuff related to XYZ Company............
> End File:
>
> Well, this isn't the exact format, but just an example. The point is
> that all of the data in each column is related and should end up in
> the same record which is related to the parent record of XYZ Company
> (or all in a single record in a single table if that is the closest I
> can get).
> Also, the rows are not always present. For example, if TRANS FEE
> doesn't apply to anything in the row, then the entire row will
> collapse and TRANS AMOUNT would be the next line after TOTAL.
> I was looking at the bcp utility and dts, but dts doesn't seem to have
> the performance capabilities (or reliability for that matter) I am
> looking for. Bcp seems like it might work if there is some advanced
> formatting commands that I can't find in the documentation - Anyone?
> The best I can come up with is to use a high level language such as C#
> or VB.NET to parse the text file into another text file that is comma
> delimited, and then use the bcp utility (or bulk insert) to import it
> into SQL Server where I can then use TSQL to manipulate it how I want.
> I am trying to eliminate the high level language parse and just go
> straight from file to database. Does anybody know an easier route?
> TIA

import hierarchical data

hi folks,

I have to import hierarchical text files like:
32;country;city;postalcode;street
21;name;firstname;salutation;title;age;nickname
21;name;firstname;salutation;title;age;nickname
...

additionally I have to eleminate doubles. what is the best way for this problem ?
I have set up a flatfilesource with two columns and a conditional split on the first column
so now I have an output with [country;city;postalcode;street] and one with [name;firstname;salutation;title;age;nickname]. How do I split this in columns, put it in a dataset keeping the relations and remove doubles ?

Iam looking forward for any helping idea.

rgrds,
matze

1. Use the Sort transform to remove dupes.

2. This is just a string manipulation problem right? There is a library of string manipulation functions available in the Derived Column transform.

-Jamie

|||

Thanks for reply Jamie,

First I cannot find any functionality to split the input column into multiple output columns in Derived Colmn Transform. Second how to get this in a pair of related tables ?

please help out

|||

Have you looked at the FINDSTRING and SUBSTRING functions?

When you say the tables are related do you mean there is a foreign key between them? if so you can use 1 f the methods defined here: http://blogs.conchango.com/jamiethomson/archive/2006/02/17/2877.aspx

-Jamie

|||Hi Jamie,

Iam back on this and its driving me nuts.
You wont believe it, but Iam a absolute novice on SSIS ;)
So please let me explain more in detail and exercise paitience with me.

thats the schema:
32;country;city;postalcode;street
21;name;firstname;salutation;title;age;nickname

32 identifies a city line, 21 identifies a person line

example:
32;england;london;2445;poolstreet 45
21;smith;joe;mr;;23;jo
21;henderson;paul;mr;doctor;54;paul
32;sweden;stockholm;4356;javanstreet 2
21;smith;joe;mr;;23;jo
32;england;london;2445;poolstreet 45
21;jhonson;pieter;mr;;33;pieter

let there be a table 'city' and a table 'person' with an m:n relatation table cityperson
I want to have a result as follows:
table city
1 england london 2445 poolstreet 45
2 sweden stockholm 4356 javanstreet 2

tabel person
1 smith joe mr 23 jo
2 henderson paul mr doctor 54 paul
3 jhonson pieter mr 33 pieter

table cityperson
1 1 1
2 1 2
3 1 3
4 2 1

whereas the first column idicates an identity value

What i did so far is reading my source file with flat file source into two columns (id, line) make a conditional split on id and make two derivied column transform. One for city lines and one for person lines. In these derived column transforms I split up the line column (eg. england;london;2445;poolstreet 45) with Findstring amd Substring into separate columns for each field. Now I have the two outputs a) all columns for a city and b) all columns for a person. What to do now ? How to get this into the tables three ?
By the way isnt it quite slow to extract the columns with Findstring and Substring form a line string ? I have to deal with files of nearly 30 megabytes. Is there a better way to accomplish this ?

|||

FINDSTRING() and SUBSTRING() in the Derived Column component are the best options and fastest available to you. I don't think you'll find a bottleneck there.

I understand that you now need a way of populating CityPerson. What I don't understand is how a relationship is defined in the source file between a city and a person.

In other words....how do you know by looking at the source file which city a person is in? Is it the City line that is immediately above it?

-Jamie

|||

Jamie,

>FINDSTRING() and SUBSTRING() in the Derived Column component are the best options and fastest available to you. I don't think you'll find a bottleneck there.

ok, that pacified me

>In other words....how do you know by looking at the source file which city a person is in? Is it the City line that is immediately above it?

Exactly. You got it, its defined by the city line above.
Not realy nice, I know. Therefore my problems keeping the relation.

again thanks for your support,
Matze

|||

OK, so there are probably a million and one ways to do this - but here's one method.

First, before the split, give each row an ID using the technique here: http://www.sqlis.com/default.aspx?37

Later in the pipeline, join the Person path back with the City path using a MERGE JOIN (you'll need a MULTICAST on the Person path). But only join where the City ID is less than the Person ID field. The output fields from the MERGE JOIN should be everything from the Person input and the ID from the City input.

The output from the MERGE JOIN should then become the input to a AGGREGATE transform. Inside there group by all the Person fields and select the maximum City ID. You will then have a record for each person along with the ID of the City field. It should then be pretty easy to constuct the CityPerson table.

Another way to describe the above is that you're building the following SQL statement:

SELECT p.*, MAX(c.ID) as CityID FROM Person p, INNER JOIN City c ON p.ID > c.ID GROUP BY p.*

where p & c are the inputs to the MERGE JOIN.

Hope that helps!!

-Jamie

|||

Jamie,

it's me the greenhorn again ;)
I have set up the ID for each row, but now become desperate on Merge Join.
>But only join where the City ID is less than the Person ID field.
I do not see a option to set up this in the Merge Join component. Seems to join only on ID equals ID.
Another issue is the requirement for piplines to be sorted. Do to the fact that the merge column is the ID and the ID is sorted, could I define this circumstance anywhere ?

thanks for your help again,
Matze

|||

Easy one first. On the output from the component where you add the IDs set IsSorted=TRUE and set SortKey=1 on the ID column. The data paths will then be sorted.

You're right about the MERGE JOIN tho. I admit I thought that you could do this and I didn't actually check it. My apologies for that. That's really bothered me actually - that's a real limitation of the component.

So, we need another solution. You can probably achieve this all in a single script component actually thinking about it. It will need to be an asynchronous component positioned before your conditional split but after your script component that adds the ID.

Loop over the rows. Each time you get a new City row store its ID in a local variable and add it to each Person row until you encounter the next City record.

It will need 2 outputs - 1 for City and 1 for Person

Here's a link that might help:

Difference between synch and asynch components: http://blogs.conchango.com/jamiethomson/archive/2005/07/25/1841.aspx

Sorry I led you the wrong way on this. Hopefully this method should work.

-Jamie

|||

Hi Matze,

I've re-read this thread and realised I've led you on a bit of a merry dance. Sorry about that.

To try and make up for it I had a go at building this myself and have come up with something pretty simple that works fine. If you want to drop me a mail via the contact link on my blog homepage (address below) then I'll be able to send it to you.

-Jamie

|||

Hi,

I replied to the address from which your mail came (mail@.mhess.com) but got a mail delivery failure.

Do you wanna email me direct at jamie.thomson@.removethisbit.conchango.com

-Jamie

sql

Import from MySQL db adds padding in CHAR (Text) fields

I'm moving a small application from a MySQL db to MSSQL2K and using the Import feature of SQL2K. The db in question is simple, only three tables, mostly text (CHAR) fields. The problem is that when I examine the field contents I find that the unused cha
racter positions within the fields have been padded with trailing spaces. (Example: original 10 char field containing "ABC" now contains "ABC ". Application not happy. I've tried a number of options in the Import wizard (ansi padding etc) and it
seems to have had no effect. Any ideas? Solutions? KB articles?
Thanks for ANY help.
-J
By definition, a char(10) is always padded with spaces up to the length of the datatype. This is why we call
char a fixed length datatype. If you want a variable length datatype, use varchar.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"JManton" <anonymous@.discussions.microsoft.com> wrote in message
news:D89560CC-AAAB-459C-A039-8073C17EB2ED@.microsoft.com...
> I'm moving a small application from a MySQL db to MSSQL2K and using the Import feature of SQL2K. The db in
question is simple, only three tables, mostly text (CHAR) fields. The problem is that when I examine the
field contents I find that the unused character positions within the fields have been padded with trailing
spaces. (Example: original 10 char field containing "ABC" now contains "ABC ". Application not happy.
I've tried a number of options in the Import wizard (ansi padding etc) and it seems to have had no effect.
Any ideas? Solutions? KB articles?
> Thanks for ANY help.
> -J
|||That was it. I guess there are a few diffs between MySQL and MSSQL. I also had to tweak the recordID field and use IDENTITY attribute. Thanks for the quick reply and on-the-mark-help.
-J

Import from MySQL db adds padding in CHAR (Text) fields

I'm moving a small application from a mysql db to MSSQL2K and using the Impo
rt feature of SQL2K. The db in question is simple, only three tables, mostl
y text (CHAR) fields. The problem is that when I examine the field contents
I find that the unused cha
racter positions within the fields have been padded with trailing spaces. (E
xample: original 10 char field containing "ABC" now contains "ABC ".
Application not happy. I've tried a number of options in the Import wizar
d (ansi padding etc) and it
seems to have had no effect. Any ideas? Solutions? KB articles?
Thanks for ANY help.
-JBy definition, a char(10) is always padded with spaces up to the length of t
he datatype. This is why we call
char a fixed length datatype. If you want a variable length datatype, use va
rchar.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"JManton" <anonymous@.discussions.microsoft.com> wrote in message
news:D89560CC-AAAB-459C-A039-8073C17EB2ED@.microsoft.com...
> I'm moving a small application from a mysql db to MSSQL2K and using the Import fea
ture of SQL2K. The db in
question is simple, only three tables, mostly text (CHAR) fields. The probl
em is that when I examine the
field contents I find that the unused character positions within the fields
have been padded with trailing
spaces. (Example: original 10 char field containing "ABC" now contains "ABC
". Application not happy.
I've tried a number of options in the Import wizard (ansi padding etc) and i
t seems to have had no effect.
Any ideas? Solutions? KB articles?
> Thanks for ANY help.
> -J|||That was it. I guess there are a few diffs between mysql and MSSQL. I also
had to tweak the recordID field and use IDENTITY attribute. Thanks for the
quick reply and on-the-mark-help.
-J

Import from MySQL db adds padding in CHAR (Text) fields

I'm moving a small application from a MySQL db to MSSQL2K and using the Import feature of SQL2K. The db in question is simple, only three tables, mostly text (CHAR) fields. The problem is that when I examine the field contents I find that the unused character positions within the fields have been padded with trailing spaces. (Example: original 10 char field containing "ABC" now contains "ABC ". Application not happy. I've tried a number of options in the Import wizard (ansi padding etc) and it seems to have had no effect. Any ideas? Solutions? KB articles?
Thanks for ANY help.
-JBy definition, a char(10) is always padded with spaces up to the length of the datatype. This is why we call
char a fixed length datatype. If you want a variable length datatype, use varchar.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"JManton" <anonymous@.discussions.microsoft.com> wrote in message
news:D89560CC-AAAB-459C-A039-8073C17EB2ED@.microsoft.com...
> I'm moving a small application from a MySQL db to MSSQL2K and using the Import feature of SQL2K. The db in
question is simple, only three tables, mostly text (CHAR) fields. The problem is that when I examine the
field contents I find that the unused character positions within the fields have been padded with trailing
spaces. (Example: original 10 char field containing "ABC" now contains "ABC ". Application not happy.
I've tried a number of options in the Import wizard (ansi padding etc) and it seems to have had no effect.
Any ideas? Solutions? KB articles?
> Thanks for ANY help.
> -J

Import from Excel with IMEX=1 still gives probs

Hi:

Am trying to import XLS data into SQL 2005 SP2 thro a SSIS Data Flow task. My Excel Connection string has IMEX=1, ImportMixedTypes is set to Text and the typeguessrows is set to 0.

Import works fine for cells of Format Text, but when I have a large number (in a general Format cell) it gets converted into scientific notation(e.g. 3.234175e+7) in the table.

What am I doing wrong?

TIA

Kar

What's the type of the column in the data flow?|||

The column is of type DT_WSTR(255) in the data flow. There's no prob with cells that are textual, or are numeric with Text Format. Am only having a prob with cells with General Format I think. These cells look normal in excel, but when I import to Ole DB, I get Scientific Notation.

I have tried to save Excel as Text, and that works, but that will introduce unnecessary additional layers to build, test and maintain. Hope to find a solution within the box.

TIA

Kar

Import from Access - Datatype conversions

I have to do a lot of inporting from Access files. Is there a place where I can change the default datatype conversion for Access Text from nvarchar to varchar?

Thanks.

Kato

If you are using the import wizard then you can affect this with the mapping files at:

%PROGRAMFILES%\Microsoft SQL Server\90\DTS\MappingFiles

I'm guessing that the one you want is JetToSSIS.xml

If you are building packages manually then you can change this in the source adapter.

-Jamie

|||

Outstanding. Exactly what I was looking for.

Thank you.

sql

Import Files Do Not Exist

I have a job that calls about 5 DTS's. Each DTS imports a text file. The problem is that some of the files may not be there every day. How do I exit the DTS without an error if the file does not exist, but I want my job to keep running...how about using an activex task to check for file and if file not found then Main = DTSTaskExecResult_Success, each of this task will then trigger the respective DTS package.

import file

Hi All,
Is there any way to import a text file into a table in sql server 2005
prefarabley in its gui (MS SQL Server Management Studio)?
Thanks.
In Management Studio, right click on a database and select
Tasks, select Import Data. This will start up the SQL Server
Import and Export Wizard. For the Data Source, select the
Flat File Source and walk through the wizard from there.
-Sue
On 6 Nov 2006 13:29:00 -0800, "sara" <sarasara82@.gmail.com>
wrote:

>Hi All,
>Is there any way to import a text file into a table in sql server 2005
>prefarabley in its gui (MS SQL Server Management Studio)?
>Thanks.
sql