November 24, 2008 at 7:30 pm
Hi:
I'm sure this issue has been addressed before in this forum, I searched but didn't get any answers, so i'm posting my issue.
1. I installed the SQL 2000 designer component on a SQL Server 2005.
2. I imported an SQL 2000 DTS package (.dts file) and it appeared under the Legacy -> Data Transformation Services folder.
3. I opened and re-saved the package in SQL Server. It creates a comma-separated file on the local d:\ drive by reading previous day's data from a local db table. I was able to run it in the design view by clicking the green (execute) button on the toolbar. The package generated the csv file in the expected location on the d:\ drive.
4. Next I went to SQL Server Agent -> Jobs -> New Job. In the Steps tab, I selected “SQL Server Integration Services Package”. Then I clicked on the Package (...) button to select the package from step 3. I setup a schedule for the job.
5. To test, I right-clicked and selected "Start Job at Step" action. The job ran OK without any errors and a little information pop-up message-box indicated that it ran successfully. I checked the d:\ drive location and I saw no output file???
Any thoughts?
Thanks,
AJ.
November 25, 2008 at 9:16 am
When I try the steps you describe, I can't see any legacy DTS packages when I click the elipse. I only see SSIS packages, so I'm not sure how you were able to select a DTS package.
Try changing the job step type to "CmdExec" and put a DTSRUN command in the command space. That's how I've set up my jobs that run DTS packages.
Greg
November 25, 2008 at 12:00 pm
After importing the DTS packages, I right-click and choose "Migrate Package". That saves the package under Integration Services -> MSDB.
When I create the job, if I choose Type as "SSIS Package" and the Package Source as "SQL Server", when I click on the ellipsis, the packages appear under SSIS Packages. If I choose the Package Source as "SSIS Package Store", when I click on the ellipsis, the packages appear under SSIS Packages -> MSDB. In either case, the job does not give the expected result.
I took your approach and chose the CmdExec as the Type. I use this format:
dtsrun /Sserver_name /Uuser_nName /Ppassword /Npackage_name /Mpackage_password
and I'm able to get the expected results.
Thanks a lot Greg.
November 25, 2008 at 1:30 pm
Amit Joneja (11/25/2008)
After importing the DTS packages, I right-click and choose "Migrate Package".
Ah, omitting that bit in your original post is what threw me off. BTW, do you still see the DTS version of the package in the Management -> Legacy folder? I ask because I believe DTSRUN only works with DTS packages, not SSIS packages. DTEXEC is used to run SSIS packages.
So, I think your job is running the DTS package rather than the SSIS package.
Greg
November 25, 2008 at 1:57 pm
Greg Charles (11/25/2008)
Amit Joneja (11/25/2008)
After importing the DTS packages, I right-click and choose "Migrate Package".Ah, omitting that bit in your original post is what threw me off. BTW, do you still see the DTS version of the package in the Management -> Legacy folder? I ask because I believe DTSRUN only works with DTS packages, not SSIS packages. DTEXEC is used to run SSIS packages.
So, I think your job is running the DTS package rather than the SSIS package.
Yes, my bad, I didn't write how I got to seeing the DTS packages in SSIS. And yes, I see the DTS version of the package in the Legacy folder. You're correct - my job is running the DTS package - I specify the package name after the /N switch.
Thanks Greg.
February 4, 2009 at 9:29 pm
can you tell me what command i need to mention to execute a DTS package on SQL Server 2005.
management-->Legacy-->DTS--> test.dts (package)
let me know command i to mention to execute test.dts package
steve
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply