executing dts package with SP 1

[Pattycake245]

I have set up a DTS package which when executed through enterprise manager works fine. When I try and run it in QA I get the follwoing errors:

output DTSRun: Loading...

DTSRun: Executing...

DTSRun OnStart: DTSStep_DTSDataPumpTask_1

DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147217887 (80040E21)

Error string: Error opening datafile: The system cannot find the path specified.



Error source: Microsoft Data Transformation Services Flat File Rowset Provider

Help file: DTSFFile.hlp

Help context: 0



Error Detail Records:



Error: 3 (3); Provider Error: 3 (3)

Error string: Error opening datafile: The system cannot find the path specified.



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.


(21 row(s) affected)


My syntax is as follows:

exec master..xp_cmdshell 'dtsrun /S SERVERNAME /N PACKAGE NAME /E'

I have limited knowledge of running DTS's so if someone can interpret these erroRs and point me in the right direction that would be greatly appreciated. If I am reading part of the message right, "Error opening datafile: The system cannot find the path specified.", does this mean it cannot find the source file? If so, how can this be since it runs through EM. Am I missing something?

thanks, Tim

 

[mrdenny]

Make sure that any file paths in the DTS package exist on the server, and that the account you are logged in as has access to those folders.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[Pattycake245]

Ahhhhh, the paths when compared to an existing package were incorrect. I have them working beautifully now.

thanks Denny

 

[mrdenny]

no problem. Glad it worked.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[Pattycake245]

One other question, when executing these packages is there a way to filter out rows? I.e. if I only want records that match certain criteria to be imported, can this be added to the package somehow?

Tim

 

[mrdenny]

You would need to alter the data transformation within the DTS package.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[Pattycake245]

Can't seem to figure out where to do that. I basically have a layout of text file (source) joined to OLE DB Provider for SQL Server. Do I need to add something else?

 

[mrdenny]

The data transformation is the black line from the text file (source) to the OLE DB Profider.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[Pattycake245]

Yes, I am aware of that. There is a spot where I can look at the individual columns and it will say something like

"Copy the contents of the source data unchanged to the destination."

But nowhere can I find to change this.

 

[mrdenny]

Wait, your using a text file as the source. That makes it a lot harder. I think that there is a way to do it, but I'm not positive. I beleive that your best bet would be to load the data into a tempory table then put the data into the production table doing the filtering within the database.

It will probably be faster to do the filtering within the database then within the text file anyway.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[Pattycake245]

OK. That is currently the way I am doing it. I thought if there was some other way to do it to speed it up I would go that route, but this ios fairly quick anyway.

thanks