Problem with SSIS 2012
-
Hello everyone, I have a problem with my SSIS 2012, if I put in made a OLDBE Source with a complex query or composite sample AS WITH TT ( SELECT DISTINCT e.UniqueID e. [Web Id] from Elements e) SELECT DISTINCT * 'Class' + CAST (ROW_NUMBER () OVER (PARTITION BY [Web Id] ORDER BY [class] DESC) AS NVARCHAR) AS ClassNum from TT I have this error: Exception From HRESULT: 0xC0202009 Error at Get Products SSIS Error Code DTS_E_OLEBDERROR. A OLE DB error has occured. Error code: 0x80004005 An OLE DB record is available. Source "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Syntax Error, permission violation, or nonspecific Reviews another error" see Exhibit attached Note that this same package works wonders on my colleague's machine with vs 2010 I do not understand the error!!
Is your package running as a SQL Agent job? You might need to change the permissions on the TEMP directory: http://support.microsoft.com/kb/933835[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
Hello everyone, I have a problem with my SSIS 2012, if I put in made a OLDBE Source with a complex query or composite sample AS WITH TT ( SELECT DISTINCT e.UniqueID e. [Web Id] from Elements e) SELECT DISTINCT * 'Class' + CAST (ROW_NUMBER () OVER (PARTITION BY [Web Id] ORDER BY [class] DESC) AS NVARCHAR) AS ClassNum from TT I have this error: Exception From HRESULT: 0xC0202009 Error at Get Products SSIS Error Code DTS_E_OLEBDERROR. A OLE DB error has occured. Error code: 0x80004005 An OLE DB record is available. Source "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Syntax Error, permission violation, or nonspecific Reviews another error" see Exhibit attached Note that this same package works wonders on my colleague's machine with vs 2010 I do not understand the error!!
amioni wrote:
e.UniqueID e. [Web Id] from Elements
I am not seeing a comma between the two columns you reference, is that correct?
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
amioni wrote:
e.UniqueID e. [Web Id] from Elements
I am not seeing a comma between the two columns you reference, is that correct?
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
Is your package running as a SQL Agent job? You might need to change the permissions on the TEMP directory: http://support.microsoft.com/kb/933835[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
i run it from VS2012 so i dont know if it s with sql agent or not (beginner ) and where i can find Temp directory
If you're running it from Visual Studio, then it's not using SQL Agent, so that's probably not the problem. There seem to be lots of potential causes for that error message:
- Having
RetainSameConnectionset toTrueon your OLEDB source[^]; - Out-of-date metadata in the package[^];
- Having
Persist Security Info=Falsein your connection string[^]. - Output parameters[^];
- Etc.[^];
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
- Having
-
If you're running it from Visual Studio, then it's not using SQL Agent, so that's probably not the problem. There seem to be lots of potential causes for that error message:
- Having
RetainSameConnectionset toTrueon your OLEDB source[^]; - Out-of-date metadata in the package[^];
- Having
Persist Security Info=Falsein your connection string[^]. - Output parameters[^];
- Etc.[^];
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
- Having
-
Hello everyone, I have a problem with my SSIS 2012, if I put in made a OLDBE Source with a complex query or composite sample AS WITH TT ( SELECT DISTINCT e.UniqueID e. [Web Id] from Elements e) SELECT DISTINCT * 'Class' + CAST (ROW_NUMBER () OVER (PARTITION BY [Web Id] ORDER BY [class] DESC) AS NVARCHAR) AS ClassNum from TT I have this error: Exception From HRESULT: 0xC0202009 Error at Get Products SSIS Error Code DTS_E_OLEBDERROR. A OLE DB error has occured. Error code: 0x80004005 An OLE DB record is available. Source "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Syntax Error, permission violation, or nonspecific Reviews another error" see Exhibit attached Note that this same package works wonders on my colleague's machine with vs 2010 I do not understand the error!!
Always read the error message - Syntax Error, permission violation, or nonspecific Reviews another error There is either: An error in the sql - you can check this by running the sql in isolation. A permission issue - harder to track, I would test this by creating a simple SSIS package and try to run it from your code. Non specific error - eliminate the first two possibilities then look into this.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
Always read the error message - Syntax Error, permission violation, or nonspecific Reviews another error There is either: An error in the sql - you can check this by running the sql in isolation. A permission issue - harder to track, I would test this by creating a simple SSIS package and try to run it from your code. Non specific error - eliminate the first two possibilities then look into this.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
NB : when i make this query in Stored procedure and i call it in the oledb source it work fine but not when i make it directly
I don't understand what that means - I will need more information to be able to help you.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
NB : when i make this query in Stored procedure and i call it in the oledb source it work fine but not when i make it directly
-
amioni wrote:
NB : when i make this query in Stored procedure and i call it in the oledb source it work fine but not when i make it directly
A user (database) can have different permissions based on direct sql versus stored procs.
-
Hello everyone, I have a problem with my SSIS 2012, if I put in made a OLDBE Source with a complex query or composite sample AS WITH TT ( SELECT DISTINCT e.UniqueID e. [Web Id] from Elements e) SELECT DISTINCT * 'Class' + CAST (ROW_NUMBER () OVER (PARTITION BY [Web Id] ORDER BY [class] DESC) AS NVARCHAR) AS ClassNum from TT I have this error: Exception From HRESULT: 0xC0202009 Error at Get Products SSIS Error Code DTS_E_OLEBDERROR. A OLE DB error has occured. Error code: 0x80004005 An OLE DB record is available. Source "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Syntax Error, permission violation, or nonspecific Reviews another error" see Exhibit attached Note that this same package works wonders on my colleague's machine with vs 2010 I do not understand the error!!
