DTS scheduling and float problem

Hi
I have some DTS-package that import textfiles. When I run
then manually everything works fine, but when i schedule
them the fields containing floatvalues is becomming
integers instead. Any one who know whats the problem is?
/AnettHi,
Inside the data Transformation task, change the destination field to Float
data type inside the package.
Thanks
Hari
SQL Server MVP
"Anett" <anonymous@.discussions.microsoft.com> wrote in message
news:051c01c54019$a2377270$a401280a@.phx.gbl...
> Hi
> I have some DTS-package that import textfiles. When I run
> then manually everything works fine, but when i schedule
> them the fields containing floatvalues is becomming
> integers instead. Any one who know whats the problem is?
> /Anett
>

DTS scheduling and float problem

Hi
I have some DTS-package that import textfiles. When I run
then manually everything works fine, but when i schedule
them the fields containing floatvalues is becomming
integers instead. Any one who know whats the problem is?
/Anett
Hi,
Inside the data Transformation task, change the destination field to Float
data type inside the package.
Thanks
Hari
SQL Server MVP
"Anett" <anonymous@.discussions.microsoft.com> wrote in message
news:051c01c54019$a2377270$a401280a@.phx.gbl...
> Hi
> I have some DTS-package that import textfiles. When I run
> then manually everything works fine, but when i schedule
> them the fields containing floatvalues is becomming
> integers instead. Any one who know whats the problem is?
> /Anett
>

DTS scheduling and float problem

Hi
I have some DTS-package that import textfiles. When I run
then manually everything works fine, but when i schedule
them the fields containing floatvalues is becomming
integers instead. Any one who know whats the problem is?
/AnettHi,
Inside the data Transformation task, change the destination field to Float
data type inside the package.
Thanks
Hari
SQL Server MVP
"Anett" <anonymous@.discussions.microsoft.com> wrote in message
news:051c01c54019$a2377270$a401280a@.phx.gbl...
> Hi
> I have some DTS-package that import textfiles. When I run
> then manually everything works fine, but when i schedule
> them the fields containing floatvalues is becomming
> integers instead. Any one who know whats the problem is?
> /Anett
>sqlsql

DTS question?????????

Hi,
I use DTS import/export to import data from DB2 into SQL 2000 tables.
It works find. However , I want to truncate the tables first before the
copy.
What is the best way to do this?
Thank you for all your suggestions.Do you have ajob set up for the DTS package?|||I have the DTS save under local PAckages, but I don't know how to
create a job for it.|||From EM right click the package, select 'schedule package'|||once you schedule the package, go to managment -> Jobs. Find the Job that you just created for the DTS. Right click on the job and go to the steps tab. There will be a step already in there for the data that you are transfering. Click on the insert button to insert a new step. A new step box will apear for you to enter info in. Name the step whatever you want. Fot Type, make sure its t-sql script. Make sure you select the right database. In the command text box type truncate table (and the name of the table yo want to clear). Click Apply. THen you will be back in steps tab. There are some arrows to rearrange the steps. Make sure that the truncate step is first. There is also a drop down box to where you select which step goes first.

Hope this helps.|||Thank you all for helping. I'll try that.

Thanks again|||No problem. Let me know if you run into a snag.

DTS question reguarding text import and tranformation

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Thanks very much for anyIn the past I had to perform similar tasks very often to import text files
generated from mainframe systems (or other third party sources that could
not directly import) to SQL Server. Normally I have done it two ways based
on the tools available:

1) Use a pre-processing tool to clean up the data and then import to SQL
Server via BCP or DTS. Sometimes I had to write my own little utility
program or script to clean up the data, sometimes I was able to find tools
that are available out there. In your case a simple search and replace tool
should do the job (probably better to have command line interface so you can
automate the process). There are many available and if you do not feel
comfortable writing your own tool you just need to Google for "search and
replace" and you will find a few. I did a quick search and here are two
links, but please test and evaluate yourself:
http://www.microsoft.com/technet/sc...05/hey0208.mspx
http://www.thefreecountry.com/progr...ndreplace.shtml
2) The second approach is to use BCP or DTS to import the text file to a
staging table with a single varchar (or nvarchar if you have UNICODE
characters in the data) column. This works well if you can fit one row of
the text file into the size of varchar(8000) or nvarchar(4000). Otherwise
you can still split it into multiple columns but processing afterwards
becomes more complex. Then you can use the various string functions in SQL
Server to perform the data clean up, split into columns and insert the final
results into your production table. In your case the REPLACE and SUBSTRING
functions could do it.

Hope this helps.

Regards,

Plamen Ratchev
http://www.SQLStudio.com|||To add on to Plamen's response, you can include an ActiveX script to task in
your DTS package to remove the extraneous quotes and then process the
cleaned up file in your transformation. You can change your transformation
to process this file as fixed-length instead of delimited.

Below is the body of an ActiveX script that will correct the sample data you
posted:

Const ForReading = 1
Const ForWriting = 2
Dim FSO, inFile, outFile, record

Set FSO = CreateObject("Scripting.FileSystemObject")
Set inFile = FSO.OpenTextFile( _
"C:\MyFiles\MyInputFile.txt", ForReading)
Set outFile = FSO.OpenTextFile( _
"C:\MyFiles\MyOutputFile.txt", ForWriting, True)

Do While inFile.AtEndOfStream = False
record = InFile.ReadLine()
record = Replace(record, """""", " ") 'replace "" with single space
record = Replace(record, """", "") 'remove "
outFile.WriteLine record
Loop

inFile.Close
outFile.Close
Set inFile = Nothing
Set outFile = Nothing
Set FSO = Nothing

--
Hope this helps.

Dan Guzman
SQL Server MVP

"lwhite" <angryaardvark@.excite.comwrote in message
news:rbadr2lnhmtl988f4brdm75btckjjpvm8i@.4ax.com...

Quote:

Originally Posted by

>
MS SQL 2000 sp4 on WinXp Pro SP2
I am very new to this so please let me know what I can do to make it
easier for you to understand the problem.
>
>
I have a non delimited text file. This text file has several columns
that for the most part are fixed length but..
The fixed format starts with a variable length number( char 10) as the
first column and a max (char 30) description field and another (char
50) long description field.
>
if the (char 30) description field has a measurement ( ie 4.5" ) then
the first field stats with a ". The subsequent description will end
with a "" or double quote. The fields can only work if the double
qoutes are replaced with a single space and then the leading single
quote is deleted. If this is done in this order then the fixed field
lengths work.
>
example of actual data***************
>
This example does not have any quotes in it*****
>
1015304 Sof Sand Block Holder RUDOLPH
INTE BH y N N nbrd EA 1.00000 .83 1.25
>
This example does have the quotes**********
>
"10154 Rud Zeb NonDisf Cush Cur ve 180Gr 7"" RUDOLPH
INTE DUZ180F5 y N N nbrd EA 1.00000 .43 .65"
>
This is the next several lines***************
>
1015401 Rud Blk Disf Cush Curved 100/180Gr RUDOLPH
INTE DUB100/1 y N N nbrd EA 1.00000 .46 .69
>
1015402 Rud Blk Disf Cush Curved 100Gr RUDOLPH
INTE DUB100F5 y N N nbrd EA 1.00000 .46 .69
>
1015405 Rud Myl Disif Violet 100 Gr RUDOLPH
INTE GLMM100F y N N nbrd EA 1.00000 .43 .65
>
1015406 Rud Myl Purple Disf 80Gr RUDOLPH
INTE GLMM080F y N N nbrd EA 1.00000 .43 .65
>
1015407 Rud Myl Light Green Disf 120Gr RUDOLPH
INTE GLMM120F y N N nbrd EA 1.00000 .43 .65
>
"1015408 Rud Myl Yellow Disf 180G r 7"" RUDOLPH
INTE GLMM180F y N N nbrd EA 1.00000 .43 .65"
>
end of example*******************
>
right now I am doing this by hand in notepad and then importing to
excel to generate a csv file. Can anyone suggest a better way to do
this or just point me to an example.
>
Thanks very much for any

DTS Project Template not available?

I need to import data from a CSV file into a db I'm designing. I figured I'd use DTS (which I understand now uses VS in the form of a 'Business Intelligence' (BI) project). My problem is that my only choices for BI projects are:

Analysis Service Project, Import Analysis Services 9.0 Database, Integration Services Project, Report Server Project Wizard, Report Model Project, Report Server Project.

No "Data Transformation Project".

I have SQL Server 2005 Developer's Edition. Might it be that DTS is not included in that version?

If I can't use DTS, what choices do I have?

It is included choose Integration Service Project but when you are importing CVS file into SQL Server, the server sees null values due to the nature of CVS files so it is a good idea to import your file into temp table before doing an INSERT INTO to your destination table. Try the link below for ready to use code but you can just use the GUI. The site is run by DTS expert. Hope this helps.

http://www.sqlis.com/

|||

Great, thanks! Yes, I have already created a 'temp' table as well as my ultimate normalized tables.

I'll check out that site.

DTS Problem

I am going to import 2 columns (out of ten) from a CSV to
a database in SQL Server 2000.
I would like how can I choose these 2 columns ? In DTS
Import / Export Wizard, I click the Transform button.
Should I change the source columns to "Ignore" for those I
don't want to import OR change the destination ones ?
Besides, for the table I am importing data, there is a
column that is non-nullable that may cause problem when I
import the columns. Is there any suggestion how to fix
it ?
Thanks
.You will have to ignore those columns, by selecting ignore from the combo
box - you are right.
Or simply, you could write a query that selects only the required columns,
and use that as your source, instead of a table.
If your target column in non-nullable and if you think your source has
NULLs, then you could use a COALESCE or ISNULL function in your source query
to replace those NULLs with something suitable.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:2dcd901c46a34$f76520a0$a301280a@.phx
.gbl...
I am going to import 2 columns (out of ten) from a CSV to
a database in SQL Server 2000.
I would like how can I choose these 2 columns ? In DTS
Import / Export Wizard, I click the Transform button.
Should I change the source columns to "Ignore" for those I
don't want to import OR change the destination ones ?
Besides, for the table I am importing data, there is a
column that is non-nullable that may cause problem when I
import the columns. Is there any suggestion how to fix
it ?
Thanks
.|||Thank you for your advice.
I would like to know should I select "Ignore" in Source
Columns only?
The table is already full and I just have to import data
to 2 columns only (1 is the key).
Actually the column causes problem is not my target column
(A column I don't import anything but it is only a non-
nullable field only) and I should not update the
production data.
Thanking you in anticipation.

>--Original Message--
>You will have to ignore those columns, by selecting
ignore from the combo
>box - you are right.
>Or simply, you could write a query that selects only the
required columns,
>and use that as your source, instead of a table.
>If your target column in non-nullable and if you think
your source has
>NULLs, then you could use a COALESCE or ISNULL function
in your source query
>to replace those NULLs with something suitable.
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message
> news:2dcd901c46a34$f76520a0$a301280a@.phx
.gbl...
>I am going to import 2 columns (out of ten) from a CSV to
>a database in SQL Server 2000.
>I would like how can I choose these 2 columns ? In DTS
>Import / Export Wizard, I click the Transform button.
>Should I change the source columns to "Ignore" for those I
>don't want to import OR change the destination ones ?
>Besides, for the table I am importing data, there is a
>column that is non-nullable that may cause problem when I
>import the columns. Is there any suggestion how to fix
>it ?
>Thanks
>
>..
>
>.
>

DTS Problem

I am going to import 2 columns (out of ten) from a CSV to
a database in SQL Server 2000.
I would like how can I choose these 2 columns ? In DTS
Import / Export Wizard, I click the Transform button.
Should I change the source columns to "Ignore" for those I
don't want to import OR change the destination ones ?
Besides, for the table I am importing data, there is a
column that is non-nullable that may cause problem when I
import the columns. Is there any suggestion how to fix
it ?
Thanks
..
You will have to ignore those columns, by selecting ignore from the combo
box - you are right.
Or simply, you could write a query that selects only the required columns,
and use that as your source, instead of a table.
If your target column in non-nullable and if you think your source has
NULLs, then you could use a COALESCE or ISNULL function in your source query
to replace those NULLs with something suitable.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:2dcd901c46a34$f76520a0$a301280a@.phx.gbl...
I am going to import 2 columns (out of ten) from a CSV to
a database in SQL Server 2000.
I would like how can I choose these 2 columns ? In DTS
Import / Export Wizard, I click the Transform button.
Should I change the source columns to "Ignore" for those I
don't want to import OR change the destination ones ?
Besides, for the table I am importing data, there is a
column that is non-nullable that may cause problem when I
import the columns. Is there any suggestion how to fix
it ?
Thanks
..
|||Thank you for your advice.
I would like to know should I select "Ignore" in Source
Columns only?
The table is already full and I just have to import data
to 2 columns only (1 is the key).
Actually the column causes problem is not my target column
(A column I don't import anything but it is only a non-
nullable field only) and I should not update the
production data.
Thanking you in anticipation.

>--Original Message--
>You will have to ignore those columns, by selecting
ignore from the combo
>box - you are right.
>Or simply, you could write a query that selects only the
required columns,
>and use that as your source, instead of a table.
>If your target column in non-nullable and if you think
your source has
>NULLs, then you could use a COALESCE or ISNULL function
in your source query
>to replace those NULLs with something suitable.
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2dcd901c46a34$f76520a0$a301280a@.phx.gbl...
>I am going to import 2 columns (out of ten) from a CSV to
>a database in SQL Server 2000.
>I would like how can I choose these 2 columns ? In DTS
>Import / Export Wizard, I click the Transform button.
>Should I change the source columns to "Ignore" for those I
>don't want to import OR change the destination ones ?
>Besides, for the table I am importing data, there is a
>column that is non-nullable that may cause problem when I
>import the columns. Is there any suggestion how to fix
>it ?
>Thanks
>
>..
>
>.
>

DTS Problem

I am going to import 2 columns (out of ten) from a CSV to
a database in SQL Server 2000.
I would like how can I choose these 2 columns ? In DTS
Import / Export Wizard, I click the Transform button.
Should I change the source columns to "Ignore" for those I
don't want to import OR change the destination ones ?
Besides, for the table I am importing data, there is a
column that is non-nullable that may cause problem when I
import the columns. Is there any suggestion how to fix
it ?
Thanks
.You will have to ignore those columns, by selecting ignore from the combo
box - you are right.
Or simply, you could write a query that selects only the required columns,
and use that as your source, instead of a table.
If your target column in non-nullable and if you think your source has
NULLs, then you could use a COALESCE or ISNULL function in your source query
to replace those NULLs with something suitable.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:2dcd901c46a34$f76520a0$a301280a@.phx.gbl...
I am going to import 2 columns (out of ten) from a CSV to
a database in SQL Server 2000.
I would like how can I choose these 2 columns ? In DTS
Import / Export Wizard, I click the Transform button.
Should I change the source columns to "Ignore" for those I
don't want to import OR change the destination ones ?
Besides, for the table I am importing data, there is a
column that is non-nullable that may cause problem when I
import the columns. Is there any suggestion how to fix
it ?
Thanks
.|||Thank you for your advice.
I would like to know should I select "Ignore" in Source
Columns only?
The table is already full and I just have to import data
to 2 columns only (1 is the key).
Actually the column causes problem is not my target column
(A column I don't import anything but it is only a non-
nullable field only) and I should not update the
production data.
Thanking you in anticipation.
>--Original Message--
>You will have to ignore those columns, by selecting
ignore from the combo
>box - you are right.
>Or simply, you could write a query that selects only the
required columns,
>and use that as your source, instead of a table.
>If your target column in non-nullable and if you think
your source has
>NULLs, then you could use a COALESCE or ISNULL function
in your source query
>to replace those NULLs with something suitable.
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2dcd901c46a34$f76520a0$a301280a@.phx.gbl...
>I am going to import 2 columns (out of ten) from a CSV to
>a database in SQL Server 2000.
>I would like how can I choose these 2 columns ? In DTS
>Import / Export Wizard, I click the Transform button.
>Should I change the source columns to "Ignore" for those I
>don't want to import OR change the destination ones ?
>Besides, for the table I am importing data, there is a
>column that is non-nullable that may cause problem when I
>import the columns. Is there any suggestion how to fix
>it ?
>Thanks
>
>..
>
>.
>

DTS permissions

I would like to import data from Source database into Target using DTS, what are the minimum SQL Server 2K privileges/server roles required for regular user to achieve that on both Source and Target instances? This import will replace existing objects on Target.

Thanks

As a client side tool, the minimum needed would be the same as if you were executing the SQL statements from any other tool. You'd need permissions to connect to the source, access the database, read from the table/view or execute whatever stored procedure. On the target, you'd need to be able to drop and create whatever objects are involved, insert into the table, etc.

-Sue

|||

There are also mulitple ways of doing this - if you're talking about transferring data between SQL Server databases, then Sue is correct. You can also export the data from the source server and store it as a file to be imported into the target. In that case, you have a lot of options, from file permissions to FTP security.

|||I was trying to use DTS in order to copy database objects from source to target, using drop target objects before copy option. In both databases user was db_owner however when I tried using DTS it was giving error saying that I need to have sysadmin privileges and sure enough as soon as I granted that to a user it worked. I also think that is overkill because I don't want developers working with DTS packages getting almighty privs.

DTS Pakcage File Import but Filename Changes

Hi All,

I am trying to automate a rather complicated file import routine using a DTS package. I'm getting on ok with it but have now reached the limits of my knowledge.

At the moment I am sent a file every month e.g. "perf04-05m1.csv". I then rename it to "import.csv" and run the DTS package. I would like to make the process more dynamic so that the the DTS package takes in the most recent file no matter it's filename e.g. I don't have to re-name the file to "import.csv" in order for the DTS package to run.

I have had a look at BOL and think that I need to use either a Dynamic Properties Task or a Global variable but I'm not sure how to tie it all together, can anyone offer any pointers or know of any good articles I can have a look at. I may also need to dynamically alter some of the SQL statements within the DTS package as well.

CheersWhy don't you do this in a sproc?

And then bcp the data...I also make sure I archive everything in the folder to a sub folder with the datetime of the move

Insert Into Ledger_Folder exec master..xp_cmdshell 'Dir d:\Data\Tax\SmartStreamExtracts\*.*'

SELECT @.Result_Count = @.@.ROWCOUNT, @.error_out = @.@.error

If @.Error_Out <> 0
BEGIN
Select @.Error_Loc = 5
Select @.Error_Type = 50001
GOTO Load_Ledger_Init_sp_Error
END

-- select * from ledger_folder

Delete From Ledger_Folder_Parsed

SELECT @.Result_Count = @.@.ROWCOUNT, @.error_out = @.@.error

If @.Error_Out <> 0
BEGIN
Select @.Error_Loc = 6
Select @.Error_Type = 50001
GOTO Load_Ledger_Init_sp_Error
END

Insert Into Ledger_Folder_Parsed (Create_Time, File_Size, File_Name )
Select Convert(datetime,Substring(dir_output,1,8)
+ ' '
+ (Substring(dir_output,11,5)
+ Case When Substring(dir_output,16,1) = 'a' Then ' AM' Else ' PM' End)) As Create_Time
, Convert(Int,LTrim(RTrim(Replace(Substring(dir_outp ut,17,22),',','')))) As File_Size
, Substring(dir_output,40,(Len(dir_output)-39)) As File_Name
From Ledger_Folder
Where Substring(dir_output,1,1) <> ' '
And (Substring(dir_output,1,1) <> ' '
And Substring(dir_output,25,5) <> '<DIR>')

SELECT @.Result_Count = @.@.ROWCOUNT, @.error_out = @.@.error

If @.Error_Out <> 0
BEGIN
Select @.Error_Loc = 7
Select @.Error_Type = 50001
GOTO Load_Ledger_Init_sp_Error
END|||Thanks Brett that certainly points me in the right direction...I'll give it a go.

DTS package with parameters

Hi,
I want to create DTS package which can accept a parameter and export / import data which match the parameter value. is it possible? if yes , can you please tell me the steps.
thanks in advance.
Satya - do not refer me to SQLDTS.com , not much help there .
RamI am not clear on what you are trying to do...

What are the source and destination connections in both the import and export scenario.

Are you trying to conditionally import or export data based on some Parameter? How are you executing the package?

DTS packages can take input parameters and return paramters via Package Variables (inner and outer). You can use an Execute package task to pass parameters to a nested DTS package which contains your logic for import or export. Depending on the complexity of your process you could create one master package (to manage the process) and two "child" packages: one for Import and one for export.

See books online for more info on the Execute Package Task...

CPNsqlsql

DTS package to import a csv file on a web server

Im trying to schedule a dts package to import a database table from a csv
file.
In enterprise manager when i attempt to create the dts process, im not sure
what datasource should i be using to connect to my csv file that resides on
the web server?
where do i specify me server to connect to; eg: www.myserver.com\dbRight-click Local Packages and select New Package, Look on Connection and
select Text File (Source). Specify the file name location on the network.
Perhaps you will need to create a shared folder on your web server and give
permissions to the SQL Server service account.
Hope this helps,
Ben Nevarez, MCDBA, OCP
Database Administrator
"bj" wrote:

> Im trying to schedule a dts package to import a database table from a csv
> file.
> In enterprise manager when i attempt to create the dts process, im not sur
e
> what datasource should i be using to connect to my csv file that resides o
n
> the web server?
> where do i specify me server to connect to; eg: www.myserver.com\db
>
>|||thanks ben
I think my biggest problem is working out what my UNC path to my web server
is to locate my text file
should it look something like this
\\syd2004\www.bradyaust.com.au\db\
"Ben Nevarez" <bnevarez@.sjm.com> wrote in message
news:118D97ED-21FC-4538-8670-53C25A1CF867@.microsoft.com...
> Right-click Local Packages and select New Package, Look on Connection and
> select Text File (Source). Specify the file name location on the network.
> Perhaps you will need to create a shared folder on your web server and
> give
> permissions to the SQL Server service account.
> Hope this helps,
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "bj" wrote:
>|||Go to your web server, select the folder containing the text file,
right-click and select Sharing and Security, Share this folder, give
Permissions (very careful here), maybe remove permissions to Everyone, Add
the SQL Server service account, etc.
Let us suppose you shared your folder as MyData, then SQL Server would be
able to access the file using \\webservername\MyData\MyTextFile.
Again, pay special attention to the permissions you give to the shared folde
r.
Ben Nevarez, MCDBA, OCP
Database Administrator
"bj" wrote:

> thanks ben
> I think my biggest problem is working out what my UNC path to my web serve
r
> is to locate my text file
> should it look something like this
> \\syd2004\www.bradyaust.com.au\db\
>
> "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> news:118D97ED-21FC-4538-8670-53C25A1CF867@.microsoft.com...
>
>

Dts package that imports difference

Hi,
I have to server and two different database on them. I want to import one
table from database1 to database2. But I want to import only difference
between two table.I want to also keep identity column value. I created a dts
package but I don't know how can I put a condition like "where myid not
in(Select myid from database2.table)" and where can I put Set
IDENTITY_INSERT mytable ON?
Thanks in advance,Assuming you are using an Execute SQL task, you can use a script like the
one below:
SET IDENTITY_INSERT Database2.dbo.MyTable ON
GO
INSERT INTO Database2.dbo.MyTable
(
ID,
MyData
)
SELECT
ID,
MyData
FROM Database1.dbo.MyTable AS t1
WHERE NOT EXISTS
(
SELECT *
FROM Database2.dbo.MyTable AS t2
WHERE t2.ID = t1.ID
)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"italic" <hugur@.hotmail.com> wrote in message
news:%23M7cQ36kHHA.4624@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have to server and two different database on them. I want to import one
> table from database1 to database2. But I want to import only difference
> between two table.I want to also keep identity column value. I created a
> dts package but I don't know how can I put a condition like "where myid
> not in(Select myid from database2.table)" and where can I put Set
> IDENTITY_INSERT mytable ON?
> Thanks in advance,
>
>|||I don't think you can connect from "Execute SQL task" to both server. You
need to select connection(server) first. Am I wrong?
Thanks
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:51021AEF-D9B2-4FA4-AD0E-3CA7F1C1C889@.microsoft.com...
> Assuming you are using an Execute SQL task, you can use a script like the
> one below:
>
> SET IDENTITY_INSERT Database2.dbo.MyTable ON
> GO
> INSERT INTO Database2.dbo.MyTable
> (
> ID,
> MyData
> )
> SELECT
> ID,
> MyData
> FROM Database1.dbo.MyTable AS t1
> WHERE NOT EXISTS
> (
> SELECT *
> FROM Database2.dbo.MyTable AS t2
> WHERE t2.ID = t1.ID
> )
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "italic" <hugur@.hotmail.com> wrote in message
> news:%23M7cQ36kHHA.4624@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> I have to server and two different database on them. I want to import one
>> table from database1 to database2. But I want to import only difference
>> between two table.I want to also keep identity column value. I created a
>> dts package but I don't know how can I put a condition like "where myid
>> not in(Select myid from database2.table)" and where can I put Set
>> IDENTITY_INSERT mytable ON?
>> Thanks in advance,
>>
>|||in DTS,
first you have to get the lastet ID (select max(id) from destination)
then you have to dynamically create the select statement (Select * from
source where id > maxid)
(or you have to use a parametrized query where the parameter will be the ID)
and finally load the data.
"italic" <hugur@.hotmail.com> wrote in message
news:Ogw1D77kHHA.2272@.TK2MSFTNGP02.phx.gbl...
>I don't think you can connect from "Execute SQL task" to both server. You
>need to select connection(server) first. Am I wrong?
> Thanks
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:51021AEF-D9B2-4FA4-AD0E-3CA7F1C1C889@.microsoft.com...
>> Assuming you are using an Execute SQL task, you can use a script like the
>> one below:
>>
>> SET IDENTITY_INSERT Database2.dbo.MyTable ON
>> GO
>> INSERT INTO Database2.dbo.MyTable
>> (
>> ID,
>> MyData
>> )
>> SELECT
>> ID,
>> MyData
>> FROM Database1.dbo.MyTable AS t1
>> WHERE NOT EXISTS
>> (
>> SELECT *
>> FROM Database2.dbo.MyTable AS t2
>> WHERE t2.ID = t1.ID
>> )
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "italic" <hugur@.hotmail.com> wrote in message
>> news:%23M7cQ36kHHA.4624@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> I have to server and two different database on them. I want to import
>> one table from database1 to database2. But I want to import only
>> difference between two table.I want to also keep identity column value.
>> I created a dts package but I don't know how can I put a condition like
>> "where myid not in(Select myid from database2.table)" and where can I
>> put Set IDENTITY_INSERT mytable ON?
>> Thanks in advance,
>>
>>
>|||>I don't think you can connect from "Execute SQL task" to both server. You
>need to select connection(server) first. Am I wrong?
You are correct. It was unclear to me from your message that the databases
were on different servers.
If you have a lot of changes, I suggest you copy the data from the source
table into a staging table on the target databse server and then use an
Execute SQL task.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"italic" <hugur@.hotmail.com> wrote in message
news:Ogw1D77kHHA.2272@.TK2MSFTNGP02.phx.gbl...
>I don't think you can connect from "Execute SQL task" to both server. You
>need to select connection(server) first. Am I wrong?
> Thanks
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:51021AEF-D9B2-4FA4-AD0E-3CA7F1C1C889@.microsoft.com...
>> Assuming you are using an Execute SQL task, you can use a script like the
>> one below:
>>
>> SET IDENTITY_INSERT Database2.dbo.MyTable ON
>> GO
>> INSERT INTO Database2.dbo.MyTable
>> (
>> ID,
>> MyData
>> )
>> SELECT
>> ID,
>> MyData
>> FROM Database1.dbo.MyTable AS t1
>> WHERE NOT EXISTS
>> (
>> SELECT *
>> FROM Database2.dbo.MyTable AS t2
>> WHERE t2.ID = t1.ID
>> )
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "italic" <hugur@.hotmail.com> wrote in message
>> news:%23M7cQ36kHHA.4624@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> I have to server and two different database on them. I want to import
>> one table from database1 to database2. But I want to import only
>> difference between two table.I want to also keep identity column value.
>> I created a dts package but I don't know how can I put a condition like
>> "where myid not in(Select myid from database2.table)" and where can I
>> put Set IDENTITY_INSERT mytable ON?
>> Thanks in advance,
>>
>>
>|||Hi
"italic" wrote:
> I don't think you can connect from "Execute SQL task" to both server. You
> need to select connection(server) first. Am I wrong?
> Thanks
You can create a linked server and reference that in your query, or as you
have a second connection to the destination server you could use a query
(task) to retrieve the highest identity from the destination server then use
that in a global variable as the start of the information retrieved from the
second server.
See http://www.sqldts.com/280.aspx
http://www.sqldts.com/205.aspx
The second method would not necessarily be transactionally consistent,
therefore you should not be changing the destination table when this is
running.
John|||Linked Server is not allowed. I can try the second way.
Thanks
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:BE7E5676-71E5-4202-9ACB-BFEC91B180C8@.microsoft.com...
> Hi
> "italic" wrote:
>> I don't think you can connect from "Execute SQL task" to both server. You
>> need to select connection(server) first. Am I wrong?
>> Thanks
> You can create a linked server and reference that in your query, or as you
> have a second connection to the destination server you could use a query
> (task) to retrieve the highest identity from the destination server then
> use
> that in a global variable as the start of the information retrieved from
> the
> second server.
> See http://www.sqldts.com/280.aspx
> http://www.sqldts.com/205.aspx
> The second method would not necessarily be transactionally consistent,
> therefore you should not be changing the destination table when this is
> running.
>
> John|||Thanks for reply.
"Jeje" <willgart@.hotmail.com> wrote in message
news:ORGk$P8kHHA.5048@.TK2MSFTNGP04.phx.gbl...
> in DTS,
> first you have to get the lastet ID (select max(id) from destination)
> then you have to dynamically create the select statement (Select * from
> source where id > maxid)
> (or you have to use a parametrized query where the parameter will be the
> ID)
> and finally load the data.
>
>
> "italic" <hugur@.hotmail.com> wrote in message
> news:Ogw1D77kHHA.2272@.TK2MSFTNGP02.phx.gbl...
>>I don't think you can connect from "Execute SQL task" to both server. You
>>need to select connection(server) first. Am I wrong?
>> Thanks
>> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>> news:51021AEF-D9B2-4FA4-AD0E-3CA7F1C1C889@.microsoft.com...
>> Assuming you are using an Execute SQL task, you can use a script like
>> the one below:
>>
>> SET IDENTITY_INSERT Database2.dbo.MyTable ON
>> GO
>> INSERT INTO Database2.dbo.MyTable
>> (
>> ID,
>> MyData
>> )
>> SELECT
>> ID,
>> MyData
>> FROM Database1.dbo.MyTable AS t1
>> WHERE NOT EXISTS
>> (
>> SELECT *
>> FROM Database2.dbo.MyTable AS t2
>> WHERE t2.ID = t1.ID
>> )
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "italic" <hugur@.hotmail.com> wrote in message
>> news:%23M7cQ36kHHA.4624@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> I have to server and two different database on them. I want to import
>> one table from database1 to database2. But I want to import only
>> difference between two table.I want to also keep identity column value.
>> I created a dts package but I don't know how can I put a condition like
>> "where myid not in(Select myid from database2.table)" and where can I
>> put Set IDENTITY_INSERT mytable ON?
>> Thanks in advance,
>>
>>
>>|||Hi
"italic" wrote:
> Linked Server is not allowed. I can try the second way.
> Thanks
>
You would need a 4 part name! What error do you get?
John|||I mean, company db admin don't want linked server.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:0C51877A-3573-4795-83F9-017AAA43B870@.microsoft.com...
> Hi
> "italic" wrote:
>> Linked Server is not allowed. I can try the second way.
>> Thanks
> You would need a 4 part name! What error do you get?
> John

Dts package that imports difference

Hi,
I have to server and two different database on them. I want to import one
table from database1 to database2. But I want to import only difference
between two table.I want to also keep identity column value. I created a dts
package but I don't know how can I put a condition like "where myid not
in(Select myid from database2.table)" and where can I put Set
IDENTITY_INSERT mytable ON?
Thanks in advance,Assuming you are using an Execute SQL task, you can use a script like the
one below:
SET IDENTITY_INSERT Database2.dbo.MyTable ON
GO
INSERT INTO Database2.dbo.MyTable
(
ID,
MyData
)
SELECT
ID,
MyData
FROM Database1.dbo.MyTable AS t1
WHERE NOT EXISTS
(
SELECT *
FROM Database2.dbo.MyTable AS t2
WHERE t2.ID = t1.ID
)
Hope this helps.
Dan Guzman
SQL Server MVP
"italic" <hugur@.hotmail.com> wrote in message
news:%23M7cQ36kHHA.4624@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have to server and two different database on them. I want to import one
> table from database1 to database2. But I want to import only difference
> between two table.I want to also keep identity column value. I created a
> dts package but I don't know how can I put a condition like "where myid
> not in(Select myid from database2.table)" and where can I put Set
> IDENTITY_INSERT mytable ON?
> Thanks in advance,
>
>|||I don't think you can connect from "Execute SQL task" to both server. You
need to select connection(server) first. Am I wrong?
Thanks
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:51021AEF-D9B2-4FA4-AD0E-3CA7F1C1C889@.microsoft.com...
> Assuming you are using an Execute SQL task, you can use a script like the
> one below:
>
> SET IDENTITY_INSERT Database2.dbo.MyTable ON
> GO
> INSERT INTO Database2.dbo.MyTable
> (
> ID,
> MyData
> )
> SELECT
> ID,
> MyData
> FROM Database1.dbo.MyTable AS t1
> WHERE NOT EXISTS
> (
> SELECT *
> FROM Database2.dbo.MyTable AS t2
> WHERE t2.ID = t1.ID
> )
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "italic" <hugur@.hotmail.com> wrote in message
> news:%23M7cQ36kHHA.4624@.TK2MSFTNGP04.phx.gbl...
>|||in DTS,
first you have to get the lastet ID (select max(id) from destination)
then you have to dynamically create the select statement (Select * from
source where id > maxid)
(or you have to use a parametrized query where the parameter will be the ID)
and finally load the data.
"italic" <hugur@.hotmail.com> wrote in message
news:Ogw1D77kHHA.2272@.TK2MSFTNGP02.phx.gbl...
>I don't think you can connect from "Execute SQL task" to both server. You
>need to select connection(server) first. Am I wrong?
> Thanks
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:51021AEF-D9B2-4FA4-AD0E-3CA7F1C1C889@.microsoft.com...
>|||>I don't think you can connect from "Execute SQL task" to both server. You
>need to select connection(server) first. Am I wrong?
You are correct. It was unclear to me from your message that the databases
were on different servers.
If you have a lot of changes, I suggest you copy the data from the source
table into a staging table on the target databse server and then use an
Execute SQL task.
Hope this helps.
Dan Guzman
SQL Server MVP
"italic" <hugur@.hotmail.com> wrote in message
news:Ogw1D77kHHA.2272@.TK2MSFTNGP02.phx.gbl...
>I don't think you can connect from "Execute SQL task" to both server. You
>need to select connection(server) first. Am I wrong?
> Thanks
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:51021AEF-D9B2-4FA4-AD0E-3CA7F1C1C889@.microsoft.com...
>|||Hi
"italic" wrote:

> I don't think you can connect from "Execute SQL task" to both server. You
> need to select connection(server) first. Am I wrong?
> Thanks
You can create a linked server and reference that in your query, or as you
have a second connection to the destination server you could use a query
(task) to retrieve the highest identity from the destination server then use
that in a global variable as the start of the information retrieved from the
second server.
See http://www.sqldts.com/280.aspx
http://www.sqldts.com/205.aspx
The second method would not necessarily be transactionally consistent,
therefore you should not be changing the destination table when this is
running.
John|||Linked Server is not allowed. I can try the second way.
Thanks
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:BE7E5676-71E5-4202-9ACB-BFEC91B180C8@.microsoft.com...
> Hi
> "italic" wrote:
>
> You can create a linked server and reference that in your query, or as you
> have a second connection to the destination server you could use a query
> (task) to retrieve the highest identity from the destination server then
> use
> that in a global variable as the start of the information retrieved from
> the
> second server.
> See http://www.sqldts.com/280.aspx
> http://www.sqldts.com/205.aspx
> The second method would not necessarily be transactionally consistent,
> therefore you should not be changing the destination table when this is
> running.
>
> John|||Thanks for reply.
"Jeje" <willgart@.hotmail.com> wrote in message
news:ORGk$P8kHHA.5048@.TK2MSFTNGP04.phx.gbl...[vbcol=seagreen]
> in DTS,
> first you have to get the lastet ID (select max(id) from destination)
> then you have to dynamically create the select statement (Select * from
> source where id > maxid)
> (or you have to use a parametrized query where the parameter will be the
> ID)
> and finally load the data.
>
>
> "italic" <hugur@.hotmail.com> wrote in message
> news:Ogw1D77kHHA.2272@.TK2MSFTNGP02.phx.gbl...|||Hi
"italic" wrote:

> Linked Server is not allowed. I can try the second way.
> Thanks
>
You would need a 4 part name! What error do you get?
John|||I mean, company db admin don't want linked server.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:0C51877A-3573-4795-83F9-017AAA43B870@.microsoft.com...
> Hi
> "italic" wrote:
>
> You would need a 4 part name! What error do you get?
> John

DTS package and Firewall

Hi all, I have some DTS packages that are used to import /export data to a
SQL box located outside in a DMZ behind a firewall.

We need to open up a port in the firewall so that the Internal Server can
communicate (Execute DTS packages against) with the SQL box located outside
the DMZ.

How do I find out what Port we need to open so that the Internal SQL box can
communicate with the external SQL box?

Thanks in advance
Mark"Mark" <markjones@.n0Sp8mTAIRAWHITIdotAC.NZ> wrote in message news:<ZFxoc.4117$XI4.156748@.news.xtra.co.nz>...
> Hi all, I have some DTS packages that are used to import /export data to a
> SQL box located outside in a DMZ behind a firewall.
> We need to open up a port in the firewall so that the Internal Server can
> communicate (Execute DTS packages against) with the SQL box located outside
> the DMZ.
> How do I find out what Port we need to open so that the Internal SQL box can
> communicate with the external SQL box?
> Thanks in advance
> Mark

In general, a DTS package is like any other SQL client:

http://support.microsoft.com/defaul...2&Product=sql2k

If you have a variety of tasks in your package (ActiveX, FTP etc.)
then you might require additional ports to be opened, but that depends
on exactly what those tasks do, what protocols they use etc.

Simon

DTS package acting weird

I have built a DTS package in SQL Server 2000 to Delete all rows from a table and then import from another table. I have scheduled it run twice a day and everytime it runs, it does not delete anything but imports all the records from another table. So when its done, I have twice the number of records in my final table. Then I go back to the server and execute the package manually and it works perfect. It deletes all and then imports new. I have enabled package logging and it shows both the steps executed perfectly (even when it executes on scheduled time).

What can be the problem?

Thanks,

Bullpit

I think I found the problem. I had two packages which did the same job and at the same time. That probably was the reason I had twice the actual number of records.

DTS Oracle Connection - Importing Data

I have SQL Server 2000 and am using DTS to simply import some data located on a remote Oracle DB.

If I have the following information:

TNS Name:

 ThisApp.world
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS =
 (COMMUNITY = tcpxxx.world)
 (PROTOCOL = TCP)
 (Host = SomeHost)
 (Port = 1234)
 )
 )
 (CONNECT_DATA =
 (SID = App)
 (GLOBAL_NAME = ThisApp.world)
 )
 )

And I also have the name of the Table.

Using the DTS Wizard, on the Choose a Data Source screen, I pick the following:

Data Source: Microsoft ODBC Drive for Oracle
Server:
Username:
Password:

I don't know what I should put into the other fields, given what I have above, i.e. what to put in for Server, Username, and Password! Or should I use a different procedure to import this Oracle data? I am almost positive I was not supplied the username/password. I was told I could connect given the above TNS information. What do you all recommned?

Your assistance will be greatly appreciated.Given the above information, how can I use Server Explorer in VS.NET 2002? It still prompts me for a Username/Password, which I do not have.

Please help anyone.

DTS Jobs Troubles

I'm trying to run DTS packages (import AS400 information) on a job and it fails, but when I program a DTS package job that imports from SQL Server to SQL Server it does not fail.
What can I do?
I hope your answer
Thanks
Cristopher SerratoCheck your connectin string to AS400.
is it a DB2 server ?
Do you have DB2 Connectivity kit installed ?

Originally posted by cjserrato
I'm trying to run DTS packages (import AS400 information) on a job and it fails, but when I program a DTS package job that imports from SQL Server to SQL Server it does not fail.

What can I do?

I hope your answer

Thanks

Cristopher Serrato|||Originally posted by Ben Mansouri
Check your connectin string to AS400.
is it a DB2 server ?
Do you have DB2 Connectivity kit installed ?

I CHECKED MY CONECTION AND IT'S GOOD, EVEN IF I PLAY THE DTS PACKAGE, IT WORKS BUT WHEN I PUT IT INTO A JOB IT FAILS.

I DOWNLOADED THE SERVICE PACK 3A, BUT IT DOES NOT WORK YET.

WHAT COULD IT BE SO?|||Take help from this KBA (http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q269074) to schedule a DTS package.|||Is there any transformation envolved on DTS ?

Originally posted by Satya
Take help from this KBA (http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q269074) to schedule a DTS package.|||Originally posted by Ben Mansouri
Is there any transformation envolved on DTS ?

No, the only one that I got is

AS400 -> SQL SERVER 2000

DTS issue

Hello,
I am using DTS (the version that shipped with MSSQL 7.0).
I am trying to import at space-delimited text file into a database table.
I have one column in my text file which tends to exced 255 characters.
When this occurs, I run into the following error during the import:
'data for source column 3(colname3) is too large for the specified buffer size.'
Microsoft recommended changing the registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engi nes\Excel\TypeGuessRows from 8 to 0.
This did not work.
Any suggestions for defeating this error would be greatly appreciated.

Thanks,
JeffJust a guess, but is the registry key relevant? It looks like something Excel-specific.