DTS question - need your help!

I am trying to use DTS load Excel data to SQL Server

The Excel Spreadsheet has 100 columns in each row. Let say I have 3 rows in Excel sheet.
Each row has 100 columns as follows,

column1, column2, ...column100

Now I want to load it to a table which has only one column.

The first record will be the column1 of the first row from the excel spreadsheet
The second record will be the column2 of the first row from the excel spreadsheet
...
The 100th record will be the column100 of the first row from the excel spreadsheet

The 101th record will be the column1 of the second row from the excel spreadsheet
...

Can anyone give me a suggections? Thanks in advance,

PeterHmm, any way you can transpose the columns in Excel? I can't think of a simple way to create an SQL Statement to transpose the columns in DTS. It might be easier to manipulate Excel, rather than trying to do the transpose in DTS.|||I agree with the first post, but you could do it starting with DTS. Suck the excel sheet into a 'holding' table and then run a stored procedure that takes each cell and makes it a record by itself. You could use a cursor to step through the records and do 100 inserts to the final table for each row in the holding table.|||Is the order truyly that important?

You can get all the data in one column by using the following SQL statement instead of a table as your source:

SELECT Column1
FROM [Sheet1$]
UNION ALL
SELECT Column2
FROM [Sheet1$]
UNION ALL
SELECT Column3
FROM [Sheet1$]
...

You'll need a lot of cut and past (or a handy little script) to generate all the UNIONed selects.

If the order truly is important, then I'd add another column to your spreadsheet for the row number. Import the entire spreadsheet into the a temp table using something like this:

SELECT TheValue = Column1,
TheRow = RowNumber, -- your row number column
TheColumn = 1
FROM [Sheet1$]
UNION ALL
...

Increment the column number for each column. Again, a script will write this monster select for you. Then you can insert into your final table:

INSERT ...
SELECT TheValue
FROM MyTempImportTable
ORDER by TheRow ASC, TheColumn ASC

FYI - You may need to use UNION rather than UNION ALL. I don't recall the exact workings of EXCEL with respect to the ALL part. A little experimenting (or a syntax) error should clear it up pretty quickly.

DTS only copies first row

Hi,

I'm trying to copy results from a view on one table to another table in a different db using DTS in SQL Server Management Studio

Both source and destination db's are version 8.0.194

Everything reports as working, but when I check the results, only one row has copied - DTS reports that 1167 rows were copied.

This happens to different sources and destinations so I doubt it's my query.

When I perfrom the same DTS task using the old enterprise manager DTS in SQL Server 2000, everything copies fine.

Obviously one answer is "Use SQL Server 2000 Enterprise Manager then. " but I'd rather upgrade to 2005 and Management Studio permanently.

Any ideas?

I don't know what the problem may be but you need to identify exactly where the problem is occurring. My first avenue of investigation would be to put a data viewer immediately prior to your destiantion to see how many rows are flowing into it. Check that the number displayed in teh GUI is correct.

-Jamie

DTS Insert or Update

Is there an easy way with DTS to pump data from one table to another so that it will update the row if it exists (the source and destination have the same value for the ID colum) or insert it if it doesn't.

I know this can be done with stored procedures/sql by doing IF EXISTS UPDATE ELSE INSERT but there are many tables and columns and this will be very tiime consuming.

i think your going to have to use VBScript in the transformation step of the DTS Package to evaluate if the record exists. Similar to what one would do in the stored proc.

hth,

mcm

DTS Import with no row terminator using VB

Using the DTS wizard in SQL 2000 Enterprise Manager, a DTS Package can be saved as a Visual Basic file. If a row terminates with a {CR}{LF}, the appropriate .ConnectionProperties("Row Delimiter") = vbCrLf is included on or around the third line of the package connection information for the text file.

I have two different files that I cannot import using the saved VB file. One is a .txt file with a carriage return {CR} as a row terminator. This imports fine using DTS but not from VB. The row delimiter is omitted when the package is saved. I have tried adding the connection properties using vbCr, {CR}, and CHR(13) as the row delimiters, but none of these will import the file from VB.

The other file, and the solution for this file can be used for the previous file, is a .dat file exported from SAP (I do not have access to pull the data directly from the Oracle servers into SQL). If the file is opened in a text editor, it contains no row terminator. DTS allows me to specify where the row ends and imports the file, but, once again, this property is omitted when the package is saved as a Visual Basic file. Unable to find a list of possible .ConnectionProperties, I have tried "Row Length", "Row Width", and every other possibility I could think of, but the file will not import. The records are 429 characters in length.

Any suggestions?

Moving to the SQL Server Integration Services forum.|||

Why moved here? It looks to be a DTS cquestion.

I would suggest the original poster headds to newsgroup microsoft.public.sqlserver.dts

-Jamie

DTS Import with no row terminator using VB

Using the DTS wizard in SQL 2000 Enterprise Manager, a DTS Package can be saved as a Visual Basic file. If a row terminates with a {CR}{LF}, the appropriate .ConnectionProperties("Row Delimiter") = vbCrLf is included on or around the third line of the package connection information for the text file.

I have two different files that I cannot import using the saved VB file. One is a .txt file with a carriage return {CR} as a row terminator. This imports fine using DTS but not from VB. The row delimiter is omitted when the package is saved. I have tried adding the connection properties using vbCr, {CR}, and CHR(13) as the row delimiters, but none of these will import the file from VB.

The other file, and the solution for this file can be used for the previous file, is a .dat file exported from SAP (I do not have access to pull the data directly from the Oracle servers into SQL). If the file is opened in a text editor, it contains no row terminator. DTS allows me to specify where the row ends and imports the file, but, once again, this property is omitted when the package is saved as a Visual Basic file. Unable to find a list of possible .ConnectionProperties, I have tried "Row Length", "Row Width", and every other possibility I could think of, but the file will not import. The records are 429 characters in length.

Any suggestions?

Moving to the SQL Server Integration Services forum.|||

Why moved here? It looks to be a DTS cquestion.

I would suggest the original poster headds to newsgroup microsoft.public.sqlserver.dts

-Jamie

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.
>

DTS Import from Text file - can't skip just one row

Hello,
I am trying to import a text file to a SQL table. The file is fixed length
format, but the first record is a header record and I want to skip it. If I
set the import wizard to skip 1 row, it skips the header AND the next row.
If I set it to skip 0 rows, I get the header AND the next record. I cannot
get it to skip just the header. I did NOT select "First row has column
names."
Any ideas?
Thanks,
- ArtHave you tried selecting the "First row has columne names"
at all? What happens when you do this? I have used this
a number of times when the column headers were actually in
the first row, but I am not sure what it will do with
random header data in te first row.
Also, is the header the correct fixed length? Maybe that
is why skipping the first row removes the first real data
row also? Or maybe there is a missing end of row
character?
Just some initial thoughts. I hope this helps somehow.
Matthew Bando
BandoM@.CSCTechnologies.com
>--Original Message--
>Hello,
>I am trying to import a text file to a SQL table. The
file is fixed length
>format, but the first record is a header record and I
want to skip it. If I
>set the import wizard to skip 1 row, it skips the header
AND the next row.
>If I set it to skip 0 rows, I get the header AND the next
record. I cannot
>get it to skip just the header. I did NOT select "First
row has column
>names."
>Any ideas?
>Thanks,
>- Art
>
>.
>|||Art,
just check the First row has column names, and do not skip any row.
Quentin
"Art Bragg" <abragg@.transedge.com> wrote in message
news:OztK6aeWDHA.3088@.tk2msftngp13.phx.gbl...
> Hello,
> I am trying to import a text file to a SQL table. The file is fixed
length
> format, but the first record is a header record and I want to skip it. If
I
> set the import wizard to skip 1 row, it skips the header AND the next row.
> If I set it to skip 0 rows, I get the header AND the next record. I
cannot
> get it to skip just the header. I did NOT select "First row has column
> names."
> Any ideas?
> Thanks,
> - Art
>

DTS Import error on CkDate

Im trying to import a tbl from access why am i getting this msg?
Error at destination for row #58
Insert error, colm 6 ('Check Date, dbtype_dbtimestamp), status 6: Data over flow.
Invalid character value for cast specification.Please look in Access at row 58 and see what the illegal character is. Use the dts to clean the data as it come in.

Take a look aWarning: Non-existent step referenced by
@.on_success_step_id.
attachment as see what these errors are???

Warning: Non-existent step referenced by @.on_success_step_id.
Warning: Non-existent step referenced by @.on_success_step_id.
Warning: Non-existent step referenced by @.on_success_step_id.
Warning: Non-existent step referenced by @.on_success_step_id.

Thanks|||The data in row 58 for check date is a regular date

I dont understand what your saying about :

Take a look aWarning: Non-existent step referenced by
@.on_success_step_id.

or how to transform data from dts|||Specifically, what do you have set in SQL Server as the datatype for your date/time column?

Can you show us several rows that you're attempting to import, including the one that failed?|||I was trying to use datetime as the colm type. I end up with no rows in my table and all the rows in my access tbl are the same format :

Date_Entered Initials Vendor Name CheckNum Type ChkDate ChkAmt
11/19/02 ns 21' Club Inc. 1083 Meal 10/16/02 1,170.59
11/15/02 ns 21' Club Inc. 1091 Meal 11/12/02 1,634.68|||how are the delimited during your import? Tabs?|||I was just using Dts wizard import from an access database.|||Just a thought.. make a backup of your MDB, pull out all the rows but one and see if it imports ok. Then try this again with the row #58 row and see if it goes through or bombs again.