DTS Question Reguarding Text Import And Tranformation

Jan 24, 2007

MS SQL 2000 sp4 on WinXp Pro SP2
I am very new to this so please let me know what I can do to make it
easier for you to understand the problem.


I have a non delimited text file. This text file has several columns
that for the most part are fixed length but..
The fixed format starts with a variable length number( char 10) as the
first column and a max (char 30) description field and another (char
50) long description field.

if the (char 30) description field has a measurement ( ie 4.5" ) then
the first field stats with a ". The subsequent description will end
with a "" or double quote. The fields can only work if the double
qoutes are replaced with a single space and then the leading single
quote is deleted. If this is done in this order then the fixed field
lengths work.

example of actual data***************

This example does not have any quotes in it*****

1015304 Sof Sand Block Holder RUDOLPH
INTE BH y N N nbrd EA 1.00000 .83 1.25

This example does have the quotes**********

"10154 Rud Zeb NonDisf Cush Cur ve 180Gr 7"" RUDOLPH
INTE DUZ180F5 y N N nbrd EA 1.00000 .43 .65"

This is the next several lines***************

1015401 Rud Blk Disf Cush Curved 100/180Gr RUDOLPH
INTE DUB100/1 y N N nbrd EA 1.00000 .46 .69

1015402 Rud Blk Disf Cush Curved 100Gr RUDOLPH
INTE DUB100F5 y N N nbrd EA 1.00000 .46 .69

1015405 Rud Myl Disif Violet 100 Gr RUDOLPH
INTE GLMM100F y N N nbrd EA 1.00000 .43 .65

1015406 Rud Myl Purple Disf 80Gr RUDOLPH
INTE GLMM080F y N N nbrd EA 1.00000 .43 .65

1015407 Rud Myl Light Green Disf 120Gr RUDOLPH
INTE GLMM120F y N N nbrd EA 1.00000 .43 .65

"1015408 Rud Myl Yellow Disf 180G r 7"" RUDOLPH
INTE GLMM180F y N N nbrd EA 1.00000 .43 .65"

end of example*******************

right now I am doing this by hand in notepad and then importing to
excel to generate a csv file. Can anyone suggest a better way to do
this or just point me to an example.

Thanks very much for any

View 2 Replies


ADVERTISEMENT

DTS Tranformation Question

Feb 2, 2001

For a project currently in development, one of our developers would like to use DTS to perform the following actions. However we both have only just begun to work with this tool so we are somewhat inexperienced with DTS and looking for guidance.

We need a way to generate multiple output rows from a single input row.
For example, the input record contains the following fields: RSN1, RSN2, RSN3, and RSN4.
I want to generate four output records.
one record containing RSN1,
one record containing RSN2,
one record containing RSN3 and
one record containing RSN4.
Could DTSTransformStat_SkipFetch be used in this situation?

If someone could provide us with a sample or some suggestions on how best to do this, it would be greatly appreciated.

Sincerely,

Michael Sinnott
Wausau Benefits

View 2 Replies View Related

Row Number Tranformation

May 3, 2006

I have looked to no avail...

Anybody see a dynamic way to set the seed in the Row Number Transformation? I do not see any expressions property and the only property that takes a variable is the FINAL number...

I would have though that a very common use would be to get the maximum IDENTITY from a table and use that value (after adding one) as the starting number for the rows being imported...

View 6 Replies View Related

PAD Function In Data Tranformation Editor

Dec 21, 2006

I have a value with a float datatype in Excel that is actually a date. For example, the value displays 1272006 for January 27, 2006. I am trying to convert this value to a date. I can do this in several stages using T-SQL by converting the datatype to integer, using replicate to make it eight characters and than using substrings to make it look like 01-27-2006 which SQL Server will recognize as a date.

My problem is that there is nothing similar to a PAD (replicate) function using the Data Tranformation Editor in Integration Services. There is a replicate function but it merely functions to repeat a value. It doesn't appear to perform the same PAD function as the T-SQL replicate does. Here is my formula in T-SQL that works okay.

right(replicate('0', 8) + convert(varchar, cast([Date] as varchar)), 8)

I could do this by creating a staging table but I want to handle the entire transformation process within Integration Services. Can anyone offer me some advice? Thank you.



David





View 7 Replies View Related

How To Call A Function Using OLE DB Command Tranformation

May 22, 2007

Hello



i am trying to call a function from the SQL server using Ole DB command Transformation using [dbo].[ConvertToDate] ?,?,?,?

there are no errors while executing this transformation

but this function returns a value

Now i need to capture this value how do i do that using the OLE DB command Transformation or any other transformation



Thanks

View 3 Replies View Related

Adding Custom Script Tranformation Component Programatically.

Oct 11, 2007

Hi All,

I am new to SSIS. I am working in adding SSIS components programmatically. I have added Data flow task, Lookup transformation, OLEDB Source and OLEDB Destination.

Now, i am facing problems in adding Custom Script tranformation component programatically. Please help me out.




Venkatesh.

View 1 Replies View Related

Import Text

Nov 9, 2000

We are trying to import a fixed length text file. It has two fields. The first is ten characters, the second is the rest of the row, which may be several hundred characters. Each row is terminated by a {lf}{cr}
The problem is the DTS text import utility. It generates a red line (column seperator) at the 85 character mark. This causes the second field to wrap. We can not move this line, or delete it.
Any idea what is going on?

View 8 Replies View Related

Text File Import

Apr 17, 2000

SQLServer 7.0

Hi,

I have a small project on that involves importing a series of csv files held within an ftp directory into our Datawarehouse. Every day a series of csv files will be added to the directory. These will be named something like:

Audit1.csv,Audit2.csv etc.

I would like to automate this process as this can involve up to 400 files at a time. The proecedure would need to identify a valid file, import it into the database, delete the file and then move onto the next one.

Does anyone know of a way to achieve this? I was thinking along the lines of using a cursor and bcp but I'm not sure how to identify these files to the database i.e. how do i make it step through the directory and process the files?

Any help would be greatly appreciated.

Thanks

Rob

View 1 Replies View Related

DTS Import Text File

Oct 20, 1999

In a DTS package I have a text file import object, a data pump, and a SQL object. The text file import object has been set up to splice a 500 character wide file into 20 columns. The data pump task does a copy column for all the columns into the appropriate table. What I need to do is have a way of changing the file name I specify in the text import object. I have 12 months worth of data in seperate files (DBF0199.TXT, DBF0299.TXT, DBF0399.TXT, etc..)
which all use the same format. Is there a way to change the text import objects file name inside the package using an active script task or something?

Any help is greatly appreciated.

Thanks,

Todd

View 4 Replies View Related

Using DTS To Import Text File

Dec 5, 2002

I'm trying to import a fixed field text file into SQL Server using DTS but everytime I go past 3640 characters, I am not able to add, delete or move column breaks after that. Is anyone else experiencing this problem and know of a work around. Any help would be appreciated. Thanks!

Using SP2 on SQL Server 2000.

View 3 Replies View Related

Import Text Files

Aug 30, 1999

Hi ppl,
I'm trying to import a fixed lenght text file into a sql 7.0 table using the SQL command "BULK INSERT".

The text file is fixed length and end with a carriage return and linefeed. Fields aren’t delimited.

Wich definition of the "FIELDTERMINATOR" should i use ?

I need some Help here!!!
Thanks.

View 1 Replies View Related

Import Text File

Sep 14, 2004

Is it possible to handle text files i MS SQL 2000 in which fields contains single quotations and also are terminated by a single quotation

Like this:
1,2,'John's',1,2

John's contain a single quotation

Or is possible to rewrite file using MS SQL to use double quotation
Like this;
1,2,"John's",1,2

Hope that someone can help me out here, and doing it using MS SQL

At the time, I'm handlig it using MySql, but I would prefer using MS SQL

Regards
Carsten H.

View 4 Replies View Related

DTS Import From A Text File

Jul 15, 2002

Hi All,
I'm having a problem in importing an Excel file into SQL server 2000.Here is the scenario with my data.
One of the column has got the mixed data which is putting null's in the SQL server table in some rows.I found in the MSFT Technet that it is a bug in SQL server 7.0/2000.The workaround for it ( according to MSFT ) is to get the data into text file and import into SQL server.
Now the question is , my data contains some currency fields and numeric fields in addition to the char and date fields. When I'm importing the table using DTS wizard , it is failing. I'm trying to use conversion functions like cdate and clng etc . Still the DTS is failing.
What I noticed is when I try to import into a table with data type varchar for all columns, it is working fine.But the data is of no use.
I would appreciate if any one can help me out in solving this problem.
Thanks,
Sammy.

View 1 Replies View Related

Import And Text Qualifier

Sep 3, 2004

Hi

How can I import txt files, in which data sometimes can contain
the letter that is used as text qualifier

Example txt file: 'abc',123, 'john's',12

Here the word -john's- contains the same letter as the text qualifier I have to use.

Can it be true, that this can't be done i MS SQL, when it can be done in MS Excel, Paradox etc..

Regards
Carsten H.

View 4 Replies View Related

Text File Import

Jan 20, 2005

Quick advice question. I import lots of text files -- many with 50 plus data columns. Few come with a table layout other than perhaps the first row having a set of column names.

When I go to pull them into SQL server the columns default to varchar 8000. Is anyone aware of a tool (as a part of SQL Server or otherwise) that can scan a column of data and recommend a data type and size.

Appreciate any recommendations.

Ray

View 3 Replies View Related

IMPORT TEXT FILE

Mar 26, 2007

All,
What will be the painless way to import large fixed field text file that has more than 260 columns in sql table using dts pkg?

Suggestions?

View 4 Replies View Related

DTS Text Import Question

Jul 23, 2005

Hi All,I use DTs Wizard to import a text file into my MS SQLServer 2000 everymonth. This text file contains 236 text fields and it's format neverchanges.The problem that i've found is that the DTS wizard sets each of thedestination fields to varchar (which is fine) but the size to 8000!!!!I have to go through each one of the fields to reduce the size down to255 ...... is there any way to change the default field size?Cheers & Merry ChristmasDave

View 1 Replies View Related

DTS Import Of A Text File

Jul 20, 2005

I have created a DTS that imports a text file to by data table. I geterrors when ever I run this since there are fields in the table thatare numeric. I understand that I need to create an activeX script toimport those fields. DOes anyone have any guidance?

View 1 Replies View Related

Text File Import With DTS

Jul 20, 2005

Does anyone know if it's possible to use the wizard or DTS Designer toaccept a source file with the following simplified format:<field1label>: <record1field1value><field2label>: <record1field2value>- - - - - - -<fieldNlabel>: <record1fieldNvalue><field1label>: <record2field1value><field2label>: <record2field2value>etc.i.e. each input record is delimited by {LF}{LF}, and each column by {LF}. Orwill it be necessary to write a Perl script (say) to convert it first into a..csv file?Thanks,Dave--************************************************** **********************Dave Stone e-mail: Join Bytes!Computing Services Telephone: +44 131-650-3314University of Edinburgh Internal ext: 503314Main Library, George Square FAX: 0131-650-3308Edinburgh EH8 9LJ************************************************** **********************

View 2 Replies View Related

Import Text Files

Apr 11, 2007

hi friends ,

Thank you for the help and support you have given me. Now i am confronted with a new problem. I have to import some textfiles to SQL Server Tables . I have to create a tool to automate the porting using C# .The columns in textfile is seperated with pipe"|" . I f any body knows this please help me .





Thanks $ Regards



Nevin









View 1 Replies View Related

How To Not Import A Text File Twice

Jun 19, 2006

Something I find myself wanting to do frequently is the traditional foreach loop looping through a directory of files and importing (which works great in SSIS) - Only I don't want to import data I have already imported.

In a previous job I used Perl for thing like this and the structure would be as follows:

For Each File:

1. Get filename and timestamp

2. Query db table with list of already imported filenames and timestamp. If Filename not in the table or is in table with older date return 1 to import or if file already imported return 0

3. Based on the result of step 2 either import or skip to next file.

Any recommendations how to do something similar in SSIS? I run stuck when I try to get the timestamp of the file and I also can't figure out how to do the conditional inside the foreach container... I am also open to other ideas on how to only import files I have not already imported.

View 5 Replies View Related

Import Text Files

Apr 11, 2007

hi friends ,

Thank you for the help and support you have given me. Now i am confronted with a new problem. I have to import some textfiles to SQL Server Tables .The columns in textfile is seperated with pipe"|" . I f any body knows this please help me .





Thanks $ Regards



Nevin









View 5 Replies View Related

DTS Import Text File

Apr 19, 2007

Hi i'm new to DTS and need to be able to import a text file into a table each day.



The main problem I have is the file is datestamped so the name of the file changes each day.



Today it would be called file20070419.txt tomorrow it would be file20070420.txt. When I select a text file as a source I have to pick a valid file ??? how can I get round this ???

View 1 Replies View Related

How To Import A Text File With Transac-sql

Mar 27, 2007

Hi allI am looking for examples of scripts that will help me doing these things:
 - import a text file delimited with the character "*", representing a new month of data, for example data from march 2007
- create a new table with the structure of an existing one to import the data, for example  Data_March_2007
- alter an existing totals table adding a new column for the new moth imported, adding a new colum for the month of March 2007.
 
 

View 1 Replies View Related

Import Text File Problem

Sep 26, 2007

Hello Everyone and thanks for your help in advance.  I am having a problem importing a text file into SQL Server 2005 and can't figure out where the issue is.  The file is in CSV format with the text delimiter field as a ".  When attempting to import the field into a SQL Server 2005 table, the import fails due to numerous truncation errors.  I have tried importing into an existing table, but have also tried importing and allowing the import to create the table.  I receive the same failures in both cases.  For whatever reason, when allowing the import to create the table, each column is created as a nvarchar(50) even thought the column sizes vary widely.  Oddly enough, when importing into a SQL Server 2000 table with the correct layout, the file imports perfectly fine, thus verifying there is nothing wrong with the data source.  It also creates the table with appropriate column sizes in SQL 2000.  I'm really at a loss as to what is going on.  Any assistance woul dbe greatly appreciated.  Thanks.

View 4 Replies View Related

Schedule Job For Import Text File

May 28, 2002

This is what I am going to do:

Everyday, there are users FTP the text files to a directory in the server. During the night, a job will be run to import these text files to a table.

First, the job need to read the file name, then open the file, read the first line and insert to the table until then end of the file. Then the second file will be read ... until no more file to be read.

I am using VB to read the file name and open the file, and my question is how to pass the file name to the second step which is in T-SQL?


Any experience to be shared in this area ?

Thanks,
Rachael

View 3 Replies View Related

Import From Text File Into Two Tables

Jul 16, 2002

Quick Question!

If you have both invoice header lines and invoice detail lines in a comma delimited file, how can I get the data in the file to be imported into two different tables. I can produce a text file eg:

1,20,10/03/2002,39 High Street Any Town,,
2,20,Fluffy Slippers,2,Red,10.99
2,20,Pyjamas,3,Black,15.99
2,20,Trousers,1,Lilac,24.99
1,21,10/03/2002,11 Gibson Close,
2,21,Sandles,1,Black,12.99
2,21,Shoes,4,Blue,23.99
1,22,13/03/2002,45 Mill Street,
2,22,Womble Feet,4,White,16.99
2,22,Glass Slipper,1,Transparent,23.99

Lines with 1 in the first column should go in the InvoiceHeader table
Lines with 2 in the first column should go in the InvoiceDetails table.

I have tried with DTS but to no avial - ActiveX scrips in the Transform Data Task can only seem to access one data destination - it one table not two.

Any Ideas?

Julia

View 3 Replies View Related

Import Text File Problem

Aug 20, 2002

I have a fixed field text file I am trying to setup an import for SQL 2000. If I use Access 2000 the file reads fine, but if I use the SQL Import feature or DTS, SQL doesn't line up the records correctly, I tried all the combinations of row returns with no luck, but Access works just fine. ANy ideas?

View 1 Replies View Related

Import Data From Text To MS SQL Server. !!! Need Help!!!

Oct 31, 2005

Hi
I need to know how to import data from .txt to the MS SQL Server. IT is really important and i have 1 hour only, any help ....... appreciated.
Thanks

View 1 Replies View Related

Filtering Lines In A DTS Text Import

Jan 19, 2004

I have a DTS that reads in a bunch of transactions daily to a tran history table. I read them in from a text file each day. The problem is, that about half of the lines in the text file contain semicolons because they are comments. What I do now is, import the whole thing, and then do a Delete on my tran hist table for all lines with semicolons. As my tranhist table grows, this Delete will start to take a long time. How do I filter it so it doesn't even import the lines with semicolons to start with, that will run faster and save me time later.

Thanks,
Andrew

View 4 Replies View Related

Import Text File In SqlServer ...

Oct 21, 2007

dear all,
I have a text file that contains data that i need to insert into sql server... the file size is about 800 MB .. and contains about 17,000,000 lines ..
some one told me that there is a way in sql server to import this data automatically by writting some scripts ...
the file looks like this

xxxxxxxxx
xxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxx
yyyy .. yyyyy "I Need only These fields (the Ys).. I don't care about the rest of the file"
yyyy .. yyyyy
xxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxx

any help is really appriciated

View 3 Replies View Related

Optimise Import Text File

Mar 11, 2008

Hi,

Please can you help me,

I have created an database with 3 fields

View 3 Replies View Related

Optimise Import Text File

Mar 11, 2008

Hi,

Please can you help me,

I have created an database with 3 indexed (not clustered) fields
key : bytes 100
error : integer
status : bytes 6
I would like to know how to import 3 million or more records quickly.
If I don't index the the fields, the operation takes 3 hours. With indexed fields it takes overnight. Therefore, how can i optomise the import operation from a .txt file.

Thanks and regards.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved