problem with SSIS package

Hi there,

I have a weird problem with SSIS package. Package is deployed and save on SQLServer2k5. It runs no problem if I start from Integration services. It fails every time when it is scheduled as a job.

The error message is: The package execution failed.The step failed.

I will appreciate any advice.

Thanks a lot.

When it's run from a job it executes under the account sql agent is running as. Are you using a data source that is sql authentication? If so you need to set the protection level when you deploy the package to something other than encrypt with user key or else it probably won't work when run as a job. There's a couple of options but if you just want it to work set it to rely on server.|||

Thank you Brent,

Unfortunately I use only Windows security. But I will try to set protection any way.

Cheers.

Michael

|||Make sure the account sql agent is running under has access to the db's you're trying to connect to. If that's a security concern, you can add a proxy account to execute the job under.|||

how can i check if this sql agent is running?

ihave this error when running my package after deploying it

Error: 0xC0202009 at Package, Connection manager "Presup Dev sql_prov": An OLE DB error has occurred. Error code: 0x80040E4D.

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Communication link failure".

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "TCP Provider: Se ha forzado la interrupción de una conexión existente por el host remoto.

|||

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=237837&SiteID=1

|||

ruk_walled wrote:

how can i check if this sql agent is running?

ihave this error when running my package after deploying it

Error: 0xC0202009 at Package, Connection manager "Presup Dev sql_prov": An OLE DB error has occurred. Error code: 0x80040E4D.

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Communication link failure".

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "TCP Provider: Se ha forzado la interrupción de una conexión existente por el host remoto.

this might help: http://support.microsoft.com/kb/918760/en-us|||

Brent,

I am having the same problem. I have thirteen SSIS packages and each one connects to an Oracle Database that uses SQL Authentication. Could you elaborate on the options you mentioned? And how would I set it to rely on the server, server Storage? (if so, doesn't work for me)

Thanks.

|||

Binh Cao wrote:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=237837&SiteID=1

Not sure if that was for me Binh, but that also did not work. My problem persists.

|||

What is the exact error you are receiving? It only happens when using SQL Agent?

Rafael

|||

I am receiving an 'AcquireConnection Error' that it cannot connect to the Oracle db. And yes, it only occurs when I try to schedule a job using SQL Agent, it runs in BIDS just fine.

Exact Error:

OnError,EESDCSDS522, [domain]\[login],Data Flow Task 1,{DF1DCB8E-74E7-4C5D-A639-7BAED849AAD7},{72E82E9B-7620-424E-A4E3-70E601497B54},8/10/2006 10:31:08 AM,8/10/2006 10:31:08 AM,-1071611876,0x,The AcquireConnection method call to the connection manager "[Remote Oracle Database]" failed with error code 0xC0202009.

EESDCSDS522 is the local db

I have taken out my domain\login and the name of the Remote Oracle DB for security reasons.

|||

_Phil_,

Is the box where you are running the package a 64-bit machine? SQL Agent will run the 64-bit version of DTSexec and all your 32-bit drivers won't be available for the package. The Work around is to schedule as command line that will call the 32-bit version of dtsexec.

Other thing to check is the credentials of the account used to run the SQL agent service; make sure it has the required permissions.

I hope this helps.

|||

Dear Rafael Salas,

This is the message we are getting when checking the job run history.

Message
The job failed. The Job was invoked by Schedule 4 (Package packageName). The last step to run was step 1 (Package packageName).

and on expanding it shows:

Executed as user: Servername\SYSTEM. The package execution failed. The step failed.

|||

Still facing the same problem.

The system account as well as the account specified to run the agen also fails.

thanks,

|||Try running DTEXEC from the command line under the account that you are attempting to use. That should give you a better error message.

Problem with SSIS and auto-increment of an ID

Hello,
I have a little problem with SQL Server 2005. I have chosen the option "auto-increment" for a column which includes the primary key. Moreover, I turned the option "Ignore double values" on. I get the datasets from a SSIS-Import-project. Unfortunately, the ID is getting incremented even if there are double values.
For example:
The first entries in the table have the IDs: 1, 2, 3, 4, 5
Then I start the SSIS-project which tries to write the first 5 entries again in the database. Of course, the entries do not appear again in the db but a new entry gets the ID 11 instead of 6. Is there a setting, that the ID won't get incremented if there double values?
Thanks
M-l-GI have not used the "auto-increment" function but the only time I had to increment a value was when the field was a key in my db. I set the key as an Identity key and so the db takes care of the incrementing. Is this something you are able to do? Just a thought!sql

problem with srever 2005 and Oracle

I am creating report model witch have connection to Oracle database. I was
created the connection and data source view, but then I want to create
Report model with wizard I am geting the error: ORA-02179: valid options:
ISOLATION LEVEL {SERIAZABLE | READ COMMITTED}.
how to change isolation level to valid oracle connection isolation level.It is my understanding that the model part of RS in 2005 is not yet
compatible with Oracle. I am tied to find where I read that on technet
but I can't. Maybe someone else can elaborate.
Thanks!|||Work-around for Report Builder:
1. Create linked server connection to Oracle database using the
OraOLEDB.Oracle provider(more up-to-date than Microsoft's).
2. Create a Data Source using the native SQL provider to the SQL Server
where you created in step 1.
3. Create a data source view; do not select objects.
4. Right-click in the DSV designer pane and create a New Named Query. Build
your query against the linked server (i.e. use 4-part names: select * from
linkedservername..schema.object). Repeat step 4 for each object you wish to
add to your model.
5. Add logical keys where applicable.
6. Build your model.
7. Deploy & build reports using Report Builder :-)
X

Problem with SQLXML BulkLoad from .NET Application

I am using BulkLoad from .NET application as follows

SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class obj = new SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class();

obj.ConnectionString = "Provider=SQLOLEDB;Server=serverName;Database=dbName;Integrated Security=SSPI";

obj.ErrorLogFile = Application.StartupPath + @."\Error.log";

obj.IgnoreDuplicateKeys = true;

try

{

obj.Execute(Application.StartupPath + @."\Schema1.xsd", Application.StartupPath + @."\Data1.xml");

obj.Execute(Application.StartupPath + @."\Schema2.xsd", Application.StartupPath + @."\Data2.xml");

}

catch (Exception ex)

{

throw ex;

}

I get following error when I run my application:

InvalidCastException was unhandled by user code.

Unable to cast COM object of type 'SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class' to interface type 'SQLXMLBULKLOADLib.ISQLXMLBulkLoad4'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{88465BA7-AEEE-49A1-9499-4416287A0160}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).

When I run the same code in a Console application it works alright. In my application the code is executed on a Background thread, could that be cause of exception? Please help.

Thanks

From SQL Server Books Online :

Using SQLXML Bulk Load in the .NET Environment

The reference to the Bulk Load component (xblkld4.dll) can also be added using the tlbimp.exe tool, which is available as part of .NET framework. This tool creates a managed wrapper for the native DLL (xblkld4.dll), which can then be used in any .NET project. For example:

c:\>tlbimp xblkld4.dll

This creates the managed wrapper DLL (SQLXMLBULKLOADLib.dll) that you can use in the .NET Framework project. In the .NET Framework, you add project reference to the newly created DLL.

Please follow the link for more info :

http://msdn2.microsoft.com/en-us/library/ms171878.aspx.

This will solve your issue.

Thanks

Naras.

|||

I think you should use

[STAThread]

staticvoid Main(string[] args)

{

//your code

}

|||I am having the same problem, however, running tlbimp does not fix it. I have to run tlbimp on the full path to the DLL because it can't find it unless I specify it. I get the same error. Please help!

Problem with SQLXML BulkLoad from .NET Application

I am using BulkLoad from .NET application as follows

SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class obj = new SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class();

obj.ConnectionString = "Provider=SQLOLEDB;Server=serverName;Database=dbName;Integrated Security=SSPI";

obj.ErrorLogFile = Application.StartupPath + @."\Error.log";

obj.IgnoreDuplicateKeys = true;

try

{

obj.Execute(Application.StartupPath + @."\Schema1.xsd", Application.StartupPath + @."\Data1.xml");

obj.Execute(Application.StartupPath + @."\Schema2.xsd", Application.StartupPath + @."\Data2.xml");

}

catch (Exception ex)

{

throw ex;

}

I get following error when I run my application:

InvalidCastException was unhandled by user code.

Unable to cast COM object of type 'SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class' to interface type 'SQLXMLBULKLOADLib.ISQLXMLBulkLoad4'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{88465BA7-AEEE-49A1-9499-4416287A0160}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).

When I run the same code in a Console application it works alright. In my application the code is executed on a Background thread, could that be cause of exception? Please help.

Thanks

From SQL Server Books Online :

Using SQLXML Bulk Load in the .NET Environment

The reference to the Bulk Load component (xblkld4.dll) can also be added using the tlbimp.exe tool, which is available as part of .NET framework. This tool creates a managed wrapper for the native DLL (xblkld4.dll), which can then be used in any .NET project. For example:

c:\>tlbimp xblkld4.dll

This creates the managed wrapper DLL (SQLXMLBULKLOADLib.dll) that you can use in the .NET Framework project. In the .NET Framework, you add project reference to the newly created DLL.

Please follow the link for more info :

http://msdn2.microsoft.com/en-us/library/ms171878.aspx.

This will solve your issue.

Thanks

Naras.

|||

I think you should use

[STAThread]

static void Main(string[] args)

{

//your code

}

|||I am having the same problem, however, running tlbimp does not fix it. I have to run tlbimp on the full path to the DLL because it can't find it unless I specify it. I get the same error. Please help!

Problem with SQLXML BulkLoad from .NET Application

I am using BulkLoad from .NET application as follows

SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class obj = new SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class();

obj.ConnectionString = "Provider=SQLOLEDB;Server=serverName;Database=dbName;Integrated Security=SSPI";

obj.ErrorLogFile = Application.StartupPath + @."\Error.log";

obj.IgnoreDuplicateKeys = true;

try

{

obj.Execute(Application.StartupPath + @."\Schema1.xsd", Application.StartupPath + @."\Data1.xml");

obj.Execute(Application.StartupPath + @."\Schema2.xsd", Application.StartupPath + @."\Data2.xml");

}

catch (Exception ex)

{

throw ex;

}

I get following error when I run my application:

InvalidCastException was unhandled by user code.

Unable to cast COM object of type 'SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class' to interface type 'SQLXMLBULKLOADLib.ISQLXMLBulkLoad4'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{88465BA7-AEEE-49A1-9499-4416287A0160}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).

When I run the same code in a Console application it works alright. In my application the code is executed on a Background thread, could that be cause of exception? Please help.

Thanks

From SQL Server Books Online :

Using SQLXML Bulk Load in the .NET Environment

The reference to the Bulk Load component (xblkld4.dll) can also be added using the tlbimp.exe tool, which is available as part of .NET framework. This tool creates a managed wrapper for the native DLL (xblkld4.dll), which can then be used in any .NET project. For example:

c:\>tlbimp xblkld4.dll

This creates the managed wrapper DLL (SQLXMLBULKLOADLib.dll) that you can use in the .NET Framework project. In the .NET Framework, you add project reference to the newly created DLL.

Please follow the link for more info :

http://msdn2.microsoft.com/en-us/library/ms171878.aspx.

This will solve your issue.

Thanks

Naras.

|||

I think you should use

[STAThread]

static void Main(string[] args)

{

//your code

}

|||I am having the same problem, however, running tlbimp does not fix it. I have to run tlbimp on the full path to the DLL because it can't find it unless I specify it. I get the same error. Please help!

problem with SQLSTATE

create function
"DBO".sf_GetStateID( @.Abbr char(2))
returns integer
begin
declare @.StateID integer;
set @.Abbr=UPPER(@.Abbr);
if @.Abbr is null
set @.Abbr=''
set @.StateID=53;

select MIN(lngStateID) into StateID from "DBA".States where strAbbr=@.Abbr;

if @.StateID is null
insert into States(strAbbr,strName) values(@.Abbr,@.Abbr)

if SQLSTATE = '00000'

set @.StateID=@.@.IDENTITY
return(@.StateID)
end

In this function, I am getting error at SQLSTATE = '00000'. I saw in books online and it says that SQLSTATE is a keyword. It is not recognizing SQLSTATE as a keyword in the function.
Can you help in that?

2nd Error: It is not accepting the statement
select MIN(lngStateID) into StateID from "DBO".States where strAbbr=@.Abbr;

I don't see any problem in the above statement. Do help in solving these two problems.

Tks
K.The 2 problems is that sqlstate is used for odbc/embedded sql - not for udfs. Also, you are returning a scalar in the udf and not a table (for the 2nd error).sql