显示标签为“dts”的博文。显示所有博文
显示标签为“dts”的博文。显示所有博文

2012年3月29日星期四

BCP/DTS/cmdshell problem

Hi
Using 2000
I am writing a .cmd file for Bulk copying data(about 25 tables of 1 million
rows each). I need your help and advice on this
1)is dts faster than BCP converting to flatfiles and again copying to
destination tables.
2) if we write cmdshell and use BCP in that instead of directly using in
.cmd file, will performance be slower. I am new to .cmd so controlling and
error handling will be easier if I write in a sp which uses cmd shell to
extract bcp
3. any resource on writing .cmd files using osql and bcp( templates etc). I
googled but no use.
--
Thanks
DevaHi deva
I think you will get a better answer if you try the DTS discussion forum
"DEva" wrote:

> Hi
> Using 2000
> I am writing a .cmd file for Bulk copying data(about 25 tables of 1 millio
n
> rows each). I need your help and advice on this
> 1)is dts faster than BCP converting to flatfiles and again copying to
> destination tables.
> 2) if we write cmdshell and use BCP in that instead of directly using in
> .cmd file, will performance be slower. I am new to .cmd so controlling and
> error handling will be easier if I write in a sp which uses cmd shell to
> extract bcp
> 3. any resource on writing .cmd files using osql and bcp( templates etc).
I
> googled but no use.
> --
> Thanks
> Deva

2012年3月27日星期二

bcp vs. dts, native sql bcp, text etc.?

One of my upcoming projects is going to be to move some data & its
related heavy processing off an production server off to a newer
server, do the processing, and then move the data back. There will be
large amounts of data going out & going in. My question deals with
efficiency of this data movement. I read in BOL that for exporting,
bcp is 3 to 6 times faster than DTS. But it didn't say whether or not
this data was coming out of SQL Server to a flat text file or bcp'ing
over to another SQL instance. So I'm not sure if I should bcp out to
text, then bcp in to my other server...or just bcp directly to the
other server. Also, I suppose I should not even consider DTS, since
bcp is just as fast, and there won't be any significant transformations
on the data? Opinions?BCP is used between a server and a file, not a server and a server.
<unc27932@.yahoo.com> wrote in message
news:1122399390.700953.95590@.z14g2000cwz.googlegroups.com...
> One of my upcoming projects is going to be to move some data & its
> related heavy processing off an production server off to a newer
> server, do the processing, and then move the data back. There will be
> large amounts of data going out & going in. My question deals with
> efficiency of this data movement. I read in BOL that for exporting,
> bcp is 3 to 6 times faster than DTS. But it didn't say whether or not
> this data was coming out of SQL Server to a flat text file or bcp'ing
> over to another SQL instance. So I'm not sure if I should bcp out to
> text, then bcp in to my other server...or just bcp directly to the
> other server. Also, I suppose I should not even consider DTS, since
> bcp is just as fast, and there won't be any significant transformations
> on the data? Opinions?
>|||OK - now that I feel like an idiot.....Should I bcp out, then in to
the other SQL Server, or would a DTS data move be more efficient?|||If you are talking about huge amounts of data, that require no
transformations, then probably BCP. Small data/ transformations probably
DTS. Try out both and see.
<unc27932@.yahoo.com> wrote in message
news:1122401357.578369.275800@.o13g2000cwo.googlegroups.com...
> OK - now that I feel like an idiot.....Should I bcp out, then in to
> the other SQL Server, or would a DTS data move be more efficient?
>|||BCP can be made to work between servers too but I came to know its not a
good practice.
Unc,
Refer this article to give you a head start.
http://www.mssqlcity.com/Tips/bulk_...ptimization.htm
"ChrisR" wrote:

> BCP is used between a server and a file, not a server and a server.
>
> <unc27932@.yahoo.com> wrote in message
> news:1122399390.700953.95590@.z14g2000cwz.googlegroups.com...
>
>

bcp vs. dts, native sql bcp, text etc.?

One of my upcoming projects is going to be to move some data & its
related heavy processing off an production server off to a newer
server, do the processing, and then move the data back. There will be
large amounts of data going out & going in. My question deals with
efficiency of this data movement. I read in BOL that for exporting,
bcp is 3 to 6 times faster than DTS. But it didn't say whether or not
this data was coming out of SQL Server to a flat text file or bcp'ing
over to another SQL instance. So I'm not sure if I should bcp out to
text, then bcp in to my other server...or just bcp directly to the
other server. Also, I suppose I should not even consider DTS, since
bcp is just as fast, and there won't be any significant transformations
on the data? Opinions?BCP is used between a server and a file, not a server and a server.
<unc27932@.yahoo.com> wrote in message
news:1122399390.700953.95590@.z14g2000cwz.googlegroups.com...
> One of my upcoming projects is going to be to move some data & its
> related heavy processing off an production server off to a newer
> server, do the processing, and then move the data back. There will be
> large amounts of data going out & going in. My question deals with
> efficiency of this data movement. I read in BOL that for exporting,
> bcp is 3 to 6 times faster than DTS. But it didn't say whether or not
> this data was coming out of SQL Server to a flat text file or bcp'ing
> over to another SQL instance. So I'm not sure if I should bcp out to
> text, then bcp in to my other server...or just bcp directly to the
> other server. Also, I suppose I should not even consider DTS, since
> bcp is just as fast, and there won't be any significant transformations
> on the data? Opinions?
>|||OK - now that I feel like an idiot.....Should I bcp out, then in to
the other SQL Server, or would a DTS data move be more efficient?|||If you are talking about huge amounts of data, that require no
transformations, then probably BCP. Small data/ transformations probably
DTS. Try out both and see.
<unc27932@.yahoo.com> wrote in message
news:1122401357.578369.275800@.o13g2000cwo.googlegroups.com...
> OK - now that I feel like an idiot.....Should I bcp out, then in to
> the other SQL Server, or would a DTS data move be more efficient?
>|||BCP can be made to work between servers too but I came to know its not a
good practice.
Unc,
Refer this article to give you a head start.
http://www.mssqlcity.com/Tips/bulk_copy_optimization.htm
"ChrisR" wrote:
> BCP is used between a server and a file, not a server and a server.
>
> <unc27932@.yahoo.com> wrote in message
> news:1122399390.700953.95590@.z14g2000cwz.googlegroups.com...
> > One of my upcoming projects is going to be to move some data & its
> > related heavy processing off an production server off to a newer
> > server, do the processing, and then move the data back. There will be
> > large amounts of data going out & going in. My question deals with
> > efficiency of this data movement. I read in BOL that for exporting,
> > bcp is 3 to 6 times faster than DTS. But it didn't say whether or not
> > this data was coming out of SQL Server to a flat text file or bcp'ing
> > over to another SQL instance. So I'm not sure if I should bcp out to
> > text, then bcp in to my other server...or just bcp directly to the
> > other server. Also, I suppose I should not even consider DTS, since
> > bcp is just as fast, and there won't be any significant transformations
> > on the data? Opinions?
> >
>
>

bcp vs. dts, native sql bcp, text etc.?

One of my upcoming projects is going to be to move some data & its
related heavy processing off an production server off to a newer
server, do the processing, and then move the data back. There will be
large amounts of data going out & going in. My question deals with
efficiency of this data movement. I read in BOL that for exporting,
bcp is 3 to 6 times faster than DTS. But it didn't say whether or not
this data was coming out of SQL Server to a flat text file or bcp'ing
over to another SQL instance. So I'm not sure if I should bcp out to
text, then bcp in to my other server...or just bcp directly to the
other server. Also, I suppose I should not even consider DTS, since
bcp is just as fast, and there won't be any significant transformations
on the data? Opinions?
BCP is used between a server and a file, not a server and a server.
<unc27932@.yahoo.com> wrote in message
news:1122399390.700953.95590@.z14g2000cwz.googlegro ups.com...
> One of my upcoming projects is going to be to move some data & its
> related heavy processing off an production server off to a newer
> server, do the processing, and then move the data back. There will be
> large amounts of data going out & going in. My question deals with
> efficiency of this data movement. I read in BOL that for exporting,
> bcp is 3 to 6 times faster than DTS. But it didn't say whether or not
> this data was coming out of SQL Server to a flat text file or bcp'ing
> over to another SQL instance. So I'm not sure if I should bcp out to
> text, then bcp in to my other server...or just bcp directly to the
> other server. Also, I suppose I should not even consider DTS, since
> bcp is just as fast, and there won't be any significant transformations
> on the data? Opinions?
>
|||OK - now that I feel like an idiot.....Should I bcp out, then in to
the other SQL Server, or would a DTS data move be more efficient?
|||If you are talking about huge amounts of data, that require no
transformations, then probably BCP. Small data/ transformations probably
DTS. Try out both and see.
<unc27932@.yahoo.com> wrote in message
news:1122401357.578369.275800@.o13g2000cwo.googlegr oups.com...
> OK - now that I feel like an idiot.....Should I bcp out, then in to
> the other SQL Server, or would a DTS data move be more efficient?
>
|||BCP can be made to work between servers too but I came to know its not a
good practice.
Unc,
Refer this article to give you a head start.
http://www.mssqlcity.com/Tips/bulk_c...timization.htm
"ChrisR" wrote:

> BCP is used between a server and a file, not a server and a server.
>
> <unc27932@.yahoo.com> wrote in message
> news:1122399390.700953.95590@.z14g2000cwz.googlegro ups.com...
>
>
sql

2012年3月22日星期四

BCP question

Hi Guys
I have a client who was having issues importing from a text file (Created by
there mainframe ) into SQL server, using DTS it would only import a certain
number of the rows (like 10 000 out of a million) we hare now trying to use
BCP and see the following problem below:
C:\>bcp CARD..AVAAF_ABSA_Employee in
c:\1ECLS_EMP.txt -fc:\1formatfile.fmt -Sman
netjie -Usa -Ppassword
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading BCP
format file
This is the format of the clients format file:
8.0
25
1 SQLCHAR 2 2 "\0" 0 num1
SQL_Latin1_General_Cp437_BIN
2 SQLCHAR 2 7 "\0" 10 Employee_no
SQL_Latin1_General_Cp437_BIN
3 SQLCHAR 2 7 "\0" 1 AB_Number
SQL_Latin1_General_Cp437_BIN
4 SQLCHAR 2 30 "\0" 3 Surname
SQL_Latin1_General_Cp437_BIN
5 SQLCHAR 2 20 "\0" 4 Name SQL_Latin1_General_Cp437_BIN
6 SQLCHAR 2 10 "\0" 2 Initials
SQL_Latin1_General_Cp437_BIN
7 SQLCHAR 2 20 "\0" 0 num2
SQL_Latin1_General_Cp437_BIN
8 SQLCHAR 2 50 "\0" 11 EmailAddress
SQL_Latin1_General_Cp437_BIN
9 SQLCHAR 2 12 "\0" 0 num3
SQL_Latin1_General_Cp437_BIN
10 SQLCHAR 2 10 "\0" 0 num4
SQL_Latin1_General_Cp437_BIN
11 SQLCHAR 2 40 "\0" 0 num5
SQL_Latin1_General_Cp437_BIN
12 SQLCHAR 2 4 "\0" 0 num6
SQL_Latin1_General_Cp437_BIN
13 SQLCHAR 2 40 "\0" 0 num7
SQL_Latin1_General_Cp437_BIN
14 SQLCHAR 2 20 "\0" 5 TelephoneNumber SQL_Latin1_General_Cp437_BIN
15 SQLCHAR 2 20 "\0" 7 FaxNumber SQL_Latin1_General_Cp437_BIN
16 SQLCHAR 2 12 "\0" 6 CellularNumber SQL_Latin1_General_Cp437_BIN
17 SQLCHAR 2 56 "\0" 0 num8
SQL_Latin1_General_Cp437_BIN
18 SQLCHAR 2 40 "\0" 9 DomicileAdddress SQL_Latin1_General_Cp437_BIN
19 SQLCHAR 2 40 "\0" 0 num9
SQL_Latin1_General_Cp437_BIN
20 SQLCHAR 2 10 "\0" 0 num10
SQL_Latin1_General_Cp437_BIN
21 SQLCHAR 2 4 "\0" 0 num11
SQL_Latin1_General_Cp437_BIN
22 SQLCHAR 2 40 "\0" 0 num12
SQL_Latin1_General_Cp437_BIN
23 SQLCHAR 2 40 "\0" 0 num13
SQL_Latin1_General_Cp437_BIN
24 SQLCHAR 2 2 "\0" 0 num14
SQL_Latin1_General_Cp437_BIN
25 SQLCHAR 2 16 "\r\n" 15 Province SQL_Latin1_General_Cp437_BIN
I cannot find any info on this error message , has anyone got any ideas,
this is SQL server 2000 SP3
Thanks
Dylan
Have you checked the event log to verify you are not getting Disk I/O
errors?
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Dylan Kruger" <carmellobear1@.hotmail.com> wrote in message
news:ev0WvtNaEHA.2892@.TK2MSFTNGP10.phx.gbl...
> Hi Guys
> I have a client who was having issues importing from a text file (Created
by
> there mainframe ) into SQL server, using DTS it would only import a
certain
> number of the rows (like 10 000 out of a million) we hare now trying to
use
> BCP and see the following problem below:
>
> C:\>bcp CARD..AVAAF_ABSA_Employee in
> c:\1ECLS_EMP.txt -fc:\1formatfile.fmt -Sman
>
> netjie -Usa -Ppassword
>
> SQLState = S1000, NativeError = 0
>
> Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading BCP
> format file
>
> This is the format of the clients format file:
>
> 8.0
> 25
> 1 SQLCHAR 2 2 "\0" 0 num1
> SQL_Latin1_General_Cp437_BIN
> 2 SQLCHAR 2 7 "\0" 10 Employee_no
> SQL_Latin1_General_Cp437_BIN
> 3 SQLCHAR 2 7 "\0" 1 AB_Number
> SQL_Latin1_General_Cp437_BIN
> 4 SQLCHAR 2 30 "\0" 3 Surname
> SQL_Latin1_General_Cp437_BIN
> 5 SQLCHAR 2 20 "\0" 4 Name SQL_Latin1_General_Cp437_BIN
> 6 SQLCHAR 2 10 "\0" 2 Initials
> SQL_Latin1_General_Cp437_BIN
> 7 SQLCHAR 2 20 "\0" 0 num2
> SQL_Latin1_General_Cp437_BIN
> 8 SQLCHAR 2 50 "\0" 11 EmailAddress
> SQL_Latin1_General_Cp437_BIN
> 9 SQLCHAR 2 12 "\0" 0 num3
> SQL_Latin1_General_Cp437_BIN
> 10 SQLCHAR 2 10 "\0" 0 num4
> SQL_Latin1_General_Cp437_BIN
> 11 SQLCHAR 2 40 "\0" 0 num5
> SQL_Latin1_General_Cp437_BIN
> 12 SQLCHAR 2 4 "\0" 0 num6
> SQL_Latin1_General_Cp437_BIN
> 13 SQLCHAR 2 40 "\0" 0 num7
> SQL_Latin1_General_Cp437_BIN
> 14 SQLCHAR 2 20 "\0" 5 TelephoneNumber
SQL_Latin1_General_Cp437_BIN
> 15 SQLCHAR 2 20 "\0" 7 FaxNumber
SQL_Latin1_General_Cp437_BIN
> 16 SQLCHAR 2 12 "\0" 6 CellularNumber
SQL_Latin1_General_Cp437_BIN
> 17 SQLCHAR 2 56 "\0" 0 num8
> SQL_Latin1_General_Cp437_BIN
> 18 SQLCHAR 2 40 "\0" 9 DomicileAdddress
SQL_Latin1_General_Cp437_BIN
> 19 SQLCHAR 2 40 "\0" 0 num9
> SQL_Latin1_General_Cp437_BIN
> 20 SQLCHAR 2 10 "\0" 0 num10
> SQL_Latin1_General_Cp437_BIN
> 21 SQLCHAR 2 4 "\0" 0 num11
> SQL_Latin1_General_Cp437_BIN
> 22 SQLCHAR 2 40 "\0" 0 num12
> SQL_Latin1_General_Cp437_BIN
> 23 SQLCHAR 2 40 "\0" 0 num13
> SQL_Latin1_General_Cp437_BIN
> 24 SQLCHAR 2 2 "\0" 0 num14
> SQL_Latin1_General_Cp437_BIN
> 25 SQLCHAR 2 16 "\r\n" 15 Province
SQL_Latin1_General_Cp437_BIN
>
>
> I cannot find any info on this error message , has anyone got any ideas,
> this is SQL server 2000 SP3
>
> Thanks
> Dylan
>
|||I have not as i thought it would be unlikely that it owuld be that , but i
will check it anyways ,
Thanks
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:u6mlGkOaEHA.1764@.TK2MSFTNGP10.phx.gbl...
> Have you checked the event log to verify you are not getting Disk I/O
> errors?
> --
> ----
--
> ----
--[vbcol=seagreen]
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Dylan Kruger" <carmellobear1@.hotmail.com> wrote in message
> news:ev0WvtNaEHA.2892@.TK2MSFTNGP10.phx.gbl...
(Created
> by
> certain
> use
> SQL_Latin1_General_Cp437_BIN
> SQL_Latin1_General_Cp437_BIN
> SQL_Latin1_General_Cp437_BIN
> SQL_Latin1_General_Cp437_BIN
> SQL_Latin1_General_Cp437_BIN
>
|||hi Dylan,
I to am getting the same error of bcp you getting. Did you find the cause...if so please let me know.
Thanks
Arzan
"Dylan Kruger" wrote:

> I have not as i thought it would be unlikely that it owuld be that , but i
> will check it anyways ,
> Thanks
> "Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
> news:u6mlGkOaEHA.1764@.TK2MSFTNGP10.phx.gbl...
> --
> --
> (Created
>
>
sql

BCP question

Hi Guys
I have a client who was having issues importing from a text file (Created by
there mainframe ) into SQL server, using DTS it would only import a certain
number of the rows (like 10 000 out of a million) we hare now trying to use
BCP and see the following problem below:
C:\>bcp CARD..AVAAF_ABSA_Employee in
c:\1ECLS_EMP.txt -fc:\1formatfile.fmt -Sman
netjie -Usa -Ppassword
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading B
CP
format file
This is the format of the clients format file:
8.0
25
1 SQLCHAR 2 2 "\0" 0 num1
SQL_Latin1_General_Cp437_BIN
2 SQLCHAR 2 7 "\0" 10 Employee_no
SQL_Latin1_General_Cp437_BIN
3 SQLCHAR 2 7 "\0" 1 AB_Number
SQL_Latin1_General_Cp437_BIN
4 SQLCHAR 2 30 "\0" 3 Surname
SQL_Latin1_General_Cp437_BIN
5 SQLCHAR 2 20 "\0" 4 Name SQL_Latin1_General_Cp437_BIN
6 SQLCHAR 2 10 "\0" 2 Initials
SQL_Latin1_General_Cp437_BIN
7 SQLCHAR 2 20 "\0" 0 num2
SQL_Latin1_General_Cp437_BIN
8 SQLCHAR 2 50 "\0" 11 EmailAddress
SQL_Latin1_General_Cp437_BIN
9 SQLCHAR 2 12 "\0" 0 num3
SQL_Latin1_General_Cp437_BIN
10 SQLCHAR 2 10 "\0" 0 num4
SQL_Latin1_General_Cp437_BIN
11 SQLCHAR 2 40 "\0" 0 num5
SQL_Latin1_General_Cp437_BIN
12 SQLCHAR 2 4 "\0" 0 num6
SQL_Latin1_General_Cp437_BIN
13 SQLCHAR 2 40 "\0" 0 num7
SQL_Latin1_General_Cp437_BIN
14 SQLCHAR 2 20 "\0" 5 TelephoneNumber SQL_Latin1_General_Cp437_BIN
15 SQLCHAR 2 20 "\0" 7 FaxNumber SQL_Latin1_General_Cp437_BIN
16 SQLCHAR 2 12 "\0" 6 CellularNumber SQL_Latin1_General_Cp437_BIN
17 SQLCHAR 2 56 "\0" 0 num8
SQL_Latin1_General_Cp437_BIN
18 SQLCHAR 2 40 "\0" 9 DomicileAdddress SQL_Latin1_General_Cp437_BIN
19 SQLCHAR 2 40 "\0" 0 num9
SQL_Latin1_General_Cp437_BIN
20 SQLCHAR 2 10 "\0" 0 num10
SQL_Latin1_General_Cp437_BIN
21 SQLCHAR 2 4 "\0" 0 num11
SQL_Latin1_General_Cp437_BIN
22 SQLCHAR 2 40 "\0" 0 num12
SQL_Latin1_General_Cp437_BIN
23 SQLCHAR 2 40 "\0" 0 num13
SQL_Latin1_General_Cp437_BIN
24 SQLCHAR 2 2 "\0" 0 num14
SQL_Latin1_General_Cp437_BIN
25 SQLCHAR 2 16 "\r\n" 15 Province SQL_Latin1_General_Cp437_BIN
I cannot find any info on this error message , has anyone got any ideas,
this is SQL server 2000 SP3
Thanks
DylanHave you checked the event log to verify you are not getting Disk I/O
errors?
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Dylan Kruger" <carmellobear1@.hotmail.com> wrote in message
news:ev0WvtNaEHA.2892@.TK2MSFTNGP10.phx.gbl...
> Hi Guys
> I have a client who was having issues importing from a text file (Created
by
> there mainframe ) into SQL server, using DTS it would only import a
certain
> number of the rows (like 10 000 out of a million) we hare now trying to
use
> BCP and see the following problem below:
>
> C:\>bcp CARD..AVAAF_ABSA_Employee in
> c:\1ECLS_EMP.txt -fc:\1formatfile.fmt -Sman
>
> netjie -Usa -Ppassword
>
> SQLState = S1000, NativeError = 0
>
> Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading
BCP
> format file
>
> This is the format of the clients format file:
>
> 8.0
> 25
> 1 SQLCHAR 2 2 "\0" 0 num1
> SQL_Latin1_General_Cp437_BIN
> 2 SQLCHAR 2 7 "\0" 10 Employee_no
> SQL_Latin1_General_Cp437_BIN
> 3 SQLCHAR 2 7 "\0" 1 AB_Number
> SQL_Latin1_General_Cp437_BIN
> 4 SQLCHAR 2 30 "\0" 3 Surname
> SQL_Latin1_General_Cp437_BIN
> 5 SQLCHAR 2 20 "\0" 4 Name SQL_Latin1_General_Cp437_BIN
> 6 SQLCHAR 2 10 "\0" 2 Initials
> SQL_Latin1_General_Cp437_BIN
> 7 SQLCHAR 2 20 "\0" 0 num2
> SQL_Latin1_General_Cp437_BIN
> 8 SQLCHAR 2 50 "\0" 11 EmailAddress
> SQL_Latin1_General_Cp437_BIN
> 9 SQLCHAR 2 12 "\0" 0 num3
> SQL_Latin1_General_Cp437_BIN
> 10 SQLCHAR 2 10 "\0" 0 num4
> SQL_Latin1_General_Cp437_BIN
> 11 SQLCHAR 2 40 "\0" 0 num5
> SQL_Latin1_General_Cp437_BIN
> 12 SQLCHAR 2 4 "\0" 0 num6
> SQL_Latin1_General_Cp437_BIN
> 13 SQLCHAR 2 40 "\0" 0 num7
> SQL_Latin1_General_Cp437_BIN
> 14 SQLCHAR 2 20 "\0" 5 TelephoneNumber
SQL_Latin1_General_Cp437_BINeen">
> 15 SQLCHAR 2 20 "\0" 7 FaxNumber
SQL_Latin1_General_Cp437_BINeen">
> 16 SQLCHAR 2 12 "\0" 6 CellularNumber
SQL_Latin1_General_Cp437_BINeen">
> 17 SQLCHAR 2 56 "\0" 0 num8
> SQL_Latin1_General_Cp437_BIN
> 18 SQLCHAR 2 40 "\0" 9 DomicileAdddress
SQL_Latin1_General_Cp437_BINeen">
> 19 SQLCHAR 2 40 "\0" 0 num9
> SQL_Latin1_General_Cp437_BIN
> 20 SQLCHAR 2 10 "\0" 0 num10
> SQL_Latin1_General_Cp437_BIN
> 21 SQLCHAR 2 4 "\0" 0 num11
> SQL_Latin1_General_Cp437_BIN
> 22 SQLCHAR 2 40 "\0" 0 num12
> SQL_Latin1_General_Cp437_BIN
> 23 SQLCHAR 2 40 "\0" 0 num13
> SQL_Latin1_General_Cp437_BIN
> 24 SQLCHAR 2 2 "\0" 0 num14
> SQL_Latin1_General_Cp437_BIN
> 25 SQLCHAR 2 16 "\r\n" 15 Province
SQL_Latin1_General_Cp437_BINeen">
>
>
> I cannot find any info on this error message , has anyone got any ideas,
> this is SQL server 2000 SP3
>
> Thanks
> Dylan
>|||I have not as i thought it would be unlikely that it owuld be that , but i
will check it anyways ,
Thanks
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:u6mlGkOaEHA.1764@.TK2MSFTNGP10.phx.gbl...
> Have you checked the event log to verify you are not getting Disk I/O
> errors?
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Dylan Kruger" <carmellobear1@.hotmail.com> wrote in message
> news:ev0WvtNaEHA.2892@.TK2MSFTNGP10.phx.gbl...
(Created[vbcol=seagreen]
> by
> certain
> use
> SQL_Latin1_General_Cp437_BIN
> SQL_Latin1_General_Cp437_BIN
> SQL_Latin1_General_Cp437_BIN
> SQL_Latin1_General_Cp437_BIN
> SQL_Latin1_General_Cp437_BIN
>|||hi Dylan,
I to am getting the same error of bcp you getting. Did you find the cause...
if so please let me know.
Thanks
Arzan
"Dylan Kruger" wrote:

> I have not as i thought it would be unlikely that it owuld be that , but i
> will check it anyways ,
> Thanks
> "Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
> news:u6mlGkOaEHA.1764@.TK2MSFTNGP10.phx.gbl...
> --
> --
> (Created
>
>

BCP question

Hi Guys
I have a client who was having issues importing from a text file (Created by
there mainframe ) into SQL server, using DTS it would only import a certain
number of the rows (like 10 000 out of a million) we hare now trying to use
BCP and see the following problem below:
C:\>bcp CARD..AVAAF_ABSA_Employee in
c:\1ECLS_EMP.txt -fc:\1formatfile.fmt -Sman
netjie -Usa -Ppassword
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading BCP
format file
This is the format of the clients format file:
8.0
25
1 SQLCHAR 2 2 "\0" 0 num1
SQL_Latin1_General_Cp437_BIN
2 SQLCHAR 2 7 "\0" 10 Employee_no
SQL_Latin1_General_Cp437_BIN
3 SQLCHAR 2 7 "\0" 1 AB_Number
SQL_Latin1_General_Cp437_BIN
4 SQLCHAR 2 30 "\0" 3 Surname
SQL_Latin1_General_Cp437_BIN
5 SQLCHAR 2 20 "\0" 4 Name SQL_Latin1_General_Cp437_BIN
6 SQLCHAR 2 10 "\0" 2 Initials
SQL_Latin1_General_Cp437_BIN
7 SQLCHAR 2 20 "\0" 0 num2
SQL_Latin1_General_Cp437_BIN
8 SQLCHAR 2 50 "\0" 11 EmailAddress
SQL_Latin1_General_Cp437_BIN
9 SQLCHAR 2 12 "\0" 0 num3
SQL_Latin1_General_Cp437_BIN
10 SQLCHAR 2 10 "\0" 0 num4
SQL_Latin1_General_Cp437_BIN
11 SQLCHAR 2 40 "\0" 0 num5
SQL_Latin1_General_Cp437_BIN
12 SQLCHAR 2 4 "\0" 0 num6
SQL_Latin1_General_Cp437_BIN
13 SQLCHAR 2 40 "\0" 0 num7
SQL_Latin1_General_Cp437_BIN
14 SQLCHAR 2 20 "\0" 5 TelephoneNumber SQL_Latin1_General_Cp437_BIN
15 SQLCHAR 2 20 "\0" 7 FaxNumber SQL_Latin1_General_Cp437_BIN
16 SQLCHAR 2 12 "\0" 6 CellularNumber SQL_Latin1_General_Cp437_BIN
17 SQLCHAR 2 56 "\0" 0 num8
SQL_Latin1_General_Cp437_BIN
18 SQLCHAR 2 40 "\0" 9 DomicileAdddress SQL_Latin1_General_Cp437_BIN
19 SQLCHAR 2 40 "\0" 0 num9
SQL_Latin1_General_Cp437_BIN
20 SQLCHAR 2 10 "\0" 0 num10
SQL_Latin1_General_Cp437_BIN
21 SQLCHAR 2 4 "\0" 0 num11
SQL_Latin1_General_Cp437_BIN
22 SQLCHAR 2 40 "\0" 0 num12
SQL_Latin1_General_Cp437_BIN
23 SQLCHAR 2 40 "\0" 0 num13
SQL_Latin1_General_Cp437_BIN
24 SQLCHAR 2 2 "\0" 0 num14
SQL_Latin1_General_Cp437_BIN
25 SQLCHAR 2 16 "\r\n" 15 Province SQL_Latin1_General_Cp437_BIN
I cannot find any info on this error message , has anyone got any ideas,
this is SQL server 2000 SP3
Thanks
DylanHave you checked the event log to verify you are not getting Disk I/O
errors?
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Dylan Kruger" <carmellobear1@.hotmail.com> wrote in message
news:ev0WvtNaEHA.2892@.TK2MSFTNGP10.phx.gbl...
> Hi Guys
> I have a client who was having issues importing from a text file (Created
by
> there mainframe ) into SQL server, using DTS it would only import a
certain
> number of the rows (like 10 000 out of a million) we hare now trying to
use
> BCP and see the following problem below:
>
> C:\>bcp CARD..AVAAF_ABSA_Employee in
> c:\1ECLS_EMP.txt -fc:\1formatfile.fmt -Sman
>
> netjie -Usa -Ppassword
>
> SQLState = S1000, NativeError = 0
>
> Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading BCP
> format file
>
> This is the format of the clients format file:
>
> 8.0
> 25
> 1 SQLCHAR 2 2 "\0" 0 num1
> SQL_Latin1_General_Cp437_BIN
> 2 SQLCHAR 2 7 "\0" 10 Employee_no
> SQL_Latin1_General_Cp437_BIN
> 3 SQLCHAR 2 7 "\0" 1 AB_Number
> SQL_Latin1_General_Cp437_BIN
> 4 SQLCHAR 2 30 "\0" 3 Surname
> SQL_Latin1_General_Cp437_BIN
> 5 SQLCHAR 2 20 "\0" 4 Name SQL_Latin1_General_Cp437_BIN
> 6 SQLCHAR 2 10 "\0" 2 Initials
> SQL_Latin1_General_Cp437_BIN
> 7 SQLCHAR 2 20 "\0" 0 num2
> SQL_Latin1_General_Cp437_BIN
> 8 SQLCHAR 2 50 "\0" 11 EmailAddress
> SQL_Latin1_General_Cp437_BIN
> 9 SQLCHAR 2 12 "\0" 0 num3
> SQL_Latin1_General_Cp437_BIN
> 10 SQLCHAR 2 10 "\0" 0 num4
> SQL_Latin1_General_Cp437_BIN
> 11 SQLCHAR 2 40 "\0" 0 num5
> SQL_Latin1_General_Cp437_BIN
> 12 SQLCHAR 2 4 "\0" 0 num6
> SQL_Latin1_General_Cp437_BIN
> 13 SQLCHAR 2 40 "\0" 0 num7
> SQL_Latin1_General_Cp437_BIN
> 14 SQLCHAR 2 20 "\0" 5 TelephoneNumber
SQL_Latin1_General_Cp437_BIN
> 15 SQLCHAR 2 20 "\0" 7 FaxNumber
SQL_Latin1_General_Cp437_BIN
> 16 SQLCHAR 2 12 "\0" 6 CellularNumber
SQL_Latin1_General_Cp437_BIN
> 17 SQLCHAR 2 56 "\0" 0 num8
> SQL_Latin1_General_Cp437_BIN
> 18 SQLCHAR 2 40 "\0" 9 DomicileAdddress
SQL_Latin1_General_Cp437_BIN
> 19 SQLCHAR 2 40 "\0" 0 num9
> SQL_Latin1_General_Cp437_BIN
> 20 SQLCHAR 2 10 "\0" 0 num10
> SQL_Latin1_General_Cp437_BIN
> 21 SQLCHAR 2 4 "\0" 0 num11
> SQL_Latin1_General_Cp437_BIN
> 22 SQLCHAR 2 40 "\0" 0 num12
> SQL_Latin1_General_Cp437_BIN
> 23 SQLCHAR 2 40 "\0" 0 num13
> SQL_Latin1_General_Cp437_BIN
> 24 SQLCHAR 2 2 "\0" 0 num14
> SQL_Latin1_General_Cp437_BIN
> 25 SQLCHAR 2 16 "\r\n" 15 Province
SQL_Latin1_General_Cp437_BIN
>
>
> I cannot find any info on this error message , has anyone got any ideas,
> this is SQL server 2000 SP3
>
> Thanks
> Dylan
>|||I have not as i thought it would be unlikely that it owuld be that , but i
will check it anyways ,
Thanks
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:u6mlGkOaEHA.1764@.TK2MSFTNGP10.phx.gbl...
> Have you checked the event log to verify you are not getting Disk I/O
> errors?
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Dylan Kruger" <carmellobear1@.hotmail.com> wrote in message
> news:ev0WvtNaEHA.2892@.TK2MSFTNGP10.phx.gbl...
> > Hi Guys
> >
> > I have a client who was having issues importing from a text file
(Created
> by
> > there mainframe ) into SQL server, using DTS it would only import a
> certain
> > number of the rows (like 10 000 out of a million) we hare now trying to
> use
> > BCP and see the following problem below:
> >
> >
> >
> > C:\>bcp CARD..AVAAF_ABSA_Employee in
> > c:\1ECLS_EMP.txt -fc:\1formatfile.fmt -Sman
> >
> >
> >
> > netjie -Usa -Ppassword
> >
> >
> >
> > SQLState = S1000, NativeError = 0
> >
> >
> >
> > Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading BCP
> > format file
> >
> >
> >
> > This is the format of the clients format file:
> >
> >
> >
> > 8.0
> >
> > 25
> >
> > 1 SQLCHAR 2 2 "\0" 0 num1
> > SQL_Latin1_General_Cp437_BIN
> >
> > 2 SQLCHAR 2 7 "\0" 10 Employee_no
> > SQL_Latin1_General_Cp437_BIN
> >
> > 3 SQLCHAR 2 7 "\0" 1 AB_Number
> > SQL_Latin1_General_Cp437_BIN
> >
> > 4 SQLCHAR 2 30 "\0" 3 Surname
> > SQL_Latin1_General_Cp437_BIN
> >
> > 5 SQLCHAR 2 20 "\0" 4 Name SQL_Latin1_General_Cp437_BIN
> >
> > 6 SQLCHAR 2 10 "\0" 2 Initials
> > SQL_Latin1_General_Cp437_BIN
> >
> > 7 SQLCHAR 2 20 "\0" 0 num2
> > SQL_Latin1_General_Cp437_BIN
> >
> > 8 SQLCHAR 2 50 "\0" 11 EmailAddress
> > SQL_Latin1_General_Cp437_BIN
> >
> > 9 SQLCHAR 2 12 "\0" 0 num3
> > SQL_Latin1_General_Cp437_BIN
> >
> > 10 SQLCHAR 2 10 "\0" 0 num4
> > SQL_Latin1_General_Cp437_BIN
> >
> > 11 SQLCHAR 2 40 "\0" 0 num5
> > SQL_Latin1_General_Cp437_BIN
> >
> > 12 SQLCHAR 2 4 "\0" 0 num6
> > SQL_Latin1_General_Cp437_BIN
> >
> > 13 SQLCHAR 2 40 "\0" 0 num7
> > SQL_Latin1_General_Cp437_BIN
> >
> > 14 SQLCHAR 2 20 "\0" 5 TelephoneNumber
> SQL_Latin1_General_Cp437_BIN
> >
> > 15 SQLCHAR 2 20 "\0" 7 FaxNumber
> SQL_Latin1_General_Cp437_BIN
> >
> > 16 SQLCHAR 2 12 "\0" 6 CellularNumber
> SQL_Latin1_General_Cp437_BIN
> >
> > 17 SQLCHAR 2 56 "\0" 0 num8
> > SQL_Latin1_General_Cp437_BIN
> >
> > 18 SQLCHAR 2 40 "\0" 9 DomicileAdddress
> SQL_Latin1_General_Cp437_BIN
> >
> > 19 SQLCHAR 2 40 "\0" 0 num9
> > SQL_Latin1_General_Cp437_BIN
> >
> > 20 SQLCHAR 2 10 "\0" 0 num10
> > SQL_Latin1_General_Cp437_BIN
> >
> > 21 SQLCHAR 2 4 "\0" 0 num11
> > SQL_Latin1_General_Cp437_BIN
> >
> > 22 SQLCHAR 2 40 "\0" 0 num12
> > SQL_Latin1_General_Cp437_BIN
> >
> > 23 SQLCHAR 2 40 "\0" 0 num13
> > SQL_Latin1_General_Cp437_BIN
> >
> > 24 SQLCHAR 2 2 "\0" 0 num14
> > SQL_Latin1_General_Cp437_BIN
> >
> > 25 SQLCHAR 2 16 "\r\n" 15 Province
> SQL_Latin1_General_Cp437_BIN
> >
> >
> >
> >
> >
> > I cannot find any info on this error message , has anyone got any ideas,
> > this is SQL server 2000 SP3
> >
> >
> >
> > Thanks
> >
> > Dylan
> >
> >
>

2012年3月20日星期二

BCP OUT with Double Quote(") text qualifier?

How do I tell BCP that I want it to output with Double Quotes around my char fields?

It's easy to do in DTS by setting the connection properties of the Text File destination. The reason I can't use DTS is because my table has 329 fields and when I try to modify the destination properties of the transform task, Enterprise Manager bombs. I'd rather use BCP and a format file anyway.

Thanks,

Ray Metz
Everett, WAI just tried using a format file to define the field seperators as "," but BCP barfed. when I switched to ',' it worked. Maybe you could use this.|||Yeah, I had a similar idea to create a view with select '"' + field + '"' for each of the char columns. But since my export has over 300 columns and many of them char or varchar, I wanted an easier solution than going through each column.

Luckily, the Oracle DBA on the other end was ok with me not sending the quotes. The field seperator was a bar (|), and so far we haven't found any bars in the data so I think we're in good shape.

Thanks for your reply.

Ray

2012年3月11日星期日

Bcp import - how?

I've exported one table using DTS to *.txt file:
"id","name","calories"
{F5F781EF-4270-4D8C-B5E0-A968F45FF771},"Ananas frisch",60
{CA0E96D8-9017-4DDD-BF29-B78D3DC8180D},"Ananas Fruchtnektar",70
{A1777D54-9303-4AF5-A721-250A39BA731C},"Ananas Fruchtsaft",60
{DACC2063-0985-41EB-9A33-B4D8A10F1C79},"Ananas getrocknet",300
{04E462EC-57B1-4882-A8DF-A1EFC1B9C4F3},"Ananas kandiert",250
How can I import this data using bcp? I've tried many combination of
parameters (-q etc.), but the result ist always wrong (that means, I've
imported but fields are filled with no or partial data). Where can I find a
good description of bcp utility with examples (msdn is very poor)?
Thanx in advance,
Adam
Adam Boczek
adam.boczek@.cs-consulting.de
Hi,
Use BULK Insert.
1. I have saved the contents as ccc.txt in drive.
2. Created a table with below script :-
create table zz(id uniqueidentifier,name varchar(30),calories int)
3. Executed the below in query analyzer
BULK INSERT master.dbo.zz
FROM 'c:\ccc.txt'
WITH
(
FIELDTERMINATOR = ','
)
4. select * from zz
id name calories
-- -- --
F5F781EF-4270-4D8C-B5E0-A968F45FF771 "Ananas frisch" 60
CA0E96D8-9017-4DDD-BF29-B78D3DC8180D "Ananas Fruchtnektar" 70
A1777D54-9303-4AF5-A721-250A39BA731C "Ananas Fruchtsaft" 60
DACC2063-0985-41EB-9A33-B4D8A10F1C79 "Ananas getrocknet" 300
04E462EC-57B1-4882-A8DF-A1EFC1B9C4F3 "Ananas kandiert" 250
(5 row(s) affected)
Thanks
Hari
MCDBA
"Adam Boczek" <adam.boczek@.NO_SPAM.cs-consulting.de> wrote in message
news:1086160529.62750@.proxy.ham.cs-consulting.de...
> I've exported one table using DTS to *.txt file:
> "id","name","calories"
> {F5F781EF-4270-4D8C-B5E0-A968F45FF771},"Ananas frisch",60
> {CA0E96D8-9017-4DDD-BF29-B78D3DC8180D},"Ananas Fruchtnektar",70
> {A1777D54-9303-4AF5-A721-250A39BA731C},"Ananas Fruchtsaft",60
> {DACC2063-0985-41EB-9A33-B4D8A10F1C79},"Ananas getrocknet",300
> {04E462EC-57B1-4882-A8DF-A1EFC1B9C4F3},"Ananas kandiert",250
> How can I import this data using bcp? I've tried many combination of
> parameters (-q etc.), but the result ist always wrong (that means, I've
> imported but fields are filled with no or partial data). Where can I find
a
> good description of bcp utility with examples (msdn is very poor)?
> Thanx in advance,
> Adam
> --
> ----
> Adam Boczek
> adam.boczek@.cs-consulting.de
> ----
>

2012年2月23日星期四

BCP and SQL Server 2K

To all,

I've got 3 questions on BCP and SQL Server 2K
1) actually is it BCP's functionalities == DTS under Enterprise Manager
?
2) I've tried to use bcp under isql (ie. the command prompt version in
accessing the SQL server) but it just return an error like this 'Could
not find stored procedure 'bcp'.'; I wonder should I run BCP in this
mode or not ?
3) assume I could bcp a table's data to a file; then how can I pump it
to another table on another database ?

Thanks!

>From Jason (Kusanagihk)<kusanagihk@.gmail.com> wrote in message
news:1115862396.057790.23710@.o13g2000cwo.googlegro ups.com...
> To all,
> I've got 3 questions on BCP and SQL Server 2K
> 1) actually is it BCP's functionalities == DTS under Enterprise Manager
> ?
> 2) I've tried to use bcp under isql (ie. the command prompt version in
> accessing the SQL server) but it just return an error like this 'Could
> not find stored procedure 'bcp'.'; I wonder should I run BCP in this
> mode or not ?

bcp is a stand alone program. I.e. you run it at the command prompt
directly, not from within ISQL.

> 3) assume I could bcp a table's data to a file; then how can I pump it
> to another table on another database ?

DTS is better for this.

> Thanks!
> >From Jason (Kusanagihk)|||For Q3.. Assuming that your servers can see each other, consider
"SELECT INTO" instead of BCP

Greg D. Moore (Strider) wrote:
> <kusanagihk@.gmail.com> wrote in message
> news:1115862396.057790.23710@.o13g2000cwo.googlegro ups.com...
> > To all,
> > I've got 3 questions on BCP and SQL Server 2K
> > 1) actually is it BCP's functionalities == DTS under Enterprise
Manager
> > ?
> > 2) I've tried to use bcp under isql (ie. the command prompt version
in
> > accessing the SQL server) but it just return an error like this
'Could
> > not find stored procedure 'bcp'.'; I wonder should I run BCP in
this
> > mode or not ?
> bcp is a stand alone program. I.e. you run it at the command prompt
> directly, not from within ISQL.
> > 3) assume I could bcp a table's data to a file; then how can I pump
it
> > to another table on another database ?
> DTS is better for this.
> > Thanks!
> > >From Jason (Kusanagihk)|||To all,

Yes, you are right, I could simply use select into instead... but the
matter is I want to copy the data from SQL server to DB2 (actually the
table schema is the same; just don't want to write numerous insert into
sql statements for the same contents)

Is it still possible to use DTS to perform cross DB server data copy /
transformation ??

>From Jason (Kusanagihk)|||(kusanagihk@.gmail.com) writes:
> Yes, you are right, I could simply use select into instead... but the
> matter is I want to copy the data from SQL server to DB2 (actually the
> table schema is the same; just don't want to write numerous insert into
> sql statements for the same contents)
> Is it still possible to use DTS to perform cross DB server data copy /
> transformation ??

I would be very surprised if that is not possible, but I have never used
DTS (and even less DB2), so I can't say for sure.

Using BCP on the SQL Server side, and similar tool on the DB2 side, also
sounds like a workable idea, particularly if the tables have identical
schema. It does require, though, that the DB2 will interpret the output
file that BCP generates correctly. I would assume any such issues to be
built into DTS.

For information on DTS, try http://www.sqldts.com and
microsoft.public.sqlserver.xml.

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

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

2012年2月18日星期六

BCP - between 2 SQL Server Tables

Any syntax where i will copy an sql server table to another table using BCP.im using sql server 2000 on vb.net, DTS isn't working for me because i want to edit some conditions on the package and it just dont work well.thanks in advance!are the 2 tables in the same database, or the same server or on the same network? you may be able to do this with a simple

INSERT INTO MyTable(MyField)
SELECT

using fully qualified names and maybe a linked server.|||if you want to use bcp, you have to do it in 2 steps:

1. use bcp with "out" or "queryout" keywords to go from source table to flat file
2. use bcp with "in" keyword to go from flat file to dest table.

if you have many millions of rows, bcp is faster than using INSERT...SELECT.

read about bcp syntax here:

http://msdn2.microsoft.com/en-us/library/ms162802.aspx

Another option, since you are using .net, is to use the sqlbulkcopy class, which has the advantage of not needing to write the intermediate file. It's quite fast. Read up here: http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx. I recommend you to use the overload of WriteToServer that takes IDataReader, not the DataTable overloads. much more memory efficient.|||Thanks..maybe i forgot that one..workin on it now..hope it will be much faster|||thats what im thinking..i have to do two steps..good if im transferring it to other database or web.will it still be much faster than 'Insert...Select' statement in two steps?...|||it depends on how many rows you are moving. if it's millions, bcp/bulk insert/sqlbulkcopy will be faster than INSERT...SELECT.

If you have lots of rows, I would try sqlbulkcopy. it's only one step, and uses the same underlying method as bcp, namely, bulk insert.

2012年2月13日星期一

Batch File

Hi ,
I create a batch file to run the zip the files. It works fine when I run
manually. But if I put into DTS package and shedule it , it cannot be run. D
o
I need to pass any user and password paramater into the DTS package ?
Travis Tan"It cannot run" doesn't give us much to go on - the errors
or problems are' If you aren't already doing so, you need
to use the command line add-on for WinZip. You can download
it from:
http://www.winzip.com/wzcline.htm
Not sure if it changed recently but it used to need admin
rights by the account executing the command so the service
account would need admin rights.
-Sue
On Wed, 12 Apr 2006 02:26:01 -0700, Travis
<Travis@.discussions.microsoft.com> wrote:

>Hi ,
> I create a batch file to run the zip the files. It works fine when I run
>manually. But if I put into DTS package and shedule it , it cannot be run.
Do
>I need to pass any user and password paramater into the DTS package ?

2012年2月11日星期六

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.