DTS schedult problem

I created a DTS package to transfer a table to a text file. Every time when
I click start manually, it'll run and transfer the data to a local txt file
with no problem. But when I schedule this DTS package and run it from the
SQL Agent - Jobs. It always fail to run. I don't know why. Anybody can help
me?|
| I created a DTS package to transfer a table to a text file. Every time
when
| I click start manually, it'll run and transfer the data to a local txt
file
| with no problem. But when I schedule this DTS package and run it from the
| SQL Agent - Jobs. It always fail to run. I don't know why. Anybody can
help
| me?
--
We need more information to help you. What errors are returned when the job
fails? Post it and we'll give it a shot.
Thanks,
--
Eric Cardenas
SQL Server support|||JL
I think that you cerated DTS package on the workstation not on the server ,
then SQL Agent on your wokstation is running under differnent account than
server.
Check it out.
"JL" <ljmagzine@.hotmail.com> wrote in message
news:eGGt1AgvDHA.2304@.TK2MSFTNGP12.phx.gbl...
> I created a DTS package to transfer a table to a text file. Every time
when
> I click start manually, it'll run and transfer the data to a local txt
file
> with no problem. But when I schedule this DTS package and run it from the
> SQL Agent - Jobs. It always fail to run. I don't know why. Anybody can
help
> me?
>|||VXJpIGlzIGNvcnJlY3QuICBJIHJhbiBpbnRvIHRoaXMgcHJvYmxlbSBhbmQgaXQgaGFkIG1lIHN0
dW1wZWQgZm9yIGEgd2hpbGUuDQoNClRoZSBzb2x1dGlvbiBpcyB0byBjcmVhdGUgdGhlIERUUyBw
YWNrYWdlIGFuZCBKb2Igd2hpbGUgcGh5c2ljYWxseSBhdCB0aGUgU1FMIFNlcnZlciBib3guDQoN
Cg0KIkpMIiA8bGptYWd6aW5lQGhvdG1haWwuY29tPiB3cm90ZSBpbiBtZXNzYWdlIG5ld3M6ZUdH
dDFBZ3ZESEEuMjMwNEBUSzJNU0ZUTkdQMTIucGh4LmdibC4uLg0KPiBJIGNyZWF0ZWQgYSBEVFMg
cGFja2FnZSB0byB0cmFuc2ZlciBhIHRhYmxlIHRvIGEgdGV4dCBmaWxlLiBFdmVyeSB0aW1lIHdo
ZW4NCj4gSSBjbGljayBzdGFydCBtYW51YWxseSwgaXQnbGwgcnVuIGFuZCB0cmFuc2ZlciB0aGUg
ZGF0YSB0byBhIGxvY2FsIHR4dCBmaWxlDQo+IHdpdGggbm8gcHJvYmxlbS4gQnV0IHdoZW4gSSBz
Y2hlZHVsZSB0aGlzIERUUyBwYWNrYWdlIGFuZCBydW4gaXQgZnJvbSB0aGUNCj4gU1FMIEFnZW50
IC0gSm9icy4gSXQgYWx3YXlzIGZhaWwgdG8gcnVuLiBJIGRvbid0IGtub3cgd2h5LiBBbnlib2R5
IGNhbiBoZWxwDQo+IG1lPw0KPiANCj4gsqlsql

DTS runs great... but not from the job list

I have a DTS package that updates a database from a text file and it works
great when I run it manually from the DTS designer. However, I right click
on it and set it up to run automatically and it will not run. I went to
view the history of the job messages and it appears that the following is
the error for this task:
Executed as user: MAHC_SQL2\SYSTEM. DTSRun: Loading... DTSRun:
Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun
OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1,
Error = -2147467259 (80004005) Error string: Error opening datafile:
The network name cannot be found. Error source: Microsoft Data
Transformation Services Flat File Rowset Provider Help file:
DTSFFile.hlp Help context: 0 Error Detail Records: Error:
67 (43); Provider Error: 67 (43) Error string: Error opening
datafile: The network name cannot be found. Error source: Microsoft
Data Transformation Services Flat File Rowset Provider Help file:
DTSFFile.hlp Help context: 0 DTSRun OnFinish:
DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process
Exit Code 1. The step failed.
Can anyone give me any clues as to what the problem could be?
Schoo
Have a look at this KB that details the common reasons a scheduled DTS
package will fail
INF: How to Run a DTS Package as a Scheduled Job
http://support.microsoft.com/default...;en-us;Q269074
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Schoo" <scott.schuman@.nospam.ma-hc.com> wrote in message
news:%23enkcwUrEHA.452@.TK2MSFTNGP09.phx.gbl...
>I have a DTS package that updates a database from a text file and it works
> great when I run it manually from the DTS designer. However, I right
> click
> on it and set it up to run automatically and it will not run. I went to
> view the history of the job messages and it appears that the following is
> the error for this task:
> Executed as user: MAHC_SQL2\SYSTEM. DTSRun: Loading... DTSRun:
> Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun
> OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
> DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1,
> Error = -2147467259 (80004005) Error string: Error opening datafile:
> The network name cannot be found. Error source: Microsoft Data
> Transformation Services Flat File Rowset Provider Help file:
> DTSFFile.hlp Help context: 0 Error Detail Records: Error:
> 67 (43); Provider Error: 67 (43) Error string: Error opening
> datafile: The network name cannot be found. Error source:
> Microsoft
> Data Transformation Services Flat File Rowset Provider Help file:
> DTSFFile.hlp Help context: 0 DTSRun OnFinish:
> DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process
> Exit Code 1. The step failed.
> Can anyone give me any clues as to what the problem could be?
> Schoo
>
|||Hi Schoo,
Thanks for you post and I would love to show my gratidue for MVP Jasper
Smith's providing that great KB:Q269074
From your Error Message, I foudn the error was "The network name cannot be
found" so that please check whether there is anything wrong with your
source and target of database. Make sure they are "online", also please
check the permission issue. It seems strange and I need more information to
make further troubleshooting.
Would you please generate a small DTS package and see whether it works
fine? To get a better picture of the problem we would like to reproduct the
issue on our end. To expedite the process, please provide us the detailed
reproduce steps.
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi Schoo,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

DTS re-uses old text file input

Hi All,

A simple DTS job I have is giving me fits. It is a straight copy column job from a pipe delimited text file into a table. The input file comes from a mapped drive linked to a shared filesystem on a sun solaris box.

The typical scenario. I run the DTS job to load 8000 rows from the input text file. Job succeeds.

A week later, the text file is updated with 9000 new rows. I run the DTS job with no changes and it loads 8000 rows from last week.

I reboot my Win XP pc and run DTS again. It now loads the 9000 new rows.

I tried mapping to a UNC to no avail.

Is it buffering the old file somewhere? I need help.

current environment:
SQL Server 2000 with all latest SP's and patches
Windows 2000 Server with all latest SP's and patches
Drive 'G' mapped to a shared filesystem on sun solaris via Samba?I don't think DTS will use such type of cache and read old settings, ensure the source file path is defined correctly and oin the next execution it may be reading old settings.|||I don't think DTS will use such type of cache and read old settings, ensure the source file path is defined correctly and oin the next execution it may be reading old settings.

Thanks Satya,

I'm not sure what you mean by "old settings"? Here's what I do know.

Drive 'G' is mapped to \\sun001\data

The input file name is weekly_updates.txt and does not change.

My DTS connection 1 properties uses
G:\weekly_updates.txt in the File Name box.

It always works correctly after a reboot, and more specifically, from any pc, not just mine. Any idea's?|||Might it be somehow related to Enterprise Manager? I wonder if there is a way to automatically force everything in EM to refresh. I dislike having to submit a job and then keep hitting refresh to see when it completes. I'm wondering if the "old" information stored in EM is related to my DTS issue?

Dts Record Count

I have a dts package that export data from a database to a text file. I could like to create a validation script to coun the data in the database that should be exported to file and then re-count again after the data is in the text file. Also count the number of columns that is exported.

Is there a way to do that within a dts package?

Help!

LystraYou ought to be able to do that using DTS Global Variables (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtsprog/dtspapps_82nn.asp).

-PatP

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 Question

Hi -

I want to create a DTS to pull data from Text file to a SQL Server Table. The problem is i want to implement some Business Logic in it.

Like the process would involve 2 SQL tables and i want to basically have to update the table data based on the ID that i have to match through other table...like a join...

How is it possible..??

Thanks for any input...

You have two problems SQL Server will see null values because your data is a text file so you need to import the text file to a temp table before moving to your destination table. The second problem is minor if you are in SQL Server 2005 because you will not have the known restrictions and limitations of text data. I have answered this question many times, SQL Server Agent needs Admin permissions and you can use a stored proc to run the DTS package. Try the link below for sample DTS code and if you are in SQL Server 2005 you could get Integration Services code. Hope this helps.

http://www.sqldts.com

sqlsql

DTS problem, please help!

I have yet another weired problem for which I have no answer.

I have a DTS process setup that needs to fireup at night, which uploads data from text file into upload table.
It runs perfact.

The problem started when I tried to schedule it and then reschedule it for another timeperiod.

I reschduled this process for about 3 times for different timeperiod. Meaning I had originally set it to fire at 1:00 am then later changed to 2:00 and then to 3:00.

Technically, it should overwrite the last schedule time and fire at the new scheduled time, ie. 3:00 am daily.

But somehow DTS is keeping the history of all the previous scheduled times and is now firing at 1:00 am, 2:00 am and also at 3:00 am.

I also looked at the Versions option in that DTS process's right click menu and removed all, but the latest version. But still the same behaviour.

Any help on this is much appreciated.

Many Thanks!Did you actually change the SQL Agent job or did you just right-click the DTS package and schedule it? Check the jobs in SQL Agent to see if there are three of them in there.

DTS problem with AS400 flat file download

I'm having trouble importing a text file that was ftped down from an AS400 fixed length flat file. The text file appears to be formated correctly when I open it (1 record per line fixed format) but when I try to import it ASCII delimited it doesn't appear to be in that format and I'm not familiar at all with the AS/400 file formatting. This seems to only be a problem when the last field in the file is variable length. If the last field is a char 1 field it imports OK. Any idea on what I'm doing wrong with the import? I'm I going about this all wrong? Is there an easier way to get data into SQL Server from this AS400 flat file?
Any help would be greatly appreciated!!
Thanks,
DeidreDo you have IBM Client Access installed on your SQL Server? If so, you can create an ODBC connection to the AS/400. I do this for several AS/400 files at work and it couldn't be easier.|||No I don't have IBM Client Access installed on our SQL Server that's why we were ftping to a text file then trying to import it. I was trying to find a download for the IBM Client Access on IBM's website but I guess that's something you have to buy extra?|||I was under the impression that Client Access is something provided when you purchase an AS400. What kind of terminal emulator do your users use now to access the 400? The ODBC connection is what you really need more than all of the emulation that Client Access provides.

I hope I'm not beating around the issue here, but if you can get an ODBC connection, it would be much easier than the text file download.|||No thanks for the help! I think that would be alot easier too - if I can get the ODBC connection.
I know nothing about the AS400 side and I think that is my main problem. There is another group that handles the AS400 system and I just need a file they created to import into my system. I've been trying to get information from the other group with not alot of luck that is why I was trying the text file download. I will try to see if they can get me Client Access.|||You haven't mentioned how your doing the "import"

Is this AS400 file from DB2?

Also, what's the lrecl and how many rows?|||Do you have a file layout for the AS400 file? If the file is fixed width you will have to set the width of each column manually based on the file layout.|||You mention ascii delimited when referencing dts, but you also mention that the file is fixed length file - which is it. You have the option to import a fixed length field file as well in dts.|||Even easier...

If the file is less than 8000 bytes...dump it in to a temp table with 1 column..you can then parse it out, do validity checks, ect...

CREATE TABLE myTable99(Col1 varchar(8000))
GO

BULK INSERT myTable99 FROM 'd:\data\filename.txt'
GO

SELECT COUNT(*) FROM myTable99
GO|||I am importing using the dts wizard. I did try the fixed length ANSI option and it doesn't work either. It seems that all the fields are fixed length except the last field but none of the row delimiter options work for me. When I choose {CR}{LF} it catches the end of the first row then wraps the other rows that are longer than the first row. If I try any of the other row delimiter options I get this message "Could not find the selected row delimiter within the first 8 kb of data. Is the selected row delimiter valid?" but I see a double vertical line separating each row.
I am having this problem on all the files except 1 that I am trying to import from the AS400. The one that I am not having problems with last field is a 1 char field not variable length.
The AS400 guy said the file is not DB2 its just a AS400 fixed length flat file and I have the file format for all the files. The simples one is one with 2 fields- 1st field 2 chars - 2nd field 30 chars - variable row count.
Any other suggestion?
Thanks again for all the suggestions,
Deidre|||So it looks like it is pipe dilimited. Have you tried that delimimter yet?|||I don't see pipe as a row delimiter option - I've tried all the options I see - {CR}{LF}, {CR},{LF},Semicolon, Comma, Tab, Vertical Bar and none of them work.
Deidre|||In the Row Delimiter drop down you can add delimiters. Just click in the drop down and put in a pipe.|||Although vertical bar may be the same as pipe. Just to see if you can get it in a database, try importing it into access. The wizard is a little easier.|||It would be a lot easier if you could post a sample...

You say

but I see a double vertical line separating each row.

Do you mean row? Or Column? Quite different you know...

How much data are we talking about?

Did you try my example...should be pretty easy|||SHICKS -I tried entering the pipe delimiter in the row delimiter but it still didn't work. It imported in to Access great just used the wizard fixed width options and set the column breaks after the 2 and 32 chars.

--
Brett -- This works but I still don't see why I can't do it will a dts import:
CREATE TABLE #tblTemp(Col1 varchar(32))
GO

BULK INSERT #tblTemp FROM 'C:\test.txt'
GO

SELECT * FROM #tblTemp

insert into tblStageMajor
(sMajorCode,
vsMajorDescription,
dtImportDate)
select substring(Col1,1,2), substring(Col1,3,30),getdate()
from #tblTemp

drop table #tblTemp

I attached the file that goes along with this as an example of what I'm trying to import. I have several different files but this is the smallest. 2 columns 32 char width total - 27 rows - 1st column 2 chars -2nd column 30 chars.
The largest files is still only 147 char total row width, variable rows count(around 10,000 is normal), 12 fields.

Thanks,
Deidre|||I don't see no stinkin pipes (|)|||but I see a double vertical line separating each row
I agree with Brett - no vertical lines in that file. It is a little early in my timezone to see double anything yet - you haven't been sipping from the champagne bottle a little early, have you ? :-)|||Originally posted by rnealejr
I agree with Brett - no vertical lines in that file. It is a little early in my timezone to see double anything yet - you haven't been sipping from the champagne bottle a little early, have you ? :-)

And what time zone is that?

Also I tried to import that file...if you play around with the end of line parameter, you'll see that there's some kind of garbage at the end...|||OK I loaded it using my method, and everything looks fine

bizzare...try this

select LEN(Col1) from #tblTemp
SELECT '123456789012345678901234567890'
UNION ALL
select Col1 from #tblTemp

I thought I'd see extra bytes at the end...but I don't..

Hey ANOTHER reason not to use DTS...they just keep stacking up...|||That garbage is 0D0A - typical carriage return/linefeed ending|||Well DTS doesn't like it...did you try to import the test.txt file?|||Yep. Just add the control characters to the delimiter area.

dsweatman - Try the following in your dts script as a transformation:

Function Main()
strSource=DTSSource("Col001")
DTSDestination("Col001") = left(strSource,2)
DTSDestination("Col002") = right(strSource,len(strSource) - 2)
Main = DTSTransformStat_OK
End Function|||Check this out...I added 1 line...it looks like it's taking the line length definition from the first row...

bizzaro...

EDIT: And I just imported it Excel and Access...NO PROBLEMS...

DTS, you quirky little thing...

I even cut and pasted all the rows in to another file..same problem...|||I got it to work if I set the text file options to comma delimited.
and did the following in the transformation.

Originally posted by rnealejr
Function Main()
strSource=DTSSource("Col001")
DTSDestination("Col001") = left(strSource,2)
DTSDestination("Col002") = right(strSource,len(strSource) - 2)
Main = DTSTransformStat_OK
End Function

Thanks everyone!!|||Happy to help and Happy New Year !

Actually, in your example, any of the delimiters work - as long as your row delimter is set for cr/lf.|||Originally posted by dsweatman
I'm having trouble importing a text file that was ftped down from an AS400 fixed length flat file. The text file appears to be formated correctly when I open it (1 record per line fixed format) but when I try to import it ASCII delimited it doesn't appear to be in that format and I'm not familiar at all with the AS/400 file formatting. This seems to only be a problem when the last field in the file is variable length. If the last field is a char 1 field it imports OK. Any idea on what I'm doing wrong with the import? I'm I going about this all wrong? Is there an easier way to get data into SQL Server from this AS400 flat file?
Any help would be greatly appreciated!!
Thanks,
Deidre

hi,

first you confirm have specified carrage return while creating text file
in AS/400, If you are using client access system will take care
regarding carriage return
if you are using CPYFRMIMPF than you should specify carriage return

regards
NANAIAH
MUMBAI

DTS Problem

Hi Folks,
I have a two node cluster and I have a DTS package, which transfers data from a text file to the database. The problem is like it runs fine when the cluster is in its base node(Node1) but when ever the cluster fails overs (to node2) the dts job fails saying that the path not found.

1. The drive is a dependent resource to sql server.

2. When I try to run the DTS package itself (from node2) its running fine.

any insights guys?

with smiles
santhoshHow is the file referenced in the DTS package (ie, is it UNC)?

Is there possibly a permissions issue (Log in to Node two as the SQL Service account and attempt to run the DTS package)?

I have a similar set up with a two-node cluster and a DTS package that references a cluster disk. No issues that I am aware of.

Please provide a bit more detail.

Regards,

hmscott

DTS Problem

Hello All.

I am posting this on behalf of a friend.

"I am encountering a funny problem. I am generating a text with copyright sign as a delimiter. But got funny signs after file generation.

However, if the DTS is executed from another workstation, it works fine. I suspect this is the problem.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;133732 "

Any advise?

Thank you.If you are generating the text document from DTS then in theory it should be just a standard text document which doesn't use True Type fonts so I doubt that is the problem...|||Originally posted by rokslide
If you are generating the text document from DTS then in theory it should be just a standard text document which doesn't use True Type fonts so I doubt that is the problem...

Thank you Rokslide.

I will tell my friend this.|||If you can post more information about the DTS package itself we can problem help more.

Is there any reason they are using the copyright symbol as the seperator as oposed to something more widely used like a | or ,

DTS Problem

I'm having a bit of a problem getting a date parsed from a text file, if anyone has some time please take a look at this DTS code and let me know if you have any ideas. Thanks in advance!

'************************************************* *********************
' Visual Basic Transformation Script
' Copy each source column to the
' destination column
'************************************************* ***********************
Function Main()
DTSDestination("TableName") = DTSSource("Col001")
DTSDestination("Code") = DTSSource("Col002")
DTSDestination("Status") = DTSSource("Col003")
DTSDestination("ISN") = DTSSource("Col004")
DTSDestination("Description") = DTSSource("Col005")
DTSDestination("LongDescription") = DTSSource("Col006")
DTSDestination("OwnerSystem") = DTSSource("Col007")
DTSDestination("CodeFieldAttribute") = DTSSource("Col008")
DTSDestination("CodeFieldLength") = DTSSource("Col009")
DTSDestination("CodeEditRules") = DTSSource("Col010")
DTSDestination("EffectiveDateMaint") = DTSSource("Col011")
DTSDestination("StartDate") = getdate(DTSSource("Col012"))
'DTSDestination("StartDateInv") = getdate(DTSSource("Col013"))
DTSDestination("EndDate") = getdate(DTSSource("Col014"))
' DTSDestination("EndDateInv") = getdate(DTSSource("Col015"))
DTSDestination("Hold") = DTSSource("Col016")
Main = DTSTransformStat_OK
End Function

Function getDate(aDate)
Dim year, month, day

If Trim(aDate) = "" Or IsNumeric(aDate) Then
getDate = Null
Exit Function
End If

year = Mid(aDate, 1, 4)
month = Mid(aDate, 5, 2)
day = Mid(aDate, 7, 2)

getDate = DateSerial(year, month, day)

End FunctionLooking at your code leads to a few questions:

What is the error that you are getting? What is the value within DTSSource("Col012") and DTSSource("Col014")? Are the date fields all coming back NULL?

If so, then is the original column data in the format 20060725?

Also, the DateSerial function takes in numeric parameters, not strings, so you might need to convert the year, month, and day.

Finally, the section:

If Trim(aDate) = "" Or IsNumeric(aDate) Then
getDate = Null
Exit Function
End If

would work better as

If IsNumeric(aDate) Then
getDate = Null
Exit Function
Elseif Trim(aDate) = "" Then
getDate = Null
Exit Function
End If

Since Trim on a Numeric will cause an error, so it is good to test the trim after confirming it is not numeric.|||a simple rearrangemet of text date data will do the trick. try the code below (this assumes your text data is in the form of yyyymmdd otherwise you need to change the code a bit)

DTSDestination("StartDate") = left(DTSSource("Col012"),4) & "-" & mid(DTSSource("Col012"),5,2) & "-" & right(DTSSource("Col012"),2)|||Thanks guys, both your posts were very helpful. The problem actually was in the text file that I was trying to import, someone had entered dates in a yy/mm/dd format rather then yyyy/mm/dd once that was fixed the function performed as it should. Thanks alot!|||A couple of questions:

1.
With the supplied code
"
year = Mid(aDate, 1, 4)
month = Mid(aDate, 5, 2)
day = Mid(aDate, 7, 2)
"
wouldn't the slashes get in the way? I would guess that the code would have to change to:
"
year = Mid(aDate, 1, 4)
month = Mid(aDate, 6, 2)
day = Mid(aDate, 9, 2)
"
to get past whatever /,-, or . that was delimiting the date?

2. How does one Charboil something? I mean, by being in water wouldn't it get overcooked but not charred? :)

DTS permission

I created a DTS package to extract data to a text file.
I want to create a login for a user who should be able ONLY to run this DTS. I don't want to give him/her any access to any database .
Is that possible?
Thanks in advance
GiorgioWithout permission on database user cannot complete the execution of DTS package.|||I find out a workaround:

1) I created a user that has permission only on the sample DB Northwind.

2) The DTS package is protected by "DTS Owner password" and DTS User password

3) The "Connection" in the DTS package has inside a username and password that has acess to the appropriate DB.

With this workaround I obtained that:

1) The user has access only to the Northwind DB
2) The user can run only the DTS package (because is protected with DTS User password).

Thanks anyway to everyone who replied to me!|||Then my reply was intended to light when you said no permission to any database.

DTS Packages

Hi ,
I have some DTS packages to transfer data from text files on different
computers to SQL server tavles. Now I need information about these package
name, the source of the text files and output tables, I want to have table t
o
hole all these data. I am sure there is a way to do it. I looked
MSDB.sysTDSpackages and other related tables, but I still don't get the data
I need , any one here knows where are the DTS source and destinition
information stored?
Thanks .This isnt exactly what you're looking for but...
Save a Package as a Visual Basic File. Then have some program that reads
that file and grabs the info you need.
"Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
news:36F6A628-4931-4A56-AAEB-EB74B70386CB@.microsoft.com...
> Hi ,
> I have some DTS packages to transfer data from text files on different
> computers to SQL server tavles. Now I need information about these package
> name, the source of the text files and output tables, I want to have table
> to
> hole all these data. I am sure there is a way to do it. I looked
> MSDB.sysTDSpackages and other related tables, but I still don't get the
> data
> I need , any one here knows where are the DTS source and destinition
> information stored?
> Thanks .sqlsql

DTS Packages

Hi ,
I have some DTS packages to transfer data from text files on different
computers to SQL server tavles. Now I need information about these package
name, the source of the text files and output tables, I want to have table to
hole all these data. I am sure there is a way to do it. I looked
MSDB.sysTDSpackages and other related tables, but I still don't get the data
I need , any one here knows where are the DTS source and destinition
information stored?
Thanks .
This isnt exactly what you're looking for but...
Save a Package as a Visual Basic File. Then have some program that reads
that file and grabs the info you need.
"Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
news:36F6A628-4931-4A56-AAEB-EB74B70386CB@.microsoft.com...
> Hi ,
> I have some DTS packages to transfer data from text files on different
> computers to SQL server tavles. Now I need information about these package
> name, the source of the text files and output tables, I want to have table
> to
> hole all these data. I am sure there is a way to do it. I looked
> MSDB.sysTDSpackages and other related tables, but I still don't get the
> data
> I need , any one here knows where are the DTS source and destinition
> information stored?
> Thanks .

DTS package question

I have created one package and schedule for everday 11.00 pm. Which
processes some data and exports in text file. My problem is I can not export
into separate file. I would like to export into exportfile<yymmdd>.txt. How
can I do this?
one way to do that is to create a global variable that set the output file
name in yoru DTS parameter. Then generate a DTSRUN statement from a T-SQL
script and execute it using xp_cmdshell. To change the file name set your
global variable with the /L option on the DTSRUN command. The /L parameter
of course is generated with the T-SQL script.
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
"Sunny" <sunny_1178@.hotmail.com> wrote in message
news:uVobU1znEHA.3876@.TK2MSFTNGP15.phx.gbl...
> I have created one package and schedule for everday 11.00 pm. Which
> processes some data and exports in text file. My problem is I can not
export
> into separate file. I would like to export into exportfile<yymmdd>.txt.
How
> can I do this?
>
|||Thanks Gregory, but I am new to SQL server. Could you please advice me step
by step.
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:%23kmhI7znEHA.3868@.TK2MSFTNGP11.phx.gbl...
> one way to do that is to create a global variable that set the output file
> name in yoru DTS parameter. Then generate a DTSRUN statement from a T-SQL
> script and execute it using xp_cmdshell. To change the file name set your
> global variable with the /L option on the DTSRUN command. The /L
parameter
> of course is generated with the T-SQL script.
> --
> ----
--
> ----
--
> -
> Need SQL Server Examples check out my website
> http://www.geocities.com/sqlserverexamples
>
> "Sunny" <sunny_1178@.hotmail.com> wrote in message
> news:uVobU1znEHA.3876@.TK2MSFTNGP15.phx.gbl...
> export
> How
>
|||I changed my mind on what was easiest here. I think you should just use the
"Dynamic Properties Task". So do this:
1) Add a "Dynamic Properties Task" to your DTS package.
2) Click on the Dynamic Properties task you just added, and then click on
the "Add" button.
3) On the "Package Properties" page expand the "Connections" item. Find the
"Text File" connection that you want to change the output file based on the
run date. Then click ont the "Property Name" called "Data Source" (this is
the property the contains your file name). Next click on the "Set" button.
4) On "Add/Edit Assignment" page expand the Source pulldown. Select the
"Query" option. Now type the following query in the the "Query:" text box "
select 'c:\temp\' + convert(char(6),getdate(),12) + '.txt' " (without the
double quotes at the beginning and end.)
5) Click Ok and then OK again.
6) Now save your package and run it. Note you might have to run it twice to
make the dynamic property take.
Good luck.
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
"Sunny" <sunny_1178@.hotmail.com> wrote in message
news:%23af2PT0nEHA.3992@.TK2MSFTNGP15.phx.gbl...
> Thanks Gregory, but I am new to SQL server. Could you please advice me
step[vbcol=seagreen]
> by step.
> "Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
> news:%23kmhI7znEHA.3868@.TK2MSFTNGP11.phx.gbl...
file[vbcol=seagreen]
T-SQL[vbcol=seagreen]
your[vbcol=seagreen]
> parameter
> ----
> --
> ----
> --
exportfile<yymmdd>.txt.
>

DTS package question

I have created one package and schedule for everday 11.00 pm. Which
processes some data and exports in text file. My problem is I can not export
into separate file. I would like to export into exportfile<yymmdd>.txt. How
can I do this?one way to do that is to create a global variable that set the output file
name in yoru DTS parameter. Then generate a DTSRUN statement from a T-SQL
script and execute it using xp_cmdshell. To change the file name set your
global variable with the /L option on the DTSRUN command. The /L parameter
of course is generated with the T-SQL script.
--
----
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
"Sunny" <sunny_1178@.hotmail.com> wrote in message
news:uVobU1znEHA.3876@.TK2MSFTNGP15.phx.gbl...
> I have created one package and schedule for everday 11.00 pm. Which
> processes some data and exports in text file. My problem is I can not
export
> into separate file. I would like to export into exportfile<yymmdd>.txt.
How
> can I do this?
>|||Thanks Gregory, but I am new to SQL server. Could you please advice me step
by step.
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:%23kmhI7znEHA.3868@.TK2MSFTNGP11.phx.gbl...
> one way to do that is to create a global variable that set the output file
> name in yoru DTS parameter. Then generate a DTSRUN statement from a T-SQL
> script and execute it using xp_cmdshell. To change the file name set your
> global variable with the /L option on the DTSRUN command. The /L
parameter
> of course is generated with the T-SQL script.
> --
> ----
--
> ----
--
> -
> Need SQL Server Examples check out my website
> http://www.geocities.com/sqlserverexamples
>
> "Sunny" <sunny_1178@.hotmail.com> wrote in message
> news:uVobU1znEHA.3876@.TK2MSFTNGP15.phx.gbl...
> > I have created one package and schedule for everday 11.00 pm. Which
> > processes some data and exports in text file. My problem is I can not
> export
> > into separate file. I would like to export into exportfile<yymmdd>.txt.
> How
> > can I do this?
> >
> >
>|||I changed my mind on what was easiest here. I think you should just use the
"Dynamic Properties Task". So do this:
1) Add a "Dynamic Properties Task" to your DTS package.
2) Click on the Dynamic Properties task you just added, and then click on
the "Add" button.
3) On the "Package Properties" page expand the "Connections" item. Find the
"Text File" connection that you want to change the output file based on the
run date. Then click ont the "Property Name" called "Data Source" (this is
the property the contains your file name). Next click on the "Set" button.
4) On "Add/Edit Assignment" page expand the Source pulldown. Select the
"Query" option. Now type the following query in the the "Query:" text box "
select 'c:\temp\' + convert(char(6),getdate(),12) + '.txt' " (without the
double quotes at the beginning and end.)
5) Click Ok and then OK again.
6) Now save your package and run it. Note you might have to run it twice to
make the dynamic property take.
Good luck.
----
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
"Sunny" <sunny_1178@.hotmail.com> wrote in message
news:%23af2PT0nEHA.3992@.TK2MSFTNGP15.phx.gbl...
> Thanks Gregory, but I am new to SQL server. Could you please advice me
step
> by step.
> "Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
> news:%23kmhI7znEHA.3868@.TK2MSFTNGP11.phx.gbl...
> > one way to do that is to create a global variable that set the output
file
> > name in yoru DTS parameter. Then generate a DTSRUN statement from a
T-SQL
> > script and execute it using xp_cmdshell. To change the file name set
your
> > global variable with the /L option on the DTSRUN command. The /L
> parameter
> > of course is generated with the T-SQL script.
> > --
> >
> ----
> --
> ----
> --
> > -
> >
> > Need SQL Server Examples check out my website
> > http://www.geocities.com/sqlserverexamples
> >
> >
> > "Sunny" <sunny_1178@.hotmail.com> wrote in message
> > news:uVobU1znEHA.3876@.TK2MSFTNGP15.phx.gbl...
> > > I have created one package and schedule for everday 11.00 pm. Which
> > > processes some data and exports in text file. My problem is I can not
> > export
> > > into separate file. I would like to export into
exportfile<yymmdd>.txt.
> > How
> > > can I do this?
> > >
> > >
> >
> >
>

DTS package problem

Hi guys, I really have a strange problem that I have no where to look for
answer. I have a DTS that generate a text file with data queries from sql
server 2K then stores it in a local drive. The last step of that DTS is to
copy that file to a network drive using a batch file called by the Process
Task. The commands in the batch file is basically create a network drive,
copies the file over, then delete the source. The file gets copy over when
I manually run the whole package in Enterprise Manager at the DTS designer.
But this DTS package is scheduled to run once a week automatically. It runs
sucessfully during the scheduled time, the file is generated, but it doesn't
copy over to the network drive. I'm not sure why it doesn't do the copying.
In fact, all command in that batch file was executed but the copying part.
I don't think the problem is in the batch file b/c when I run the batch file
alone either by Execute Step or run in command line, the file gets copied
over. So I just don't get it why the file is not copying over when it's
being run at schedule time. Does this has anything to do with security?
Any comments is appreciated. thanks

WeiSound like a proxy acct problem. Please see if this article helps:
http://support.microsoft.com/?id=264155

--
-oj
http://www.rac4sql.net

"WKC" <wchen@.eibilling.com> wrote in message
news:Xm_Kb.64762$Cs3.8068955@.news4.srv.hcvlny.cv.n et...
> Hi guys, I really have a strange problem that I have no where to look for
> answer. I have a DTS that generate a text file with data queries from sql
> server 2K then stores it in a local drive. The last step of that DTS is to
> copy that file to a network drive using a batch file called by the Process
> Task. The commands in the batch file is basically create a network drive,
> copies the file over, then delete the source. The file gets copy over when
> I manually run the whole package in Enterprise Manager at the DTS designer.
> But this DTS package is scheduled to run once a week automatically. It runs
> sucessfully during the scheduled time, the file is generated, but it doesn't
> copy over to the network drive. I'm not sure why it doesn't do the copying.
> In fact, all command in that batch file was executed but the copying part.
> I don't think the problem is in the batch file b/c when I run the batch file
> alone either by Execute Step or run in command line, the file gets copied
> over. So I just don't get it why the file is not copying over when it's
> being run at schedule time. Does this has anything to do with security?
> Any comments is appreciated. thanks
> Wei|||"WKC" <wchen@.eibilling.com> wrote in message
news:Xm_Kb.64762$Cs3.8068955@.news4.srv.hcvlny.cv.n et...
> Hi guys, I really have a strange problem that I have no where to look for
> answer. I have a DTS that generate a text file with data queries from
sql
> server 2K then stores it in a local drive. The last step of that DTS is to
> copy that file to a network drive using a batch file called by the Process
> Task. The commands in the batch file is basically create a network
drive,
> copies the file over, then delete the source. The file gets copy over
when
> I manually run the whole package in Enterprise Manager at the DTS
designer.
> But this DTS package is scheduled to run once a week automatically. It
runs
> sucessfully during the scheduled time, the file is generated, but it
doesn't
> copy over to the network drive. I'm not sure why it doesn't do the
copying.
> In fact, all command in that batch file was executed but the copying part.
> I don't think the problem is in the batch file b/c when I run the batch
file
> alone either by Execute Step or run in command line, the file gets copied
> over. So I just don't get it why the file is not copying over when it's
> being run at schedule time. Does this has anything to do with security?
> Any comments is appreciated. thanks

Sounds very much like a security issue: access rights on the remote
machine drive directory need to be granted appropriately to the
service doing the writing. I'd firstly coodinated/check:

the ownership of the DTS step (and task),
the service SQL Agent login account, and
the remote access restrictions (other machine).

Good luck,

Pete Brown
Falls Creek
Oz

DTS Package Manipulations

Hi,

How do I delete out 4 statements contained within a text file, each followed by a newline which requires to be deleted also. I have already performed a number of transformations to structure the txt file and insert it into columns in a table but noticed the following text randomly appearing throughout the txt file; there are 3 other statements simular to this also:

res1 = 0, res2 = 0, res3 = 0, res4 = 0

Server Host = irea3nbu002 <-- delete
<-- delete
media_id = IAP004, partner_id = *NULL*, version = 1either use a staging (temp) table to import and delete from that table before copying to final table. or use a Activex step to open that text file with filesystemobject, read every line and correct it.

DTS Package issue

Hi,

I am trying to convert a .dbf file into text file, in which i have no issues. But the the dbf file changes every time i recieve the file, say for example currently i have the column name as cs_16,cs_17 etc for this month where as for the next month i would recieve the column names as cs_30,cs_31. Could any one please let me know how can i resolve this problem using the DTS Packages.

If any one could provide me with an example then it would be of great help.

Thanks & Regards
Mudasar

Quote:

Originally Posted by Mudasar

Hi,

I am trying to convert a .dbf file into text file, in which i have no issues. But the the dbf file changes every time i recieve the file, say for example currently i have the column name as cs_16,cs_17 etc for this month where as for the next month i would recieve the column names as cs_30,cs_31. Could any one please let me know how can i resolve this problem using the DTS Packages.

If any one could provide me with an example then it would be of great help.

Thanks & Regards
Mudasar

Hi there,

Unless you change column name manually according to the one specified in SQL database, it's going to take up a lot of time doing it. Good luck & Take care.

p.s/ Let see what others experts have to say on the above issue.

dts package errors

I am transferring data from a text file to a table in SQL Server. Is
there any way to export the dts package logs or a similar error log to
a text file. I need to know if the errors are duplicate keys or
missing files. I don't know if I need to write some code or what I
need to do. Any help'It largely depends on what type of transformation you're using. If you are
using the Transform Data Task then on the options tab of the task properties
you can set a filename for the "Exception file". If you uncheck the '7.0
format' option and select the other 3 check boxes you'll get an exception
file and 2 other files - each containing the erroring rows. If you are using
the BULK INSERT task then there is no such granular option. You could try
package logging (right-click on the white background, "Package Properties",
"Logging" tab and "Error file:" text box) but AFAIR you don't get much
useful info there.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||I have tried the exception file but if the file does not exist there is
no error written to that file. I will need to know this.
Paul Ibison wrote:
> It largely depends on what type of transformation you're using. If you are
> using the Transform Data Task then on the options tab of the task properties
> you can set a filename for the "Exception file". If you uncheck the '7.0
> format' option and select the other 3 check boxes you'll get an exception
> file and 2 other files - each containing the erroring rows. If you are using
> the BULK INSERT task then there is no such granular option. You could try
> package logging (right-click on the white background, "Package Properties",
> "Logging" tab and "Error file:" text box) but AFAIR you don't get much
> useful info there.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .