DTS for Import Export TO And From EXCEL

Hi All,
I want to design a DTS Package that will read an EXCEL Document (One Data
Source) and ONE SQL Server (2nd Data Source) and Execute one Query which
will have a JOIN from Both the source and Export the result to another Excel
Document.
How Can I perform that using DTS?
I have took 3 Connections 1) SQL Server 2) Excel -> These tow for Source
And 3) Excel Connection for Export the Result.
My Requirement is to get the value from One of the column from one of the
Sheet and use that values to get a Joined Record from TWO tables of SQL
Server.
Ex: -
Sheet2$ : Having Column "EmployeeID" with 100 rows.
IN SQL Server I have 2 Tables. 1) Employee 2) Dept.
I want to export the LIST of the Departments for the Employee that are in
the Excel Sheet2.
Please Suggest how can I do that or any Better solution using DTS.
Thanks
PrabhatYou could use OPENDATASOURCE
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended
properties=Excel 5.0')...xactions
Or you can create a linked server of the source XL spreadsheet from the
SQL Server. You then query that and export to XL destination.
You cannot use the Excel connections to do this ........Yet.
Allan
"Prabhat" <not_a_mail@.hotmail.com> wrote in message
news:not_a_mail@.hotmail.com:

> Hi All,
> I want to design a DTS Package that will read an EXCEL Document (One Data
> Source) and ONE SQL Server (2nd Data Source) and Execute one Query which
> will have a JOIN from Both the source and Export the result to another Exc
el
> Document.
> How Can I perform that using DTS?
> I have took 3 Connections 1) SQL Server 2) Excel -> These tow for Source
> And 3) Excel Connection for Export the Result.
> My Requirement is to get the value from One of the column from one of the
> Sheet and use that values to get a Joined Record from TWO tables of SQL
> Server.
> Ex: -
> Sheet2$ : Having Column "EmployeeID" with 100 rows.
> IN SQL Server I have 2 Tables. 1) Employee 2) Dept.
> I want to export the LIST of the Departments for the Employee that are in
> the Excel Sheet2.
> Please Suggest how can I do that or any Better solution using DTS.
>
> Thanks
> Prabhat|||306397 How To Use Excel with SQL Server Linked Servers and Distributed
Queries
http://support.microsoft.com/?id=306397
-Doug
--
Douglas Laudenschlager
Microsoft SQL Server documentation team
Redmond, Washington, USA
This posting is provided "AS IS" with no warranties, and confers no rights.
"Prabhat" <not_a_mail@.hotmail.com> wrote in message
news:%23iSD4RHXFHA.3464@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> I want to design a DTS Package that will read an EXCEL Document (One Data
> Source) and ONE SQL Server (2nd Data Source) and Execute one Query which
> will have a JOIN from Both the source and Export the result to another
> Excel
> Document.
> How Can I perform that using DTS?
> I have took 3 Connections 1) SQL Server 2) Excel -> These tow for Source
> And 3) Excel Connection for Export the Result.
> My Requirement is to get the value from One of the column from one of the
> Sheet and use that values to get a Joined Record from TWO tables of SQL
> Server.
> Ex: -
> Sheet2$ : Having Column "EmployeeID" with 100 rows.
> IN SQL Server I have 2 Tables. 1) Employee 2) Dept.
> I want to export the LIST of the Departments for the Employee that are in
> the Excel Sheet2.
> Please Suggest how can I do that or any Better solution using DTS.
>
> Thanks
> Prabhat
>|||"Douglas Laudenschlager [MS]" <douglasl@.online.microsoft.com> wrote in
message news:OOnZmB$XFHA.2884@.tk2msftngp13.phx.gbl...
> 306397 How To Use Excel with SQL Server Linked Servers and Distributed
> Queries
> http://support.microsoft.com/?id=306397
> -Doug
> --
> Douglas Laudenschlager
> Microsoft SQL Server documentation team
> Redmond, Washington, USA
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Prabhat" <not_a_mail@.hotmail.com> wrote in message
> news:%23iSD4RHXFHA.3464@.TK2MSFTNGP10.phx.gbl...
Data
the
in
>

DTS Excel Import, Transform - how do I use "OR" clause in SQL Query

Howdy. I'm trying to build a query that will take an Excel file and
pull a few rows of data from a particular sheet. I'm having a problem
with my WHERE clause - I can can tell it to import WHERE a field
matches a value, or WHERE the field matches another value, but not
both.
I've tried bunches of different variations, but can't get it to work.
Is there any way to do this?

Works:
select F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12
from `RAF082604$`
where ((F1 = 'Body'))

Works:
select F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12
from `RAF082604$`
where F1 = 'Cash'

Doesn't:
select F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12
from `RAF082604$`
where F1 = 'Body' OR F1 = 'Cash'

Doesn't:
select F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12
from `RAF082604$`
where (F1 = 'Body' OR F1 = 'Cash')

Doesn't:
select F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12
from `RAF082604$`
where ((F1 = 'Body') OR (F1 = 'Cash'))Did you try:

select F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12
from `RAF082604$`
where F1 = 'Body'
union all
select F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12
from `RAF082604$`
where F1 = 'Cash'

or

select F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12
from `RAF082604$`
where F1 in ( 'Body', 'Cash')

"Michael Bourgon" <bourgon@.gmail.com> wrote in message
news:558b578d.0409010509.1f10c665@.posting.google.c om...
> Howdy. I'm trying to build a query that will take an Excel file and
> pull a few rows of data from a particular sheet. I'm having a problem
> with my WHERE clause - I can can tell it to import WHERE a field
> matches a value, or WHERE the field matches another value, but not
> both.
> I've tried bunches of different variations, but can't get it to work.
> Is there any way to do this?
> Works:
> select F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12
> from `RAF082604$`
> where ((F1 = 'Body'))
> Works:
> select F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12
> from `RAF082604$`
> where F1 = 'Cash'
> Doesn't:
> select F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12
> from `RAF082604$`
> where F1 = 'Body' OR F1 = 'Cash'
> Doesn't:
> select F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12
> from `RAF082604$`
> where (F1 = 'Body' OR F1 = 'Cash')
> Doesn't:
> select F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12
> from `RAF082604$`
> where ((F1 = 'Body') OR (F1 = 'Cash'))

DTS Error when using text file as destination in DataPump Task

I want to export to a Text File (destination) from a SQL query using a DTS DataPump Task. My query has 28 columns, some how when I try to define the destination columns for my text file the Microsoft Managment Console Crashes completely.

I tough may be my query has two many columns only to find out that it has one to many. If I ramove a column form my query, any column. I get no error at all.

Is there a limit to a Text File destination connection?I got the same problem. I cannot imagine it has anything to do with nr of columns.
Would be interested in solution...

dajm|||I do believe there is a limit to the number of columns but it can't be 28. Still since there's no error besides the crash I have not figured it out yet. Im going to try some test querys.|||I know this isn't what you asked, but I always use bcp for this. Are you doing complex transformations in the data pump? If it is just SQL, you can go to a command line (execute process task if you want DTS to do it) and run bcp "your query" queryout destination.txt -c -T -t "|" (for pipe-delimited). If it is an entire table (or view), you can run bcp "tablename" out destination.txt -c -T -t"|"

DTS Error ? No query specification returned by transform status

I'm getting this error in a simple DTS package.
No query specification returned by transform status.
Any ideas what's causing this ?
DTS is simply running a query (which returns data via preview)
and insert results into a table.
Thanks
MarkTry to redefine the package and run.
Ensure the account used is having required privilege to insert the data.sql

DTS Error

I need to export some table data to Excel which I normally do with Query Analyzer as a .CSV file. However, one of the fields has a ton of comma's which screws up the formatting so I'm using the DTS Import/Export Wizard. I choose the .xls file and paste my query. When it runs I get the following error: "Failed to copy 1 table(s). Double click the error..." When I click on the error it gives me an "Invalid Pointer" message. I'm not sure where to go from here so any help would be greatly appreciated.
Thanks,
Jim
I figured it out...I had "USE (Name of Database)" in my query. Once I took that line out everything worked quite well.
Jim
|||Please address future DTS (SSIS in SQL Server 2005) to the SSIS forum: http://forums.microsoft.com/msdn/ShowForum.aspx?ForumID=80

dts dynamic query & automap columns question

Hello All,

I am sorry if this has been asked - I tried searching but the search kept timing out.

I have designed a DTS package which extracts a query into an excel file.

It uses a query that changes dynamically based on user preferences, so I have used the dynamic property SourceSQLStatement to feed the exact query into the DTS package.

The issue, however, is that the query can be run a multitude of ways, and return a different number of columns each time. On one run, the query could return a 3 column record-set, on the next it could return an 8 column record-set.

Currently, the DTS package errors on each attempt because it expects a certain column set.

Is there a way to tell it to auto-map the columns at the time it executes? I could not find a dynamic property which did that.

I would hate to have to set up a different DTS package for each possible column set.

I am sure I am missing something.

Thanks in advance.

- CharlesI would use a sproc with dynamic sql, bcp and xp_cmdshell personally|||Hi Brett,

Thanks for the response. I have to admit I'm fairly new to the more complex aspects of SQLServer (Stored Procedures, cmd_shell, etc). What I have so far was built using the Package Designer - to give you an idea of my level of expertise with DTS. But I'm not opposed to learning if that's what it takes.

Are you aware of any other "simpler" options?|||DTS is such a flukey thing...see it's a dangerous drug...you get used to the GUI, the you start to push it...which means you need to start using ActiveX and such.

What happens then is that DTS acts like a cursor and has to affect rows, one by one. Which slows things down dramatically.

If you could give me/us and example of what you are trying to do, I'm sure we can get you an elegant/effecient solution.

Read the sticky at the top of this thread (ok second sticky) and post what it asks for.|||lol Brett - you have described my addiction exactly. It all started so innocently...

I will try as best as possible to meet your requirements.

The Question: How can I get the result set from an ad-hoc query to export to any format supported by DTS?

This question does not deal with any specific tables, data or queries. I will use examples.

User Interface:
A web-based interface allows a user to create a report from a number of factors, including choosing which fields should display. This allows for variations in the number of fields being generated. The user may also choose from a number of formats.

The web application then dynamically assembles the query based on the user's selections.

Example Query A (5 columns / fields in record-set):
select name, address, state, zip_code, phone_number
from clients
where zip_code like '06%'

Example Query B (8 columns / fields in record-set):
select name, address, state, zip_code, phone_number, billing_number, last_invoice_date, billing_cycle
from clients
where zip_code like '06%'

In the DTS Package:
1) Connection 1 is set to work from a query. The query is set using the Dynamic Properties Task. It could use Query A one time, and Query B another.

2) Connection 2 is set as the output file type. For our purposes it will be xls.

3) The transformation between Connection 1 and Connection 2 exports the query results into the xls file.

The Error:
When Query A is exported, the DTS has 5 columns to map. When Query B is exported, it has 8 columns to map. The one DTS package errors out if the number of columns are different than it expects to find. It is not possible to limit users to a specific number of columns.|||How about as a cheap solution for the 5 column solution, just return empty strings for the last three columns, and that way all of the calls will return 8 columns, but in Excel, it will show as 5.

Will that work for you?|||OK, this is what I would do...

#1. Make sure all access to the database is done through strored procedures
#2. Dynamically build a view based on your users requirements. make it so it's a single column concatenated as a tab or comma delimited and you can even add a headr
#3. bcp out the view with xp_cmdshell

Let me work up a sample|||Wow Brett! Thanks, I'm intruiged. I can't wait to see it.

Thanks again.

- Charles|||What form do you collect the requirements in? Do formulate the query in the front end? Sounds like you do...|||Hi Brett,

You are correct the query if built first in ColdFusion. The user interface is a form through which the fields and associations are made.

The query is created, then saved in a sql table, then loaded as a dynamic property at runtime.

Thanks again,

- Charles

DTS DataPump from a Stored Procedure

Hi,

assume I have a stored proc which returns a result set. Can I use it as an Sql source query in a TransformData task within DTS ?

The goal was to pick up the result set and create Excel sheets. First I wrote VBA code in Excel, and it worked just fine with OleDb. Later, we found the corporate standard is DTS, so I attemped to set up a Task in the Package Designer Wizard ( no DTS-VBA code )

At the SQL Query box, I have entered: exec my_proc

The preview function shows the data as expected. But in the Destination Tab, I get an empty list of columns. As if DTS would be unable to recognise the column names and types if they come from a stored proc.

I used a workaround, by altering the stored proc to deposit data into a work table. But now I'm still interested to know: is this assumed to work ?Yes
Just invoque your stored procedure on your DTS. Then use the results as you want.

Paulo

Originally posted by andrewsc
Hi,

assume I have a stored proc which returns a result set. Can I use it as an Sql source query in a TransformData task within DTS ?

The goal was to pick up the result set and create Excel sheets. First I wrote VBA code in Excel, and it worked just fine with OleDb. Later, we found the corporate standard is DTS, so I attemped to set up a Task in the Package Designer Wizard ( no DTS-VBA code )

At the SQL Query box, I have entered: exec my_proc

The preview function shows the data as expected. But in the Destination Tab, I get an empty list of columns. As if DTS would be unable to recognise the column names and types if they come from a stored proc.

I used a workaround, by altering the stored proc to deposit data into a work table. But now I'm still interested to know: is this assumed to work ?

DTS- Data Driven Query Task

I use a data driven query task to perform row updates along with appending row data to a text file. In the source I use 'select top 1000 field1, ..., fieldn from table'. The package hangs when executing the data driven task. If I reduce the return count to 'select top 100...' the package execute with no problems. I can do that 10 times, with no problems. But anything more then 100 will hang the package at that task. Please help!

Thanks,
MoniqueTake help from PROFILER and see where it hangs.

The other method of limiting the size of a result set is to execute a SET ROWCOUNT n statement before executing a statement. SET ROWCOUNT differs from TOP.

The TOP clause applies to the single SELECT statement in which it is specified. SET ROWCOUNT remains in effect until another SET ROWCOUNT statement is executed, such as SET ROWCOUNT 0 to turn the option off.

DTS and hash tables

Is it possible that we create a hash table at the starting point of execution of a DTS package and query the hash table till the end of the execution of the package.

YOu will have to put verything which is used in DTS on disk (create a tempoaray table)

Jens K. Suessmeyer

http://www.sqlserver2005.de

DTS and Excel File

I attempt to export data from a query via DTS to an excel
spreadsheet.
When I select the option:
"Create Destination Table" and "Drop and Recreate
Destination Table", I find that whenever I run the DTS,
rows are appended to the Destination Table.
In this way, I just create a dummy Excel spreadsheet and I
select "Delete rows in Destination Table". I suppose that
it will delete all rows and replaced with the result of
DTS Select Statement. However, when I run the DTS, I get
the error message "Deleting data in a linked table is not
supported by this ISAM".
Your advice is sought.
Thanks
Hi
There are a couple of mentions for your error message
http://search.microsoft.com/search/r...=&qn=&c=10&s=1
but I not sure if they help in your case!
The problem with appending data sounds like you have cleared the cells and
not deleted the rows. If this is the case you new data will appear at the end
of the cells you have previously cleared.
John
"Stephen" wrote:

> I attempt to export data from a query via DTS to an excel
> spreadsheet.
> When I select the option:
> "Create Destination Table" and "Drop and Recreate
> Destination Table", I find that whenever I run the DTS,
> rows are appended to the Destination Table.
> In this way, I just create a dummy Excel spreadsheet and I
> select "Delete rows in Destination Table". I suppose that
> it will delete all rows and replaced with the result of
> DTS Select Statement. However, when I run the DTS, I get
> the error message "Deleting data in a linked table is not
> supported by this ISAM".
> Your advice is sought.
> Thanks
>

DTS and Excel File

I attempt to export data from a query via DTS to an excel
spreadsheet.
When I select the option:
"Create Destination Table" and "Drop and Recreate
Destination Table", I find that whenever I run the DTS,
rows are appended to the Destination Table.
In this way, I just create a dummy Excel spreadsheet and I
select "Delete rows in Destination Table". I suppose that
it will delete all rows and replaced with the result of
DTS Select Statement. However, when I run the DTS, I get
the error message "Deleting data in a linked table is not
supported by this ISAM".
Your advice is sought.
ThanksHi
There are a couple of mentions for your error message
http://search.microsoft.com/search/...a=&qn=&c=10&s=1
but I not sure if they help in your case!
The problem with appending data sounds like you have cleared the cells and
not deleted the rows. If this is the case you new data will appear at the en
d
of the cells you have previously cleared.
John
"Stephen" wrote:

> I attempt to export data from a query via DTS to an excel
> spreadsheet.
> When I select the option:
> "Create Destination Table" and "Drop and Recreate
> Destination Table", I find that whenever I run the DTS,
> rows are appended to the Destination Table.
> In this way, I just create a dummy Excel spreadsheet and I
> select "Delete rows in Destination Table". I suppose that
> it will delete all rows and replaced with the result of
> DTS Select Statement. However, when I run the DTS, I get
> the error message "Deleting data in a linked table is not
> supported by this ISAM".
> Your advice is sought.
> Thanks
>

DTS and Excel File

I attempt to export data from a query via DTS to an excel
spreadsheet.
When I select the option:
"Create Destination Table" and "Drop and Recreate
Destination Table", I find that whenever I run the DTS,
rows are appended to the Destination Table.
In this way, I just create a dummy Excel spreadsheet and I
select "Delete rows in Destination Table". I suppose that
it will delete all rows and replaced with the result of
DTS Select Statement. However, when I run the DTS, I get
the error message "Deleting data in a linked table is not
supported by this ISAM".
Your advice is sought.
ThanksHi
There are a couple of mentions for your error messag
http://search.microsoft.com/search/results.aspx?view=msdn&st=a&na=81&qu=&qp=Deleting+data+in+a+linked+table+is+not+supported+by+this+ISAM&qa=&qn=&c=10&s=1
but I not sure if they help in your case!
The problem with appending data sounds like you have cleared the cells and
not deleted the rows. If this is the case you new data will appear at the end
of the cells you have previously cleared.
John
"Stephen" wrote:
> I attempt to export data from a query via DTS to an excel
> spreadsheet.
> When I select the option:
> "Create Destination Table" and "Drop and Recreate
> Destination Table", I find that whenever I run the DTS,
> rows are appended to the Destination Table.
> In this way, I just create a dummy Excel spreadsheet and I
> select "Delete rows in Destination Table". I suppose that
> it will delete all rows and replaced with the result of
> DTS Select Statement. However, when I run the DTS, I get
> the error message "Deleting data in a linked table is not
> supported by this ISAM".
> Your advice is sought.
> Thanks
>

DTS and appending to a text file

can I run a query within DTS and APPEND it to a text file?
please adviseRafael Chemtob wrote:
> can I run a query within DTS and APPEND it to a text file?
Yes. On the Connection toolbar select the "Text File (Destination)"
icon (it looks like a Text File icon w/ a left-pointing arrow).
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

DTS activex task to query soap web service ?

Hello there
I wanted to know if anybody has an example of an activex task in a dts that
will query a soap service ?
I never queries a soap service from vbscript so I guess i need to see an
example to start ? please
ThanksSimo Sentissi wrote:
> Hello there
> I wanted to know if anybody has an example of an activex task in a dts tha
t
> will query a soap service ?
> I never queries a soap service from vbscript so I guess i need to see an
> example to start ? please
> Thanks
>
There have been various toolkits released and the .Net tools have good
support for SOAP. Trying to write that by hand in VBS would seem like
very hard work. I would look to develop something in .Net that does the
work and wrap it either as a custom task or as a COM DLL for use from
the ActiveX Script.
Darren
http://www.sqldts.com
http://www.sqlis.com

DTS activex task to query soap web service ?

Hello there
I wanted to know if anybody has an example of an activex task in a dts that
will query a soap service ?
I never queries a soap service from vbscript so I guess i need to see an
example to start ? please
Thanks
Simo Sentissi wrote:
> Hello there
> I wanted to know if anybody has an example of an activex task in a dts that
> will query a soap service ?
> I never queries a soap service from vbscript so I guess i need to see an
> example to start ? please
> Thanks
>
There have been various toolkits released and the .Net tools have good
support for SOAP. Trying to write that by hand in VBS would seem like
very hard work. I would look to develop something in .Net that does the
work and wrap it either as a custom task or as a COM DLL for use from
the ActiveX Script.
Darren
http://www.sqldts.com
http://www.sqlis.com

DTS activex task to query soap web service ?

Hello there
I wanted to know if anybody has an example of an activex task in a dts that
will query a soap service ?
I never queries a soap service from vbscript so I guess i need to see an
example to start ? please
ThanksSimo Sentissi wrote:
> Hello there
> I wanted to know if anybody has an example of an activex task in a dts tha
t
> will query a soap service ?
> I never queries a soap service from vbscript so I guess i need to see an
> example to start ? please
> Thanks
>
There have been various toolkits released and the .Net tools have good
support for SOAP. Trying to write that by hand in VBS would seem like
very hard work. I would look to develop something in .Net that does the
work and wrap it either as a custom task or as a COM DLL for use from
the ActiveX Script.
Darren
http://www.sqldts.com
http://www.sqlis.com

DTS activex task to query soap web service ?

Hello there
I wanted to know if anybody has an example of an activex task in a dts that
will query a soap service ?
I never queries a soap service from vbscript so I guess i need to see an
example to start ? please
ThanksSimo Sentissi wrote:
> Hello there
> I wanted to know if anybody has an example of an activex task in a dts that
> will query a soap service ?
> I never queries a soap service from vbscript so I guess i need to see an
> example to start ? please
> Thanks
>
There have been various toolkits released and the .Net tools have good
support for SOAP. Trying to write that by hand in VBS would seem like
very hard work. I would look to develop something in .Net that does the
work and wrap it either as a custom task or as a COM DLL for use from
the ActiveX Script.
--
Darren
http://www.sqldts.com
http://www.sqlis.com

DTS 2005 and quoted table names

We're trying to use DTS from SQL Server 2005 beta 2 to query data
from an Informix IDS server via OLEDB.

Unfortunately DTS is building queries of the form:

select * from "database":"owner"."tabname"

and the quoted table name is being rejected by the Informix server as a
syntax error.

Is there a way to keep DTS from quoting the table name in queries? Or is
this happening within the OLEDB provider?

Thanks.

--
John Hardin
Development and Technology group (Seattle)
CRS Retail Systems, Inc.John Hardin (jhardin@.crsretail.com) writes:
> We're trying to use DTS from SQL Server 2005 beta 2 to query data
> from an Informix IDS server via OLEDB.
> Unfortunately DTS is building queries of the form:
> select * from "database":"owner"."tabname"
> and the quoted table name is being rejected by the Informix server as a
> syntax error.
> Is there a way to keep DTS from quoting the table name in queries? Or is
> this happening within the OLEDB provider?

That question is best asked in microsoft.private.sqlserver2005.dts.

See http://go.microsoft.com/fwlink/?linkid=31765 for access information.

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

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

DTS - SQL SERVER

Does the possibility exist of executing a DTS created in SQL Server 7
through a Query using a page ASP or Visual Basic?Hi Frank.
DTS has a COM API, so you can invoke packages (& even create / edit
packages) via the COM interfaces.
You can find examples of how to do this in VB here:
http://www.sqldts.com/default.aspx?208
Or in T-SQL here:
http://www.databasejournal.com/features/mssql/article.php/1459181
HTH
Regards,
Greg Linwood
SQL Server MVP
"Frank Dulk" <fdulk@.bol.com.br> wrote in message
news:Oj4MW3bnDHA.1656@.tk2msftngp13.phx.gbl...
>
> Does the possibility exist of executing a DTS created in SQL Server 7
> through a Query using a page ASP or Visual Basic?
>
>

DTS - Import Text Files

Hi,

I am really new to DTS so please excuse if you find my query to simple.

I have a text file which has lots of records which I am trying to import into a SQL table using a DTS package.

The format of the text file is like this

Field1 ,Field2 ,Field3 ,Field4
Field1 ,Field2 ,Field3 ,Field4
Field1 ,Field2 ,Field3 ,Field4
Field1 ,Field2 ,Field3 ,Field4

As you can see, the text is properly organized but I am facing the following problem

1) If I try to import it as a comma delimited file
Some of the fields inside the text have comma within them. For example field1 in row 1 is like AA,BB,CC and field1 in row 2 is like FFFF. This causes a problem as DTS puts AA and FFFF in one column and BB and Field2 in the next column and so on. Effectively thus, the no. of columns keep increasing and I end up getting an error
"non-white spaces have been found at the end of last column"

2) If I try to import it is a fixed width file

It does extract properly but also places the comma along with the fields in the table. How do I get rid of them?

I will appreciate if someone can give me a solution fix for both the above methods or atleast one of the above.

Thanks a lot

If you trying to do this in DTS, it is a wrong forum. This is SSIS forum. SSIS in SQL2005, is replacement for DTS in SQL 2000.

Anyway, the answer to your question is as follows :

You can't use comma delimited for file format as comma exists within the data (AA,BB,CC).

You have to use fixed field file format. If you have column heading on row one select "Skip Rows = 1", else leave as 0. Make sure you select the commas before field2, field3 and field4 as separate columns in Fixed Field Column Positions. Then you can ignore those columns when you do transformation mappings. This is all you have to do in Text File (Source).

During transformations, you have select ActiveX, instead of Copy Column. the code below should replace all the commas with nothing.
Function Main()

DTSDestination("Field1") = Replace( DTSSource("Col001"),",","")
Main = DTSTransformStat_OK

End Function
You would need to replace DTSSource("Col001") and DTSDestination("Field1") with correct source and destination columns in your scenario.

I have written a simple package to test this. If you wish to have please e-mail me.

Thanks
Sutha

|||Sorry for my late reply Sutha.

Thank you very much. I will attempt your method tmr in the office.

Rochak