I have a DTS package which copies rows from 150 tables using Union ALL.
Around 30 million rows are involved. However package fails after 8.6 million
rows with the error:
Error at Source for Row number 8697091. Cannot convert nvarchar value of
('XXX ') to smallint. Errors encountered so far in this task: 1.
The column on which this fails is a Varchar. It looks like the DTS package
is treating the source package as a Smallint. Is there a way aroun this?I would have thought it is trying to convert a varchar (source value)
to a smallint (destination value) '|||I don't think that is the case. My Source (SQL query) column has values like
2, 'XXX' and the column in the destination table is defined to be varchar.
The Source tables have this column defn as smallint and varchar.
"Barry" <barry.oconnor@.singers.co.im> wrote in message
news:1127930452.777681.80430@.g43g2000cwa.googlegroups.com...
>I would have thought it is trying to convert a varchar (source value)
> to a smallint (destination value) '
>|||How do you have a column as Smallint & Varchar ?|||This is one time data transfer and this is my staging table. I am bringing
data from various tables which may be int or varchar.
"Barry" <barry.oconnor@.singers.co.im> wrote in message
news:1127930934.509640.143400@.z14g2000cwz.googlegroups.com...
> How do you have a column as Smallint & Varchar ?
>|||Hmm strange...
Maybe you could replace 'XXX' with zero in your Select statement.
I know if you extract Data to a text file you skip the exceptions and
write them to an exceptions file.
If you used this approach you could have several txt files, which would
be from your source tables, and then import these in to new table.
Sorry I can't be of any more help
Barry|||I have tried exporting this to a text file, but same issue.
Anyone? All I need I think is a way to tell the DTS that the source column
is varchar.
"Barry" <barry.oconnor@.singers.co.im> wrote in message
news:1127931933.066339.209980@.g49g2000cwa.googlegroups.com...
> Hmm strange...
> Maybe you could replace 'XXX' with zero in your Select statement.
> I know if you extract Data to a text file you skip the exceptions and
> write them to an exceptions file.
> If you used this approach you could have several txt files, which would
> be from your source tables, and then import these in to new table.
> Sorry I can't be of any more help
> Barry
>|||You need to specifically convert the value to varchar for the column that
has integer value.
try this:
select '0e'
Union
Select 1
It will give you the error mesage.
But this one won't:
select '0e'
Union
Select Convert(varchar(5), 1).
Perayu
"XXX" <sa@.nomail.com> wrote in message
news:%23Ji7dqFxFHA.2008@.TK2MSFTNGP10.phx.gbl...
>I have tried exporting this to a text file, but same issue.
> Anyone? All I need I think is a way to tell the DTS that the source column
> is varchar.
> "Barry" <barry.oconnor@.singers.co.im> wrote in message
> news:1127931933.066339.209980@.g49g2000cwa.googlegroups.com...
>
Showing posts with label million. Show all posts
Showing posts with label million. Show all posts
Sunday, March 25, 2012
Friday, February 17, 2012
DTS Import Text File Replacement - too many errors with DTS
I am using the DTS COM object to import a bunch of different text files.
some are 2 or 3 million rows. if there's an invalid row (can't find
row delimiter or column delimiter) sometimes DTS will crash and stop the
import... it even does it if I try to import it from the GUI, and going
into DTS tasks and messing with the errors rows, etc.
So, I am looking for a better way to batch import... can't use bulk
insert because of field qualifiers... any ideas? any 3rd party object i
can use to quickly import a large file?
i using c#, btw.
thanks
EdDid you look at the bcp utility? It ain't pretty, but for brute force,
it is hard to beat.|||Bulk insert and bcp both can use format file (i.e. you can specify your
custom terminator), batch size, and allowable max error. You should check
them out.
-oj
"Ed West" <west@.westville.com> wrote in message
news:OQxMbDxCFHA.3492@.TK2MSFTNGP12.phx.gbl...
>I am using the DTS COM object to import a bunch of different text files.
>some are 2 or 3 million rows. if there's an invalid row (can't find row
>delimiter or column delimiter) sometimes DTS will crash and stop the
>import... it even does it if I try to import it from the GUI, and going
>into DTS tasks and messing with the errors rows, etc.
> So, I am looking for a better way to batch import... can't use bulk insert
> because of field qualifiers... any ideas? any 3rd party object i can use
> to quickly import a large file?
> i using c#, btw.
> thanks
> Ed|||hi - many thanks, bcp/bulk insert is working great... i am creating a
format file dynamically.
thanks again
oj wrote:
> Bulk insert and bcp both can use format file (i.e. you can specify your
> custom terminator), batch size, and allowable max error. You should check
> them out.
>|||Hello - what about a Field Qualifier? how can I specify that? For some
files, the field qualifier is " and that is showing up in the database
field after being imported... thanks
- Ed
oj wrote:
> Bulk insert and bcp both can use format file (i.e. you can specify your
> custom terminator), batch size, and allowable max error. You should check
> them out.
>
some are 2 or 3 million rows. if there's an invalid row (can't find
row delimiter or column delimiter) sometimes DTS will crash and stop the
import... it even does it if I try to import it from the GUI, and going
into DTS tasks and messing with the errors rows, etc.
So, I am looking for a better way to batch import... can't use bulk
insert because of field qualifiers... any ideas? any 3rd party object i
can use to quickly import a large file?
i using c#, btw.
thanks
EdDid you look at the bcp utility? It ain't pretty, but for brute force,
it is hard to beat.|||Bulk insert and bcp both can use format file (i.e. you can specify your
custom terminator), batch size, and allowable max error. You should check
them out.
-oj
"Ed West" <west@.westville.com> wrote in message
news:OQxMbDxCFHA.3492@.TK2MSFTNGP12.phx.gbl...
>I am using the DTS COM object to import a bunch of different text files.
>some are 2 or 3 million rows. if there's an invalid row (can't find row
>delimiter or column delimiter) sometimes DTS will crash and stop the
>import... it even does it if I try to import it from the GUI, and going
>into DTS tasks and messing with the errors rows, etc.
> So, I am looking for a better way to batch import... can't use bulk insert
> because of field qualifiers... any ideas? any 3rd party object i can use
> to quickly import a large file?
> i using c#, btw.
> thanks
> Ed|||hi - many thanks, bcp/bulk insert is working great... i am creating a
format file dynamically.
thanks again
oj wrote:
> Bulk insert and bcp both can use format file (i.e. you can specify your
> custom terminator), batch size, and allowable max error. You should check
> them out.
>|||Hello - what about a Field Qualifier? how can I specify that? For some
files, the field qualifier is " and that is showing up in the database
field after being imported... thanks
- Ed
oj wrote:
> Bulk insert and bcp both can use format file (i.e. you can specify your
> custom terminator), batch size, and allowable max error. You should check
> them out.
>
Tuesday, February 14, 2012
DTS how make our own?
How to make our own DTS package I have to split the data on my own.. Data is very biig almost 30 to 40 million. I need to splitt them into 10k chunks in database and with my desired table name.
Waiting for a +tive reply.
Regards
Shani ;)Using DTS wizard you can design the DTS package and save with password for your use only.
And also you can import data based on conditional requirements.
What is the condition for those 10K chunks?|||Originally posted by Satya
Using DTS wizard you can design the DTS package and save with password for your use only.
And also you can import data based on conditional requirements.
What is the condition for those 10K chunks?
Hi,
I have Database of 4.2million Email address. I am now splitting records into a new database. Now what I want is that Thorugh a trigger or something else SQL server should split up the one table that has 4.2 email address into 10,000 records in every new new table, going again and again to DTS wizard is very difficult do u have anu other solution to this problem.
Regards
Shani ;)|||So let me be sure I understand... You want us to help you to help your customers SPAM us?
-PatP|||Originally posted by Pat Phelan
So let me be sure I understand... You want us to help you to help your customers SPAM us?
-PatP
na na na email is just an example... its cc info of people who have purchased with us and their contact info its not online it is our local outlet system.... for a chain of stores... why do u take evrything to spam|||Refer to this link (http://www.1sqlstreet.com/vb/scripts/ShowCode.asp?txtCodeId=819&lngWId=5) to get nth record from a table and use normal [INSERT INTO... SELECT] to export to new table depending upon your ability.
Refer to books online for other stuff and syntax.
HTH|||Originally posted by shani
why do u take evrything to spam Because I get SO blasted much of the stuff, because databases are a key component to generating SPAM, and because your question is one that frequently appears from address harvestors trying to clean and resell their lists. It may be a coincidence, but you've certainly hit a "hot button" with me!
-PatP
Waiting for a +tive reply.
Regards
Shani ;)Using DTS wizard you can design the DTS package and save with password for your use only.
And also you can import data based on conditional requirements.
What is the condition for those 10K chunks?|||Originally posted by Satya
Using DTS wizard you can design the DTS package and save with password for your use only.
And also you can import data based on conditional requirements.
What is the condition for those 10K chunks?
Hi,
I have Database of 4.2million Email address. I am now splitting records into a new database. Now what I want is that Thorugh a trigger or something else SQL server should split up the one table that has 4.2 email address into 10,000 records in every new new table, going again and again to DTS wizard is very difficult do u have anu other solution to this problem.
Regards
Shani ;)|||So let me be sure I understand... You want us to help you to help your customers SPAM us?
-PatP|||Originally posted by Pat Phelan
So let me be sure I understand... You want us to help you to help your customers SPAM us?
-PatP
na na na email is just an example... its cc info of people who have purchased with us and their contact info its not online it is our local outlet system.... for a chain of stores... why do u take evrything to spam|||Refer to this link (http://www.1sqlstreet.com/vb/scripts/ShowCode.asp?txtCodeId=819&lngWId=5) to get nth record from a table and use normal [INSERT INTO... SELECT] to export to new table depending upon your ability.
Refer to books online for other stuff and syntax.
HTH|||Originally posted by shani
why do u take evrything to spam Because I get SO blasted much of the stuff, because databases are a key component to generating SPAM, and because your question is one that frequently appears from address harvestors trying to clean and resell their lists. It may be a coincidence, but you've certainly hit a "hot button" with me!
-PatP
DTS Help
I have around 400 different Select -- Insert queries getting data from 200+
tbles. Data in each table can vary from 1 million to 3 million. All the data
is being inserted in the same table.
What's the best way to do this? Insert statement obviously wouldn't work. Is
there a way I can use all the queries inside one DTS package?XXX,
BPC out and BCP or BULK INSERT in might be a faster option. Also, do you
need to bring over all of the records or can you restrict the data being
transfered i.e., date range? One other thing, you might consider a data
archival policy and indexing strategies to make your queries run faster.
HTH
Jerry
"XXX" <sa@.nomail.com> wrote in message
news:OXyD%23P6vFHA.2792@.tk2msftngp13.phx.gbl...
>I have around 400 different Select -- Insert queries getting data from 200+
>tbles. Data in each table can vary from 1 million to 3 million. All the
>data is being inserted in the same table.
> What's the best way to do this? Insert statement obviously wouldn't work.
> Is there a way I can use all the queries inside one DTS package?
>|||No, I have a client who does registeration and Profile building in
PepopleSoft.
They are building a new system, where data from several tables (200) gets
merged into 3 tables. I have to get the initial load before the new system
goes live, after that this will be done differently (through PeopleSoft).
To answer your question all the data needs to be pulled out. I was hoping
there was something easier than BCP...
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eyU93T6vFHA.708@.TK2MSFTNGP10.phx.gbl...
> XXX,
> BPC out and BCP or BULK INSERT in might be a faster option. Also, do you
> need to bring over all of the records or can you restrict the data being
> transfered i.e., date range? One other thing, you might consider a data
> archival policy and indexing strategies to make your queries run faster.
> HTH
> Jerry
> "XXX" <sa@.nomail.com> wrote in message
> news:OXyD%23P6vFHA.2792@.tk2msftngp13.phx.gbl...
>|||Before you do the initial loads be sure to increase the transaction log
space and max setting. You might also want to begin with a small load and
monitor the t-log usage using DBCC SQLPERF(LOGSPACE) to ensure adequate log
space. Be sure to perform a backup and shrink the t-log when the loads are
complete (or as you increment the loads if needed).
HTH
Jerry
"XXX" <sa@.nomail.com> wrote in message
news:upR0zk6vFHA.464@.TK2MSFTNGP15.phx.gbl...
> No, I have a client who does registeration and Profile building in
> PepopleSoft.
> They are building a new system, where data from several tables (200) gets
> merged into 3 tables. I have to get the initial load before the new system
> goes live, after that this will be done differently (through PeopleSoft).
> To answer your question all the data needs to be pulled out. I was hoping
> there was something easier than BCP...
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:eyU93T6vFHA.708@.TK2MSFTNGP10.phx.gbl...
>
tbles. Data in each table can vary from 1 million to 3 million. All the data
is being inserted in the same table.
What's the best way to do this? Insert statement obviously wouldn't work. Is
there a way I can use all the queries inside one DTS package?XXX,
BPC out and BCP or BULK INSERT in might be a faster option. Also, do you
need to bring over all of the records or can you restrict the data being
transfered i.e., date range? One other thing, you might consider a data
archival policy and indexing strategies to make your queries run faster.
HTH
Jerry
"XXX" <sa@.nomail.com> wrote in message
news:OXyD%23P6vFHA.2792@.tk2msftngp13.phx.gbl...
>I have around 400 different Select -- Insert queries getting data from 200+
>tbles. Data in each table can vary from 1 million to 3 million. All the
>data is being inserted in the same table.
> What's the best way to do this? Insert statement obviously wouldn't work.
> Is there a way I can use all the queries inside one DTS package?
>|||No, I have a client who does registeration and Profile building in
PepopleSoft.
They are building a new system, where data from several tables (200) gets
merged into 3 tables. I have to get the initial load before the new system
goes live, after that this will be done differently (through PeopleSoft).
To answer your question all the data needs to be pulled out. I was hoping
there was something easier than BCP...
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eyU93T6vFHA.708@.TK2MSFTNGP10.phx.gbl...
> XXX,
> BPC out and BCP or BULK INSERT in might be a faster option. Also, do you
> need to bring over all of the records or can you restrict the data being
> transfered i.e., date range? One other thing, you might consider a data
> archival policy and indexing strategies to make your queries run faster.
> HTH
> Jerry
> "XXX" <sa@.nomail.com> wrote in message
> news:OXyD%23P6vFHA.2792@.tk2msftngp13.phx.gbl...
>|||Before you do the initial loads be sure to increase the transaction log
space and max setting. You might also want to begin with a small load and
monitor the t-log usage using DBCC SQLPERF(LOGSPACE) to ensure adequate log
space. Be sure to perform a backup and shrink the t-log when the loads are
complete (or as you increment the loads if needed).
HTH
Jerry
"XXX" <sa@.nomail.com> wrote in message
news:upR0zk6vFHA.464@.TK2MSFTNGP15.phx.gbl...
> No, I have a client who does registeration and Profile building in
> PepopleSoft.
> They are building a new system, where data from several tables (200) gets
> merged into 3 tables. I have to get the initial load before the new system
> goes live, after that this will be done differently (through PeopleSoft).
> To answer your question all the data needs to be pulled out. I was hoping
> there was something easier than BCP...
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:eyU93T6vFHA.708@.TK2MSFTNGP10.phx.gbl...
>
Subscribe to:
Posts (Atom)