DTS from XML

Does anyone know how to create a dts package that will take a XML document and import the contents to a new table in sql.
I have looked in the tasks of DTS and cannot find the xml connection.
Any pointers to some samples would be great.
ThanksConsider upgrading to SQL Server 2005, which has extended support for XML including data import through the Integration Services application. (Interestingly, Integration Services does NOT export XML. Go figure...).sql

DTS from Excel problems

I am trying to import an Excel spreadsheet into SQL Server. Why can I impor
t
an empty Excel column only if it is a text field? If the field is a number
or date field, I get a conversion error.
i.e.
create table labresults
(
analyte varchar(50),
sampledate datetime,
result numeric(19,6)
)
The excel worksheet has 3 columns:
analyte (formatted as text)
sampledate (formatted as date)
result (formatted as number)
If the first 7 rows in the analyte column are empty, the file imports fine,
but if the first 7 rows of sampledate or result are empty, I get a
"Conversions invalid for data types" error.
Anybody have any good suggestions on how to fix this issue?
Thanks.
ArcherHi
Sounds like it is related to:
http://www.sqldts.com/default.aspx?254
John
"bagman3rd" wrote:

> I am trying to import an Excel spreadsheet into SQL Server. Why can I imp
ort
> an empty Excel column only if it is a text field? If the field is a numbe
r
> or date field, I get a conversion error.
> i.e.
> create table labresults
> (
> analyte varchar(50),
> sampledate datetime,
> result numeric(19,6)
> )
> The excel worksheet has 3 columns:
> analyte (formatted as text)
> sampledate (formatted as date)
> result (formatted as number)
> If the first 7 rows in the analyte column are empty, the file imports fine
,
> but if the first 7 rows of sampledate or result are empty, I get a
> "Conversions invalid for data types" error.
> Anybody have any good suggestions on how to fix this issue?
> Thanks.
> Archer
>sql

dts Foxpro MEMO Field

how can I import memo field from Foxpro table'
Many Thanks in Advance!!You can transform it to a large varchar() column or a text column.
Which of these to use would depend on the average and max lengths of the
memo field.
* in vfp
select max(len(theMemoField)) as max_len from theTable
If' the max length is over 8000, you'll have to use text. If it's <=8000
_and you can say with assurance_ that the max length will stay <=8000,
I'd tend to go with a large varchar column.
SQLbeginner wrote:

>how can I import memo field from Foxpro table'
>Many Thanks in Advance!!
>|||Thanks Trey.
"Trey Walpole" wrote:

> You can transform it to a large varchar() column or a text column.
> Which of these to use would depend on the average and max lengths of the
> memo field.
> * in vfp
> select max(len(theMemoField)) as max_len from theTable
> If' the max length is over 8000, you'll have to use text. If it's <=8000
> _and you can say with assurance_ that the max length will stay <=8000,
> I'd tend to go with a large varchar column.
>
> SQLbeginner wrote:
>
>|||Thanks very much for the helps
"SQLbeginner" wrote:

> how can I import memo field from Foxpro table'
> Many Thanks in Advance!!|||I was able to import the MEMO field using the Foxpro ODBC driver, however,
not all information from the memo fields are imported. For example, a recor
d
has 11 lines of information, it would only import part of it and some record
s
didn't even get any information transferred. On the SQL database table, I
use 'text' as the data type.
Does anyone has a solution?
Many Thanks in Advance.
Nancy
"SQLbeginner" wrote:

> how can I import memo field from Foxpro table'
> Many Thanks in Advance!!

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 Export Object

I have created a DTS object to import a text. This works fine, however, I
need to find a way to 'generate a script' to be able to recreate this same
DTS in another places.
ArthurArthur,
when you Save As ... the package, in the Location dropdown you get 3
choices. You can save to a server, local or remote, as a structured storage
file or as a vb file. I have not worked with a vb file. But if you save as
a structured storage file, you can copy it to other place and there right
click Local Packages and choose Open.
hth
Quentin
"Arthur C" <arthur.christy@.tamut.edu.delete.me> wrote in message
news:O7gND6lmDHA.1884@.TK2MSFTNGP09.phx.gbl...
> I have created a DTS object to import a text. This works fine, however, I
> need to find a way to 'generate a script' to be able to recreate this same
> DTS in another places.
> Arthur
>

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 too many columns found

Trying to import a text file with a 11 columns, column delimiter is ;
record delimiter is CR+ LF.
Using a DTS package (bulk copy)
Then I got erros with these discription
------
SYMPTOMS
When you import a text file into Microsoft SQL Server by using the Transform Data task, if no text qualifier is specified and a row with too many columns is encountered, the load of the text file may fail with the following error message:

Error Source: Microsoft Data Transformation Services Flat File Rowset Provider
Error Description:Too many columns found in the current row; non-whitespace characters were found after the last defined column's data.
Error Help FileTSFFile.hlp
Error Help Context ID:0
The preceding error message repeats in the DTS exception log N times, where N is the value of the Max Error Count setting on the Transform Data tasks options property page.

CAUSE
This problem occurs because of a malformed source text file in which the DTS Data Pump encounters a row in the source file that contains too many columns.

http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b300181

--------
I tryed the fix of Microsoft but no result yet.Did you get the specified hotfix from MS Support?
For information refer to thisSQL MAG (http://www.sqlmag.com/Forums/messageview.cfm?catid=11&threadid=7326) thread.|||Originally posted by Satya
Did you get the specified hotfix from MS Support?
For information refer to thisSQL MAG (http://www.sqlmag.com/Forums/messageview.cfm?catid=11&threadid=7326) thread.

Yes I got the fix en installed but I have still the errors.|||Then better to report back to MS Support.

DTS Error - Data too large for specified buffer size

I'm trying to import data into my table and I'm received an error message
that I've been able to resolve.
The message is:
Error Source: Microsoft Transformation Services (DTS) Data Pump
Error Description: Data for source column 3 ("column_name") is too large for
the specified buffer size.
The destination column size is 500 in the table and the data in the import
file for that row and column is 280 characters.
Any ideas?
Thanks in advance,
Scott
It's working now.
This link was helpful as well I switched from ODBC to OLE connection.
http://support.microsoft.com/kb/281517/EN-US/
Thanks,
Scott
"Scott Natwick" <nospamplease@.yahoo.com> wrote in message
news:j7idnVAdiJWFwz7cRVn-gA@.comcast.com...
> I'm trying to import data into my table and I'm received an error message
> that I've been able to resolve.
> The message is:
> Error Source: Microsoft Transformation Services (DTS) Data Pump
> Error Description: Data for source column 3 ("column_name") is too large
> for the specified buffer size.
> The destination column size is 500 in the table and the data in the import
> file for that row and column is 280 characters.
> Any ideas?
> Thanks in advance,
> Scott
>

DTS Error - Data too large for specified buffer size

I'm trying to import data into my table and I'm received an error message
that I've been able to resolve.
The message is:
Error Source: Microsoft Transformation Services (DTS) Data Pump
Error Description: Data for source column 3 ("column_name") is too large for
the specified buffer size.
The destination column size is 500 in the table and the data in the import
file for that row and column is 280 characters.
Any ideas?
Thanks in advance,
ScottIt's working now.
This link was helpful as well I switched from ODBC to OLE connection.
http://support.microsoft.com/kb/281517/EN-US/
Thanks,
Scott
"Scott Natwick" <nospamplease@.yahoo.com> wrote in message
news:j7idnVAdiJWFwz7cRVn-gA@.comcast.com...
> I'm trying to import data into my table and I'm received an error message
> that I've been able to resolve.
> The message is:
> Error Source: Microsoft Transformation Services (DTS) Data Pump
> Error Description: Data for source column 3 ("column_name") is too large
> for the specified buffer size.
> The destination column size is 500 in the table and the data in the import
> file for that row and column is 280 characters.
> Any ideas?
> Thanks in advance,
> Scott
>

DTS error - Cannot import a table

I have schedule DTS to import every day few tables. It had been working
great and had no problem. All of a sudden I started getting this error.
ERROR AT DESTINATION FOR ROW NUMBER 100. ERROR ENCOUNTERED SO FAR IN THE
TASK. 1 UNIDENTIFIED ERROR.
Thanks.
any constraint violations caused by the new row? pk, unique, rule, default
etc?
"Mac" <mac@.hotmail.com> wrote in message news:Tz%oe.7720$nr3.842@.trnddc02...
>I have schedule DTS to import every day few tables. It had been working
> great and had no problem. All of a sudden I started getting this error.
> ERROR AT DESTINATION FOR ROW NUMBER 100. ERROR ENCOUNTERED SO FAR IN THE
> TASK. 1 UNIDENTIFIED ERROR.
> Thanks.
>
>
|||Also, turn on exception reporting for the Data Pump (Transformation Task).
This will output the source row and any other error messages.
Also, turn on package logging. You can usually get more detailed
information from these logs that what is genereated in the Job Agent.
Sincerely,
Anthony Thomas

"Richard Ding" <rding@.acadian-asset.com> wrote in message
news:elb$rqsaFHA.3032@.TK2MSFTNGP10.phx.gbl...
any constraint violations caused by the new row? pk, unique, rule, default
etc?
"Mac" <mac@.hotmail.com> wrote in message news:Tz%oe.7720$nr3.842@.trnddc02...
>I have schedule DTS to import every day few tables. It had been working
> great and had no problem. All of a sudden I started getting this error.
> ERROR AT DESTINATION FOR ROW NUMBER 100. ERROR ENCOUNTERED SO FAR IN THE
> TASK. 1 UNIDENTIFIED ERROR.
> Thanks.
>
>

DTS error - Cannot import a table

I have schedule DTS to import every day few tables. It had been working
great and had no problem. All of a sudden I started getting this error.
ERROR AT DESTINATION FOR ROW NUMBER 100. ERROR ENCOUNTERED SO FAR IN THE
TASK. 1 UNIDENTIFIED ERROR.
Thanks.any constraint violations caused by the new row? pk, unique, rule, default
etc?
"Mac" <mac@.hotmail.com> wrote in message news:Tz%oe.7720$nr3.842@.trnddc02...
>I have schedule DTS to import every day few tables. It had been working
> great and had no problem. All of a sudden I started getting this error.
> ERROR AT DESTINATION FOR ROW NUMBER 100. ERROR ENCOUNTERED SO FAR IN THE
> TASK. 1 UNIDENTIFIED ERROR.
> Thanks.
>
>|||Also, turn on exception reporting for the Data Pump (Transformation Task).
This will output the source row and any other error messages.
Also, turn on package logging. You can usually get more detailed
information from these logs that what is genereated in the Job Agent.
Sincerely,
Anthony Thomas
"Richard Ding" <rding@.acadian-asset.com> wrote in message
news:elb$rqsaFHA.3032@.TK2MSFTNGP10.phx.gbl...
any constraint violations caused by the new row? pk, unique, rule, default
etc?
"Mac" <mac@.hotmail.com> wrote in message news:Tz%oe.7720$nr3.842@.trnddc02...
>I have schedule DTS to import every day few tables. It had been working
> great and had no problem. All of a sudden I started getting this error.
> ERROR AT DESTINATION FOR ROW NUMBER 100. ERROR ENCOUNTERED SO FAR IN THE
> TASK. 1 UNIDENTIFIED ERROR.
> Thanks.
>
>sql

DTS error - Cannot import a table

I have schedule DTS to import every day few tables. It had been working
great and had no problem. All of a sudden I started getting this error.
ERROR AT DESTINATION FOR ROW NUMBER 100. ERROR ENCOUNTERED SO FAR IN THE
TASK. 1 UNIDENTIFIED ERROR.
Thanks.any constraint violations caused by the new row? pk, unique, rule, default
etc?
"Mac" <mac@.hotmail.com> wrote in message news:Tz%oe.7720$nr3.842@.trnddc02...
>I have schedule DTS to import every day few tables. It had been working
> great and had no problem. All of a sudden I started getting this error.
> ERROR AT DESTINATION FOR ROW NUMBER 100. ERROR ENCOUNTERED SO FAR IN THE
> TASK. 1 UNIDENTIFIED ERROR.
> Thanks.
>
>|||Also, turn on exception reporting for the Data Pump (Transformation Task).
This will output the source row and any other error messages.
Also, turn on package logging. You can usually get more detailed
information from these logs that what is genereated in the Job Agent.
Sincerely,
Anthony Thomas
"Richard Ding" <rding@.acadian-asset.com> wrote in message
news:elb$rqsaFHA.3032@.TK2MSFTNGP10.phx.gbl...
any constraint violations caused by the new row? pk, unique, rule, default
etc?
"Mac" <mac@.hotmail.com> wrote in message news:Tz%oe.7720$nr3.842@.trnddc02...
>I have schedule DTS to import every day few tables. It had been working
> great and had no problem. All of a sudden I started getting this error.
> ERROR AT DESTINATION FOR ROW NUMBER 100. ERROR ENCOUNTERED SO FAR IN THE
> TASK. 1 UNIDENTIFIED ERROR.
> Thanks.
>
>

DTS error

I'm creating an import package to read a DBF into a sql table. It's all
pretty simple - no transformations or anything that ought to be difficult.
Yet when I run it, I get the error:
Copy Data from Results to Results Task
--
The task reported failure on execution.
INSERT failed because the following SET options have incorrect settings:
'ARITHABORT'.
OK
--
I cannot understand this error message. What does ARITHABORT have to do with
anything? There's no arithmetic being performed. In fact, there's only one
numeric column, an identity column being transferred from integer to integer
(and the option to allow identity setting is on). The other columns are
strings and dates.
When I tested the transformations individually, each worked fine. (They're
only simple copies anyway, no transformation being performed.)
I don't see any way to set arithabort in the package anyhow. And if I set it
in the Query Analyzer, it makes no difference.
What is this error message trying to tell me?
Also, after attempting this several times, I got a "stack overflow" message
and the Enterprise Manager abruptly quit.The ARITHABORT ON connection option is required in order to modify a table
with indexed views or indexes on computed columns.
You can set this as the database default with ALTER DATABASE:
ALTER DATABASE MyDatabase
SET ARITHABORT ON
Alternatively, you can specify this as the default at the server level by
using sp_configure to turn on bit 64 of the 'user options' bitmask.
Hope this helps.
Dan Guzman
SQL Server MVP
"Paul Pedersen" <no-reply@.swen.com> wrote in message
news:eByRp2ANFHA.244@.tk2msftngp13.phx.gbl...
> I'm creating an import package to read a DBF into a sql table. It's all
> pretty simple - no transformations or anything that ought to be difficult.
> Yet when I run it, I get the error:
> --
> Copy Data from Results to Results Task
> --
> The task reported failure on execution.
> INSERT failed because the following SET options have incorrect settings:
> 'ARITHABORT'.
>
> --
> OK
> --
>
> I cannot understand this error message. What does ARITHABORT have to do
> with anything? There's no arithmetic being performed. In fact, there's
> only one numeric column, an identity column being transferred from integer
> to integer (and the option to allow identity setting is on). The other
> columns are strings and dates.
> When I tested the transformations individually, each worked fine. (They're
> only simple copies anyway, no transformation being performed.)
> I don't see any way to set arithabort in the package anyhow. And if I set
> it in the Query Analyzer, it makes no difference.
> What is this error message trying to tell me?
>
> Also, after attempting this several times, I got a "stack overflow"
> message and the Enterprise Manager abruptly quit.
>
>|||Woo hoo, that did the trick. Thank you!
But how in the world is anyone supposed to know this stuff? I ran, or
thought I ran, a pretty exhaustive search on arithabort in BOL, and didn't
find this little factoid.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:u8b7okGNFHA.2680@.TK2MSFTNGP09.phx.gbl...
> The ARITHABORT ON connection option is required in order to modify a table
> with indexed views or indexes on computed columns.
> You can set this as the database default with ALTER DATABASE:
> ALTER DATABASE MyDatabase
> SET ARITHABORT ON
> Alternatively, you can specify this as the default at the server level by
> using sp_configure to turn on bit 64 of the 'user options' bitmask.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Paul Pedersen" <no-reply@.swen.com> wrote in message
> news:eByRp2ANFHA.244@.tk2msftngp13.phx.gbl...
>

DTS Error

I need to copy some datas from Oracle to MS Access db. To do it, I use SQL Server Import and Export Data tool. But I want to move it to a Visual Basic program. I saved DTS package from Import and Export tool. I embedded to my VB program. But sometimes it gives me error like:

XXX Step failed
Microsoft Data Transformation Services (DTS) Data Pump
The number of failing rows exceeds the maximum specified. (Microsoft JET Database Engine (80004005):
Could not update; currently locked by user 'Admin' on machine 'X'.)

How can I solve the problem?
My connections strings are;

For Oracle :
Set oConnection = goPackage.Connections.New("OraOLEDB.Oracle")

oConnection.ConnectionProperties("Persist Security Info") = False
oConnection.ConnectionProperties("User ID") = user
oConnection.ConnectionProperties("Data Source") = db
oConnection.ConnectionProperties("Window Handle") = 0
oConnection.ConnectionProperties("Locale Identifier") = 1055
oConnection.ConnectionProperties("Prompt") = 2
oConnection.ConnectionProperties("OLE DB Services") = -1

oConnection.Name = "Connection 1"
oConnection.ID = 1
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = db
oConnection.UserID = user
oConnection.password = password
oConnection.ConnectionTimeout = 60
oConnection.UseTrustedConnection = False
oConnection.UseDSL = False

For Access:
Set oConnection = goPackage.Connections.New("Microsoft.Jet.OLEDB.4.0")

oConnection.ConnectionProperties("Data Source") = " & download_form.destination_text.text & "
oConnection.ConnectionProperties("Mode") = 3


oConnection.Name = "Connection 2"
oConnection.ID = 2
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = download_form.destination_text.Text
oConnection.ConnectionTimeout = 160
oConnection.UseTrustedConnection = False
oConnection.UseDSL = FalseDepending on what version of SQL Server you are running you can create a graphical DTS package instead of just using the import export tool...

I believe if you are running above SQL v. 7 it has the capabilites of the actual dts...

Underneath your console root you should have access to the Data Transformation Services folder... when the node is opened, click once on local packages, then right click and design new package... When you create your dts package through this and use the transform data task from the oracle db connection to the access db, you are then going to want to save the package... You can save it in VB format. To do this under the Location drop down in the save box, select vb file and the directory where you want to save it...|||We use sql server 2000. I created package. I have no problem about creating package. I think the problem is parameters of connection or driver of access connection. Which parameter determines the max. rows which are inserted ?

Originally posted by justastef
Depending on what version of SQL Server you are running you can create a graphical DTS package instead of just using the import export tool...

I believe if you are running above SQL v. 7 it has the capabilites of the actual dts...

Underneath your console root you should have access to the Data Transformation Services folder... when the node is opened, click once on local packages, then right click and design new package... When you create your dts package through this and use the transform data task from the oracle db connection to the access db, you are then going to want to save the package... You can save it in VB format. To do this under the Location drop down in the save box, select vb file and the directory where you want to save it...|||I'm sorry I can't be more of help however, I did ask a friend for you and he suggested to try these few things

Increase the connection timeout to a higher number (try 120 or above) and make sure you have the latest service pack installed for SQL Server.sql

DTS doesnt recognize CRLF in Text File

I'm using DTS to import a text file (fixed field). In my sample data, I have 21 rows of data.

DTS only brings in 15 or so, because it fails to recognize the CRLF on some of the rows, and just treats it and the subsequent row as part of the previous row.

The text file is being produced via FTP from an IBM Iseries. It imports just fine into Notepad and Excel.

Anyone have any ideas why DTS would have trouble with this ?

Thanks

Gregnevermind...apparently DTS requires flat text files to be padded with blanks in order to make all the records exactly the same length. I don't know why DTS is so picky about this when every other Micro$oft application isn't.

I reckon I'll just make it comma delimited and be done with it.

DTS csv import fails when using double quotes as the Text Qualifier, the last column is a

I am using DTS to import csv files that I receive from a third party.
Each day I receive many file which I import into many different SQL
Server tables depending on the name of the file. I am doing this as
part of a larger automation process written in C#. Due to the csv
file names differing each day (they contain a date) and the large
number of DB Table schemas, I am programmatically/dynamically
creating/building a generic DTS package for each file and calling it
to import the csv file into the appropriate table.
The data contains commas for the column delimiters, double quotes for
the Text Qualifier and CR LF for the row delimiter. I have set the
appropriate DTSFlatFile connection properties and successfully
imported many of the csv files except files which meet the following
criteria:
1) the last column of the SQL Server table that corresponds to this
file is of type varchar or char
2) the data value for the last column of the record is always null
For these file I get the following error message when I try to execute
the package: "A DTSTransformCopy must specify no columns (signifying a
sequential 1-to-1 mapping of all columns) or the same number of source
and destination columns."
e.g.
File 1 -- imports fine:
--
"1","a, b","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
06:25:58","0088288574800","dd"
"2","cd","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
06:25:58","0088288574800",
"3","e, f","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
06:25:58","0088288574800",
File 2 -- imports fine:
--
"1","c, d","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
06:25:58","0088288574800",
"2","ab","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
06:25:58","0088288574800","dd"
"3","c, d","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
06:25:58","0088288574800",
File 3 -- imports fine:
--
"1","a, b","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
06:25:58","0088288574800",
"2","cd","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
06:25:58","0088288574800",
"3","e, f","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
06:25:58","0088288574800","dd"
File 4 -- fails:
--
"1","a, b","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
06:25:58","0088288574800",
"2","cd","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
06:25:58","0088288574800",
"3","e, f","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
06:25:58","0088288574800",
Table schema:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table1]
GO
CREATE TABLE [dbo].[Table1] (
[Field1] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[Field2] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[Field3] [datetime] NOT NULL ,
[Field4] [datetime] NULL ,
[Field5] [datetime] NULL ,
[Field6] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[Field7] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
I am using SQL 2000 sp3a:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.0 (Build 2195: Service Pack 3)
BTW I found an 1.5 year old posting about this issue but no solution
was ever posted:
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=%23XiCPwG3CHA.1624%40TK2MSFTNGP12.phx.gbl&rnum=1&prev=/groups%3Fhl%3Den%26lr%3D%26ie%3DUTF-8%26selm%3D%2523XiCPwG3CHA.1624%2540TK2MSFTNGP12.phx.gbl
Thanks,
DavidSo why you can't process this text file manually in your C# program
It will be a simple loop with output to the table
"David E Herbst" wrote:
> I am using DTS to import csv files that I receive from a third party.
> Each day I receive many file which I import into many different SQL
> Server tables depending on the name of the file. I am doing this as
> part of a larger automation process written in C#. Due to the csv
> file names differing each day (they contain a date) and the large
> number of DB Table schemas, I am programmatically/dynamically
> creating/building a generic DTS package for each file and calling it
> to import the csv file into the appropriate table.
> The data contains commas for the column delimiters, double quotes for
> the Text Qualifier and CR LF for the row delimiter. I have set the
> appropriate DTSFlatFile connection properties and successfully
> imported many of the csv files except files which meet the following
> criteria:
> 1) the last column of the SQL Server table that corresponds to this
> file is of type varchar or char
> 2) the data value for the last column of the record is always null
> For these file I get the following error message when I try to execute
> the package: "A DTSTransformCopy must specify no columns (signifying a
> sequential 1-to-1 mapping of all columns) or the same number of source
> and destination columns."
> e.g.
> File 1 -- imports fine:
> --
> "1","a, b","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
> 06:25:58","0088288574800","dd"
> "2","cd","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
> 06:25:58","0088288574800",
> "3","e, f","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
> 06:25:58","0088288574800",
> File 2 -- imports fine:
> --
> "1","c, d","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
> 06:25:58","0088288574800",
> "2","ab","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
> 06:25:58","0088288574800","dd"
> "3","c, d","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
> 06:25:58","0088288574800",
> File 3 -- imports fine:
> --
> "1","a, b","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
> 06:25:58","0088288574800",
> "2","cd","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
> 06:25:58","0088288574800",
> "3","e, f","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
> 06:25:58","0088288574800","dd"
> File 4 -- fails:
> --
> "1","a, b","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
> 06:25:58","0088288574800",
> "2","cd","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
> 06:25:58","0088288574800",
> "3","e, f","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
> 06:25:58","0088288574800",
> Table schema:
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[Table1]') and OBJECTPROPERTY(id, N'IsUserTable') => 1)
> drop table [dbo].[Table1]
> GO
> CREATE TABLE [dbo].[Table1] (
> [Field1] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [Field2] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [Field3] [datetime] NOT NULL ,
> [Field4] [datetime] NULL ,
> [Field5] [datetime] NULL ,
> [Field6] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [Field7] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> I am using SQL 2000 sp3a:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Developer Edition on Windows NT 5.0 (Build 2195: Service Pack 3)
> BTW I found an 1.5 year old posting about this issue but no solution
> was ever posted:
> http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=%23XiCPwG3CHA.1624%40TK2MSFTNGP12.phx.gbl&rnum=1&prev=/groups%3Fhl%3Den%26lr%3D%26ie%3DUTF-8%26selm%3D%2523XiCPwG3CHA.1624%2540TK2MSFTNGP12.phx.gbl
> Thanks,
> David
>|||Thank you for the suggestion.
Yes, that is an option but I am importing a many different csv files
to a large number of DB tables (each with a different number of
columns and column types) and I'm trying to avoid writing custom file
parsing code for each of the table schemas. Importing csv files seems
like a pretty standard problem that has been solved many times already
so I'm also trying to avoid spending time writing my own generic csv
file parser/loader which seems like reinventing the wheel. In
addition I've heard that large numbers of INSERTs though ADO.NET is
not that efficient and news groups postings usually suggest DTS
instead?
BTW I forgot to mention that I am taking advantage of the fact that
since the source and destination have the same number of columns in
the same order I don't have to define and add any column objects to
the DTS.DataPumpTransformCopy transformation object. This is useful
since I can use the same generic package creation code for all of the
DB tables and not embed any knowledge of the column schema in my code.
See:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtsprog/dtspapps_45k3.asp?frame=true
(see comment in the example)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtsprog/dtspapps_976r.asp?frame=true
(first bullet points)sql

DTS copy all objects failed with invalid column name

I am trying to use the Import Data wizard to copy one database into
another on the same server and keep getting this error.
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name
'ColumnName'
There are 6 columns in the list and all of them either don't exist
anymore or have been moved to another table in the database. I don't
understand why it keeps trying to copy columns that either aren't
there or have been moved. It looks like the database has become
corrupted. Does anyone know how I might fix this?
Regards,
Aaron
I have also tried dropping all the tables, recreating them and
re-inserting the data and I still get these errors when trying to copy
the database.
|||I have also tried dropping all the tables, recreating them and
re-inserting the data and I still get these errors when trying to copy
the database.
Regards,
Aaron

DTS copy all objects failed with invalid column name

I am trying to use the Import Data wizard to copy one database into
another on the same server and keep getting this error.
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name
'ColumnName'
There are 6 columns in the list and all of them either don't exist
anymore or have been moved to another table in the database. I don't
understand why it keeps trying to copy columns that either aren't
there or have been moved. It looks like the database has become
corrupted. Does anyone know how I might fix this?
Regards,
AaronI have also tried dropping all the tables, recreating them and
re-inserting the data and I still get these errors when trying to copy
the database.|||I have also tried dropping all the tables, recreating them and
re-inserting the data and I still get these errors when trying to copy
the database.
Regards,
Aaron

DTS connecting to CA Datacom

I am trying to import data from a CA Datacom database on a mainframe using SQL Server2000 Data Transformation Services. I can sign in with my username and password and see all the CA Datacom tables listed in DTS. When I select a table I get an error:

"Your provider does not support all the interfaces/methods required by DTS."

I am using the ODBC driver: CA Datacom/DB version 78.73.7970.

I have read in some other posts on the internet of people using this ODBC driver and being able to import data into SQL Server. Anybody have any ideas? Anybody doing this successfully?

your input is greatly appreciated.Ok.. I figured it out. For anyone else that may ever try doing this:

Instead of selecting the CA /Datacom DB driver in the pulldown list in DTS, I chose "Other ODBC data source" and this let me download tables.

DTS Classes Not Showing in Visual Studio

I am trying to use VB.NET to run SSIS packages. However I don't have the various dts namespaces available. When I attempt to import them I only have Microsoft.SqlServer.Server in intellisense.

I am running on XP sp2, VS 2005 (full install) and even went so far as to install sql 2005 sp1 full install on my local machine.

What gives with only having Microsoft.SqlServer.Server available?

thanks,

Scott

Did you install SSIS?|||

Yes I did a full install of both vs and sql (including books and samples).

Some more info:

I'm trying to do this from an asp.net web service project. Do I need to add the assemblies to web.config? If so, does anyone have the assembly details (PublicKeyToken, etc)?

thanks--Scott

|||

But did you specifically install Integration Services? You can have the development tools without having all of SSIS.

However there's no reason I can think of that the Add Reference dialog in Visual Studio wouldn't show a long list of Microsoft.SqlServer... assemblies, unless they weren't there. You may need to tweak Web permissions later to use them successfully in the deployed app, but you should at least see them.

-Doug

|||You need to reference SSIS assemblies, like Microsoft.SQLServer.ManagedDTS.dll.
They are by default in C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies.|||

Doug,

Thanks for the reply. Yes, SSIS is installed. I had originally installed sql tools and books online. After doing a number or reinstalls of the tools and VS (rebooting here and there) I installed sql server dev ed (including the database engine, SSRS, SSIS, SSNS). I then set the services to manual (to not bog my machine down).

|||

Michael,

Thank you for your reply. That's my problem...I can't reference the assemblies. The imports statement only show Microsoft.SqlServer.Server.

If I add a reference to web.config like:

<add assembly="Microsoft.SqlServer.Dts.Runtime, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845DCD8080CC91"/>

I receive an error, "Could not load assembly...<listed above>...system cannot find the file specified.

If I have a full install of sql server (all services installed locally), why can't my system find the assembly?

thanks again for your help

|||The assembly name is Microsoft.SqlServer.ManagedDTS, not Microsoft.SqlServer.Dts.Runtime (which is one of the namespaces defined in this assembly).|||

Ah, I was not aware of that. I added the following to my web.config and it works fine.

thanks for your help.

<add assembly="Microsoft.SqlServer.ManagedDTS, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845DCD8080CC91"/>

sql