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
Showing posts with label file. Show all posts
Showing posts with label file. Show all posts
Thursday, March 29, 2012
DTS runs OK, but not scheduled job
Hi,
When I run DTS manually, it works fine. But when I run the scheduled job, it
failes.
The error said cannot find a file specified. It imports Excel file to
SQL2000 Server database. I set same domain user id for DTS creater and Agent
executer and job owner.
I read several articles same problem like this, but I haven't get solution...
Thank you,
Masako
Where is located the EXCEL File?
It should be located on server and not on the your workstation.
"Masako" <Masako@.discussions.microsoft.com> wrote in message
news:949CB8A5-FC52-4D2D-9208-F8FD750EE40C@.microsoft.com...
> Hi,
> When I run DTS manually, it works fine. But when I run the scheduled job,
it
> failes.
> The error said cannot find a file specified. It imports Excel file to
> SQL2000 Server database. I set same domain user id for DTS creater and
Agent
> executer and job owner.
> I read several articles same problem like this, but I haven't get
solution...
> --
> Thank you,
|||Hi Uri,
Does it have to? The Excel file is located on another server.
I had no problem like this job flow previous SQL Server. We used to Windows
NT server + SQL7, now new server is Windows2000 english version + SQL2000
Japanese version.
"Uri Dimant" wrote:
> Masako
> Where is located the EXCEL File?
> It should be located on server and not on the your workstation.
>
> "Masako" <Masako@.discussions.microsoft.com> wrote in message
> news:949CB8A5-FC52-4D2D-9208-F8FD750EE40C@.microsoft.com...
> it
> Agent
> solution...
>
>
|||Maskao
Make sure that SQL Server Agent is running under Domain Account not a Local
account.
"Masako" <Masako@.discussions.microsoft.com> wrote in message
news:CC48C0DC-A9F9-4E3A-8277-F6C55BF6C588@.microsoft.com...
> Hi Uri,
> Does it have to? The Excel file is located on another server.
> I had no problem like this job flow previous SQL Server. We used to
Windows[vbcol=seagreen]
> NT server + SQL7, now new server is Windows2000 english version + SQL2000
> Japanese version.
> "Uri Dimant" wrote:
job,[vbcol=seagreen]
When I run DTS manually, it works fine. But when I run the scheduled job, it
failes.
The error said cannot find a file specified. It imports Excel file to
SQL2000 Server database. I set same domain user id for DTS creater and Agent
executer and job owner.
I read several articles same problem like this, but I haven't get solution...
Thank you,
Masako
Where is located the EXCEL File?
It should be located on server and not on the your workstation.
"Masako" <Masako@.discussions.microsoft.com> wrote in message
news:949CB8A5-FC52-4D2D-9208-F8FD750EE40C@.microsoft.com...
> Hi,
> When I run DTS manually, it works fine. But when I run the scheduled job,
it
> failes.
> The error said cannot find a file specified. It imports Excel file to
> SQL2000 Server database. I set same domain user id for DTS creater and
Agent
> executer and job owner.
> I read several articles same problem like this, but I haven't get
solution...
> --
> Thank you,
|||Hi Uri,
Does it have to? The Excel file is located on another server.
I had no problem like this job flow previous SQL Server. We used to Windows
NT server + SQL7, now new server is Windows2000 english version + SQL2000
Japanese version.
"Uri Dimant" wrote:
> Masako
> Where is located the EXCEL File?
> It should be located on server and not on the your workstation.
>
> "Masako" <Masako@.discussions.microsoft.com> wrote in message
> news:949CB8A5-FC52-4D2D-9208-F8FD750EE40C@.microsoft.com...
> it
> Agent
> solution...
>
>
|||Maskao
Make sure that SQL Server Agent is running under Domain Account not a Local
account.
"Masako" <Masako@.discussions.microsoft.com> wrote in message
news:CC48C0DC-A9F9-4E3A-8277-F6C55BF6C588@.microsoft.com...
> Hi Uri,
> Does it have to? The Excel file is located on another server.
> I had no problem like this job flow previous SQL Server. We used to
Windows[vbcol=seagreen]
> NT server + SQL7, now new server is Windows2000 english version + SQL2000
> Japanese version.
> "Uri Dimant" wrote:
job,[vbcol=seagreen]
DTS runs OK, but not scheduled job
Hi,
When I run DTS manually, it works fine. But when I run the scheduled job, it
failes.
The error said cannot find a file specified. It imports Excel file to
SQL2000 Server database. I set same domain user id for DTS creater and Agent
executer and job owner.
I read several articles same problem like this, but I haven't get solution..
.
Thank you,Masako
Where is located the EXCEL File?
It should be located on server and not on the your workstation.
"Masako" <Masako@.discussions.microsoft.com> wrote in message
news:949CB8A5-FC52-4D2D-9208-F8FD750EE40C@.microsoft.com...
> Hi,
> When I run DTS manually, it works fine. But when I run the scheduled job,
it
> failes.
> The error said cannot find a file specified. It imports Excel file to
> SQL2000 Server database. I set same domain user id for DTS creater and
Agent
> executer and job owner.
> I read several articles same problem like this, but I haven't get
solution...
> --
> Thank you,|||Hi Uri,
Does it have to? The Excel file is located on another server.
I had no problem like this job flow previous SQL Server. We used to Windows
NT server + SQL7, now new server is Windows2000 english version + SQL2000
Japanese version.
"Uri Dimant" wrote:
> Masako
> Where is located the EXCEL File?
> It should be located on server and not on the your workstation.
>
> "Masako" <Masako@.discussions.microsoft.com> wrote in message
> news:949CB8A5-FC52-4D2D-9208-F8FD750EE40C@.microsoft.com...
> it
> Agent
> solution...
>
>|||Maskao
Make sure that SQL Server Agent is running under Domain Account not a Local
account.
"Masako" <Masako@.discussions.microsoft.com> wrote in message
news:CC48C0DC-A9F9-4E3A-8277-F6C55BF6C588@.microsoft.com...
> Hi Uri,
> Does it have to? The Excel file is located on another server.
> I had no problem like this job flow previous SQL Server. We used to
Windows[vbcol=seagreen]
> NT server + SQL7, now new server is Windows2000 english version + SQL2000
> Japanese version.
> "Uri Dimant" wrote:
>
job,[vbcol=seagreen]
When I run DTS manually, it works fine. But when I run the scheduled job, it
failes.
The error said cannot find a file specified. It imports Excel file to
SQL2000 Server database. I set same domain user id for DTS creater and Agent
executer and job owner.
I read several articles same problem like this, but I haven't get solution..
.
Thank you,Masako
Where is located the EXCEL File?
It should be located on server and not on the your workstation.
"Masako" <Masako@.discussions.microsoft.com> wrote in message
news:949CB8A5-FC52-4D2D-9208-F8FD750EE40C@.microsoft.com...
> Hi,
> When I run DTS manually, it works fine. But when I run the scheduled job,
it
> failes.
> The error said cannot find a file specified. It imports Excel file to
> SQL2000 Server database. I set same domain user id for DTS creater and
Agent
> executer and job owner.
> I read several articles same problem like this, but I haven't get
solution...
> --
> Thank you,|||Hi Uri,
Does it have to? The Excel file is located on another server.
I had no problem like this job flow previous SQL Server. We used to Windows
NT server + SQL7, now new server is Windows2000 english version + SQL2000
Japanese version.
"Uri Dimant" wrote:
> Masako
> Where is located the EXCEL File?
> It should be located on server and not on the your workstation.
>
> "Masako" <Masako@.discussions.microsoft.com> wrote in message
> news:949CB8A5-FC52-4D2D-9208-F8FD750EE40C@.microsoft.com...
> it
> Agent
> solution...
>
>|||Maskao
Make sure that SQL Server Agent is running under Domain Account not a Local
account.
"Masako" <Masako@.discussions.microsoft.com> wrote in message
news:CC48C0DC-A9F9-4E3A-8277-F6C55BF6C588@.microsoft.com...
> Hi Uri,
> Does it have to? The Excel file is located on another server.
> I had no problem like this job flow previous SQL Server. We used to
Windows[vbcol=seagreen]
> NT server + SQL7, now new server is Windows2000 english version + SQL2000
> Japanese version.
> "Uri Dimant" wrote:
>
job,[vbcol=seagreen]
DTS runs OK, but not scheduled job
Hi,
When I run DTS manually, it works fine. But when I run the scheduled job, it
failes.
The error said cannot find a file specified. It imports Excel file to
SQL2000 Server database. I set same domain user id for DTS creater and Agent
executer and job owner.
I read several articles same problem like this, but I haven't get solution...
--
Thank you,Masako
Where is located the EXCEL File?
It should be located on server and not on the your workstation.
"Masako" <Masako@.discussions.microsoft.com> wrote in message
news:949CB8A5-FC52-4D2D-9208-F8FD750EE40C@.microsoft.com...
> Hi,
> When I run DTS manually, it works fine. But when I run the scheduled job,
it
> failes.
> The error said cannot find a file specified. It imports Excel file to
> SQL2000 Server database. I set same domain user id for DTS creater and
Agent
> executer and job owner.
> I read several articles same problem like this, but I haven't get
solution...
> --
> Thank you,|||Hi Uri,
Does it have to? The Excel file is located on another server.
I had no problem like this job flow previous SQL Server. We used to Windows
NT server + SQL7, now new server is Windows2000 english version + SQL2000
Japanese version.
"Uri Dimant" wrote:
> Masako
> Where is located the EXCEL File?
> It should be located on server and not on the your workstation.
>
> "Masako" <Masako@.discussions.microsoft.com> wrote in message
> news:949CB8A5-FC52-4D2D-9208-F8FD750EE40C@.microsoft.com...
> > Hi,
> >
> > When I run DTS manually, it works fine. But when I run the scheduled job,
> it
> > failes.
> > The error said cannot find a file specified. It imports Excel file to
> > SQL2000 Server database. I set same domain user id for DTS creater and
> Agent
> > executer and job owner.
> >
> > I read several articles same problem like this, but I haven't get
> solution...
> >
> > --
> > Thank you,
>
>|||Maskao
Make sure that SQL Server Agent is running under Domain Account not a Local
account.
"Masako" <Masako@.discussions.microsoft.com> wrote in message
news:CC48C0DC-A9F9-4E3A-8277-F6C55BF6C588@.microsoft.com...
> Hi Uri,
> Does it have to? The Excel file is located on another server.
> I had no problem like this job flow previous SQL Server. We used to
Windows
> NT server + SQL7, now new server is Windows2000 english version + SQL2000
> Japanese version.
> "Uri Dimant" wrote:
> > Masako
> > Where is located the EXCEL File?
> > It should be located on server and not on the your workstation.
> >
> >
> > "Masako" <Masako@.discussions.microsoft.com> wrote in message
> > news:949CB8A5-FC52-4D2D-9208-F8FD750EE40C@.microsoft.com...
> > > Hi,
> > >
> > > When I run DTS manually, it works fine. But when I run the scheduled
job,
> > it
> > > failes.
> > > The error said cannot find a file specified. It imports Excel file to
> > > SQL2000 Server database. I set same domain user id for DTS creater and
> > Agent
> > > executer and job owner.
> > >
> > > I read several articles same problem like this, but I haven't get
> > solution...
> > >
> > > --
> > > Thank you,
> >
> >
> >
When I run DTS manually, it works fine. But when I run the scheduled job, it
failes.
The error said cannot find a file specified. It imports Excel file to
SQL2000 Server database. I set same domain user id for DTS creater and Agent
executer and job owner.
I read several articles same problem like this, but I haven't get solution...
--
Thank you,Masako
Where is located the EXCEL File?
It should be located on server and not on the your workstation.
"Masako" <Masako@.discussions.microsoft.com> wrote in message
news:949CB8A5-FC52-4D2D-9208-F8FD750EE40C@.microsoft.com...
> Hi,
> When I run DTS manually, it works fine. But when I run the scheduled job,
it
> failes.
> The error said cannot find a file specified. It imports Excel file to
> SQL2000 Server database. I set same domain user id for DTS creater and
Agent
> executer and job owner.
> I read several articles same problem like this, but I haven't get
solution...
> --
> Thank you,|||Hi Uri,
Does it have to? The Excel file is located on another server.
I had no problem like this job flow previous SQL Server. We used to Windows
NT server + SQL7, now new server is Windows2000 english version + SQL2000
Japanese version.
"Uri Dimant" wrote:
> Masako
> Where is located the EXCEL File?
> It should be located on server and not on the your workstation.
>
> "Masako" <Masako@.discussions.microsoft.com> wrote in message
> news:949CB8A5-FC52-4D2D-9208-F8FD750EE40C@.microsoft.com...
> > Hi,
> >
> > When I run DTS manually, it works fine. But when I run the scheduled job,
> it
> > failes.
> > The error said cannot find a file specified. It imports Excel file to
> > SQL2000 Server database. I set same domain user id for DTS creater and
> Agent
> > executer and job owner.
> >
> > I read several articles same problem like this, but I haven't get
> solution...
> >
> > --
> > Thank you,
>
>|||Maskao
Make sure that SQL Server Agent is running under Domain Account not a Local
account.
"Masako" <Masako@.discussions.microsoft.com> wrote in message
news:CC48C0DC-A9F9-4E3A-8277-F6C55BF6C588@.microsoft.com...
> Hi Uri,
> Does it have to? The Excel file is located on another server.
> I had no problem like this job flow previous SQL Server. We used to
Windows
> NT server + SQL7, now new server is Windows2000 english version + SQL2000
> Japanese version.
> "Uri Dimant" wrote:
> > Masako
> > Where is located the EXCEL File?
> > It should be located on server and not on the your workstation.
> >
> >
> > "Masako" <Masako@.discussions.microsoft.com> wrote in message
> > news:949CB8A5-FC52-4D2D-9208-F8FD750EE40C@.microsoft.com...
> > > Hi,
> > >
> > > When I run DTS manually, it works fine. But when I run the scheduled
job,
> > it
> > > failes.
> > > The error said cannot find a file specified. It imports Excel file to
> > > SQL2000 Server database. I set same domain user id for DTS creater and
> > Agent
> > > executer and job owner.
> > >
> > > I read several articles same problem like this, but I haven't get
> > solution...
> > >
> > > --
> > > Thank you,
> >
> >
> >
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!
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 run from .NET
My dts package has to pick up a file which is in a mapped drive of the
sql server... (system1)...
when I run it from code the execution ends up in an error... but if I
have the drive mapped (drive where I have the source file) onto the
web server (system2) the execution goes through fine.. I donot want to
have the drive mapped onto the web server.. how can I do this..Try and use the UNC path.
sql server... (system1)...
when I run it from code the execution ends up in an error... but if I
have the drive mapped (drive where I have the source file) onto the
web server (system2) the execution goes through fine.. I donot want to
have the drive mapped onto the web server.. how can I do this..Try and use the UNC path.
Joel Scavone|||I did use a full netwok path and even did add identity impersonate in
the web.config... but does not help..
The package runs fine when I register the sql server my machine and run
the package..
Sigh! Don't know what wrong
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
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?
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?
Tuesday, March 27, 2012
DTS Restore from a file
We have a production server and a bunch of development servers. The
Production server is located in a NOC (Network Operations Center)
facility and is physically unaccessible on a regular basis. There are
DTS packages on the production server which we'd like to pull down to
our Dev servers.
Production server is located in a NOC (Network Operations Center)
facility and is physically unaccessible on a regular basis. There are
DTS packages on the production server which we'd like to pull down to
our Dev servers.
I was able to save the DTS package to a file and downloaded it to our
Dev Servers. But how in the world do I restore it?? I'm a fairly
technical guy and for the life of me, I'm not able to figure this one
out! How do I restore the DTS package which has been stored into a
..dts file?
Please help.
Thank you in advance,
-Umar.I think what you want to know is this:
Right-click on the "Data Transformation Services" FOLDER icon and select
"Open Package..." from the options. This brings up a dialog box to hunt for
your DTS file(s).
"Umar Farooq" <UmarAlFarooq@.gmail.com> wrote in message
news:1110475810.551932.46600@.f14g2000cwb.googlegro ups.com...
> We have a production server and a bunch of development servers. The
> Production server is located in a NOC (Network Operations Center)
> facility and is physically unaccessible on a regular basis. There are
> DTS packages on the production server which we'd like to pull down to
> our Dev servers.
> I was able to save the DTS package to a file and downloaded it to our
> Dev Servers. But how in the world do I restore it?? I'm a fairly
> technical guy and for the life of me, I'm not able to figure this one
> out! How do I restore the DTS package which has been stored into a
> .dts file?
> Please help.
> Thank you in advance,
> -Umar.
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
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 - copy table from SQL server to non-SQL server
I'm breaking into SQL 2000, and have completed a DTS package that imports and massages a file and creates a table with about 200M records.
Now I want to add a process that will export the table to a directory on a Windows 2000 server, in a format such as Access, dBase, or someting else, to make the table available for users who do not have assces to SQL 2000.
I'm having a problem deciding which DTS 'Task' should be used. I've tried several, but haven't stumbled on the correct one, or if I have, haven't realized it because of error messages.
Any tips to get me started will be appreciated.
Thanks,
RandyFirst, add a connection object that specifies your preferred destination (Access, text, Excel, whatever). Then, holding down your CTRL key, select first the source connection (your server), then click on the destination connection (the one you just added). Next click on the data pump task (aka Transform Data Task; it's the black arrow pointing to the yellow...er...thingy).
Then define your source query (or table), your destination and any mapping changes you want to make.
Best of luck,
hmscott
...or, contact Brett Kaiser about how to do a BCP... ;-)sqlsql
Now I want to add a process that will export the table to a directory on a Windows 2000 server, in a format such as Access, dBase, or someting else, to make the table available for users who do not have assces to SQL 2000.
I'm having a problem deciding which DTS 'Task' should be used. I've tried several, but haven't stumbled on the correct one, or if I have, haven't realized it because of error messages.
Any tips to get me started will be appreciated.
Thanks,
RandyFirst, add a connection object that specifies your preferred destination (Access, text, Excel, whatever). Then, holding down your CTRL key, select first the source connection (your server), then click on the destination connection (the one you just added). Next click on the data pump task (aka Transform Data Task; it's the black arrow pointing to the yellow...er...thingy).
Then define your source query (or table), your destination and any mapping changes you want to make.
Best of luck,
hmscott
...or, contact Brett Kaiser about how to do a BCP... ;-)sqlsql
DTS question
I have package that Extracts a zip file. Now the zip file is updated bi-weekly between 9-11am. So the DTS package is schedule to run at 9am. Once the zip is extracted it is renamed (different from original).
But before extraction, i have a few tasks that check whether the zip exists or not.
PROBLEM: If the zip new file exists at 9am, it's all good and the package will be successful. The problem i'm facing is, say the zip file is not available at 9am, I want the package to check if the file exists - every x minutes till 11am.
This polling for the file is creating a alot of problems for me.
Any help/advise would be appreciated.
ThanksHello!
I assume that you have an ActiveXTask as one of the first steps in that DTS Package, that checks the existence of the ZIP-File.
If you make this task loop until it finds the ZIP-File, that would be the first step. In this loop there should be a command that calls a VBScript with something like "WScript.Sleep 600000" (which should stop the script from continuing for 10 minutes!).
Hope that gave you some hints!
Greetings,
Carsten
Originally posted by vmlal
I have package that Extracts a zip file. Now the zip file is updated bi-weekly between 9-11am. So the DTS package is schedule to run at 9am. Once the zip is extracted it is renamed (different from original).
But before extraction, i have a few tasks that check whether the zip exists or not.
PROBLEM: If the zip new file exists at 9am, it's all good and the package will be successful. The problem i'm facing is, say the zip file is not available at 9am, I want the package to check if the file exists - every x minutes till 11am.
This polling for the file is creating a alot of problems for me.
Any help/advise would be appreciated.
Thanks
But before extraction, i have a few tasks that check whether the zip exists or not.
PROBLEM: If the zip new file exists at 9am, it's all good and the package will be successful. The problem i'm facing is, say the zip file is not available at 9am, I want the package to check if the file exists - every x minutes till 11am.
This polling for the file is creating a alot of problems for me.
Any help/advise would be appreciated.
ThanksHello!
I assume that you have an ActiveXTask as one of the first steps in that DTS Package, that checks the existence of the ZIP-File.
If you make this task loop until it finds the ZIP-File, that would be the first step. In this loop there should be a command that calls a VBScript with something like "WScript.Sleep 600000" (which should stop the script from continuing for 10 minutes!).
Hope that gave you some hints!
Greetings,
Carsten
Originally posted by vmlal
I have package that Extracts a zip file. Now the zip file is updated bi-weekly between 9-11am. So the DTS package is schedule to run at 9am. Once the zip is extracted it is renamed (different from original).
But before extraction, i have a few tasks that check whether the zip exists or not.
PROBLEM: If the zip new file exists at 9am, it's all good and the package will be successful. The problem i'm facing is, say the zip file is not available at 9am, I want the package to check if the file exists - every x minutes till 11am.
This polling for the file is creating a alot of problems for me.
Any help/advise would be appreciated.
Thanks
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
sqlsqlDTS Question
I have a DTS job that exports into a tab-delimited file. The tab delimiting
is working great BUT it also using quotes around the fields as well. (I
assume because they are string vars). Can this quoting "feature" be turned
off?
is working great BUT it also using quotes around the fields as well. (I
assume because they are string vars). Can this quoting "feature" be turned
off?
ThanksThank you, Ray. That did it!
"Ray Higdon" <rayhigdon@.higdonconsulting.com> wrote in message
news:3f280abb$0$197$75868355@.news.frii.net...
> Yes, open the connection to your text file in dts designer and go to
> properties, there you will be able to specify if you want double-quotes
> as a text qualifier,row-delimiters, column delimiters, etc.
> HTH
> Ray Higdon MCSE, MCDBA, CCNA
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
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.
Sunday, March 25, 2012
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
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
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
Hi
I am trying to invoke a dts from asp.net and was informed that I need to create a name key pair file and runtime callable wrapper...which consiste sof changing the command line...but I cannot fine the files. I am using web matrix...which I think is the problem.
Has anyone found the same problems...I am looking for the Bin directory in web matrix and tlbimp.exe.
Any help would really be appreciated.
ThanksIt sounds to me like you are trying to do things the complicated way.
Are you wanting to create a new dts with your asp.Net or just execute an existing dts package?
There are plently of stored procedures you can call via standard ADO to excute existing pacakges, if that's what you want to do.|||Hi
Thanks for your reply...I am just trying to invoke a dts from an asp.net web page...I have searched the internet but cannot find any information on how to do this in vb.
Do you know of any good sites?
Thanks|||But it is an existing DTS package right?|||yeah it is
Thanks|||Well the way I normally run a dts package from asp (and it will work from asp.Net as well) is to create a job to execute the package and then (whenever I want to run it) I make an ADO call to the database using sp_start_job.
Now I know there is another stored proc or something that other people use to run the specific dts (and thus you don't need a job) but I can't remember what it was.
If you want to run things from the command line, why not use dtsrun to execute your package?|||Hi
Thanks for the reply...I have found asp code to run the dts...do you know what asp.net uses instead of the 'set'
Thanks|||just in clude a reference to the component in question, then you should be able to just declare the varible the same way you would any other variable.
set in asp is only used for objects, in theory everything in asp.Net is an object so you don't need to diffientate
so if you are using c# something like... (note it's only like this)
using Data.OLEDB;
will set you reference then.
connection myConn = blah blah blah;
will create your connection object.
HTHsqlsql
I am trying to invoke a dts from asp.net and was informed that I need to create a name key pair file and runtime callable wrapper...which consiste sof changing the command line...but I cannot fine the files. I am using web matrix...which I think is the problem.
Has anyone found the same problems...I am looking for the Bin directory in web matrix and tlbimp.exe.
Any help would really be appreciated.
ThanksIt sounds to me like you are trying to do things the complicated way.
Are you wanting to create a new dts with your asp.Net or just execute an existing dts package?
There are plently of stored procedures you can call via standard ADO to excute existing pacakges, if that's what you want to do.|||Hi
Thanks for your reply...I am just trying to invoke a dts from an asp.net web page...I have searched the internet but cannot find any information on how to do this in vb.
Do you know of any good sites?
Thanks|||But it is an existing DTS package right?|||yeah it is
Thanks|||Well the way I normally run a dts package from asp (and it will work from asp.Net as well) is to create a job to execute the package and then (whenever I want to run it) I make an ADO call to the database using sp_start_job.
Now I know there is another stored proc or something that other people use to run the specific dts (and thus you don't need a job) but I can't remember what it was.
If you want to run things from the command line, why not use dtsrun to execute your package?|||Hi
Thanks for the reply...I have found asp code to run the dts...do you know what asp.net uses instead of the 'set'
Thanks|||just in clude a reference to the component in question, then you should be able to just declare the varible the same way you would any other variable.
set in asp is only used for objects, in theory everything in asp.Net is an object so you don't need to diffientate
so if you are using c# something like... (note it's only like this)
using Data.OLEDB;
will set you reference then.
connection myConn = blah blah blah;
will create your connection object.
HTHsqlsql
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? :)
'************************************************* *********************
' 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.
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.
Thursday, March 22, 2012
DTS Parsing Delimited Record
I am running SQLServer 2000 and am parsing a delimited file. Each line
is delimited with a crlf, but each line also ends with a tilda (~) that
I do not want to process. Each line does not have the same number of
elements, but every line ends with a tilda. Is there a way to set the
input file parser to strip off the tilda (that is, define the end of
line marker to be ~+cr+lf ?
Thanks,
greg lindstromhi,
why don't use each line as 'fixed field' from the beginning till the end and
then, using VbScript and substring do what you want.
--
current location: alicante (es)
"tuba_ranger" wrote:
> I am running SQLServer 2000 and am parsing a delimited file. Each line
> is delimited with a crlf, but each line also ends with a tilda (~) that
> I do not want to process. Each line does not have the same number of
> elements, but every line ends with a tilda. Is there a way to set the
> input file parser to strip off the tilda (that is, define the end of
> line marker to be ~+cr+lf ?
> Thanks,
> greg lindstrom
>
is delimited with a crlf, but each line also ends with a tilda (~) that
I do not want to process. Each line does not have the same number of
elements, but every line ends with a tilda. Is there a way to set the
input file parser to strip off the tilda (that is, define the end of
line marker to be ~+cr+lf ?
Thanks,
greg lindstromhi,
why don't use each line as 'fixed field' from the beginning till the end and
then, using VbScript and substring do what you want.
--
current location: alicante (es)
"tuba_ranger" wrote:
> I am running SQLServer 2000 and am parsing a delimited file. Each line
> is delimited with a crlf, but each line also ends with a tilda (~) that
> I do not want to process. Each line does not have the same number of
> elements, but every line ends with a tilda. Is there a way to set the
> input file parser to strip off the tilda (that is, define the end of
> line marker to be ~+cr+lf ?
> Thanks,
> greg lindstrom
>
Subscribe to:
Posts (Atom)