bcp utf8 file

I'm outputting a file from a mySQL server that contains 1 column that
has input it many languages. The base definition for the column is
UTF8.

I output the data into a flat file and bcp it to a sql2000 server, and
the multi-byte data does not survive the translation.

Any ideas on how to input a UTF8 flat file into the server using BCP?

I've tried the -n -N -w options -- no luck.

thanks,

edAssuming that your destination column is nvarchar or nchar, then -w
should be the correct option; -n or -N are specific to MSSQL, so
they're unlikely to work. I've never used bcp to import a file like
that myself, so I can't really say for sure what the solution is, but
under "Copying Data Between Different Collations" in Books Online, it
suggests using a bcp format file with specific collations. You might
also try using DTS to import the data, as it's generally a lot smarter
than bcp, however again I've never tried importing a UTF8 file with it.

Simon|||(geekboyed@.gmail.com) writes:
> I'm outputting a file from a mySQL server that contains 1 column that
> has input it many languages. The base definition for the column is
> UTF8.
> I output the data into a flat file and bcp it to a sql2000 server, and
> the multi-byte data does not survive the translation.
> Any ideas on how to input a UTF8 flat file into the server using BCP?

Since you cannot store UTF-8 data in SQL 2000 in any civilized way,
you need to first convert the file to UCS-2. (Or UTF_16 if you prefer.
I hope you are not using surrogates.)

A simple way to do this is to open the file in Notepad, and then change
encoding when you do Save As.

I'm a little uncertain how any byte-order mark would affect BCP.
Notepad is likely to add one, but I don't know if BCP is smart
enough to ignore it.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

BCP IN trouble

I try to transfert data table from one server to another one by using BCP.
The 2 servers are running SQLS2k sp3.
The outpout work fine, but the input (option: -m2 -N) in destination DB return a message:

Starting copy...
SQLState = 37000, NativeError = 156
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'Order'.
SQLState = 37000, NativeError = 156
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'Order'.

i guess that i have the keyword "order" in my .bcp file so that disturb the input command.
How can i manage this ?

PS: i tried already the -w option for out-in command

Many thanks.Can you put in your bcp statement here? -T&R, Suresh.|||here after my BCP statement:

bcp.exe "Production.dbo.[Backup_Ordersdetail]" In "C:\...\bcpfiles\Backup_Ordersdetail.bcp" -m2 -N -Sxxxxxxx -Uxx -Pxxxxxxx

BCP column insert question

HI,
I'm trying to insert records to a table using bcp command. The
problem is the input file to the bcp is a text file that looks like
this:

Text file data:
1234 abc def ghi jkl mno

Expected result:
column1 1234
column2 abc def ghi jkl mno
column3 null
column4 N

My table has four columns (column1, column2, column3, column4). I
would like 1234 to go to column1, 'abc def ghi jkl mno' go to column2,
column3 is blank and column4 is always 'N' as shown above. I setup the
column4 to be defaulted to 'N'. However, I'm getting an error
regarding string truncation because, I think, SQL server is trying to
insert the 'abc def ghi jkl mno' into different columns. So the
question is what can I do to tell the bcp utility that 'abc def ghi
jkl mno' belongs to column2?

Appreciated any help.

Thanks,
TeresaTeresa,

Try delimiting your data with a , (comma) or (pipe) |. I use the pipe
because then if there is a comma in the data it won't hose your bulk insert.

Then you can use the WITH FIELDTERMINATOR = "|" in your Bulk Insert. It
should map correctly once it is delimited.

Here is an example from the Transact SQL online:

BULK INSERT Northwind.dbo.[Order Details]
FROM 'f:\orders\lineitem.tbl'
WITH
(
FIELDTERMINATOR = '|',
)
Hope this helps!

Barry

"TThai" <tpthai@.pepco.com> wrote in message
news:7fedd9b2.0410210736.11593f4c@.posting.google.c om...
> HI,
> I'm trying to insert records to a table using bcp command. The
> problem is the input file to the bcp is a text file that looks like
> this:
> Text file data:
> 1234 abc def ghi jkl mno
> Expected result:
> column1 1234
> column2 abc def ghi jkl mno
> column3 null
> column4 N
> My table has four columns (column1, column2, column3, column4). I
> would like 1234 to go to column1, 'abc def ghi jkl mno' go to column2,
> column3 is blank and column4 is always 'N' as shown above. I setup the
> column4 to be defaulted to 'N'. However, I'm getting an error
> regarding string truncation because, I think, SQL server is trying to
> insert the 'abc def ghi jkl mno' into different columns. So the
> question is what can I do to tell the bcp utility that 'abc def ghi
> jkl mno' belongs to column2?
> Appreciated any help.
> Thanks,
> Teresa|||"Barry Young" <youngbar@.insightbb.com> wrote in message news:<gY_dd.226559$wV.94703@.attbi_s54>...
> Teresa,
> Try delimiting your data with a , (comma) or (pipe) |. I use the pipe
> because then if there is a comma in the data it won't hose your bulk insert.
> Then you can use the WITH FIELDTERMINATOR = "|" in your Bulk Insert. It
> should map correctly once it is delimited.
> Here is an example from the Transact SQL online:
> BULK INSERT Northwind.dbo.[Order Details]
> FROM 'f:\orders\lineitem.tbl'
> WITH
> (
> FIELDTERMINATOR = '|',
> )
> Hope this helps!
> Barry
> "TThai" <tpthai@.pepco.com> wrote in message
> news:7fedd9b2.0410210736.11593f4c@.posting.google.c om...
> > HI,
> > I'm trying to insert records to a table using bcp command. The
> > problem is the input file to the bcp is a text file that looks like
> > this:
> > Text file data:
> > 1234 abc def ghi jkl mno
> > Expected result:
> > column1 1234
> > column2 abc def ghi jkl mno
> > column3 null
> > column4 N
> > My table has four columns (column1, column2, column3, column4). I
> > would like 1234 to go to column1, 'abc def ghi jkl mno' go to column2,
> > column3 is blank and column4 is always 'N' as shown above. I setup the
> > column4 to be defaulted to 'N'. However, I'm getting an error
> > regarding string truncation because, I think, SQL server is trying to
> > insert the 'abc def ghi jkl mno' into different columns. So the
> > question is what can I do to tell the bcp utility that 'abc def ghi
> > jkl mno' belongs to column2?
> > Appreciated any help.
> > Thanks,
> > Teresa

Hi Barry,
Appreciated your response. I'll try it and keep you posted. Have a good day.

Thanks,
Teresa|||"Barry Young" <youngbar@.insightbb.com> wrote in message news:<gY_dd.226559$wV.94703@.attbi_s54>...
> Teresa,
> Try delimiting your data with a , (comma) or (pipe) |. I use the pipe
> because then if there is a comma in the data it won't hose your bulk insert.
> Then you can use the WITH FIELDTERMINATOR = "|" in your Bulk Insert. It
> should map correctly once it is delimited.
> Here is an example from the Transact SQL online:
> BULK INSERT Northwind.dbo.[Order Details]
> FROM 'f:\orders\lineitem.tbl'
> WITH
> (
> FIELDTERMINATOR = '|',
> )
> Hope this helps!
Hi Barry,
I just tried and am getting an error 'Server: Msg 4860, Level 16,
State 1, Line 1
Could not bulk insert. File 'C:\transactions.txt' does not exist.' Is
there any preliminary setup that I have to do to recognize the file?
Here is what I executed in SQL analyzer.

BULK INSERT xxx_TEST.DBO.TRANSACTION_CORRECTION
FROM 'C:\transactions.txt'
with
(FIELDTERMINATOR = '|')

Thanks,
Teresa

> Barry
> "TThai" <tpthai@.pepco.com> wrote in message
> news:7fedd9b2.0410210736.11593f4c@.posting.google.c om...
> > HI,
> > I'm trying to insert records to a table using bcp command. The
> > problem is the input file to the bcp is a text file that looks like
> > this:
> > Text file data:
> > 1234 abc def ghi jkl mno
> > Expected result:
> > column1 1234
> > column2 abc def ghi jkl mno
> > column3 null
> > column4 N
> > My table has four columns (column1, column2, column3, column4). I
> > would like 1234 to go to column1, 'abc def ghi jkl mno' go to column2,
> > column3 is blank and column4 is always 'N' as shown above. I setup the
> > column4 to be defaulted to 'N'. However, I'm getting an error
> > regarding string truncation because, I think, SQL server is trying to
> > insert the 'abc def ghi jkl mno' into different columns. So the
> > question is what can I do to tell the bcp utility that 'abc def ghi
> > jkl mno' belongs to column2?
> > Appreciated any help.
> > Thanks,
> > Teresa|||TThai (tpthai@.pepco.com) writes:
> Hi Barry,
> I just tried and am getting an error 'Server: Msg 4860, Level 16,
> State 1, Line 1
> Could not bulk insert. File 'C:\transactions.txt' does not exist.' Is
> there any preliminary setup that I have to do to recognize the file?
> Here is what I executed in SQL analyzer.
> BULK INSERT xxx_TEST.DBO.TRANSACTION_CORRECTION
> FROM 'C:\transactions.txt'
> with
> (FIELDTERMINATOR = '|')

BULK INSERT operates on the server, so it is looking a C:\ at your server.
If your file is on a client machine, you are better off with BCP. You
can specify field terminator with the -t options. Since | is a meta-
character for the command shell, you need to quote it:

bcp db..tbl in yourfile.txt -c -t "|" -S ...

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns958BF277E1611Yazorman@.127.0.0.1>...
> TThai (tpthai@.pepco.com) writes:
> > Hi Barry,
> > I just tried and am getting an error 'Server: Msg 4860, Level 16,
> > State 1, Line 1
> > Could not bulk insert. File 'C:\transactions.txt' does not exist.' Is
> > there any preliminary setup that I have to do to recognize the file?
> > Here is what I executed in SQL analyzer.
> > BULK INSERT xxx_TEST.DBO.TRANSACTION_CORRECTION
> > FROM 'C:\transactions.txt'
> > with
> > (FIELDTERMINATOR = '|')
> BULK INSERT operates on the server, so it is looking a C:\ at your server.
> If your file is on a client machine, you are better off with BCP. You
> can specify field terminator with the -t options. Since | is a meta-
> character for the command shell, you need to quote it:
> bcp db..tbl in yourfile.txt -c -t "|" -S ...

Thank you very much. It worked!

Teresa

BCP character conversion

I bcped in data into a database with SQL_Latin1_General_Cp1_CI_AS collation. The input data has an embedded character (ascii 174). I did not specify any code page using the -C parm. The data was converted to character (ascii 171). I ran the bcp trying -C1252 and -CRAW and both maintained the correct character. -C437 and -COEM change the character to .
Why did this happen? I thought that data would be converted to correctly without any code page specification.Different code pages map binary values to glyphs (the graphic symbols that humans know and love) differently. One binary value can map to many different glyphs using different code pages.

If BPC doesn't know which code page to use for translation, you get "pot luck", especially for characters that aren't well defined. Typically, you want the code page that created the data. Occaisionally, you want the code page that was intended (or at least used) to view the data. Because of the pot-pouri of mappings supported by the different code pages, the business of getting data from point A to point B has grown yet another potentially "interesting" twist to amuse those of us that do the moving!

-PatP|||Thanks for the quick reply.
BOL states:
"When bulk copying data using native or character format, bcp, by default, converts character data to:

OEM code page characters when exporting data from an instance of Microsoft SQL Server.

ANSI/Microsoft Windows code page characters when importing data into an instance of SQL Server. "

So wouldn't the bcp in use code page 1252 by default. This should be similar to -C1252.|||Well where did the data come from?|||The bcp ran from my workstation with a code page 437 - if that's your question.|||From Books Online topic bcp: "OEM Default code page used by the client. This is the default code page used by bcp if -C is not specified."
From that I suppose that SQL Server interpreted your file as being OEM 437 CP. mojza|||I guess I still don't understand why the character was changed during the bcp. I can view it correctly from my workstation which is 437, but if I bcp using -C437 or without -C(which uses default OEM code page) it gets converted. I think I'm missing something.|||In what editor can you see that character correctly? in ANSI (e.g.Notepad) or in OEM (e.g.Edit)? mojza|||Correctly in notepad or textpad, not correctly in edit. So bcp, running in a command window, is using 437 which changes the character to ?|||Then, in my opinion, your file was created in code page ANSI 1252 (notepad ok) and bcp interprets your file as cp 437 (default client OEM code page). That leads to a loss of some extended characters that are not compatible between these two pages unless you tell sql server to interpret him as 1252 or without any translation (RAW). Check out this Microsoft article. There is a good explanation and excellent examples. mojza

http://support.microsoft.com/default.aspx?scid=kb;en-us;199819|||Thanks for your help. That article definitely helped explain things. I also looked at the nls files for 437 and 1252 and character 174(offset x0178) reflects in the 1252 file and int 437 file.
Again, thanks.

BCP - Hint exceeds

Query hints exceed maximum command buffer size of 1023 bytes(1029 bytes input).
i tried a long query in BCP because i have to insert a character to one of its fields.
hope somebody had encountered and solved this (sql server 2000). thanks in advance..what's your bcp command line?|||here...

bcp "Select 'A'+Customer_Code, PFW_Customer_Code, Newsys_Customer_Code, SBT_Customer_Code, BM_Customer_Code,

Customer_Class_Code, Customer_Company_Name, Customer_Market_Name, Contact_Person_Name, Contact_Person_Position,

Contact_Person_Contact_Number, Contact_Person_Fax_Number, Contact_Person_Email_Address, Customer_Delivery_Address,

Customer_Market_Type_Code, Customer_Market_Cluster_Code, Customer_Level_Code, Customer_Location_Code,

Customer_Route_Name, Customer_CreditLimit, Customer_Status, Customer_Status_Decription, Customer_Auto_Approve,

Customer_Set_Code, Customer_Receipt_Type, Customer_Outstanding_Balance, Customer_Available_Balance,

Customer_Last_Order_Date, Customer_Last_Order_Amount, NewSys_Customer_Salesman_Code, OnHold_Status, OnHold_Date,

OnHold_Remarks, AR_CashAccountCode, AR_CashAccountDesciption, Create_User_Code, Create_Date, Modify_User_Code,

Modify_Date, Status, SpareField1, SpareField2, Remarks1, Remarks2, newsys_terms_fpm, newsys_terms_gp,

newsys_terms_ham from CDO_MAIN..GenMKT_Customer_Masterfile" out c:\GenMKT_Customer_Masterfile.txt -t, -f

c:\GenMKT_Customer_Masterfile.xml -S10.10.1.8 -Usa -Psa|||Perhaps you could make that into a view in SQL Server, and just bcp out the view?|||you should be using queryout, not out.|||wrong code, i already replaced it with queryout but still fails...thus view in sql server runs bcp?|||in that case I would do as mcrowley suggests. create a view, and then run this bcp:

bcp CDO_MAIN..YourView out c:\GenMKT_Customer_Masterfile.txt -t, -f c:\GenMKT_Customer_Masterfile.xml -S10.10.1.8 -Usa -Psa|||Thanks a lot MCrowley & jezemine!!!|||-Usa -Psa
But first you should change the sa-password, put it in a vault somewhere and only use it in emergencies... :angel:

batch update in stored procedure

The following code is a part of my stored procedure MySP. I would like to update Users table with input variable @.userIDs which has the following format:

101, 102, 103

I got the error message:

Syntax error converting the varchar value '101, 102, 103' to a column of data type int.

Obviously, UserID has datatype int. How can I write this SP?

CREATE PROCEDURE dbo.MySP
@.userIDs varchar(100)
AS
SET NOCOUNT ON

BEGIN TRANSACTION

UPDATE Users SET IsActive = 1 WHERE UserID IN (@.userIDs)

IF @.@.ERROR <> 0
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION

SET NOCOUNT OFFOriginally posted by gyuan
Obviously, UserID has datatype int.

Obviousley it doesn't...

Check this out:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830&SearchTerms=udf,csv,string

Use the UDF that's posted in there|||You could use dynamic query - sp_executesql or execute it.|||Originally posted by Brett Kaiser
Use the UDF that's posted in there

Brett,

I read the posters from the link you posted but I'm not very clear how to use UDF on my stored procedure since UDF is new to me. Can you give me some hint? UserID is the primary key in the table Users and its datatype is int. Thanks.|||Cut and paste the following code in to QA and run it...

USE Northwind
GO

CREATE FUNCTION CSVTable(@.Str varchar(7000))
RETURNS @.t table (numberval int, stringval varchar(100), DateVal datetime)
AS
BEGIN
DECLARE @.i int, @.c varchar(100);
SELECT @.Str = @.Str + ',', @.i = 1, @.c = '';
WHILE @.i <= LEN(@.Str)
BEGIN
IF substring(@.Str,@.i,1) = ','
BEGIN
INSERT INTO @.t(numberval, stringval, DateVal)
VALUES ( CASE WHEN ISNUMERIC(@.c)=1 THEN @.c ELSE Null END
, RTRIM(LTRIM(@.c))
, CASE WHEN ISDATE(@.c)=1 THEN @.c ELSE Null END)
SET @.c = ''
END
ELSE

SET @.c = @.c + substring(@.Str,@.i,1)
SET @.i = @.i +1
END
RETURN
END
GO

CREATE TABLE myTable99(Col1 int IDENTITY(1,1), Col2 int)
GO

INSERT INTO myTable99(Col2)
SELECT 100 UNION ALL SELECT 101 UNION ALL SELECT 102 UNION SELECT 103 UNION ALL
SELECT 104 UNION ALL SELECT 105 UNION ALL SELECT 106 UNION SELECT 107 UNION ALL
SELECT 108 UNION ALL SELECT 109 UNION ALL SELECT 110
GO

DECLARE @.userIDs varchar(100)
SELECT @.userIDs = '101,102,103'

SELECT *
FROM myTable99
WHERE Col2 IN (SELECT StringVal FROM dbo.CSVTable(@.userIDs))
GO

DROP FUNCTION CSVTable
DROP TABLE myTable99
GO

Thank you Dr. Cross Join

http://www.sqlteam.com/forums/pop_profile.asp?mode=display&id=6859|||I run it and get the result I need. So now do I need to add the function to the stored procedure?|||Just make you're WHERE Clause look like mine...

Just need to make sure the udf is in the same enviroment where you release the procedure...

Get it?|||Yes, it works. That is a great function. Thank you, Brett.

By the way, can you tell me what the difference is between UDF and dynamic SQL? Performance?|||Yeah, go to the link and give thanks to Jeff..

Use dynamic sql as a last resort...and never in application code...

I use it for admin support...but it's not released...

Batch Process

I have a problem running a batch file, now the problem is that when i run the batch file the command prompts the user for an input, but I have all the output of the bat file going into a log file. So when i run the bat file the process just sits there until i hit the 'y' key or unless i nput sumthin manually. This is a problem becuase this batch file is running on the UAT server as a job and there is no one there to input once the job is running. The commnd in the batch only requires an input once a month.

for eg if run: launch_scrt.bat and i want to put sum parameters such as 'y' or 'n' to avoid the manual input once the job is running. Any ideas?

Thanks in advanceyou can have environment variable, parameter file, or the presence/abscence of the file to replace those values.|||I may be able to help you if you provide a little code as where it gets stuck for response ?|||For example:

Say my batch test.bat file contains the following:

del test.txt /P

now this command asks for confirmation from the user whether to delete the file or not and it could wait for input forever. So is there way to have 'y' or 'n' in the batch file that could be changed depending on the user running this batch process?|||/P is used to Prompt "y", "N" .. remove that and if that doesn't work use /Q

: /Q Quiet mode, do not ask if ok to delete on global wildcard|||Originally posted by aashu
/P is used to Prompt "y", "N" .. remove that and if that doesn't work use /Q

: /Q Quiet mode, do not ask if ok to delete on global wildcard

but i want to use /P and send 'y' or 'n' as a parameter.|||but i want to use /P and send 'y' or 'n' as a parameter.

Now you are probably getting into a realm where you should use a better scripting-language. Not too many are available in Windows environments but there are bound to be some. Python, for example.|||Well i got simple script in my bat process:
---------
REM ..Batch file...begins
REM ......Delete file......

if %1 == N goto Bottom
if %1 == Y goto TOP

:TOP
echo delete
del rundts.bat /q
GOTO END

:Bottom
echo dont delete
GOTO END

:END
REM ..Batch file...End
----------

but now the problem is that the command I'm using instead of del is a different exe and this program is being run through command prompt. This program generates hostnames. But once every month the hostnames are to be downloaded from the interent. So once every month some one has to sit on the server where this process is running and enter 'y' or 'n'. Before downloading the hostnames from the internet it prompts the user for a 'y' or 'n'. So onec a month this has to be done manually (which is a pain). So thats why I wanted to write a script so that this can be avoided.|||why don't to keep 2 batch files one with Y and another with N

Batch insert (OLAP)

Hi can any one help me with the skeleton script (sample one)of running Bulk insert in batches...... I need to do it in batches as the input data is huge....

The logic is I have to insert thru bcp in fact table...

After that batch execution for 50,000 thousand record... wise... if any of the batch failes i need to identify and have to rerun from that point onwards..... this is OLAP thing...what's the data source?

50,000 ain't that much btw|||50,000 record comes in one batch that waht I meant...... so if there is 1 million record 20 batches will be there...

The source come from DB2 or so which we get it as source file... we create staging table for that in Sql Server... Now we need to do the rest porting data to Fact & Dimension tables|||what script?
you can use create a failover process in dts to allow x number of error rows to pass through the ETL and then you can clean them up the next day if you prefer. its part of the error reporting process in dts tasks.|||So the data is already in a table and you're trying to limit the impact to the logs...can you describe your process how are you building your warehouse..|||:eek: I didnt get u I never asked for error reporting stuff!!!!!|||http://www.winnetmag.com/article/articleid/42903/42903.html

I was trying to do the same way how the above article describe to solve such kinda stuff

Basic Text File Data Flow Source

In DTS 2000 I had a situation where I had a text file as input source and text file as output source. On migrating the package to 2005 it puts a wrapper around it which executes it as a 2000 package, the rest of the tasks are neatly converted to 2005 style tasks. I presume this to mean that this will not be supported through to the next version, and there is no direct equivalent in 2005.

My question is how do I import a non-flat file source which has different numbers of columns per line. I did ,somehow, manage to do this with 2000 but cannot seem to get anywhere with 2005.

The flat file source seems to be expecting a common number of columns and just can't seem to cope with no column delimiters on some lines. If anybody knows different I would be glad to hear about it.

Raw data is not helpful to me as only works with a specific raw type (apparently)

Went onto Bulk Insert Task but got this message

[Bulk Insert Task] Error: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.Bulk load: An unexpected end of file was encountered in the data file.".

Have already browsed with this on web but only find comments about changing timeout setting.

Can find timeout settings in DataFlow source and DataFlow destinations but not in Bulk Insert Task.

As you can see this is a long and protracted question.

If the answer is simple I apologise if not blame Microsoft. Other than that have found SSIS has some nice improvements, apart from the odd vague error message I keep coming across.

Set up your flat file source to read in one big long line. Then inside your data flow, use a derived column to substring() out your different columns. OR you can use a conditional split to move your different record layouts to their own flat files, to be read inside another data flow.

Search around this forum for conversations and examples on this topic.|||

I am certainly no expert, but there have been a few discussions on getting around the flat file import with variable columns. After a very brief search, I would suggest that you see the following thread:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1134754&SiteID=1

As well as:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1546075&SiteID=1

As to your other question, I am not certain of the bulk insert task error that you are getting, but I am sure someone else will be along to answer it for you shortly.

:-)

(Of course there is a reply in the time it takes me to type this up....)

|||

Have been looking into this answer.

Seem to have hit a row limit. Imports the first 158383 lines out of 665525 and then stops. Have had a brief browse to see if there is a number of rows limit but other than someone mentioning a million lines in a flat file have found no other information.

Can anybody confirm this number or can this problem be remedied.

|||

Well, I know that the limit, if there is one, is not as low as 158383 as I have retrieved over 700k lines from a flat file. (I do not believe that there is an upper bound on the number of rows a flat file source can handle)

Have you turned on logging to see if there is a problem with the import? Or, have you set up an event handler or anything of that nature? I would have to think that there is something wrong with the data...

|||

With a bit of playing around I found out that if I set the second column to text only 158383 get copied into the table whereas with varchar(255) 665517 get copied.

Looking into the scripting solution but so far have only got 15 columns of blanks being written. Tried putting in breakpoints but must be missing something really basic as not breaking and when going back into script breakpoint vanished. I am using this event

PublicOverridesSub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

as set up by SSIS I presume this is set up to work with each row received from the data source. Am I wrong? Am I missing some settings?

|||Obviously replied to wrong one earlier for previous post see up a couple of messages. Have partially discovered what is wrong. Am correctly applying data to right output columns. However inital column data is wrong. I seem to trying to split the Row.Column1.ToString which returns the type blob? A bit puzzled here. Having great difficulty locating where the column data actually is so I can split it.|||

Madhattuer wrote:

Obviously replied to wrong one earlier for previous post see up a couple of messages. Have partially discovered what is wrong. Am correctly applying data to right output columns. However inital column data is wrong. I seem to trying to split the Row.Column1.ToString which returns the type blob? A bit puzzled here. Having great difficulty locating where the column data actually is so I can split it.

How are you trying to do this?

# Delimiter character is ";" in this example; change to what you need:
Dim myStringSplit as String() = Split(Row.Column1.ToString, ';')|||If it is a blob(and it would be if you set the type to DT_TEXT or if the data is binary), you have to read the data using the GetBlobData method, and then convert it to a string, before applying the split function.|||

jwelch wrote:

If it is a blob(and it would be if you set the type to DT_TEXT or if the data is binary), you have to read the data using the GetBlobData method, and then convert it to a string, before applying the split function.

Ah yes... And in converting that blob data to a string, won't the developer have to split that blob stream up into 4,000 byte chunks? (That is, assuming the input string length is more than 4,000 characters...)|||

Yes I would split using the method you described earlier.

Yes correctly identified that the input column to the scripting was of type TEXT.

I did notice the GetBlobData and noticed that it returned in byte form. I might have another brief glance at this. Hopefully 255 characters should be enough. On one of the files the largest column on one of the rows has a maximum of 60 but the other columns shouldn't really get as big as this.

Thanks for all the input.

|||DT_STR will give you 4,000 characters to work with. As long as each row length is less than 4,000 characters, that's what I'd use. One caveat is that if you have embedded carriage returns/line feeds/NULLs, you'll have to read in the row as DT_TEXT (binary data).|||

Whoops showing my ignorance, mind obviously still set on old 255 limits.

Ah 4000 hence your comments on the [TEXT] earler assuming length 4000 plus.

Will be using crlf as end of line so I don't thinks any of those get passed through. There are no NULLS. Thanks this has saved me a lot of time and effort.