April 19, 2005 at 2:08 am
Hello everybody.
I created a DTS package which read a csv file and dump the data into a table in the database.
The package runs without any errors when i manually execute the DTS Package.
When i schedule the package,it gives me the error:
DTSRun: Loading... Error: -2147217900 (80040E14); Provider Error: 14262 (37B6) Error string: The specified DTS Package ('Name = 'DataDump'; ID.VersionID = {[not specified]}.{[not specified]}') does not exist. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0. Process Exit Code 1. The step failed.
Please help me out and suggest what are the settings that i should do while reading a csvfile.
I selected text file-source as the data source.
Thanks in advance.
April 19, 2005 at 3:31 am
open a cmd window,
type "dtsrunui"
select server and dts package
run the package correctly
then click generate command button
Copy the line and paste into your batch file. It should be fine.
My Blog:
April 19, 2005 at 9:06 am
Hi Dinesh i tried this.Still it gives the error.
I think it has something to do with the opening of csv file.
Any security setting that i need to setup??
Please help.
Thanks in advance.
April 19, 2005 at 9:20 am
Hello frnds,
I get the following error after considering the Dinesh's idea.
DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_4 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_4 DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string: Error opening datafile: Access is denied. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 Error Detail Records: Error: 5 (5); Provider Error: 5 (5) Error string: Error opening datafile: Access is denied. 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.
pls help.
April 19, 2005 at 1:27 pm
You need to check a no. of things.
1. Check if you have, Set package on first error.
2. Check the contents of the csv file.
It could be possible, that it is having extra (or) less columns than required by the target table. Therefore a data pump task is failling.
3. Check to see if the CSV file is having any header.The DTS package should be configured accordingly.
4. Your flat-file data provider may have been corrupted. Try to use a .udl connection or a ODBC connection to the excel file instead.
April 20, 2005 at 12:35 am
April 20, 2005 at 1:42 am
Also, another point, don't forget that when you run the DTS package manually it runs under YOUR security context i.e. the credentials under which you registered the sql server. When it is scheduled it runs under the account that the SQL Server Agent service is set up to run under. If this is not a domain account or has insufficient priviledges to access the file you will received this error. Try putting the file locally to the database server e.g. C:\, and change the path in the dts package to point to C:\myfile.csv and test that. If that works then you know it is simply an access problem on the part of the SQL Server Agent service.
If any of this doesn't make sense, let me know.
Cheers
Dan
April 20, 2005 at 3:09 am
I have this exact same problem as well, but my data is coming from a text file. The thing that is really boggling me is that my DTS script will run to over 70% of the time it takes to execute the job, i.e. 70% of the data is being inputted into the db so I cant see it being a problem with permissions(in my case anyway). Anymore suggestions would be much appreciated.
M
April 20, 2005 at 4:51 am
Hello noggin,
i tried by putting the file in different location.
Now i get some other error.
Please help me if i need to change some setting in the Schedule Job.
... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_4 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_4 DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 1000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 1000 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 2000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 2000 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 3000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 3000 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 4000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 4000 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 5000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 5000 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 6000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 6000 ... Process Exit Code 1. The step failed.
Also thanks for megana,kamal and maertean for u r ideas.
Please help me in solving the above error.
Thanks in advance.
April 20, 2005 at 5:01 am
Syed,
From the trace it seems you are successfully importing data until sometime after row 6000. I need more information on the error to help you find out what this is. It could potentially be a number of things including bad data in the source file i.e. an incompatible value for your destination table. Could be text in a numeric column, a text value that's too long for the column you're importing into, an invalid date for a smalldatetime/datetime column etc.
If you have a look at the package logs and double click the failure message it should give you more information as to why that step failed. If you can find that after you have investigated compatability with your destination table, post it and I'll have a look.
Regards
April 20, 2005 at 5:15 am
Syed
Also try to write your error rows to an exception file so that you can analyse if the problem lies with your source file.
you can do this by doing the following
Go to options tab in the Transform Data task properties
1. Uncheck File 7.0 format
2. Check the Error text , Source Error rows, dest error rows
3. give a name to the exception file.
by looking at the exception file , you might know why the package is failing (if it is some data issue)
Regards
Meghana
April 20, 2005 at 5:33 am
Hello noggin,
This is the error loged in the package log.
Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:Logon failed: MapiLogonEx Failed due to MAPI error 273: MAPI Logon failed.
Step Error code: 80040480
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100
please advice.
Thanks
April 20, 2005 at 7:09 am
Syed,
It now sounds like the reason it works from your machine but fails with the Access Denied when scheduled and now with the MAPI error is as I said the account under which the SQL Agent account is running is not set up as a domain account i.e. it has no access to your network or an email account.
Get your Exchange (or whatever email system you use) adminstrator to ensure there is an email account for the DOMAIN account the SQL Agent service is set to run under.
April 20, 2005 at 7:18 am
Hello noggin,
My package works fine with sheduled job.
I removed the mail task that i had in the package and changed the location of the file.
But still help me how to over come this(mail) problem.
when i use mail task,i get this error.
Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:Logon failed: MapiLogonEx Failed due to MAPI error 273: MAPI Logon failed.
Step Error code: 80040480
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100
Thanks a lot to all of u...noggin,megana,kamal and all..
April 20, 2005 at 7:51 am
You need to check the SQL Mail set up. i.e.
Right click the SQL Mail item under Support Services in Enterprise Manager. What is the Profile Name that is set up ? This profile should be associated with the user account that the MSSQLServer service runs under on the server. You can specify an alternative profile to use on the Send Mail DTS task, under Properties, but this also has to be set up on the server and the associated user account the server services run under.
For example, when you go to Control Panel, Mail, Show Profiles you should see YOUR windows account name. The MSSQLServer service also needs a windows account just like you to run, access network resources, and send emails. Check the account the services run under and their associated email accounts.
The following link may also provide a useful description
http://databasejournal.com/features/mssql/article.php/3109191
under the Send Mail task heading.
Best of luck
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply