Import Password Protected File Via DTS

I am trying to import a password-protected Excel file into SQL Server
using DTS. I am getting an error that it can't decrypt file. Does
anyone know how I can pass the password to the file during the DTS
execution. Please help.

Thanks,
MichelleThe Excel connection object has a Password property - you can set it
using a dynamic properties task or an ActiveX task.

Simon

Import Paradox 8 DB into SQL Server 2000 using DTS

Hi,

I'm trying to import a Paradox 8 db into SQL Server 2000 using DTS but the list of data sources in the connections dialog only caters for version 5.x or older. Does anybody know how to get the data source for v8?

If not, is there any other quick and efficient way to import a paradox 8 db as I have to import around 15 of them to do.

ThanksHave you tried the highest version available to see if it works ?|||You can also purchase odbc software from datadirect:

link (http://www.datadirect-technologies.com/products/odbc/matrix/connectodbc.htm)|||Cheers for your help. I've tried the highest version, but it doesn't work. I've since discovered that you can save Paradox 8 tables as v5 and then import them. It's time consuming though as it appears you can only save them as v5 table by table, not the whole database as it would work with something like MS Access.|||No problem. Either spend the time (and money) to find a solution or purchase the driver - normally it is cheaper to purchase the driver, however, to convince those above you may be more difficult.

Import only yhe changed data

I want to import via DTS to big table only the records that changed last day. can i do it without a time field? because this is a key table for DWH and not a fact table.
THX
InonIt would be bit difficult to import without a time field, which is key to compile the data.

If your task is about changed data then why not consider replication.|||Sure, but you need a staging environment...

1. bcp (I can't abvide DTS unless the data is in Excel or Access, even then...) in to a stage table
2. Write 3 sql statements to compare new data with old...determine, based on keys, which data was added, which data was deleted, and which data was updated..

3. Then INSERT, DELETE and UPDATE those sets of data...

Got a link somewhere...

Hold on...|||Here's the code:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=28305|||You can use the BINARY_CHECKSUM function to determine whether data was changed.sql

IMPORT new Data since last IMPORT - DTS/Stored Procs?

Hello:

I am not sure how to implement the following, but I believe it entails using DTS, and hopefully it is fine that I post it here b/c ultimately I will need this backend data for my frontend .aspx pages:

On a weekly basis, I need to IMPORT some data located on a remote Oracle DB into SQL Server 2k. Since there is so much data to transfer, I would only like to transfer the data that is new to the table since the last IMPORT, i.e. a week ago and leave behin the OLD data.

Is DTS the correct way to go or do I have more control via DTS with STORED PROCEDURES? Does anyone have any good references for me?

On a similar note, once this Oracle data is IMPORTED into a certain table, I would like to EXPORT some of these NEWLY acquired rows matching certain criteria into another table for auditing purposes. For this scenario, should I implement a TRIGGER UPDATE event here on the first table?

Any advice will be greatly appreciated!I wouldn't use a trigger.

You can use DTS to call stored procedures, so you can just do it all in one DTS package.

'a week ago and leave behin the OLD data' You need to filter the data coming in. You can pass variables to DTS jobs to alter the way they execute. I think you could pass in a variable to alter the where clause within the query you specify for DTS. I haven't used variables for that reason, so I 'm not sure. Anyway, schedule that with a job and pass in the appropriate date for your filter. That would get the import into SQL. Then run whatever procedures you need to clean up the data, check for validity, etc. Then call a step to export records based on whatever criteria. I'd do it all in one DTS package.

Here's a place to start
http://www.swynk.com/friends/green/DTSHowTo3.asp|||What DTS book do you recommend I acquire to learn more about passing variables to DTS and other related topics?

I also referenced the URL you gave, but I didn't believe it was too helpful in my case.

Thanks.|||I'm a google junkie; I don't have any books on DTS.

http://www.databasejournal.com/features/mssql/article.php/1461501

This link is on the page of the first link and gets a little bit more there. You have to use the same concept as in these examples.

What I would do is this:

1) create a DTS package that uses a query to pull from the oracle database. Just a canned one that has your desired date range specified so you can see what your where clause will look like.

2) save the package to a Visual Basic File. Open that file in Notepad and find the query you specified and you can see what all the object names/details are that you'll need to reference

3) Follow the examples in these two links to create an activex task in your package that will alter the query with variable that you pass into it. Actually, you don't need to pass in variables if you don't want. You could just put the logic into the activex task that determines, based on the current date, what the date range should be. It's just VBScript, so you have all the date/time functions available.

I know this is a little sketchy, but I used these examples to figure it out the first time so they do help. Swynk has some good stuff so you could search their site or join their forum also.

Import Multi-Row Record Text File with DTS

I have a text file I need to import into a SQL Server table. Each record spans several lines. Does anyone have a vbscript routine that wil go thru this text file and put each record on one row? Once the records are one row, DTS will easily handle the import. Or is there a better way?

Sample multi-row records:

WRLDWYXCDS1 ALT101 APR04 21:30:24 6879 FAIL ALT
HOST 00 0 08 00 DN 3073477171 1st CYCLE
TEST TYPE CKTTST DIAGNOSTIC RESULT BIC/EBS LC TRBL:PTRN 000E S= 1 R= 0
ACTION REQUIRED Replace Card CARD TYPE 6X21AC

WRLDWYXCDS1 ALT101 APR04 22:31:37 7672 FAIL ALT
HOST 00 0 08 00 DN 3073477171 1st CYCLE
TEST TYPE CKTTST DIAGNOSTIC RESULT BIC/EBS LC TRBL:PTRN 000E S= 1 R= 0
ACTION REQUIRED Replace Card CARD TYPE 6X21ACDTS questions don't get a lot of answers on this forum. Perhaps most developers feel as I do, that DTS is a kluge solution of several different poorly integrated technologies. If you are lucky, my posting this reply will shoot your post back to the top of the list queue and somebody proficient in DTS will give you an answer.

Otherwise, I use DTS only for what it is best at; transfering and importing raw data. I avoid using DTS for transforming data, prefering instead to load the data into staging tables in my database and then run stored procedures to transfer it to the main database schema. I think one of the big advantages of this is that you separate the import process from the transform process, allowing you to flag and retain records that fail your business rules.|||Originally posted by eoffshore
I have a text file I need to import into a SQL Server table. Each record spans several lines. Does anyone have a vbscript routine that wil go thru this text file and put each record on one row? Once the records are one row, DTS will easily handle the import. Or is there a better way?

Sample multi-row records:

WRLDWYXCDS1 ALT101 APR04 21:30:24 6879 FAIL ALT
HOST 00 0 08 00 DN 3073477171 1st CYCLE
TEST TYPE CKTTST DIAGNOSTIC RESULT BIC/EBS LC TRBL:PTRN 000E S= 1 R= 0
ACTION REQUIRED Replace Card CARD TYPE 6X21AC

WRLDWYXCDS1 ALT101 APR04 22:31:37 7672 FAIL ALT
HOST 00 0 08 00 DN 3073477171 1st CYCLE
TEST TYPE CKTTST DIAGNOSTIC RESULT BIC/EBS LC TRBL:PTRN 000E S= 1 R= 0
ACTION REQUIRED Replace Card CARD TYPE 6X21AC

You don't need VB for this. It would be faster to create a staging table with one column. You want the column to be a char so you don't lose your fixed length. Have a select statement push it into a second staging table with char, but big enough for all four rows.

Then, you can either use a final parsing statement to divide it up, or export to a fixed length text file. It should be quicker to do it this way in sets then to parse through in VB where you'll have to parse through each line and length section.|||In your case it looks like you have records with five elements, where each element is a separate line in your raw data.

One solution to this is to import each record into a staging table as a single string of characters. A possible layout for your staging table would be:

RecordID (Identity)
GroupID (Int)
RecordType (Int)
RecordString (Varchar(500))
ImportErrors (Varchar(100))

As the data is imported into the staging table a RecordID is created to maintain the order.

Next you run a query(s) against your table to identify the RecordType for each record. For instance, maybe all the type 2 records start with 'HOST'

Lastly, group the records like this:

Update StagingTable
set GroupID = SubTable.GroupID
from StagingTable
inner join
(select StagingTable.RecordID, Min(GroupRecords.RecordID) GroupID
from StagingTable
inner join StagingTable GroupRecords
on StagingTable.RecordID >= GroupRecords.RecordID
and GroupRecords.RecordType = 1) SubTable
on StagingTable.RecordID = SubTable.RecordID

Now you have identified your imported records and their relationships to eachother, and you can load them into your schema. It's not neat or pretty, but it works. For each step you can flag problems by appending an error message to the ImportErrors field.

import multiple accessfiles in dts

Hi,

How can i import multiple accessfiles in sql server 2000 by using a dts
package?

The dts package should import files with the most current date in a
directory (not today's date). The date can be found in the name, e.g.:
<companyname>_20041214.mdb.

Also i would like to use the companyname from the filename to fill in a
empty column called companyname, so i can import all files into one
normalized table.

The files are placed in on a root drive.

If someone can help me on this i would be very happy.

Thnx.Hi

The following should get you started:
http://www.sqldts.com/default.aspx?292
http://www.sqldts.com/default.aspx?248
http://www.sqldts.com/default.aspx?201

You can store the company name in a global variable, then use an activeX
transform to populate a column from that global variable.
http://www.sqldts.com/default.aspx?279,4

John

"Ezekil" <ezekil@.lycos.com> wrote in message
news:41c281c0$0$45956$a344fe98@.news.wanadoo.nl...
> Hi,
> How can i import multiple accessfiles in sql server 2000 by using a dts
> package?
> The dts package should import files with the most current date in a
> directory (not today's date). The date can be found in the name, e.g.:
> <companyname>_20041214.mdb.
> Also i would like to use the companyname from the filename to fill in a
> empty column called companyname, so i can import all files into one
> normalized table.
> The files are placed in on a root drive.
> If someone can help me on this i would be very happy.
> Thnx.

Import Legacy DTS Packages into SQL Server 2005 using SMO

I was hoping someone can point me to a

resource for SMO and importing legacy DTS packages from SQL Server

2000.

We are getting ready to upgrade our

SQL Servers from 2000 to 2005. We have a lot of DTS packages that we plan on

continuing to use in 2005. I found a script I used to export all of the old DTS

packages out of our 2000 servers;

DECLARE @.TARGETDIR varchar(1000)

SET @.TARGETDIR = 'C:\DTSTest\'

SELECT distinct

'DTSRUN.EXE /S '

+ CONVERT(varchar(200), SERVERPROPERTY('servername'))

+ '

/E '

+ '

/N '

+ '"' + name + '"'

+ '

/F '

+ '"' + @.TARGETDIR + name + '.dts"'

+ '

/!X'

FROM msdb.dbo.sysdtspackages P

Now I need to write a script to

import them into 2005.

I have been reading that I should be

using SMO to do my database scripting in 2005. As I have been going through the

libraries, almost everything seems to be geared for importing SSIS packages, and

not legacy DTS packages.

Does anyone know of someone or some

resource that might be able to help me out.

Thank

you,

dfpelican

Our developers here found that there were sufficient problems in upgrading DTS packages that it was worth rebuilding the packages in SSIS directly, taking advantage of the new control flow processes in SSIS.|||

+1

I can second that from my experience.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Migration business is not good. Just buy the new stuff and redo. Actually that is what the software vendor really wants you to do.|||

did you have any luck with your DTS upgrading?

or did you rewrite?, it sounded like you had a lot of them to upgrade

or i guess you would have already re-written instead.

I'm in a similar boat with out flagship salon software product.

Any spockish tips would be appreciated.

Craig Kelly-Soens

http://www.salonsoftwaresystem.com

Import Legacy DTS Packages into SQL Server 2005 using SMO

I was hoping someone can point me to a resource for SMO and importing legacy DTS packages from SQL Server 2000.

We are getting ready to upgrade our SQL Servers from 2000 to 2005. We have a lot of DTS packages that we plan on continuing to use in 2005. I found a script I used to export all of the old DTS packages out of our 2000 servers;

DECLARE @.TARGETDIR varchar(1000)

SET @.TARGETDIR = 'C:\DTSTest\'

SELECT distinct

'DTSRUN.EXE /S '

+ CONVERT(varchar(200), SERVERPROPERTY('servername'))

+ ' /E '

+ ' /N '

+ '"' + name + '"'

+ ' /F '

+ '"' + @.TARGETDIR + name + '.dts"'

+ ' /!X'

FROM msdb.dbo.sysdtspackages P

Now I need to write a script to import them into 2005.

I have been reading that I should be using SMO to do my database scripting in 2005. As I have been going through the libraries, almost everything seems to be geared for importing SSIS packages, and not legacy DTS packages.

Does anyone know of someone or some resource that might be able to help me out.

Thank you,

dfpelican

Our developers here found that there were sufficient problems in upgrading DTS packages that it was worth rebuilding the packages in SSIS directly, taking advantage of the new control flow processes in SSIS.|||

+1

I can second that from my experience.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Migration business is not good. Just buy the new stuff and redo. Actually that is what the software vendor really wants you to do.|||

did you have any luck with your DTS upgrading?

or did you rewrite?, it sounded like you had a lot of them to upgrade

or i guess you would have already re-written instead.

I'm in a similar boat with out flagship salon software product.

Any spockish tips would be appreciated.

Craig Kelly-Soens

http://www.salonsoftwaresystem.com

sql

Import into SQL Server Oracle Database from Export File (SQL*Loader)

I need to import an Oracle Database into SQL Server 2000.
I know this can be done easily using DTS but I do not have access to the
Oracle Database, I only have an Oracle Database Export File.
The Export file has been generated using SQL*Loader.
Oracle Export File is created with a proprietery format that can only be
read by the importer. This is the same problem if you take a SQL backup file
and try to load it into Oracle without access to a SQLServer.
-oj
"K Kelly" <kkelly@.nospam.com> wrote in message
news:eryCZe8$EHA.2608@.TK2MSFTNGP10.phx.gbl...
>I need to import an Oracle Database into SQL Server 2000.
> I know this can be done easily using DTS but I do not have access to the
> Oracle Database, I only have an Oracle Database Export File.
> The Export file has been generated using SQL*Loader.
>
|||On Fri, 21 Jan 2005 14:42:22 -0000, "K Kelly" <kkelly@.nospam.com> wrote:

>I need to import an Oracle Database into SQL Server 2000.
>I know this can be done easily using DTS but I do not have access to the
>Oracle Database, I only have an Oracle Database Export File.
>The Export file has been generated using SQL*Loader.
>
While it may not be strictly legal from a licensing standpoint, there is an approach that may work. First, Oracle
makes copies of their older software available for downloading from their website. Therefore, you could download a
copy of Oracle and install it on a suitable system. Next, import the Oracle database from the export file. Be
warned -- the export file also includes information on directory locations for database files, rollback segments,
and so on. In order for the import to work properly, you need to make sure that you replicate the file system
environment exactly. Oracle will not re-create it on the fly, so you may spend quite a bit of time reading error
logs before you can get it right. In the end, though, it should work correctly.
Once you have the Oracle database up and running, use DTS from SQL Server to cross over the data you need,
remembering to deal with issues such as data type conversions, etc. (pardon me for straying into "I know that!!"
areas). After you get it crossed over, then kill the Oracle installation before the license police catch you.
I once did a similar thing years ago in the opposite direction. I needed to move a DocsOpen SQL 4.0 database into
Oracle 7.2, but the Docs application no longer supported SQL 4, only SQL 6.5 and above. However, Microsoft had a
120-day demonstration version of SQL 6.5 on their website. So I downloaded that, used it to upgrade the SQL 4
database to SQL 6.5, then ran DocsOpen to move it from SQL Server to Oracle 7.2. Worked a treat. Crazily enough, I
have to now move that same database from Oracle 8.1 to SQL Server 2000 in the next couple of weeks.
Good luck.
|||Clever! ;-)
-oj
"Norm Powroz" <npowroz.delete.this.part@.and.this.part.rogers.com > wrote in
message news:1a45v09n8p0j4ud4ep57cdu91vgav6qv2r@.4ax.com...
> On Fri, 21 Jan 2005 14:42:22 -0000, "K Kelly" <kkelly@.nospam.com> wrote:
>
> While it may not be strictly legal from a licensing standpoint, there is
> an approach that may work. First, Oracle
> makes copies of their older software available for downloading from their
> website. Therefore, you could download a
> copy of Oracle and install it on a suitable system. Next, import the
> Oracle database from the export file. Be
> warned -- the export file also includes information on directory locations
> for database files, rollback segments,
> and so on. In order for the import to work properly, you need to make sure
> that you replicate the file system
> environment exactly. Oracle will not re-create it on the fly, so you may
> spend quite a bit of time reading error
> logs before you can get it right. In the end, though, it should work
> correctly.
> Once you have the Oracle database up and running, use DTS from SQL Server
> to cross over the data you need,
> remembering to deal with issues such as data type conversions, etc.
> (pardon me for straying into "I know that!!"
> areas). After you get it crossed over, then kill the Oracle installation
> before the license police catch you.
> I once did a similar thing years ago in the opposite direction. I needed
> to move a DocsOpen SQL 4.0 database into
> Oracle 7.2, but the Docs application no longer supported SQL 4, only SQL
> 6.5 and above. However, Microsoft had a
> 120-day demonstration version of SQL 6.5 on their website. So I downloaded
> that, used it to upgrade the SQL 4
> database to SQL 6.5, then ran DocsOpen to move it from SQL Server to
> Oracle 7.2. Worked a treat. Crazily enough, I
> have to now move that same database from Oracle 8.1 to SQL Server 2000 in
> the next couple of weeks.
> Good luck.
>
sql

import html file error

Im trying to import an html file using dts. When I click on the file when
using dts designer, I get the message:
External table is not in the expected format.
Any ideas?
SQL2K SP3
TIA, ChrisR
In message <udOcQh54EHA.936@.TK2MSFTNGP12.phx.gbl>, ChrisR
<bla@.noemail.com> writes
>Im trying to import an html file using dts. When I click on the file when
>using dts designer, I get the message:
>External table is not in the expected format.
>Any ideas?
>--
>SQL2K SP3
>TIA, ChrisR
>
Does the error not say it all? The HTML connection cannot
parse/recognise a valid html table in that file. No table, no import.
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com
PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

import html file error

Im trying to import an html file using dts. When I click on the file when
using dts designer, I get the message:
External table is not in the expected format.
Any ideas?
--
SQL2K SP3
TIA, ChrisRIn message <udOcQh54EHA.936@.TK2MSFTNGP12.phx.gbl>, ChrisR
<bla@.noemail.com> writes
>Im trying to import an html file using dts. When I click on the file when
>using dts designer, I get the message:
>External table is not in the expected format.
>Any ideas?
>--
>SQL2K SP3
>TIA, ChrisR
>
Does the error not say it all? The HTML connection cannot
parse/recognise a valid html table in that file. No table, no import.
--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com
PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Import HTML and Tab delimited into SQL

Hi,
I need to import files from either HTML or Tab delimited format into SQL
Server. I imagine this would involve DTS and/or a DSN, but I'm not sure of
the details. I'm able to standardize the filename and directory using code,
so that's not a problem. Any ideas?
HTML? I don't think so. Tab delimited, you bet.
Yes, you would set up a DTS packages using the
Import / Export wizard.
If you want to script this and run it, check out this
VBScript code sample:
http://www.eggheadcafe.com/articles/20030923.asp
2005 Microsoft MVP C#
Robbe Morris
http://www.masterado.net
http://www.mastervb.net
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:C603F79A-3654-4785-B144-84909C69473E@.microsoft.com...
> Hi,
> I need to import files from either HTML or Tab delimited format into SQL
> Server. I imagine this would involve DTS and/or a DSN, but I'm not sure of
> the details. I'm able to standardize the filename and directory using
> code,
> so that's not a problem. Any ideas?

Import From TAB File Error

I am trying to import data into a table in my database using DTS.
The table (SYS_DD_SIC_Codes) has the following structure:
[SIC_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[SIC_Code] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SIC_80] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SIC_92] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
The import procedure works, but then fails at the very last line in the TAB
file, giving an error that it cannot insert a NULL value into [SIC_ID] for
the last line in the TAB file.
This is correct as the fields are all NOT NULL, but why is it trying to
insert a NULL value when none exist in the TAB file to insert?
Do you have an extra line in your text file?

>--Original Message--
>I am trying to import data into a table in my database
using DTS.
>The table (SYS_DD_SIC_Codes) has the following structure:
> [SIC_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
> [SIC_Code] [varchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [SIC_80] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [SIC_92] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL
>The import procedure works, but then fails at the very
last line in the TAB
>file, giving an error that it cannot insert a NULL value
into [SIC_ID] for
>the last line in the TAB file.
>This is correct as the fields are all NOT NULL, but why
is it trying to
>insert a NULL value when none exist in the TAB file to
insert?
>
>.
>
|||There is probably a blank line at the end of the file. You
could try adding a where clause along the lines of: where
sic_id is not null.
-Sue
On Wed, 7 Apr 2004 12:26:10 +0100, "Keith" <@..> wrote:

>I am trying to import data into a table in my database using DTS.
>The table (SYS_DD_SIC_Codes) has the following structure:
> [SIC_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
> [SIC_Code] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [SIC_80] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [SIC_92] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
>The import procedure works, but then fails at the very last line in the TAB
>file, giving an error that it cannot insert a NULL value into [SIC_ID] for
>the last line in the TAB file.
>This is correct as the fields are all NOT NULL, but why is it trying to
>insert a NULL value when none exist in the TAB file to insert?
>
|||I have checked and there is not an extra line at the end of the file!
"keene" <anonymous@.discussions.microsoft.com> wrote in message
news:1978201c41ca2$dd0a3590$a401280a@.phx.gbl...
> Do you have an extra line in your text file?
> using DTS.
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> SQL_Latin1_General_CP1_CI_AS NOT NULL
> last line in the TAB
> into [SIC_ID] for
> is it trying to
> insert?
|||I have checked and there is not an extra line at the end of the file!
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:hsv7705e7u3eg7braj1hhqv5qsk5de57j4@.4ax.com...
> There is probably a blank line at the end of the file. You
> could try adding a where clause along the lines of: where
> sic_id is not null.
> -Sue
> On Wed, 7 Apr 2004 12:26:10 +0100, "Keith" <@..> wrote:
,
TAB
for
>
|||Check the last line, maybe you're missing a tab (so the line is being
imported as 3 columns rather than four)?
"Keith" <@..> wrote in message news:uTU4BMJHEHA.3288@.TK2MSFTNGP12.phx.gbl...
> I am trying to import data into a table in my database using DTS.
> The table (SYS_DD_SIC_Codes) has the following structure:
> [SIC_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
> [SIC_Code] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
> [SIC_80] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [SIC_92] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> The import procedure works, but then fails at the very last line in the
TAB
> file, giving an error that it cannot insert a NULL value into [SIC_ID] for
> the last line in the TAB file.
> This is correct as the fields are all NOT NULL, but why is it trying to
> insert a NULL value when none exist in the TAB file to insert?
>
|||I have checked this, but it is correct that only 3 columns out of the 4 are
being imported. The first column is an IDENTITY column which autonumbers
itself so does not require data importing into it. I have made sure that
DTS is aware of the IDENTITY column.
I am so confused by this.
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:O5Zv6sKHEHA.2576@.TK2MSFTNGP09.phx.gbl...
> Check the last line, maybe you're missing a tab (so the line is being
> imported as 3 columns rather than four)?
>
> "Keith" <@..> wrote in message
news:uTU4BMJHEHA.3288@.TK2MSFTNGP12.phx.gbl...
NULL
> ,
> TAB
for
>

Import From TAB File Error

I am trying to import data into a table in my database using DTS.
The table (SYS_DD_SIC_Codes) has the following structure:
[SIC_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[SIC_Code] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[SIC_80] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NUL
L ,
[SIC_92] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NUL
L
The import procedure works, but then fails at the very last line in the TAB
file, giving an error that it cannot insert a NULL value into [SIC_ID] f
or
the last line in the TAB file.
This is correct as the fields are all NOT NULL, but why is it trying to
insert a NULL value when none exist in the TAB file to insert?Do you have an extra line in your text file?

>--Original Message--
>I am trying to import data into a table in my database
using DTS.
>The table (SYS_DD_SIC_Codes) has the following structure:
> [SIC_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
> [SIC_Code] [varchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [SIC_80] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [SIC_92] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL
>The import procedure works, but then fails at the very
last line in the TAB
>file, giving an error that it cannot insert a NULL value
into [SIC_ID] for
>the last line in the TAB file.
>This is correct as the fields are all NOT NULL, but why
is it trying to
>insert a NULL value when none exist in the TAB file to
insert?
>
>.
>|||There is probably a blank line at the end of the file. You
could try adding a where clause along the lines of: where
sic_id is not null.
-Sue
On Wed, 7 Apr 2004 12:26:10 +0100, "Keith" <@..> wrote:

>I am trying to import data into a table in my database using DTS.
>The table (SYS_DD_SIC_Codes) has the following structure:
> [SIC_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
> [SIC_Code] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NO
T NULL ,
> [SIC_80] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT N
ULL ,
> [SIC_92] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT N
ULL
>The import procedure works, but then fails at the very last line in the TAB
>file, giving an error that it cannot insert a NULL value into [SIC_ID]
for
>the last line in the TAB file.
>This is correct as the fields are all NOT NULL, but why is it trying to
>insert a NULL value when none exist in the TAB file to insert?
>|||I have checked and there is not an extra line at the end of the file!
"keene" <anonymous@.discussions.microsoft.com> wrote in message
news:1978201c41ca2$dd0a3590$a401280a@.phx
.gbl...
> Do you have an extra line in your text file?
>
> using DTS.
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> SQL_Latin1_General_CP1_CI_AS NOT NULL
> last line in the TAB
> into [SIC_ID] for
> is it trying to
> insert?|||I have checked and there is not an extra line at the end of the file!
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:hsv7705e7u3eg7braj1hhqv5qsk5de57j4@.
4ax.com...
> There is probably a blank line at the end of the file. You
> could try adding a where clause along the lines of: where
> sic_id is not null.
> -Sue
> On Wed, 7 Apr 2004 12:26:10 +0100, "Keith" <@..> wrote:
>
,
TAB
for
>|||Check the last line, maybe you're missing a tab (so the line is being
imported as 3 columns rather than four)?
"Keith" <@..> wrote in message news:uTU4BMJHEHA.3288@.TK2MSFTNGP12.phx.gbl...
> I am trying to import data into a table in my database using DTS.
> The table (SYS_DD_SIC_Codes) has the following structure:
> [SIC_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
> [SIC_Code] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL[
/color]
,
> [SIC_80] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
> [SIC_92] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
> The import procedure works, but then fails at the very last line in the
TAB
> file, giving an error that it cannot insert a NULL value into [SIC_ID]
for
> the last line in the TAB file.
> This is correct as the fields are all NOT NULL, but why is it trying to
> insert a NULL value when none exist in the TAB file to insert?
>|||I have checked this, but it is correct that only 3 columns out of the 4 are
being imported. The first column is an IDENTITY column which autonumbers
itself so does not require data importing into it. I have made sure that
DTS is aware of the IDENTITY column.
I am so confused by this.
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:O5Zv6sKHEHA.2576@.TK2MSFTNGP09.phx.gbl...
> Check the last line, maybe you're missing a tab (so the line is being
> imported as 3 columns rather than four)?
>
> "Keith" <@..> wrote in message
news:uTU4BMJHEHA.3288@.TK2MSFTNGP12.phx.gbl...
NULL
> ,
> TAB
for
>

Import From TAB File Error

I am trying to import data into a table in my database using DTS.
The table (SYS_DD_SIC_Codes) has the following structure:
[SIC_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[SIC_Code] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SIC_80] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SIC_92] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
The import procedure works, but then fails at the very last line in the TAB
file, giving an error that it cannot insert a NULL value into [SIC_ID] for
the last line in the TAB file.
This is correct as the fields are all NOT NULL, but why is it trying to
insert a NULL value when none exist in the TAB file to insert?Do you have an extra line in your text file?
>--Original Message--
>I am trying to import data into a table in my database
using DTS.
>The table (SYS_DD_SIC_Codes) has the following structure:
> [SIC_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
> [SIC_Code] [varchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [SIC_80] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [SIC_92] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL
>The import procedure works, but then fails at the very
last line in the TAB
>file, giving an error that it cannot insert a NULL value
into [SIC_ID] for
>the last line in the TAB file.
>This is correct as the fields are all NOT NULL, but why
is it trying to
>insert a NULL value when none exist in the TAB file to
insert?
>
>.
>|||There is probably a blank line at the end of the file. You
could try adding a where clause along the lines of: where
sic_id is not null.
-Sue
On Wed, 7 Apr 2004 12:26:10 +0100, "Keith" <@..> wrote:
>I am trying to import data into a table in my database using DTS.
>The table (SYS_DD_SIC_Codes) has the following structure:
> [SIC_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
> [SIC_Code] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [SIC_80] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [SIC_92] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
>The import procedure works, but then fails at the very last line in the TAB
>file, giving an error that it cannot insert a NULL value into [SIC_ID] for
>the last line in the TAB file.
>This is correct as the fields are all NOT NULL, but why is it trying to
>insert a NULL value when none exist in the TAB file to insert?
>|||I have checked and there is not an extra line at the end of the file!
"keene" <anonymous@.discussions.microsoft.com> wrote in message
news:1978201c41ca2$dd0a3590$a401280a@.phx.gbl...
> Do you have an extra line in your text file?
> >--Original Message--
> >I am trying to import data into a table in my database
> using DTS.
> >
> >The table (SYS_DD_SIC_Codes) has the following structure:
> >
> > [SIC_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
> > [SIC_Code] [varchar] (255) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> > [SIC_80] [varchar] (50) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> > [SIC_92] [varchar] (50) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL
> >
> >The import procedure works, but then fails at the very
> last line in the TAB
> >file, giving an error that it cannot insert a NULL value
> into [SIC_ID] for
> >the last line in the TAB file.
> >
> >This is correct as the fields are all NOT NULL, but why
> is it trying to
> >insert a NULL value when none exist in the TAB file to
> insert?
> >
> >
> >.
> >|||I have checked and there is not an extra line at the end of the file!
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:hsv7705e7u3eg7braj1hhqv5qsk5de57j4@.4ax.com...
> There is probably a blank line at the end of the file. You
> could try adding a where clause along the lines of: where
> sic_id is not null.
> -Sue
> On Wed, 7 Apr 2004 12:26:10 +0100, "Keith" <@..> wrote:
> >I am trying to import data into a table in my database using DTS.
> >
> >The table (SYS_DD_SIC_Codes) has the following structure:
> >
> > [SIC_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
> > [SIC_Code] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
> > [SIC_80] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> > [SIC_92] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> >
> >The import procedure works, but then fails at the very last line in the
TAB
> >file, giving an error that it cannot insert a NULL value into [SIC_ID]
for
> >the last line in the TAB file.
> >
> >This is correct as the fields are all NOT NULL, but why is it trying to
> >insert a NULL value when none exist in the TAB file to insert?
> >
>|||Check the last line, maybe you're missing a tab (so the line is being
imported as 3 columns rather than four)?
"Keith" <@..> wrote in message news:uTU4BMJHEHA.3288@.TK2MSFTNGP12.phx.gbl...
> I am trying to import data into a table in my database using DTS.
> The table (SYS_DD_SIC_Codes) has the following structure:
> [SIC_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
> [SIC_Code] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
> [SIC_80] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [SIC_92] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> The import procedure works, but then fails at the very last line in the
TAB
> file, giving an error that it cannot insert a NULL value into [SIC_ID] for
> the last line in the TAB file.
> This is correct as the fields are all NOT NULL, but why is it trying to
> insert a NULL value when none exist in the TAB file to insert?
>|||I have checked this, but it is correct that only 3 columns out of the 4 are
being imported. The first column is an IDENTITY column which autonumbers
itself so does not require data importing into it. I have made sure that
DTS is aware of the IDENTITY column.
I am so confused by this.
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:O5Zv6sKHEHA.2576@.TK2MSFTNGP09.phx.gbl...
> Check the last line, maybe you're missing a tab (so the line is being
> imported as 3 columns rather than four)?
>
> "Keith" <@..> wrote in message
news:uTU4BMJHEHA.3288@.TK2MSFTNGP12.phx.gbl...
> > I am trying to import data into a table in my database using DTS.
> >
> > The table (SYS_DD_SIC_Codes) has the following structure:
> >
> > [SIC_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
> > [SIC_Code] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
> ,
> > [SIC_80] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> > [SIC_92] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> >
> > The import procedure works, but then fails at the very last line in the
> TAB
> > file, giving an error that it cannot insert a NULL value into [SIC_ID]
for
> > the last line in the TAB file.
> >
> > This is correct as the fields are all NOT NULL, but why is it trying to
> > insert a NULL value when none exist in the TAB file to insert?
> >
> >
>

Import from file

I am looking for a method to pull the data from several
files in the same directory into a SQL db. I can get DTS
to work for a single file but not multiple files. If
someone has an example, great, if not, where do I start?
Am I in the right place with DTS?
Thanks!
JamesSee if this helps: http://www.sqldts.com/default.aspx?6,103,246,0,1
--
Andrew J. Kelly
SQL Server MVP
"James Wood" <jgwood@.dot.state.az.us> wrote in message
news:09eb01c347f2$02d0c540$a001280a@.phx.gbl...
> I am looking for a method to pull the data from several
> files in the same directory into a SQL db. I can get DTS
> to work for a single file but not multiple files. If
> someone has an example, great, if not, where do I start?
> Am I in the right place with DTS?
> Thanks!
> James|||That is what I was looking for. I will give it a shot!
Thanks!
James
>--Original Message--
>See if this helps: http://www.sqldts.com/default.aspx?
6,103,246,0,1
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"James Wood" <jgwood@.dot.state.az.us> wrote in message
>news:09eb01c347f2$02d0c540$a001280a@.phx.gbl...
>> I am looking for a method to pull the data from several
>> files in the same directory into a SQL db. I can get
DTS
>> to work for a single file but not multiple files. If
>> someone has an example, great, if not, where do I
start?
>> Am I in the right place with DTS?
>> Thanks!
>> James
>
>.
>

Import from adaptive server anywhere 9

Hi,

I'm trying to import data into sql 2005 from sybase sql anywhere 9
(9.0.2.3320) using DTS (using the asademo db). I can select the tables I
want to import but during the actual import on Setting Source Connection I
always get an OLE DB Error :

Could not connect source component.

Error 0xc0202009: Source - contact[1]: An OLE DB error has occured. Error
code : 0x80040E21

Error 0xc020204a: Source - contact[1]: Unabtle to retrieve column
information from the data source.
Make sure your target table in the database is available.

Does anybody have an idea of what is going wrong here ?
Is there another way of accessing sybase that will work and where I can select all tables I need ?

thanx

Did you install the actual OLE DB Provider on your box and is it configured correctly?

Does your login has the appropriate permissions to access the database?

Do you have only asademo.db running? Did you specify the instance name (I think -n in the startup parameter is the right one for naming the instance. You can have several databases running under one instance name in ASA)

If this is not a regular job, you can quickly transport the data by defining a linked server for this database instance and doing a insert into ... select ... from linked.server.databaase.owner.table

Regards

Norbert

|||

Did you install the actual OLE DB Provider on your box and is it configured correctly?
I have a complete installation of Sybase ASA 9.02 and it is configured correctly.

Does your login has the appropriate permissions to access the database?
yep.

Do you have only asademo.db running? Did you specify the instance name (I think -n in the startup parameter is the right one for naming the instance. You can have several databases running under one instance name in ASA)
yep.

If this is not a regular job, you can quickly transport the data by defining a linked server for this database instance and doing a insert into ... select ... from linked.server.databaase.owner.table
What do you mean ? That I connect to sybase from SQL Server Management Studio ?

Regards

Norbert

|||You can define a linked server in Manangement Studio for the ASA database. Then you can start the query insert into ... select ... from the query window. This should create a table and should move the data via the OLE DB Providersql

Import from a file does not load data

I am using sql server 2000 on my Desktop (Windows 2000). I exported a
table to a .txt file, When I imported table using DTS wizard. I do
not get any errors. Yet, no data is loaded in the table. How can I
troubleshoot the problem. Which logs I can look into to find the
problem.
I am new to SQL SERVER, I am an Oracle DBA trying to learn SQL SERVER
2000.Hi
I assume you are using the import wizard in which case it should have shown
a preview of the data. If you chose the run immediately option it should have
shown the number if records inserted once the copy data step has competed.
How many rows are you expecting from the file?
Are you loading this into a new table?
If the import summary showed no rows but you know the file has data then you
may want to change the deliminators you have specified!
John
"zigzagdna@.yahoo.com" wrote:
> I am using sql server 2000 on my Desktop (Windows 2000). I exported a
> table to a .txt file, When I imported table using DTS wizard. I do
> not get any errors. Yet, no data is loaded in the table. How can I
> troubleshoot the problem. Which logs I can look into to find the
> problem.
> I am new to SQL SERVER, I am an Oracle DBA trying to learn SQL SERVER
> 2000.
>|||Import summary showed 2 rows were loaded, yet nothing was loaded.
When I did export from the wizard, it put 2 rows in the file, no create
table staement. My table employee alerady exists in the table, it has no
rows before import. I was hpoing it will have 2 rows after import, but it did
not. Is there anyway to turn on some tracing to see where the import had
problems.
"John Bell" wrote:
> Hi
> I assume you are using the import wizard in which case it should have shown
> a preview of the data. If you chose the run immediately option it should have
> shown the number if records inserted once the copy data step has competed.
> How many rows are you expecting from the file?
> Are you loading this into a new table?
> If the import summary showed no rows but you know the file has data then you
> may want to change the deliminators you have specified!
> John
>
> "zigzagdna@.yahoo.com" wrote:
> > I am using sql server 2000 on my Desktop (Windows 2000). I exported a
> > table to a .txt file, When I imported table using DTS wizard. I do
> > not get any errors. Yet, no data is loaded in the table. How can I
> > troubleshoot the problem. Which logs I can look into to find the
> > problem.
> >
> > I am new to SQL SERVER, I am an Oracle DBA trying to learn SQL SERVER
> > 2000.
> >
> >|||Hi
When you export using the Export wizard it will only create a data file, you
can script the table creation script through Enterprise Manager or Query
Analyser.
Have you tables under a different schema with the same name?
If you create a DTS package from the Import/Export Wizard you can then
modify the package and use the DTS to log information and handle errors.
John
"Prem Mehrotra" wrote:
> Import summary showed 2 rows were loaded, yet nothing was loaded.
> When I did export from the wizard, it put 2 rows in the file, no create
> table staement. My table employee alerady exists in the table, it has no
> rows before import. I was hpoing it will have 2 rows after import, but it did
> not. Is there anyway to turn on some tracing to see where the import had
> problems.
> "John Bell" wrote:
> > Hi
> >
> > I assume you are using the import wizard in which case it should have shown
> > a preview of the data. If you chose the run immediately option it should have
> > shown the number if records inserted once the copy data step has competed.
> > How many rows are you expecting from the file?
> > Are you loading this into a new table?
> > If the import summary showed no rows but you know the file has data then you
> > may want to change the deliminators you have specified!
> >
> > John
> >
> >
> >
> > "zigzagdna@.yahoo.com" wrote:
> >
> > > I am using sql server 2000 on my Desktop (Windows 2000). I exported a
> > > table to a .txt file, When I imported table using DTS wizard. I do
> > > not get any errors. Yet, no data is loaded in the table. How can I
> > > troubleshoot the problem. Which logs I can look into to find the
> > > problem.
> > >
> > > I am new to SQL SERVER, I am an Oracle DBA trying to learn SQL SERVER
> > > 2000.
> > >
> > >|||I finally figured the problem. Name of the export file has to match with
table being exported/imported. So when importing, it created a new table and
was putting rows
there. Also while in Enterpirse Manager, I did not see these tables
until today.
It seems to see new tables screated by import, one must disconnect from
database and reconnect to it, that's why I had no idea what was going on.
Thanks a lot for all your help.
"John Bell" wrote:
> Hi
> When you export using the Export wizard it will only create a data file, you
> can script the table creation script through Enterprise Manager or Query
> Analyser.
> Have you tables under a different schema with the same name?
> If you create a DTS package from the Import/Export Wizard you can then
> modify the package and use the DTS to log information and handle errors.
> John
> "Prem Mehrotra" wrote:
> > Import summary showed 2 rows were loaded, yet nothing was loaded.
> > When I did export from the wizard, it put 2 rows in the file, no create
> > table staement. My table employee alerady exists in the table, it has no
> > rows before import. I was hpoing it will have 2 rows after import, but it did
> > not. Is there anyway to turn on some tracing to see where the import had
> > problems.
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > I assume you are using the import wizard in which case it should have shown
> > > a preview of the data. If you chose the run immediately option it should have
> > > shown the number if records inserted once the copy data step has competed.
> > > How many rows are you expecting from the file?
> > > Are you loading this into a new table?
> > > If the import summary showed no rows but you know the file has data then you
> > > may want to change the deliminators you have specified!
> > >
> > > John
> > >
> > >
> > >
> > > "zigzagdna@.yahoo.com" wrote:
> > >
> > > > I am using sql server 2000 on my Desktop (Windows 2000). I exported a
> > > > table to a .txt file, When I imported table using DTS wizard. I do
> > > > not get any errors. Yet, no data is loaded in the table. How can I
> > > > troubleshoot the problem. Which logs I can look into to find the
> > > > problem.
> > > >
> > > > I am new to SQL SERVER, I am an Oracle DBA trying to learn SQL SERVER
> > > > 2000.
> > > >
> > > >|||Hi
The wizard will default the name of the table, but you do have the option to
change it.
John
"Prem Mehrotra" wrote:
> I finally figured the problem. Name of the export file has to match with
> table being exported/imported. So when importing, it created a new table and
> was putting rows
> there. Also while in Enterpirse Manager, I did not see these tables
> until today.
> It seems to see new tables screated by import, one must disconnect from
> database and reconnect to it, that's why I had no idea what was going on.
> Thanks a lot for all your help.
> "John Bell" wrote:
> > Hi
> >
> > When you export using the Export wizard it will only create a data file, you
> > can script the table creation script through Enterprise Manager or Query
> > Analyser.
> >
> > Have you tables under a different schema with the same name?
> >
> > If you create a DTS package from the Import/Export Wizard you can then
> > modify the package and use the DTS to log information and handle errors.
> >
> > John
> > "Prem Mehrotra" wrote:
> >
> > > Import summary showed 2 rows were loaded, yet nothing was loaded.
> > > When I did export from the wizard, it put 2 rows in the file, no create
> > > table staement. My table employee alerady exists in the table, it has no
> > > rows before import. I was hpoing it will have 2 rows after import, but it did
> > > not. Is there anyway to turn on some tracing to see where the import had
> > > problems.
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi
> > > >
> > > > I assume you are using the import wizard in which case it should have shown
> > > > a preview of the data. If you chose the run immediately option it should have
> > > > shown the number if records inserted once the copy data step has competed.
> > > > How many rows are you expecting from the file?
> > > > Are you loading this into a new table?
> > > > If the import summary showed no rows but you know the file has data then you
> > > > may want to change the deliminators you have specified!
> > > >
> > > > John
> > > >
> > > >
> > > >
> > > > "zigzagdna@.yahoo.com" wrote:
> > > >
> > > > > I am using sql server 2000 on my Desktop (Windows 2000). I exported a
> > > > > table to a .txt file, When I imported table using DTS wizard. I do
> > > > > not get any errors. Yet, no data is loaded in the table. How can I
> > > > > troubleshoot the problem. Which logs I can look into to find the
> > > > > problem.
> > > > >
> > > > > I am new to SQL SERVER, I am an Oracle DBA trying to learn SQL SERVER
> > > > > 2000.
> > > > >
> > > > >|||THe import wizard allows you to specify the name of the table being
created, or to specify an existing table. For an existing table you
can specify if the table is to be truncated first, or the new data
appended to the old. When you get to the window in the wizard with
three columns - Source, Destination, Transform - you can click on
Desination to make changes to the name or select an existing table
from the list. Click on Transform to specify append or replace, and
other options such as controlling the names and data types of the
columns in the table being created.
There is more to the import wizard, and I suggest it is worth your
time playing with it for a while and exploring the options so you are
familiar with what is there. The most important feature not mentioned
so far is that you can save it as a DTS package and then edit it in
the DTS interface - good for minor tweaking.
Roy Harvey
Beacon Falls, CT
On Mon, 20 Aug 2007 18:48:02 -0700, Prem Mehrotra
<PremMehrotra@.discussions.microsoft.com> wrote:
>I finally figured the problem. Name of the export file has to match with
>table being exported/imported. So when importing, it created a new table and
>was putting rows
>there. Also while in Enterpirse Manager, I did not see these tables
>until today.
>It seems to see new tables screated by import, one must disconnect from
>database and reconnect to it, that's why I had no idea what was going on.
>Thanks a lot for all your help.
>"John Bell" wrote:
>> Hi
>> When you export using the Export wizard it will only create a data file, you
>> can script the table creation script through Enterprise Manager or Query
>> Analyser.
>> Have you tables under a different schema with the same name?
>> If you create a DTS package from the Import/Export Wizard you can then
>> modify the package and use the DTS to log information and handle errors.
>> John
>> "Prem Mehrotra" wrote:
>> > Import summary showed 2 rows were loaded, yet nothing was loaded.
>> > When I did export from the wizard, it put 2 rows in the file, no create
>> > table staement. My table employee alerady exists in the table, it has no
>> > rows before import. I was hpoing it will have 2 rows after import, but it did
>> > not. Is there anyway to turn on some tracing to see where the import had
>> > problems.
>> >
>> > "John Bell" wrote:
>> >
>> > > Hi
>> > >
>> > > I assume you are using the import wizard in which case it should have shown
>> > > a preview of the data. If you chose the run immediately option it should have
>> > > shown the number if records inserted once the copy data step has competed.
>> > > How many rows are you expecting from the file?
>> > > Are you loading this into a new table?
>> > > If the import summary showed no rows but you know the file has data then you
>> > > may want to change the deliminators you have specified!
>> > >
>> > > John
>> > >
>> > >
>> > >
>> > > "zigzagdna@.yahoo.com" wrote:
>> > >
>> > > > I am using sql server 2000 on my Desktop (Windows 2000). I exported a
>> > > > table to a .txt file, When I imported table using DTS wizard. I do
>> > > > not get any errors. Yet, no data is loaded in the table. How can I
>> > > > troubleshoot the problem. Which logs I can look into to find the
>> > > > problem.
>> > > >
>> > > > I am new to SQL SERVER, I am an Oracle DBA trying to learn SQL SERVER
>> > > > 2000.
>> > > >
>> > > >|||I looked at DTS Wizard. Is there any way to export all the
tables/views/packages etc using one "command". I find DTS is table based, so
it only exports schema of a table and its data. How ablout views? I want to
export all the tables at the same time and then selectively import. Oracle
lets you do that. I am sure sql server also allows that, but how?
"Roy Harvey" wrote:
> THe import wizard allows you to specify the name of the table being
> created, or to specify an existing table. For an existing table you
> can specify if the table is to be truncated first, or the new data
> appended to the old. When you get to the window in the wizard with
> three columns - Source, Destination, Transform - you can click on
> Desination to make changes to the name or select an existing table
> from the list. Click on Transform to specify append or replace, and
> other options such as controlling the names and data types of the
> columns in the table being created.
> There is more to the import wizard, and I suggest it is worth your
> time playing with it for a while and exploring the options so you are
> familiar with what is there. The most important feature not mentioned
> so far is that you can save it as a DTS package and then edit it in
> the DTS interface - good for minor tweaking.
> Roy Harvey
> Beacon Falls, CT
> On Mon, 20 Aug 2007 18:48:02 -0700, Prem Mehrotra
> <PremMehrotra@.discussions.microsoft.com> wrote:
> >I finally figured the problem. Name of the export file has to match with
> >table being exported/imported. So when importing, it created a new table and
> >was putting rows
> >there. Also while in Enterpirse Manager, I did not see these tables
> >until today.
> >
> >It seems to see new tables screated by import, one must disconnect from
> >database and reconnect to it, that's why I had no idea what was going on.
> >
> >Thanks a lot for all your help.
> >
> >"John Bell" wrote:
> >
> >> Hi
> >>
> >> When you export using the Export wizard it will only create a data file, you
> >> can script the table creation script through Enterprise Manager or Query
> >> Analyser.
> >>
> >> Have you tables under a different schema with the same name?
> >>
> >> If you create a DTS package from the Import/Export Wizard you can then
> >> modify the package and use the DTS to log information and handle errors.
> >>
> >> John
> >> "Prem Mehrotra" wrote:
> >>
> >> > Import summary showed 2 rows were loaded, yet nothing was loaded.
> >> > When I did export from the wizard, it put 2 rows in the file, no create
> >> > table staement. My table employee alerady exists in the table, it has no
> >> > rows before import. I was hpoing it will have 2 rows after import, but it did
> >> > not. Is there anyway to turn on some tracing to see where the import had
> >> > problems.
> >> >
> >> > "John Bell" wrote:
> >> >
> >> > > Hi
> >> > >
> >> > > I assume you are using the import wizard in which case it should have shown
> >> > > a preview of the data. If you chose the run immediately option it should have
> >> > > shown the number if records inserted once the copy data step has competed.
> >> > > How many rows are you expecting from the file?
> >> > > Are you loading this into a new table?
> >> > > If the import summary showed no rows but you know the file has data then you
> >> > > may want to change the deliminators you have specified!
> >> > >
> >> > > John
> >> > >
> >> > >
> >> > >
> >> > > "zigzagdna@.yahoo.com" wrote:
> >> > >
> >> > > > I am using sql server 2000 on my Desktop (Windows 2000). I exported a
> >> > > > table to a .txt file, When I imported table using DTS wizard. I do
> >> > > > not get any errors. Yet, no data is loaded in the table. How can I
> >> > > > troubleshoot the problem. Which logs I can look into to find the
> >> > > > problem.
> >> > > >
> >> > > > I am new to SQL SERVER, I am an Oracle DBA trying to learn SQL SERVER
> >> > > > 2000.
> >> > > >
> >> > > >
>|||You need to use the "Transfer Objects and Data between SQL Server Databases" DTS task. This exports
the schemas information to a set of text files, exports the data to files, created the objects at
the other end and then imports the data.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Prem Mehrotra" <PremMehrotra@.discussions.microsoft.com> wrote in message
news:C1314416-D22B-4C62-8AEE-49935AED8A01@.microsoft.com...
>I looked at DTS Wizard. Is there any way to export all the
> tables/views/packages etc using one "command". I find DTS is table based, so
> it only exports schema of a table and its data. How ablout views? I want to
> export all the tables at the same time and then selectively import. Oracle
> lets you do that. I am sure sql server also allows that, but how?
> "Roy Harvey" wrote:
>> THe import wizard allows you to specify the name of the table being
>> created, or to specify an existing table. For an existing table you
>> can specify if the table is to be truncated first, or the new data
>> appended to the old. When you get to the window in the wizard with
>> three columns - Source, Destination, Transform - you can click on
>> Desination to make changes to the name or select an existing table
>> from the list. Click on Transform to specify append or replace, and
>> other options such as controlling the names and data types of the
>> columns in the table being created.
>> There is more to the import wizard, and I suggest it is worth your
>> time playing with it for a while and exploring the options so you are
>> familiar with what is there. The most important feature not mentioned
>> so far is that you can save it as a DTS package and then edit it in
>> the DTS interface - good for minor tweaking.
>> Roy Harvey
>> Beacon Falls, CT
>> On Mon, 20 Aug 2007 18:48:02 -0700, Prem Mehrotra
>> <PremMehrotra@.discussions.microsoft.com> wrote:
>> >I finally figured the problem. Name of the export file has to match with
>> >table being exported/imported. So when importing, it created a new table and
>> >was putting rows
>> >there. Also while in Enterpirse Manager, I did not see these tables
>> >until today.
>> >
>> >It seems to see new tables screated by import, one must disconnect from
>> >database and reconnect to it, that's why I had no idea what was going on.
>> >
>> >Thanks a lot for all your help.
>> >
>> >"John Bell" wrote:
>> >
>> >> Hi
>> >>
>> >> When you export using the Export wizard it will only create a data file, you
>> >> can script the table creation script through Enterprise Manager or Query
>> >> Analyser.
>> >>
>> >> Have you tables under a different schema with the same name?
>> >>
>> >> If you create a DTS package from the Import/Export Wizard you can then
>> >> modify the package and use the DTS to log information and handle errors.
>> >>
>> >> John
>> >> "Prem Mehrotra" wrote:
>> >>
>> >> > Import summary showed 2 rows were loaded, yet nothing was loaded.
>> >> > When I did export from the wizard, it put 2 rows in the file, no create
>> >> > table staement. My table employee alerady exists in the table, it has no
>> >> > rows before import. I was hpoing it will have 2 rows after import, but it did
>> >> > not. Is there anyway to turn on some tracing to see where the import had
>> >> > problems.
>> >> >
>> >> > "John Bell" wrote:
>> >> >
>> >> > > Hi
>> >> > >
>> >> > > I assume you are using the import wizard in which case it should have shown
>> >> > > a preview of the data. If you chose the run immediately option it should have
>> >> > > shown the number if records inserted once the copy data step has competed.
>> >> > > How many rows are you expecting from the file?
>> >> > > Are you loading this into a new table?
>> >> > > If the import summary showed no rows but you know the file has data then you
>> >> > > may want to change the deliminators you have specified!
>> >> > >
>> >> > > John
>> >> > >
>> >> > >
>> >> > >
>> >> > > "zigzagdna@.yahoo.com" wrote:
>> >> > >
>> >> > > > I am using sql server 2000 on my Desktop (Windows 2000). I exported a
>> >> > > > table to a .txt file, When I imported table using DTS wizard. I do
>> >> > > > not get any errors. Yet, no data is loaded in the table. How can I
>> >> > > > troubleshoot the problem. Which logs I can look into to find the
>> >> > > > problem.
>> >> > > >
>> >> > > > I am new to SQL SERVER, I am an Oracle DBA trying to learn SQL SERVER
>> >> > > > 2000.
>> >> > > >
>> >> > > >

Import Files Do Not Exist

I have a job that calls about 5 DTS's. Each DTS imports a text file. The problem is that some of the files may not be there every day. How do I exit the DTS without an error if the file does not exist, but I want my job to keep running...how about using an activex task to check for file and if file not found then Main = DTSTaskExecResult_Success, each of this task will then trigger the respective DTS package.

Import Export in SQL Server 2005

In case of Sql Server 2000, we have the option for import / Export data using DTS by creating objects.

Doing this we are getting the default value of colms of tables int the destination database.

But in case of SQL Server 2005 i am not getting this feature.

If i am using the Import /Export I am not getting the default values.

So first i am generating the script for table and create table in the destination database and then using import/export option to copy data.

Is there any other option to do this?

DEFAULT values are elements of the table schema. If you are not getting the default values, there are two possible explanitions.

1. Your script that creates the tables does not include the DEFAULT values.

2. Your data has empty strings instead of NULL values (DEFAULT values are ONLY used if the data is missing or NULL.)

|||

Yah what you are saying is right.

But what i am asking is , by default i am not getting default values while using import/ export to a new data base.

For that i am first generating script for tables and the table syntax include the default. and then using import/export data.

Is it possible, with out generating the script for tables from the existing database will get the default value in the new database.

|||Yes it is possible -but ONLY if the missing data is [NULL], NOT empty string [''].|||

You need to use the SQL Server specific version of import/export, look here for more info;

http://blogs.msdn.com/euanga/archive/2006/07/18/668916.aspx

sql