duplicate rows in 1 csv file

Hi, I am trying to import data from a csv files to a OLE DB Destination. The csv files contains all transactional changes . For example for a particular record the firstname, lastname, email address records change within the same csv file. I need to save only the last updated record from the csv files. I have tried "slowly changing dimensions" but these dont work when there is duplictes within the same csv file. Also have tried 'Sort' but this only stores the first occurance.
Any ideas how i can store the latest changed data within 1 csv file.

How does the process distinguish between the latest data and duplicate data?

Is there a date as part of the row or is it just the last row wins?

Kirk Haselden
Author "SQL Server Integration Services"

|||the last row wins. there is no timestamp field . This is the way designed by the third party developers. Records are saved in csv files as changes are made. I have to put all these rows of data into a datawarehouse without duplicating the primary key.

Duplicate primary keys in input file

'm trying to import a text file but the primary key column contains duplicatres (tunrs out to be the nature of the legacy data). How can I kick out all duplicates except, say, for a single primary key value?

TIA,

Barkingdog

SORT component has the ability to eradicate dupes. Have you looked into using that?

-Jamie

|||

No, but I will.

BTW does SORT allow me to send those dups to an "erorr" file so I can see I can further investigate those "bad" records?

Barkingdog

|||

No, you can't do that. But you could easily use an AGGREGATE followed by a conditional split to get all those where the key appears more than once.

-Jamie

duplicate error report

I have an app which downloads data from mainframe daily.
Because the primary key definition is different, I have to verify duplicates
before import to my local table.
I would like to know that is it possible to insert duplicates into a table
to investigate later on.
If yes, where is the best place to do it?
For example may I use insert triggers?
I may insert current record into my table if insert fails.
If I am wrong, any information is great appreciated.
Souris,Hi
If you load the data into a loading table then you could do something like:
INSERT INTO Duplicates
SELECT *
FROM LoadingTable
WHERE PKCol IN( SELECT PKCol FROM DestinationTable )
INSERT INTO DestinationTable
SELECT *
FROM LoadingTable
WHERE PKCol NOT IN ( SELECT PKCol FROM DestinationTable )
John
"souris" <soukkris@.viddotron.com> wrote in message
news:ORYSttMEFHA.1408@.TK2MSFTNGP10.phx.gbl...
>I have an app which downloads data from mainframe daily.
> Because the primary key definition is different, I have to verify
> duplicates before import to my local table.
> I would like to know that is it possible to insert duplicates into a table
> to investigate later on.
> If yes, where is the best place to do it?
> For example may I use insert triggers?
> I may insert current record into my table if insert fails.
> If I am wrong, any information is great appreciated.
> Souris,
>
>|||Thanks millions,
Souris,
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:u6GmI3QEFHA.3992@.tk2msftngp13.phx.gbl...
> Hi
> If you load the data into a loading table then you could do something
> like:
> INSERT INTO Duplicates
> SELECT *
> FROM LoadingTable
> WHERE PKCol IN( SELECT PKCol FROM DestinationTable )
> INSERT INTO DestinationTable
> SELECT *
> FROM LoadingTable
> WHERE PKCol NOT IN ( SELECT PKCol FROM DestinationTable )
> John
> "souris" <soukkris@.viddotron.com> wrote in message
> news:ORYSttMEFHA.1408@.TK2MSFTNGP10.phx.gbl...
>|||Thanks for the sample code.
This works for one primary key column.
Are there any example for 4 primary key columns ?
Thanks again
Souris,
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:u6GmI3QEFHA.3992@.tk2msftngp13.phx.gbl...
> Hi
> If you load the data into a loading table then you could do something
> like:
> INSERT INTO Duplicates
> SELECT *
> FROM LoadingTable
> WHERE PKCol IN( SELECT PKCol FROM DestinationTable )
> INSERT INTO DestinationTable
> SELECT *
> FROM LoadingTable
> WHERE PKCol NOT IN ( SELECT PKCol FROM DestinationTable )
> John
> "souris" <soukkris@.viddotron.com> wrote in message
> news:ORYSttMEFHA.1408@.TK2MSFTNGP10.phx.gbl...
>|||Hi
Try:
INSERT INTO Duplicates
SELECT *
FROM LoadingTable L
WHERE EXISTS ( SELECT * FROM DestinationTable D WHERE D.PKCol1 =
L.PKCol1 AND D.PKCol2 = L.PKCol2 AND D.PKCol3 = L.PKCol3 AND D.PKCol4 =
L.PKCol4 )
INSERT INTO DestinationTable
SELECT *
WHERE NOT EXISTS ( SELECT * FROM DestinationTable D WHERE D.PKCol1 =
L.PKCol1 AND D.PKCol2 = L.PKCol2 AND D.PKCol3 = L.PKCol3 AND D.PKCol4 =
L.PKCol4 )
John
souris wrote:
> Thanks for the sample code.
> This works for one primary key column.
> Are there any example for 4 primary key columns ?
> Thanks again
> Souris,
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:u6GmI3QEFHA.3992@.tk2msftngp13.phx.gbl...
something
a|||Thanks millions,
Souris,
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1108538560.326295.15100@.c13g2000cwb.googlegroups.com...
> Hi
> Try:
> INSERT INTO Duplicates
> SELECT *
> FROM LoadingTable L
> WHERE EXISTS ( SELECT * FROM DestinationTable D WHERE D.PKCol1 =
> L.PKCol1 AND D.PKCol2 = L.PKCol2 AND D.PKCol3 = L.PKCol3 AND D.PKCol4 =
> L.PKCol4 )
> INSERT INTO DestinationTable
> SELECT *
> WHERE NOT EXISTS ( SELECT * FROM DestinationTable D WHERE D.PKCol1 =
> L.PKCol1 AND D.PKCol2 = L.PKCol2 AND D.PKCol3 = L.PKCol3 AND D.PKCol4 =
> L.PKCol4 )
> John
>
> souris wrote:
> something
> a
>sql

dup check while import from excel

There have been some pretty advanced stuff here I've gone through, but I am
a
.NET programmer by trade, so T-SQL is a bit different for me.
I have an outside list of items with ID codes that I need to import to an
existing table, but I need to have the procedure check for dups in the excel
list BEFORE inserting them, if the ID already exists in the database, then
skip and move onto the next one.
(See the OOP concept here?)
I was thinking about something along the line of an If Exist followed by a
skip record or insert, but I think I am making this too complicated.
can someone give me a down-to-earth example of how to accomplish this?
Environment:
From: Excel File named InItem
ID field: ItemID
to:
SQL Database named dbo.tblInItem field ItemID
Thank you for any assistance...
-Tobias Mazzei
Wraith SystemsI prefer adding a linked server to the excel file (look up sp_addlinkedserve
r
in Books Online) to bring the two data sources to common ground.
Building an INSERT query is pretty simple from there:
insert <SQL_table>
(
<destination_column_list>
)
select <source_column_list>
from <Excel_table>
where (not exists (
..the excluding query is
dependent on the actual data
..basically you need to list
items that already exist in the destination table
))
For better help, please post DDL and sample data.
ML|||This is ussually how I check if a record already exsists, you can change the
creteria
IF EXISTS (SELECT 'True' FROM tb_Alerts WHERE ID = @.AlertID)
BEGIN
UPDATE tb_Alerts
SET AlertName = @.AlertName
WHERE ID = @.AlertID;
SELECT @.AlertID;
END
ELSE
BEGIN
INSERT INTO tb_Alerts (AlertName)
VALUES (@.AlertName)
SELECT @.@.IDENTITY;
END
-Mark
"Wraith Systems" <WraithSystems@.discussions.microsoft.com> wrote in message
news:AD088624-7F0F-4F88-BD8C-5C0AE09F754D@.microsoft.com...
> There have been some pretty advanced stuff here I've gone through, but I
> am a
> .NET programmer by trade, so T-SQL is a bit different for me.
> I have an outside list of items with ID codes that I need to import to an
> existing table, but I need to have the procedure check for dups in the
> excel
> list BEFORE inserting them, if the ID already exists in the database, then
> skip and move onto the next one.
> (See the OOP concept here?)
> I was thinking about something along the line of an If Exist followed by a
> skip record or insert, but I think I am making this too complicated.
> can someone give me a down-to-earth example of how to accomplish this?
> Environment:
> From: Excel File named InItem
> ID field: ItemID
> to:
> SQL Database named dbo.tblInItem field ItemID
> Thank you for any assistance...
> --
> -Tobias Mazzei
> Wraith Systems
>|||I am getting closer. Thanks for the response, I tried looking up "not exists
"
in the help files, and with the lack of definition, it brought me here.
where (not exist (exceltableID=SQLtableID))
This was my initial concept, but it didn't look like SQL understood what I
was trying to tell it, the words not and exists were greyed.
So the concept was to select the items where the SQLtable did not have (or
equal to) what was the current record the datareader was at, right?
is this accurate useage? and can this be done in a stored procedure?
-Tobias Mazzei
Wraith Systems
"ML" wrote:

> I prefer adding a linked server to the excel file (look up sp_addlinkedser
ver
> in Books Online) to bring the two data sources to common ground.
> Building an INSERT query is pretty simple from there:
> insert <SQL_table>
> (
> <destination_column_list>
> )
> select <source_column_list>
> from <Excel_table>
> where (not exists (
> ...the excluding query is
> dependent on the actual data
> ...basically you need to list
> items that already exist in the destination table
> ))
>
> For better help, please post DDL and sample data.
>
> ML|||On Mon, 7 Nov 2005 15:09:02 -0800, Wraith Systems wrote:

>I am getting closer. Thanks for the response, I tried looking up "not exist
s"
>in the help files, and with the lack of definition, it brought me here.
>where (not exist (exceltableID=SQLtableID))
>This was my initial concept, but it didn't look like SQL understood what I
>was trying to tell it, the words not and exists were greyed.
>So the concept was to select the items where the SQLtable did not have (or
>equal to) what was the current record the datareader was at, right?
>is this accurate useage? and can this be done in a stored procedure?
Hi Tobias,
No, this is not a correct query. The argument to an EXISTS clause should
be a subquery. And in this case, the subquery should be correlated to
the main query.
Try adapting the code below to your tables:
INSERT INTO DestTable (KeyColumn, DataColumn1, DataColumn2)
SELECT s.KeyColumn, s.DataColumn1, s.DataColumn2
FROM SourceTable AS s
WHERE NOT EXISTS (SELECT *
FROM DestTable AS d
WHERE d.KeyColumn = s.KeyColumn)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thank you Hugo. I think Microsoft finally answered the question as well, but
using a <not in> statement. This seems to work as well. If there are
performance factors, I have not found them yet, but I am only inserting
around 1,000 items at a time per batch.
-Tobias Mazzei
Wraith Systems
"Hugo Kornelis" wrote:

> On Mon, 7 Nov 2005 15:09:02 -0800, Wraith Systems wrote:
>
> Hi Tobias,
> No, this is not a correct query. The argument to an EXISTS clause should
> be a subquery. And in this case, the subquery should be correlated to
> the main query.
> Try adapting the code below to your tables:
> INSERT INTO DestTable (KeyColumn, DataColumn1, DataColumn2)
> SELECT s.KeyColumn, s.DataColumn1, s.DataColumn2
> FROM SourceTable AS s
> WHERE NOT EXISTS (SELECT *
> FROM DestTable AS d
> WHERE d.KeyColumn = s.KeyColumn)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||On Wed, 9 Nov 2005 08:46:33 -0800, Wraith Systems wrote:

>Thank you Hugo. I think Microsoft finally answered the question as well, bu
t
>using a <not in> statement. This seems to work as well. If there are
>performance factors, I have not found them yet, but I am only inserting
>around 1,000 items at a time per batch.
Hi Tobias,
There are two things to consider when you use NOT IN with a subquery:
1. If one of the rows in the subquery returns a NULL, the NOT IN will
never evaluate to True. You have to make sure that the subquery can't
return NULLs, else your query will fail.
If you use NOT EXISTS, then you don't have this problem.
2. The NOT IN can only be used if you have a sigle-key column. For a
multi-key column, NOT IN can't be used, since SQL Server doesn't
implement the ANSI standard row constructor.
With NOT EXISTS, the number of columns in the key doesn't matter.
For these reasons, I recommend to always rewrite NOT IN with a subquery
to NOT EXISTS with a subquery. Using NOT IN with a list of constants (as
in "WHERE Status NOT IN ('Sold out', 'Discontinued')") is okay, though.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Here is what I am using currently, but based on your responses, I anticipate
issues.
Insert Into <server>.tblInItemAddDescr
select ItemId,AddlDescr,ts
from xclItemAddDescr...[tblInItemAddDescr$]
where itemid not in(select itemid from <server>.tblInItemAddDescr)
go
I am not sure what multi-key vs. single-key would be considered, would you
be able to give a bit more detail on that?
-Tobias Mazzei
Wraith Systems
"Hugo Kornelis" wrote:

> On Wed, 9 Nov 2005 08:46:33 -0800, Wraith Systems wrote:
>
> Hi Tobias,
> There are two things to consider when you use NOT IN with a subquery:
> 1. If one of the rows in the subquery returns a NULL, the NOT IN will
> never evaluate to True. You have to make sure that the subquery can't
> return NULLs, else your query will fail.
> If you use NOT EXISTS, then you don't have this problem.
> 2. The NOT IN can only be used if you have a sigle-key column. For a
> multi-key column, NOT IN can't be used, since SQL Server doesn't
> implement the ANSI standard row constructor.
> With NOT EXISTS, the number of columns in the key doesn't matter.
> For these reasons, I recommend to always rewrite NOT IN with a subquery
> to NOT EXISTS with a subquery. Using NOT IN with a list of constants (as
> in "WHERE Status NOT IN ('Sold out', 'Discontinued')") is okay, though.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||On Wed, 9 Nov 2005 16:27:57 -0800, Wraith Systems wrote:

>Here is what I am using currently, but based on your responses, I anticipat
e
>issues.
>Insert Into <server>.tblInItemAddDescr
>select ItemId,AddlDescr,ts
>from xclItemAddDescr...[tblInItemAddDescr$]
>where itemid not in(select itemid from <server>.tblInItemAddDescr)
>go
Hi Tobias,
If the column itemid in <server>.tblInItemAddDescr can't be NULL, then
you won't have any issued with this. You couls still compare it to the
corresponding NOT EXISTS version to test for performance differences,
though.
OTOH, if the itemid column can hold NULLs, then you *will* have issues.

>I am not sure what multi-key vs. single-key would be considered, would you
>be able to give a bit more detail on that?
My bad. I messed up when typping. I meant multi-column vs single-column
keys.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

dtswiz sqlserver2005

Is there somthing like dtswiz (from sqlserver 2000) in sqlserver 2005?
I want to import a tab separated file into a table.
Sure. In Management Studio Object Explorer right click on a database and
select Tasks>Import Data
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Anonymous" <anonymous@.noemail.com> wrote in message
news:%231v1Y%23S5FHA.1276@.TK2MSFTNGP09.phx.gbl...
> Is there somthing like dtswiz (from sqlserver 2000) in sqlserver 2005?
> I want to import a tab separated file into a table.
>
|||"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:eaCH41V5FHA.3760@.TK2MSFTNGP14.phx.gbl...
> Sure. In Management Studio Object Explorer right click on a database and
> select Tasks>Import Data
> --
> HTH
> Jasper Smith (SQL Server MVP)
<snip>
Ok, so I now know I am not losing my mind, when I look through the
Management Studio, and feel like I can't do anything. When I right click my
DB, I don't see the Import Data option. I am trying to move from one ISP
using SQL 2000 to one using SQL 2005. I downloaded:
Microsoft SQL Server Management Studio Express 9.00.1399.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00
(xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 6.0.2900.2180
Microsoft .NET Framework 2.0.50727.42
Operating System 5.1.2600
What am I doing wrong? Management Studio seems so crippled, that I can't do
anything.
BV.
|||I don't think that functionality is available with the
Express Edition.
The Express Edition of Management Studio doesn't not include
all the features available in the other versions of SSMS.
-Sue
On Mon, 16 Jan 2006 23:25:24 -0500, "BenignVanilla"
<bvanillaREMOVE@.tibetanbeefgarden.com> wrote:

>"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
>news:eaCH41V5FHA.3760@.TK2MSFTNGP14.phx.gbl...
><snip>
>Ok, so I now know I am not losing my mind, when I look through the
>Management Studio, and feel like I can't do anything. When I right click my
>DB, I don't see the Import Data option. I am trying to move from one ISP
>using SQL 2000 to one using SQL 2005. I downloaded:
>Microsoft SQL Server Management Studio Express 9.00.1399.00
>Microsoft Data Access Components (MDAC) 2000.085.1117.00
>(xpsp_sp2_rtm.040803-2158)
>Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
>Microsoft Internet Explorer 6.0.2900.2180
>Microsoft .NET Framework 2.0.50727.42
>Operating System 5.1.2600
>What am I doing wrong? Management Studio seems so crippled, that I can't do
>anything.
>BV.
>

DTSing Oracle data into SQL Server

I need to import some data from Oracle into Sql Server.
I've installed the Oracle's OleDB provider (OraOLEDB_92020.exe).
Do I need to install anything else on the SQL Server so that I can import the data?
Cheers
NickOriginally posted by riggers
I need to import some data from Oracle into Sql Server.

I've installed the Oracle's OleDB provider (OraOLEDB_92020.exe).

Do I need to install anything else on the SQL Server so that I can import the data?

Cheers

Nick
I don't think so|||linked server|||I've tried just installing the OleDb provider but it appears from furthering reading on the web that I need to install the oracle client tools aswell as the OleDb provider.

I've been told that we are running Oracle 10.7 and I can not appear to find the client tools for this on Oracle's website.

Does anyone know where the client tools for this version of Oracle are?

Cheers

Nick

DTS_E_PRIMEOUTPUTFAILED

I'm attempting to import a dbf file into a SQL server staging table. The following error is being reported:

[DTS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "DBF File" (1) returned error code 0x80040E21. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

Data provider is Jet 4.0. Is this just a corrupt file? Or does this indicate something else? The package has run successfully on all previous occasions. Thanks in advance.

Please provide the surrounding errors as well.|||

This is the first error in the chain. The others seem to be a result of this error, but for completeness, I will include them here.

[DTS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "OLE DB Source" (1) returned error code 0x80040E21. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

[DTS.Pipeline] Error: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.

[DTS.Pipeline] Error: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.

[DTS.Pipeline] Error: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.

Thank you for your kind assistance.

|||You might want to read through this to see if anything works for you.

http://adopenstatic.com/faq/80040e21.asp|||Well, the file is definitely corrupt. I'm getting an error when I try to import it to Access.|||

Benda Fenda wrote:

Well, the file is definitely corrupt. I'm getting an error when I try to import it to Access.

Glad you got to that point at least.

DTS: import large database

Importing data from an Access database, I cannot overcome the limit of 1,000 records.
In DTS, I "copy one or more tables", select tables, run, and cannot see my 1,052 entries.
Where can I set a max size of ~1,500 in my sql target base?There's a limit on what you can copy? By default, I have been able to transfer (even from Access) more than 100,000 records at a time. Heck, from other db's, I have been able to transfer MILLIONS of records by default.

A quick workaround would be to do an export to text (preferrably .csv) and import that into SQL. You can even do that through SQL, I think, by selecting your source as Access and your target as Text file through DTS.

DTS: how to dynamically import Excel spreadsheet into SQL Server?

(SQL Server 2000, SP3a)
Hello all!
I have a series of Excel spreadsheets, of which I don't know the columns beforehand, that
I'd like to import into SQL Server. These spreadsheets are pretty simple in that they'll
always have a title row and all the data will be on the primary (first) worksheet. I'd
like to dynamically create a *new* table in my database that maps one-to-one with the
columns in the spreadsheet.
Obviously, from the DTS Designer, this is pretty straightforward using the Transform Data
Task on a specific spreadsheet basis. However, I'd like to make the package dynamic,
where I can specify a spreadsheet, have DTS introspect the spreadsheet columns (is this
even possible?), create a destination table, and squirt the data into the table.
I'd be very grateful for any help anyone can provide! :-)
John Peterson
John,
You can do this with an ad hoc query:
select * into #abc
from OpenRowSet( 'Microsoft.Jet.OLEDB.4.0', 'Excel
8.0;Database=c:\excel\yourFile.xls;HDR=YES;IMEX=1' ,Sheet1$)
See the threads at
http://groups.google.com/groups?q=29...8-39393E666E76
for some more information about importing from Excel.
In order to make this dynamic, you'll need to create the entire query
dynamically, something like this:
declare @.sql nvarchar(2000)
set @.sql = 'select * into ##table##
from OpenRowSet( ''Microsoft.Jet.OLEDB.4.0'', ''Excel
8.0;Database=c:\excel\##file##.xls;HDR=YES;IMEX=1' ' ,Sheet1$) '
set @.sql = replace(@.sql,'##file##','thisfileistheone')
set @.sql = replace(@.sql,'##table##','thisisthetablenametogive it)
exec(@.sql)
Do not let users type in filenames, or if you do, replace using
QUOTENAME(@.filename,char(39)) and not without the quotename. Failure to
do this opens the door for SQL Injection attacks. See
http://www.sommarskog.se/dynamic_sql.html
Steve Kass
Drew University
John Peterson wrote:

>(SQL Server 2000, SP3a)
>Hello all!
>I have a series of Excel spreadsheets, of which I don't know the columns beforehand, that
>I'd like to import into SQL Server. These spreadsheets are pretty simple in that they'll
>always have a title row and all the data will be on the primary (first) worksheet. I'd
>like to dynamically create a *new* table in my database that maps one-to-one with the
>columns in the spreadsheet.
>Obviously, from the DTS Designer, this is pretty straightforward using the Transform Data
>Task on a specific spreadsheet basis. However, I'd like to make the package dynamic,
>where I can specify a spreadsheet, have DTS introspect the spreadsheet columns (is this
>even possible?), create a destination table, and squirt the data into the table.
>I'd be very grateful for any help anyone can provide! :-)
>John Peterson
>
>
|||Steve Kass <skass@.drew.edu> wrote in message ...
[vbcol=seagreen]

> You can do this with an ad hoc query:
> select * into #abc
> from OpenRowSet( 'Microsoft.Jet.OLEDB.4.0', 'Excel
> 8.0;Database=c:\excel\yourFile.xls;HDR=YES;IMEX=1' ,Sheet1$)
Now all the OP needs to know is how to find the name of the first
worksheet. The first sheet isn't necessarily named Sheet1 and vice
versa. Hint: how would you find the name of the first table in a SQL
Server DB?
Jamie.
|||Oh -- that's clever! I think I can work with that -- thanks guys!
One question: does the OPENQUERY operate on the path from the SQL Server? I'm betting
that it does, which adds a considerable wrinkle...
Jamie, in my case, I think that there will only be one Worksheet that I need to worry
about -- so I'm hoping that determining which is the "first" sheet won't be a problem.
Though, if I understand your concern correctly -- it seems like I need to know the *name*
of that worksheet beforehand in the OPENQUERY?
"Jamie Collins" <jamiecollins@.xsmail.com> wrote in message
news:2ed66b75.0407150616.17615762@.posting.google.c om...
> Steve Kass <skass@.drew.edu> wrote in message ...
>
> Now all the OP needs to know is how to find the name of the first
> worksheet. The first sheet isn't necessarily named Sheet1 and vice
> versa. Hint: how would you find the name of the first table in a SQL
> Server DB?
> Jamie.
> --
|||"John Peterson" wrote ...

> Jamie, in my case, I think that there will
> only be one Worksheet that I need to worry
> about -- so I'm hoping that determining which
> is the "first" sheet won't be a problem.
> Though, if I understand your concern correctly
> -- it seems like I need to know the *name*
> of that worksheet beforehand in the OPENQUERY?
Correct, unless you are using a defined Name ('named range') in the
Excel workbook in which case you'd need to know the Name's name.
Jamie.
|||I'm still at kind of an impasse with this. I had thought that Steve's suggestion might
work for me, but there are issues with OPENROWSET that are kind of stymieing me.
Now I'm kind of thinking that if I can introspect the Excel spreadsheet to identify all
the columns that are "in use" and create Transformations for them, maybe I can do
something with that. Problem is, that seems like a *lot* of work.
If anyone has any other suggestions, I'd be obliged!
"Jamie Collins" <jamiecollins@.xsmail.com> wrote in message
news:2ed66b75.0407160030.7a3ae242@.posting.google.c om...
> "John Peterson" wrote ...
>
> Correct, unless you are using a defined Name ('named range') in the
> Excel workbook in which case you'd need to know the Name's name.
> Jamie.
> --
|||John,
You can retrieve the sheet and named region names from an excel file
that has been added as a linked server, if that's some help:
exec sp_tables_ex exlsrv
Can you be more specific about what your Excel file contains and what
you need to retrieve?
Steve
John Peterson wrote:

>I'm still at kind of an impasse with this. I had thought that Steve's suggestion might
>work for me, but there are issues with OPENROWSET that are kind of stymieing me.
>Now I'm kind of thinking that if I can introspect the Excel spreadsheet to identify all
>the columns that are "in use" and create Transformations for them, maybe I can do
>something with that. Problem is, that seems like a *lot* of work.
>If anyone has any other suggestions, I'd be obliged!
>
>"Jamie Collins" <jamiecollins@.xsmail.com> wrote in message
>news:2ed66b75.0407160030.7a3ae242@.posting.google. com...
>
>
>

DTS: how to dynamically import Excel spreadsheet into SQL Server?

(SQL Server 2000, SP3a)
Hello all!
I have a series of Excel spreadsheets, of which I don't know the columns beforehand, that
I'd like to import into SQL Server. These spreadsheets are pretty simple in that they'll
always have a title row and all the data will be on the primary (first) worksheet. I'd
like to dynamically create a *new* table in my database that maps one-to-one with the
columns in the spreadsheet.
Obviously, from the DTS Designer, this is pretty straightforward using the Transform Data
Task on a specific spreadsheet basis. However, I'd like to make the package dynamic,
where I can specify a spreadsheet, have DTS introspect the spreadsheet columns (is this
even possible?), create a destination table, and squirt the data into the table.
I'd be very grateful for any help anyone can provide! :-)
John PetersonJohn,
You can do this with an ad hoc query:
select * into #abc
from OpenRowSet( 'Microsoft.Jet.OLEDB.4.0', 'Excel
8.0;Database=c:\excel\yourFile.xls;HDR=YES;IMEX=1' ,Sheet1$)
See the threads at
http://groups.google.com/groups?q=29C76785-22D9-46A2-A398-39393E666E76
for some more information about importing from Excel.
In order to make this dynamic, you'll need to create the entire query
dynamically, something like this:
declare @.sql nvarchar(2000)
set @.sql = 'select * into ##table##
from OpenRowSet( ''Microsoft.Jet.OLEDB.4.0'', ''Excel
8.0;Database=c:\excel\##file##.xls;HDR=YES;IMEX=1'' ,Sheet1$) '
set @.sql = replace(@.sql,'##file##','thisfileistheone')
set @.sql = replace(@.sql,'##table##','thisisthetablenametogiveit)
exec(@.sql)
Do not let users type in filenames, or if you do, replace using
QUOTENAME(@.filename,char(39)) and not without the quotename. Failure to
do this opens the door for SQL Injection attacks. See
http://www.sommarskog.se/dynamic_sql.html
Steve Kass
Drew University
John Peterson wrote:
>(SQL Server 2000, SP3a)
>Hello all!
>I have a series of Excel spreadsheets, of which I don't know the columns beforehand, that
>I'd like to import into SQL Server. These spreadsheets are pretty simple in that they'll
>always have a title row and all the data will be on the primary (first) worksheet. I'd
>like to dynamically create a *new* table in my database that maps one-to-one with the
>columns in the spreadsheet.
>Obviously, from the DTS Designer, this is pretty straightforward using the Transform Data
>Task on a specific spreadsheet basis. However, I'd like to make the package dynamic,
>where I can specify a spreadsheet, have DTS introspect the spreadsheet columns (is this
>even possible?), create a destination table, and squirt the data into the table.
>I'd be very grateful for any help anyone can provide! :-)
>John Peterson
>
>|||Steve Kass <skass@.drew.edu> wrote in message ...
> > I have a series of Excel spreadsheets, of which
> > I don't know the columns beforehand, that
> > I'd like to import into SQL Server. These spreadsheets
> > are pretty simple in that they'll
> > always have a title row and all the data will be
> > on the primary (first) worksheet.
> You can do this with an ad hoc query:
> select * into #abc
> from OpenRowSet( 'Microsoft.Jet.OLEDB.4.0', 'Excel
> 8.0;Database=c:\excel\yourFile.xls;HDR=YES;IMEX=1' ,Sheet1$)
Now all the OP needs to know is how to find the name of the first
worksheet. The first sheet isn't necessarily named Sheet1 and vice
versa. Hint: how would you find the name of the first table in a SQL
Server DB?
Jamie.
--|||Oh -- that's clever! I think I can work with that -- thanks guys!
One question: does the OPENQUERY operate on the path from the SQL Server? I'm betting
that it does, which adds a considerable wrinkle...
Jamie, in my case, I think that there will only be one Worksheet that I need to worry
about -- so I'm hoping that determining which is the "first" sheet won't be a problem.
Though, if I understand your concern correctly -- it seems like I need to know the *name*
of that worksheet beforehand in the OPENQUERY?
"Jamie Collins" <jamiecollins@.xsmail.com> wrote in message
news:2ed66b75.0407150616.17615762@.posting.google.com...
> Steve Kass <skass@.drew.edu> wrote in message ...
> > > I have a series of Excel spreadsheets, of which
> > > I don't know the columns beforehand, that
> > > I'd like to import into SQL Server. These spreadsheets
> > > are pretty simple in that they'll
> > > always have a title row and all the data will be
> > > on the primary (first) worksheet.
> > You can do this with an ad hoc query:
> >
> > select * into #abc
> > from OpenRowSet( 'Microsoft.Jet.OLEDB.4.0', 'Excel
> > 8.0;Database=c:\excel\yourFile.xls;HDR=YES;IMEX=1' ,Sheet1$)
> Now all the OP needs to know is how to find the name of the first
> worksheet. The first sheet isn't necessarily named Sheet1 and vice
> versa. Hint: how would you find the name of the first table in a SQL
> Server DB?
> Jamie.
> --|||"John Peterson" wrote ...
> Jamie, in my case, I think that there will
> only be one Worksheet that I need to worry
> about -- so I'm hoping that determining which
> is the "first" sheet won't be a problem.
> Though, if I understand your concern correctly
> -- it seems like I need to know the *name*
> of that worksheet beforehand in the OPENQUERY?
Correct, unless you are using a defined Name ('named range') in the
Excel workbook in which case you'd need to know the Name's name.
Jamie.
--|||I'm still at kind of an impasse with this. I had thought that Steve's suggestion might
work for me, but there are issues with OPENROWSET that are kind of stymieing me.
Now I'm kind of thinking that if I can introspect the Excel spreadsheet to identify all
the columns that are "in use" and create Transformations for them, maybe I can do
something with that. Problem is, that seems like a *lot* of work.
If anyone has any other suggestions, I'd be obliged!
"Jamie Collins" <jamiecollins@.xsmail.com> wrote in message
news:2ed66b75.0407160030.7a3ae242@.posting.google.com...
> "John Peterson" wrote ...
> > Jamie, in my case, I think that there will
> > only be one Worksheet that I need to worry
> > about -- so I'm hoping that determining which
> > is the "first" sheet won't be a problem.
> > Though, if I understand your concern correctly
> > -- it seems like I need to know the *name*
> > of that worksheet beforehand in the OPENQUERY?
> Correct, unless you are using a defined Name ('named range') in the
> Excel workbook in which case you'd need to know the Name's name.
> Jamie.
> --|||John,
You can retrieve the sheet and named region names from an excel file
that has been added as a linked server, if that's some help:
exec sp_tables_ex exlsrv
Can you be more specific about what your Excel file contains and what
you need to retrieve?
Steve
John Peterson wrote:
>I'm still at kind of an impasse with this. I had thought that Steve's suggestion might
>work for me, but there are issues with OPENROWSET that are kind of stymieing me.
>Now I'm kind of thinking that if I can introspect the Excel spreadsheet to identify all
>the columns that are "in use" and create Transformations for them, maybe I can do
>something with that. Problem is, that seems like a *lot* of work.
>If anyone has any other suggestions, I'd be obliged!
>
>"Jamie Collins" <jamiecollins@.xsmail.com> wrote in message
>news:2ed66b75.0407160030.7a3ae242@.posting.google.com...
>
>>"John Peterson" wrote ...
>>
>>Jamie, in my case, I think that there will
>>only be one Worksheet that I need to worry
>>about -- so I'm hoping that determining which
>>is the "first" sheet won't be a problem.
>>Though, if I understand your concern correctly
>>-- it seems like I need to know the *name*
>>of that worksheet beforehand in the OPENQUERY?
>>
>>Correct, unless you are using a defined Name ('named range') in the
>>Excel workbook in which case you'd need to know the Name's name.
>>Jamie.
>>--
>>
>
>

DTS: how to dynamically import Excel spreadsheet into SQL Server?

(SQL Server 2000, SP3a)
Hello all!
I have a series of Excel spreadsheets, of which I don't know the columns bef
orehand, that
I'd like to import into SQL Server. These spreadsheets are pretty simple in
that they'll
always have a title row and all the data will be on the primary (first) work
sheet. I'd
like to dynamically create a *new* table in my database that maps one-to-one
with the
columns in the spreadsheet.
Obviously, from the DTS Designer, this is pretty straightforward using the T
ransform Data
Task on a specific spreadsheet basis. However, I'd like to make the package
dynamic,
where I can specify a spreadsheet, have DTS introspect the spreadsheet colum
ns (is this
even possible?), create a destination table, and squirt the data into the ta
ble.
I'd be very grateful for any help anyone can provide! :-)
John PetersonJohn,
You can do this with an ad hoc query:
select * into #abc
from OpenRowSet( 'Microsoft.Jet.OLEDB.4.0', 'Excel
8.0;Database=c:\excel\yourFile.xls;HDR=YES;IMEX=1' ,Sheet1$)
See the threads at
http://groups.google.com/groups?q=2...98-39393E666E76
for some more information about importing from Excel.
In order to make this dynamic, you'll need to create the entire query
dynamically, something like this:
declare @.sql nvarchar(2000)
set @.sql = 'select * into ##table##
from OpenRowSet( ''Microsoft.Jet.OLEDB.4.0'', ''Excel
8.0;Database=c:\excel\##file##.xls;HDR=YES;IMEX=1'' ,Sheet1$) '
set @.sql = replace(@.sql,'##file##','thisfileistheon
e')
set @.sql = replace(@.sql,'##table##','thisisthetable
nametogiveit)
exec(@.sql)
Do not let users type in filenames, or if you do, replace using
QUOTENAME(@.filename,char(39)) and not without the quotename. Failure to
do this opens the door for SQL Injection attacks. See
http://www.sommarskog.se/dynamic_sql.html
Steve Kass
Drew University
John Peterson wrote:

>(SQL Server 2000, SP3a)
>Hello all!
>I have a series of Excel spreadsheets, of which I don't know the columns be
forehand, that
>I'd like to import into SQL Server. These spreadsheets are pretty simple i
n that they'll
>always have a title row and all the data will be on the primary (first) wor
ksheet. I'd
>like to dynamically create a *new* table in my database that maps one-to-on
e with the
>columns in the spreadsheet.
>Obviously, from the DTS Designer, this is pretty straightforward using the
Transform Data
>Task on a specific spreadsheet basis. However, I'd like to make the packag
e dynamic,
>where I can specify a spreadsheet, have DTS introspect the spreadsheet colu
mns (is this
>even possible?), create a destination table, and squirt the data into the t
able.
>I'd be very grateful for any help anyone can provide! :-)
>John Peterson
>
>|||Steve Kass <skass@.drew.edu> wrote in message ...

[vbcol=seagreen]
> You can do this with an ad hoc query:
> select * into #abc
> from OpenRowSet( 'Microsoft.Jet.OLEDB.4.0', 'Excel
> 8.0;Database=c:\excel\yourFile.xls;HDR=YES;IMEX=1' ,Sheet1$)
Now all the OP needs to know is how to find the name of the first
worksheet. The first sheet isn't necessarily named Sheet1 and vice
versa. Hint: how would you find the name of the first table in a SQL
Server DB?
Jamie.|||Oh -- that's clever! I think I can work with that -- thanks guys!
One question: does the OPENQUERY operate on the path from the SQL Server?
I'm betting
that it does, which adds a considerable wrinkle...
Jamie, in my case, I think that there will only be one Worksheet that I need
to worry
about -- so I'm hoping that determining which is the "first" sheet won't be
a problem.
Though, if I understand your concern correctly -- it seems like I need to kn
ow the *name*
of that worksheet beforehand in the OPENQUERY?
"Jamie Collins" <jamiecollins@.xsmail.com> wrote in message
news:2ed66b75.0407150616.17615762@.posting.google.com...
> Steve Kass <skass@.drew.edu> wrote in message ...
>
>
> Now all the OP needs to know is how to find the name of the first
> worksheet. The first sheet isn't necessarily named Sheet1 and vice
> versa. Hint: how would you find the name of the first table in a SQL
> Server DB?
> Jamie.
> --|||"John Peterson" wrote ...

> Jamie, in my case, I think that there will
> only be one Worksheet that I need to worry
> about -- so I'm hoping that determining which
> is the "first" sheet won't be a problem.
> Though, if I understand your concern correctly
> -- it seems like I need to know the *name*
> of that worksheet beforehand in the OPENQUERY?
Correct, unless you are using a defined Name ('named range') in the
Excel workbook in which case you'd need to know the Name's name.
Jamie.|||I'm still at kind of an impasse with this. I had thought that Steve's sugge
stion might
work for me, but there are issues with OPENROWSET that are kind of stymieing
me.
Now I'm kind of thinking that if I can introspect the Excel spreadsheet to i
dentify all
the columns that are "in use" and create Transformations for them, maybe I c
an do
something with that. Problem is, that seems like a *lot* of work.
If anyone has any other suggestions, I'd be obliged!
"Jamie Collins" <jamiecollins@.xsmail.com> wrote in message
news:2ed66b75.0407160030.7a3ae242@.posting.google.com...
> "John Peterson" wrote ...
>
> Correct, unless you are using a defined Name ('named range') in the
> Excel workbook in which case you'd need to know the Name's name.
> Jamie.
> --|||John,
You can retrieve the sheet and named region names from an excel file
that has been added as a linked server, if that's some help:
exec sp_tables_ex exlsrv
Can you be more specific about what your Excel file contains and what
you need to retrieve?
Steve
John Peterson wrote:

>I'm still at kind of an impasse with this. I had thought that Steve's sugg
estion might
>work for me, but there are issues with OPENROWSET that are kind of stymiein
g me.
>Now I'm kind of thinking that if I can introspect the Excel spreadsheet to
identify all
>the columns that are "in use" and create Transformations for them, maybe I
can do
>something with that. Problem is, that seems like a *lot* of work.
>If anyone has any other suggestions, I'd be obliged!
>
>"Jamie Collins" <jamiecollins@.xsmail.com> wrote in message
>news:2ed66b75.0407160030.7a3ae242@.posting.google.com...
>
>
>

DTS: EXCEL TO SQL: Returns an empty recodset

Hi there,

I use Asp.Net Application to upload a Excel file and then a DTS to import data from the file to the SQL2000 and finally to display the read data on the screen.

The DTS starts with setting some variables with the help of Dynamic properties.
On Succes.
DTS rum 2 simultaneous Transform Data task importing data from excel to SQL2000.

This works fine for "most" of the time, but then there are the other times.
One of the TDT(Transform Data task) reads nothing from the excel file. but it can read the data if i upload the same file again right after.

Any kind of input is welcome

Thanx
/bs26Originally posted by bs26
Hi there,

I use Asp.Net Application to upload a Excel file and then a DTS to import data from the file to the SQL2000 and finally to display the read data on the screen.

The DTS starts with setting some variables with the help of Dynamic properties.
On Succes.
DTS rum 2 simultaneous Transform Data task importing data from excel to SQL2000.

This works fine for "most" of the time, but then there are the other times.
One of the TDT(Transform Data task) reads nothing from the excel file. but it can read the data if i upload the same file again right after.

Any kind of input is welcome

Thanx
/bs26

It sounds as if you are experiencing a locking problem with the two Transform Data tasks. I have had similar problems with importing and exporting to Access databases.

Try it with only one TDT and see if it fixes it.

If you need the additional speed of multiple TDT's, you might try splitting up or duplicating the data over several excel workbooks.

DTS: excel to sql server

I posted this earlier, but it did not seem to get any views. Please help!

I'm trying to import an excel spreadsheet into SQL Server. I keep getting an error saying that 2 columns cannot except NULL values. Here's the setup of each file.

SQL TBL:
My table has 9 columns. the first column is a Primary Key and IDENTITY(1,1). My last column is a dateCreated column with a default of getDate(). Both values are set to NOT NULL. The 7 inside columns are just plain varChar datatypes.

XLS:
My spreadsheet has 7 columns which corelate to the 7 inside columns of my SQL TBL. I do not want to specify my first and last columns because they should be unique to the SQL TBL (identity and getDate()).

Any help with how to do this would be great. If you need more info, please let me know.

Thanks,
TimTim:

If this table is already full of data, edit the DTS package Transformation properties to NOT Copy to EITHER your PK or Date field.

Otherwise, if the table's empty, drop the PK & Date fields for the initial import THEN re-add them back.

Hope this helps

RobbieD

DTS: Data from Excel to Table

I'm having some trouble with import data from an excel document to a table. The scenario is as follows:
The excel document has two columns A and B. Col A has to be copied into the table (table has 3 columns Col 1 - Primay key with autoincrement feature, Col 2 - is Col A from the Excel document, Col 3 - irrelevant)

Depending on Col B from excel doc the corresponding Col A data should be copied to the table. I tired using a DTS pacakage to do this but it complains becuase Col 1 (table) gets a valus of NULL for every value in Col A. I mapped only Col A to Col 2 in the Transformaton tab.

Any ideas? Seems like a simple job?!?

Thanks for any inputWhy not just DTS it in to a work table, the use sql to do what you want to do?

It'll be easier and more powerful.|||Originally posted by vmlal
I'm having some trouble with import data from an excel document to a table. The scenario is as follows:
The excel document has two columns A and B. Col A has to be copied into the table (table has 3 columns Col 1 - Primay key with autoincrement feature, Col 2 - is Col A from the Excel document, Col 3 - irrelevant)

Depending on Col B from excel doc the corresponding Col A data should be copied to the table. I tired using a DTS pacakage to do this but it complains becuase Col 1 (table) gets a valus of NULL for every value in Col A. I mapped only Col A to Col 2 in the Transformaton tab.

Any ideas? Seems like a simple job?!?

Thanks for any input

I had that idea, I was just looking for another idea involving some ActiveX script while Transformation columns.

Thanks newayz|||Originally posted by vmlal
I had that idea, I was just looking for another idea involving some ActiveX script while Transformation columns.

Thanks newayz

It just slows down the transfer in my experience, because it has to handle a row at a time...

If you use sql, you can perform set operation, which will be faster in the long run.

DTS: Converting datatype in the source before import

Hi, In my source (text file connection) there is a date field but the date
is stored yyyymmdd (20080226)
I need to convert this into a real date 02/26/2008 and then import it into
the sql server database into a datetime field.
I'm a newbie with DTS so very detailed insturctions would be greatly
appreciated. When I just tried having it import into SQL Server I got an
error obviously since the date in the source data is not in a format sql
server will understandI'm not going to get detailed but I'll give you one way to do this. You'll
have to do some research on your own.
Import into a varchar column then use convert to transform and push into
your destination datetime column
--
Sincerely,
John K
Knowledgy Consulting
http://knowledgy.org/
Atlanta's Business Intelligence and Data Warehouse Experts
"Patrick Hill" <phill@.nospam_quickcomm.com> wrote in message
news:uKzCVkIeIHA.1212@.TK2MSFTNGP05.phx.gbl...
> Hi, In my source (text file connection) there is a date field but the date
> is stored yyyymmdd (20080226)
> I need to convert this into a real date 02/26/2008 and then import it into
> the sql server database into a datetime field.
> I'm a newbie with DTS so very detailed insturctions would be greatly
> appreciated. When I just tried having it import into SQL Server I got an
> error obviously since the date in the source data is not in a format sql
> server will understand
>
>|||i forgot to mention use convert function when pushing varchar into datetime
column
--
Sincerely,
John K
Knowledgy Consulting
http://knowledgy.org/
Atlanta's Business Intelligence and Data Warehouse Experts
"Knowledgy" <atlanta business intelligence consultants> wrote in message
news:XaednbkftahIS1nanZ2dnUVZ_gWdnZ2d@.comcast.com...
> I'm not going to get detailed but I'll give you one way to do this.
> You'll have to do some research on your own.
> Import into a varchar column then use convert to transform and push into
> your destination datetime column
> --
> Sincerely,
> John K
> Knowledgy Consulting
> http://knowledgy.org/
> Atlanta's Business Intelligence and Data Warehouse Experts
>
> "Patrick Hill" <phill@.nospam_quickcomm.com> wrote in message
> news:uKzCVkIeIHA.1212@.TK2MSFTNGP05.phx.gbl...
>> Hi, In my source (text file connection) there is a date field but the
>> date is stored yyyymmdd (20080226)
>> I need to convert this into a real date 02/26/2008 and then import it
>> into the sql server database into a datetime field.
>> I'm a newbie with DTS so very detailed insturctions would be greatly
>> appreciated. When I just tried having it import into SQL Server I got an
>> error obviously since the date in the source data is not in a format sql
>> server will understand
>>
>

DTS/SSIS too many tables warning and subsequent error

I need to bring over a large number of tables' records (200+ tables) with the Import/Export Wizard. The tables are being imported from MS Access. A separate script run previously will create the tables, so the DTS wizard is only to bring over the data from the Access tables into the empty SQL ones.

First, I get the warning that indicates "a large number of tables are selected for copying, and the wizard may not be able to copy all the tables in a session. Select no to go back and unselect some tables, or select Yes to attempt to copy all the currently selected tables at one time".

Well, I proceed with the DTS and it tries to validate and takes a fair bit, but then it errors indicating:

"Error 0xc0202009: {2F0FABA0-5F4B-4310-97C0-76EA19893547}: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Unspecified error".
(SQL Server Import and Export Wizard)"

Can anyone shed any light on why I receive an "unspecified error" when tring to DTS a larger number of tables. It does not error, if I import 40 or so tables.

This was never an issue with SQL 2000 DTS.

Thanks

"a large number of tables are selected for copying, and the wizard may not be able to copy all the tables in a session. Select no to go back and unselect some tables, or select Yes to attempt to copy all the currently selected tables at one time".

The wizard told you that it may not be able to do it.

|||

Hey,

Hah! Nice man. Well I hear you on that one, but why is this an issue with the SQL 2005 DTS and not the older SQL 2000 DTS, and is there any way around it?

Thanks

DTS/Async Stored procedure/Import huge data

I have a table which contains approx 3,00,000 records. I need to
import this data into another table by executing a stored procedure.
This stored procedure accepts the values from the table as params. My
current solution is reading the table in cursor and executing the
stored procedure. This takes tooooooo long. approx 5-6 hrs. I need to
make it better.

Can anyone help ?

SamirCursors are evil!!!!

However, to be able to fully answer your question, we need to see your
stored proc/schema or even the results of a showplan.

I have used DTS/stored procs to import all sorts of data and have
never once had to resort to cursors (maybe I have just been lucky).
Generally speaking, I normally just do a select statement from the
source table which is used as input to the destination and doing any
casting/coercion along the way.

eg

create proc finky as

insert into
source
(a
,b
,c
)
select
a as char(2)
,IsNull(b, '')
,c
from
destination

samirpandey@.hotmail.com (Samir Pandey) wrote in message news:<33d418fa.0308071922.5061c0a4@.posting.google.com>...
> I have a table which contains approx 3,00,000 records. I need to
> import this data into another table by executing a stored procedure.
> This stored procedure accepts the values from the table as params. My
> current solution is reading the table in cursor and executing the
> stored procedure. This takes tooooooo long. approx 5-6 hrs. I need to
> make it better.
> Can anyone help ?
> Samir|||Hey thanks for your help. but...i just can't simply insert data in
the destination table. I need to run the values thru a logic which
exists in stored procedure.(e.g. if existing price is less than the
new price, then update another field which means that the price has
reduced/increased on a particular day)... and some other i make in the
stored procedure.

Thats why i need to run the values thru a stored procedure.

now can you help...?
thanks alot...

PromisedOyster@.hotmail.com (Mystery Man) wrote in message news:<87c81238.0308080439.79404088@.posting.google.com>...
> Cursors are evil!!!!
> However, to be able to fully answer your question, we need to see your
> stored proc/schema or even the results of a showplan.
>
> I have used DTS/stored procs to import all sorts of data and have
> never once had to resort to cursors (maybe I have just been lucky).
> Generally speaking, I normally just do a select statement from the
> source table which is used as input to the destination and doing any
> casting/coercion along the way.
> eg
> create proc finky as
> insert into
> source
> (a
> ,b
> ,c
> )
> select
> a as char(2)
> ,IsNull(b, '')
> ,c
> from
> destination

DTS Wizard fails to retrieve long data

We are trying to import data into SQL Server 2005 from MySQL 5.0.x using DTS Wizard via MySQLOLE DB Provider developed by Cherry City Software .

We can read BLOB data from MySQL via ADO 2.7 and ADO.NET 2.0. We can also use DTS Wizard to read the data on Preview. For the test, we defined a table as tBlob (lText VARCHAR(9000)) and inserted one record with a few characters. However, DTS Wizard always fails at the executing step with the following error messages:

Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code: 0x80040E21.
(SQL Server Import and Export Wizard)

Error 0xc0208265: Data Flow Task: Failed to retrieve long data for column "lText".
(SQL Server Import and Export Wizard)

Error 0xc020901c: Data Flow Task: There was an error with output column "lText" (17) on output "OLE DB Source Output" (11). The column status returned was: "DBSTATUS_UNAVAILABLE".
(SQL Server Import and Export Wizard)

Error 0xc0209029: Data Flow Task: The "output column "lText" (17)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "lText" (17)" specifies failure on error. An error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)

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

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

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

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

This could be a defect in the DTS Wizard. How do we workaround this problem? Any help will be greatly appreciated.

Moving to the Is forum|||Unfortunately, the OLE DB error information available doesn't provide enough information to pinpoint the problem. Since you were able to use ADO.Net, you should trying using the DataReader Source adapter (it uses ADO.Net) instead of the OLE DB Source adapter.|||I should have said that I was able to use OleDb.Net and ADO 2.7+ as well to read the data back. With OleDb.Net, I was able to the data via OleDbDataReader.The weird thing is that the DTS Wizard can read the data on the preview. Why can't it read data on executing step?

DTS Wizard fails to retrieve long data

We are trying to import data into SQL Server 2005 from MySQL 5.0.x using DTS Wizard via MySQLOLE DB Provider developed by Cherry City Software .

We can read BLOB data from MySQL via ADO 2.7 and ADO.NET 2.0. We can also use DTS Wizard to read the data on Preview. For the test, we defined a table as tBlob (lText VARCHAR(9000)) and inserted one record with a few characters. However, DTS Wizard always fails at the executing step with the following error messages:

Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code: 0x80040E21.
(SQL Server Import and Export Wizard)

Error 0xc0208265: Data Flow Task: Failed to retrieve long data for column "lText".
(SQL Server Import and Export Wizard)

Error 0xc020901c: Data Flow Task: There was an error with output column "lText" (17) on output "OLE DB Source Output" (11). The column status returned was: "DBSTATUS_UNAVAILABLE".
(SQL Server Import and Export Wizard)

Error 0xc0209029: Data Flow Task: The "output column "lText" (17)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "lText" (17)" specifies failure on error. An error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)

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

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

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

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

This could be a defect in the DTS Wizard. How do we workaround this problem? Any help will be greatly appreciated.

Moving to the Is forum|||Unfortunately, the OLE DB error information available doesn't provide enough information to pinpoint the problem. Since you were able to use ADO.Net, you should trying using the DataReader Source adapter (it uses ADO.Net) instead of the OLE DB Source adapter.|||I should have said that I was able to use OleDb.Net and ADO 2.7+ as well to read the data back. With OleDb.Net, I was able to the data via OleDbDataReader.The weird thing is that the DTS Wizard can read the data on the preview. Why can't it read data on executing step?

DTS Wizard - Buggy?

Greetings,
I have been trying to use the DTS Import Wizard to import data, but SQL Server seems to have a bug that Microsoft has not yet addressed (or have they?)
Basically, when I import data from a text file into one of my existing databases that contains a field with an identity, I run into problems. Basically, SQL gives an error and refuses to import my data if the column is set to having an identity. In the Column Mappings, I set that row to "ignore." I also tried checking Enable Identity Insert, and I tried unchecking it. Nothing works. I receive this error message: "...The statement has been terminated. Cannot insert the value NULL into column 'MovieID', table 'myproject.dbo.MovieStars'; column does not allow nulls. INSERT fails."
I thought that checking "Enable Identity Insert" would automatically tell DTS to insert an incremental identity value.
When I use this transformation code, it doesn't work either:
Function Main()
DTSDestination("MovieName") = DTSSource("Col001")
DTSDestination("MovieStar") = DTSSource("Col002")
Main = DTSTransformStat_OK
End Function
This gives me a different but similar error: "Insert error, column 1, ('MovieID', DBTYPE_I4), status 10: Integrity violation; attempt insert NULL data or data which violates constraints. Unspecified error"
I think this is a SQL Server bug. My google searches have revealed MANY people with this similar problem, but their problem almost never gets answered. If anyone could please offer assistance, that would be great, as my head is turning black and blue from banging it on my desk for the past 3 days.
ThanksI don't think DTS is buggy you are using text file which have empty columns but IDENTITY is a primary key which cannot accept NULL, so I would import the data to a table without IDENTITY column and no key before moving it with an INSERT INTO statement to your destination table. The integrity violation is also primary key related because ANSI SQL rule primary key cannot accept NULL value but foreign key can. Yes your table will be denormalized but INSERT INTO will move the data to your destination table. Hope this helps.|||Thank you, your post is solely responsible for me getting around the problem. I put it into a temp table first, then into the main table. Thanks again!|||I am glad you got it working.