Author |
Topic |
freesoul
Starting Member
35 Posts |
Posted - 2007-08-10 : 09:57:53
|
Migrated Packages from DTS to SSIS...Tried running the jobs calling these DTS packages ....but getting error ......the step in job is :DTSRUN /S<servername> /U<username> /P<password> /N"ATL_Get_Down_Dates"giving error :Executed as user: USATLBNH\SYSTEM. DTSRun: Loading... Error: -2147220438 (8004042A); Provider Error: 0 (0) Error string: The specified Storage File contains multiple Packages; Loading requires a Name or Guid. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 713. Process Exit Code 1. The step failed.Help Required !!!!!Thanks in advance !!! |
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-08-10 : 10:29:03
|
>> The specified Storage File contains multiple Packages; Loading requires a Name or Guid.That's fairly clear.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
freesoul
Starting Member
35 Posts |
Posted - 2007-08-10 : 10:58:19
|
I have the DTS packages in the Data Transmission Service folder under LEGACY folder under MANAGEMENT and the Migrated SSIS packages for these DTS under MSDB under STORED PACKAGES in Integration Services As the Job step has DTSRUN \S<server_name> \U<username> \P<Password> \N "<Package_name>"which package is the step reffering to ??? do we need to delete the legacy DTS packages for this to reffer to SSIS packages?? |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-08-11 : 01:19:04
|
Don't delete legacy dts packages, they are stored in msdb as well. |
|
|
freesoul
Starting Member
35 Posts |
Posted - 2007-08-13 : 02:58:01
|
Then how will i know whether the step as i have mentioned above is refering to the DTS packages or the SSIS package,I need it to point to SSIS package ....and one more thing ....How do we find the GUID of a Package ?????? |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-08-13 : 22:35:53
|
You can create new job to run ssis package, and can get package id from msdb..sysdtspackages90. |
|
|
freesoul
Starting Member
35 Posts |
Posted - 2007-08-14 : 08:56:26
|
DTSRUN \S<server_name> \U<username> \P<Password> \V {"GUID"} \N "<Package_name>"DTExec \S<server_name> \U<username> \P<Password> \V {"GUID"} \DTS "<Package_name>" I tried running the job with above mentioned changes .....but still the same error msg is coming ..giving error :Executed as user: USATLBNH\SYSTEM. DTSRun: Loading... Error: -2147220438 (8004042A); Provider Error: 0 (0) Error string: The specified Storage File contains multiple Packages; Loading requires a Name or Guid. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 713. Process Exit Code 1. The step failed.As i have lots of jobs, so i cannot create a new job for all ....how to manupulate the existing jobs to run successfully ...Thanks for the reply . |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-08-14 : 22:53:36
|
How did you create those jobs? Or migrated from sql2k also? |
|
|
freesoul
Starting Member
35 Posts |
Posted - 2007-08-16 : 05:53:32
|
I made a change and it worked for few jobs , but i am stuck with others , another error has come up .....what i did was :DTExec \Ser <server_name> \U <username> \P <Password> \SQ "<Package_name>" but the new error i am facing is :Error: 2007-08-16 03:33:49.41 Code: 0xC0016016 Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.From Where and How to change the Protection level for Packages ?????once i have imported and migrated the DTS packages to SSIS ...and the migration is going from SQL Server 7.0 to SQL Server 2005.Thanks for the response. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-08-16 : 07:10:20
|
I would usually put passwords in a config file (or table) then you don't get encryption problems.How were you making the dts packages portable - that sort of thing usually used a lot of activex scripts in DTS but can be replaced by config entries in SSIS.Why are you holding the packages in msdb - it makes things a lot simpler to hold them as files in a folder. You can then look at the data in the packages without the designer and compare versions.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
freesoul
Starting Member
35 Posts |
Posted - 2007-08-16 : 08:19:52
|
Thanks for the reply Actually i first imported the DTS packages to SQL Server 2005 as they were used in SQL Server 7.0 earier and then migrated to SSIS, they were automatically stored in MSDB folder under integration services ....nyways is there any other way so that i can change the Protection level for Packages ?????or get rid of this error ?? |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-08-16 : 23:14:42
|
Looks like those package are password protected, may need recretate jobs. |
|
|
freesoul
Starting Member
35 Posts |
Posted - 2007-08-17 : 03:14:46
|
Finally ran that package .....I opened that package in the SQL Server BIDS and changed the ProtectionLevel property to "save package to SQL Server and use rely-on-server protection mode" and it worked , as a job step in a job....Thanks for all the replies |
|
|
freesoul
Starting Member
35 Posts |
Posted - 2007-12-14 : 06:01:20
|
Please tell me how to view the packages without designer in context to what you said , after storing them in the file system.quote: Originally posted by nr I would usually put passwords in a config file (or table) then you don't get encryption problems.How were you making the dts packages portable - that sort of thing usually used a lot of activex scripts in DTS but can be replaced by config entries in SSIS.Why are you holding the packages in msdb - it makes things a lot simpler to hold them as files in a folder. You can then look at the data in the packages without the designer and compare versions.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-12-14 : 22:35:37
|
Depends on file format saved. |
|
|
|