January 12, 2009 at 11:25 am
In a procedure I'm writing, I want to test for the existence of an SSIS package to make sure it exists before the data is imported from a flat file to a SQL table.
I would have thought this would work (this is what I used in SQL 2000):
select * from msdb..sysdtspackages where name = 'name of ssis package'
However, this isn't finding the package that's in place and which I can run manually.
Any thoughts?
Thank you.
Rog
January 12, 2009 at 11:34 am
If it's an SQL 2005 SSIS package, it gets stored in sysdtspackages90, not in sysdtspackages. Try there.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 12, 2009 at 11:56 am
Perfect! That did the trick... however I can't get it to run in the stored proc.. that is, actually execute the package.
Here's what I'm using:
Declare @dtscmd varchar(400) <- one of several variables assigned at the beginning
select @dtscmd = 'dtexec /sq (name of SSIS package) /ser (server name) /U sa /P (password)'
EXEC master..xp_cmdshell @dtscmd, NO_OUTPUT
Thank you.
Roger
January 12, 2009 at 12:02 pm
Does it give you an error, or just do nothing at all? Have you tried running it without the "no_output" option to see what that tells you?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 12, 2009 at 12:35 pm
I do get some errors and not sure what they all mean:
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.3042.00 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
NULL
Started: 2:25:06 PM
Error: 2009-01-12 14:25:06.65
Code: 0xC0016016
Source:
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.
End Error
Progress: 2009-01-12 14:25:06.80
Source: Data Flow Task
Validating: 0% complete
End Progress
Error: 2009-01-12 14:25:06.82
Code: 0xC0202009
Source: CSYS_GL_FILE_IMPORTER Connection manager "DestinationConnectionOLEDB"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'sa'.".
End Error
Error: 2009-01-12 14:25:06.82
Code: 0xC020801C
Source: Data Flow Task Destination - CSYS_GL_FILE_CONVERSION [50]
Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "DestinationConnectionOLEDB" failed with error code 0xC0202009. There may be error messages posted before thi
s with more information on why the AcquireConnection method call failed.
End Error
Error: 2009-01-12 14:25:06.82
Code: 0xC0047017
Source: Data Flow Task DTS.Pipeline
Description: component "Destination - CSYS_GL_FILE_CONVERSION" (50) failed validation and returned error code 0xC020801C.
End Error
Progress: 2009-01-12 14:25:06.82
Source: Data Flow Task
Validating: 50% complete
End Progress
Error: 2009-01-12 14:25:06.82
Code: 0xC004700C
Source: Data Flow Task DTS.Pipeline
Description: One or more component failed validation.
End Error
Error: 2009-01-12 14:25:06.82
Code: 0xC0024107
Source: Data Flow Task
Description: There were errors during task validation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 2:25:06 PM
Finished: 2:25:06 PM
Elapsed: 0.297 seconds
NULL
MSG
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
NULL
Rog
January 12, 2009 at 12:39 pm
Those mean that either you have the wrong password for the sa account (possible), or that the package was saved with encryption. When you save an SSIS package from BIDS, it gives you options with regard to encryption and security. I usually use the last one (rely on server security).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 12, 2009 at 12:55 pm
Many many thanks!
That did the trick.
Roger
January 12, 2009 at 12:55 pm
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply