'Run Package' Works On Server, But Doesn't Complete As Job.
Mar 10, 2008
I have an SSIS package is made up of SQL tasks and dataflows. The dataflows connect to an Oracle database using Native OLE DBOracle Provider for OLE DB (10g). This is the first package dealing with oracle that runs on the server.
I can execute the package manually by right clicking and going to 'Run Package' while logged in remotely from the server, but it gets hung up and does nothing if I run it as a job. I always have to quit the job. I can disable everything but the dataflows in the package and the job completes and runs fine.
Anyone have any ideas or similiar situations?
Thanks.
View 5 Replies
ADVERTISEMENT
Jun 26, 2007
hi,
I have many jobs on sql 05 and all work but one. This one writes to an Access DB on the same server as SQL. The package works fine. But when executed in the context of the SQL Agent job, it fails.
Jobs that write to a text file work fine. The Access DB has no password required. By the way, that job in sql 2000 worked fine.
Any ideas?
View 4 Replies
View Related
Feb 21, 2008
SELECT favorites.FID, favorites.filename, favorites.username, files.status, files.private, files.views, files.title FROM favorites INNER JOIN files ON favorites.filename = files.filename WHERE (favorites.username = @username) AND (files.status IS NULL) AND (files.private = @private)@private is manually set to 'no'@username is set to profile.usernamewhen I run the above query in microsoft sql server mgmt studio express on the database, it presents all the information i'm asking for very easily. however when i try to implement this query on a aspx .net2.0 page with teh sqldatasource and a gridview, no data is displayed.anyone know what my problem is?
View 1 Replies
View Related
Mar 31, 2006
I have a stored procedure with a where clause like this:
WHERE
Q.EffectiveDate >= @FromEffectiveDate AND Q.EffectiveDate <= @ToEffectiveDate AND I.InsuredName LIKE '%' + isnull(@PreQuoteDesc,I.InsuredName) + '%' AND isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBProperty,Q.LineOfBusinessDescription,'') + '%' AND isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBGeneralLiability,Q.LineOfBusinessDescription,'') + '%' AND isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBInlandMarine,Q.LineOfBusinessDescription,'') + '%' AND isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBMotorTruckCargo,Q.LineOfBusinessDescription,'') + '%' AND isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBOwnersContractorsProtective,Q.LineOfBusinessDescription,'') + '%' AND isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBSpecialEvents,Q.LineOfBusinessDescription,'') + '%' AND rsu.FirstName LIKE '%' + isnull(@OwnerFirstName, rsu.FirstName) + '%' AND rsu.LastName LIKE '%' + isnull(@OwnerLastName, rsu.LastName) + '%' AND Q.quoteID = isnull(@quoteID,Q.QuoteID) AND Q.QuoteStatusID = isnull(@quoteStatusID, Q.QuoteStatusID) AND rsu.AspNetUserID = isnull(@ASPNetUserID, rsu.AspNetUserID)
-------------------------------------------------------------------
All is working well except for the line of business:
------------------------------------------------------------
AND isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBProperty,Q.LineOfBusinessDescription,'') + '%' AND isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBGeneralLiability,Q.LineOfBusinessDescription,'') + '%' AND isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBInlandMarine,Q.LineOfBusinessDescription,'') + '%' AND isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBMotorTruckCargo,Q.LineOfBusinessDescription,'') + '%' AND isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBOwnersContractorsProtective,Q.LineOfBusinessDescription,'') + '%' AND isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBSpecialEvents,Q.LineOfBusinessDescription,'') + '%'
---------------------------------------------------------------------------------
If the user checks just 'Property' results look like:
Property
Property
Property, General Liability
If the user checks just 'General Liability' the resultes look like:
Genral Liablility
General Liability
General Liability, Inland Marine
If the user checks both Property and General Liability all they get back is:
Property, General Liability
They should get back everything including just Property or just General Liability or both.
So I tried to change the ANDs to ORs and it doesn't work.
-----------------------------------------
AND ( isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBProperty,Q.LineOfBusinessDescription,'') + '%' OR isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBGeneralLiability,Q.LineOfBusinessDescription,'') + '%' OR isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBInlandMarine,Q.LineOfBusinessDescription,'') + '%' OR isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBMotorTruckCargo,Q.LineOfBusinessDescription,'') + '%' OR isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBOwnersContractorsProtective,Q.LineOfBusinessDescription,'') + '%' OR isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBSpecialEvents,Q.LineOfBusinessDescription,'') + '%'
)
I know this is incredibly hard to follow because its incredibly hard to write out.
Is there anyone smart out there who can figure this out?
Thanks
View 2 Replies
View Related
May 7, 2008
I'm trying to connect to my SQL, and both ways I tried with CLI work. However, I have no luck with native C++, with the same string. Any ideas why? Here's the error message I get, and the source.
IM008
[Microsoft][ODBC Driver Manager] Dialog failed
Code Snippet
#include<iostream>
#include<windows.h>
#include<sql.h>
#include <sqlext.h>
using namespace std;
#using <mscorlib.dll>
#using <System.dll>
#using <System.Data.dll>
using namespace System::Data;
void main()
{
System::Data::SqlClient::SqlConnection^ cSql=gcnew System::Data::SqlClient::SqlConnection("Data Source=II.II.II.II;Initial Catalog=XXXXX;Integrated Security=SSPI;uid=YYYYY;pwd=ZZZZZ");
cSql->Open();
cSql->Close();
System::Data::OleDb::OleDbConnection^ cOledb=gcnew System::Data::OleDb::OleDbConnection("Data Source=II.II.II.II;Initial Catalog=XXXXX;Integrated Security=SSPI;uid=YYYYY;pwd=ZZZZZ;PROVIDER=SQLOLEDB");
cOledb->Open();
cOledb->Close();
SQLRETURN iRet;
SQLHANDLE EnvHndl;
SQLHANDLE ConnHndl;
iRet = SQLAllocHandle(1, 0, &EnvHndl);
iRet = SQLSetEnvAttr(EnvHndl, 200, (SQLPOINTER)3, 0);
iRet = SQLAllocHandle(2,EnvHndl, &ConnHndl);
wchar_t ConnStr[255];
wcscpy(ConnStr,L"Data Source=II.II.II.II;Initial Catalog=XXXXX;Integrated Security=SSPI;uid=YYYYY;pwd=ZZZZZ;PROVIDER=SQLOLEDB");
short ConnStrLength=wcslen(ConnStr);
iRet = SQLDriverConnect(ConnHndl, 0, (wchar_t*)ConnStr, ConnStrLength, (wchar_t*)ConnStr, 255, &ConnStrLength, 1);
long NativeError;
wchar_t*Msg=new wchar_t[10000];
wchar_t*SqlState=new wchar_t[10000];
int iParDiag=1;
short MsgLen=10000;
iRet = SQLGetDiagRec(2, ConnHndl, iParDiag, SqlState, &NativeError, Msg, 512, &MsgLen);
wcout<<ConnStr<<L'';
wcout<<SqlState<<L'';
wcout<<Msg<<L'';
}
View 1 Replies
View Related
May 14, 2007
I have been successful in getting a Merge Replication to happen via the web using ReplMerge.exe, but unsuccessful in doing the same using RMO.
The following code is what I am using for merge replication via the web using the ReplMerge.exe and this is working fine.
replString = "C:Program FilesMicrosoft SQL Server90COMREPLMERG.EXE"
Dim procID As Integer
Dim newProc As Diagnostics.Process
Dim si As Diagnostics.ProcessStartInfo
si.WindowStyle = ProcessWindowStyle.Hidden
newProc.StartInfo = si
newProc = Diagnostics.Process.Start(replString, "-Publication pub_mergetest
-Publisher publishername
-Subscriber subscribername
-Distributor distributorname
-DistributorLogin sa
-DistributorPassword pwd
-DistributorSecurityMode 0
-PublisherDB MergeTest-Publisher
-SubscriberDB MergeTest-Subscriber
-PublisherLogin sa
-PublisherPassword pwd
-SubscriberLogin sa
-SubscriberPassword pwd
-SubscriptionType 1
-SubscriberSecurityMode 0
-LoginTimeOut 30
-ParallelUploadDownload 1
-QueryTimeOut 300")
procID = newProc.Id
newProc.WaitForExit()
Dim procEC As Integer = -1
If newProc.HasExited = -1 Then
procEC = newProc.ExitCode
End If
However, I cannot get the same to work via RMO. The error i get back is "The process could not connect to subscriber "subscribername". What am I missing? Any help and feedback is greatly appreciated.
Dim _mergeAgent As MergeSynchronizationAgent = New MergeSynchronizationAgent()
_mergeAgent.Publication = "pub_mergetest"
_mergeAgent.Publisher = "publishername "
_mergeAgent.Subscriber = "subscribername"
_mergeAgent.Distributor = "distributorname"
_mergeAgent.DistributorLogin = "sa"
_mergeAgent.DistributorPassword = "pwd"
_mergeAgent.DistributorSecurityMode = SecurityMode.Standard
_mergeAgent.PublisherDatabase = "MergeTest-Publisher"
_mergeAgent.SubscriberDatabase = "MergeTest-Subscriber"
_mergeAgent.SubscriberLogin = "sa"
_mergeAgent.SubscriberPassword = "pwd"
_mergeAgent.SubscriberSecurityMode = SecurityMode.Standard
_mergeAgent.SubscriptionType = SubscriptionOption.Pull
_mergeAgent.PublisherLogin = "sa"
_mergeAgent.PublisherPassword = "pwd"
_mergeAgent.PublisherSecurityMode = SecurityMode.Standard
_mergeAgent.LoginTimeout = 30
_mergeAgent.QueryTimeout = 300
_mergeAgent.UseWebSynchronization = True
_mergeAgent.InternetUrl = "https://###.###.###.###/virtualdirectory/replisapi.dll"
_mergeAgent.InternetLogin = "domainusername"
_mergeAgent.InternetPassword = "pwd"
_mergeAgent.InternetTimeout = 5000
_mergeAgent.Synchronize()
View 3 Replies
View Related
Feb 1, 2007
I have a problem running an SSIS package in a SQL Server job. The package runs fine if I run it from the MSDB location, but if I try to run the job it fails. The job is set to Run as: SQL Agent Service Account. The SQL Service Agent service runs as a domain user SQLExec. I have logged in as this user and run the SSIS package and it runs fine, but if I create a job with only this step it fails. There isn't much information about where there is a problem. Any ideas or ways to troubleshoot this problem would be very much appreciated.
Thanks, john
View 3 Replies
View Related
May 3, 2007
Most of my packages that I've created in BIDS will NOT run in SQL Server 2005. The simplest one that I have fails during a script task that calls external managed code. I've done all the steps outlined in "Referencing Other Assemblies...", but I'm still getting "Object reference not set to an instance of an object." Here's a sample of a script that's having a problem. The line in green is the one that seems to be cause of the error. This is extremely frustrating. This code will even run from a command line console without error. Why is it so difficult to deploy one of these projects with managed code?
Code Snippet
Public Sub Main()
Dim variable1 As String = DirectCast(Dts.Variables("packagevariable1").Value, String)
Dim variable2 As String = DirectCast(Dts.Variables("packagevariable2").Value, String)
Dim variable3 As Integer = DirectCast(Dts.Variables("packagevariable3").Value, Integer)
Dim variable4 As String = DirectCast(Dts.Variables("packagevariable4").Value, String)
Dim filePath As String = DirectCast(Dts.Variables("filePath").Value, String)
Dim variable5 As String = DirectCast(Dts.Variables("packagevariable5").Value, String)
Dim results As Boolean
Dim fileGenerator As IProviderInterface
Dim intFactory As integrationServiceFactory = New ProviderIntegrationServiceFactory()
fileGenerator = intFactory.GetProviderEnrollmentGenerator(variable2, variable5)
results = fileGenerator.GenerateFile(variable3, variable1, filePath, variable2)
If results Then
Dts.TaskResult = Dts.Results.Success
Else
Dts.TaskResult = Dts.Results.Failure
End If
End Sub
View 9 Replies
View Related
Jun 21, 2007
I have a longstanding problem where Stored Procedures or complex T-SQL called from VB.NET will not populate a DataTable object, but will work fine with a DataSet. For example: 'oConn is defined elsewhere... Dim sErr as String = "" Dim dt As New DataTable If Not oConn Is Nothing Then Try Dim sSQL as String = "select 1" Dim oCommand As New OdbcDataAdapter(sSQL, oConn) oCommand.Fill(dt) Catch ex As Exception sErr = "Database Error: " & ex.Message Finally sqlCloseConnection(oConn) End Try End Ifthis works fine and my dt DataTable object gets one row. However using this as the SQL: Dim sSQL as String = "declare @foo table(mycol integer);insert @foo select 1;select mycol from @foo;"does not work. It executes with no errors, but the DataTable has no rows. Finally, if I replace the DataTable with: Dim ds as DataSetI can then get the data in ds.Tables(0) no problem.So, if the results of the sql are a single result table being put at index 0 of a DataSet, why are they not being put in a single DataTable?When a sql is a simple select statement it always works directly to a DataTable. Only when it's a SP or sql with some logic does it require the DataSet approach. This is a reporting utility so I need to standardize the code though the sql will be dynamic. Any ideas?
View 1 Replies
View Related
Apr 17, 2007
I have scheduled a job in Management Studio, but it doesn't work. However, when I run it maually in Visual Studio it works. I have connected an outside server by mapping it to mine. Maybe this is the problem?
I have also tried to configure a linked server, but I cannot find out how to connect my SSIS package to the linked server.
Can anybody help me?
Thank's!
View 8 Replies
View Related
Mar 31, 2004
hi all
I have the following query which works fine when it's executed as a single query. but when i union the result of this query with other queries, it returns a different set of data.
any one know why that might be the case??
select top 100 max(contact._id) "_id", max(old_trans.date) "callback_date", 7 "priority", max(old_trans.date) "recency", count(*) "frequency" --contact._id, contact.callback_date
from topcat.class_contact contact inner join topcat.MMTRANS$ old_trans on contact.phone_num = old_trans.phone
where contact.phone_num is not null
and contact.status = 'New Contact'
group by contact._id
order by "recency" desc, "frequency" desc
i've included the union query here for completeness of the question
begin
declare @current_date datetime
set @current_date = GETDATE()
select top 100 _id, callback_date, priority, recency, frequency from
(
(
select top 10 _id, callback_date, 10 priority, @current_date recency, 1 frequency --, DATEPART(hour, callback_date) "hour", DATEPART(minute, callback_date) "min"
from topcat.class_contact
where status ='callback'
and (DATEPART(year, callback_date) <= DATEPART(year, @current_date))
and (DATEPART(dayofyear, callback_date) <= DATEPART(dayofyear, @current_date)) -- all call backs within that hour will be returned
and (DATEPART(hour, callback_date) <= DATEPART(hour, @current_date))
and (DATEPART(hour, callback_date) <> 0)
order by callback_date asc
--order by priority desc, DATEPART(hour, callback_date) asc, DATEPART(minute, callback_date) asc, callback_date asc
)
union
(
select top 10 _id, callback_date, 9 priority, @current_date recency, 1 frequency
from topcat.class_contact
where status = 'callback'
and callback_date is not null
and (DATEPART(year, callback_date) <= DATEPART(year, @current_date))
and (DATEPART(dayofyear, callback_date) <= DATEPART(dayofyear, @current_date))
and (DATEPART(hour, callback_date) <= DATEPART(hour, @current_date))
and (DATEPART(hour, callback_date) = 0)
order by callback_date asc
)
union
(
select top 10 _id, callback_date, 8 priority, @current_date recency, 1 frequency
from topcat.class_contact
where status = 'No Connect'
and callback_date is not null
and (DATEPART(year, callback_date) <= DATEPART(year, @current_date))
and (DATEPART(dayofyear, callback_date) <= DATEPART(dayofyear, @current_date))
and (DATEPART(hour, callback_date) <= DATEPART(hour, @current_date))
order by callback_date asc
)
union
(
select top 100 max(contact._id) "_id", max(old_trans.date) "callback_date", 7 "priority", max(old_trans.date) "recency", count(*) "frequency" --contact._id, contact.callback_date
from topcat.class_contact contact inner join topcat.MMTRANS$ old_trans on contact.phone_num = old_trans.phone
where contact.phone_num is not null
and contact.status = 'New Contact'
group by contact._id
order by "recency" desc, "frequency" desc
)
) contact_queue
order by priority desc, recency desc, callback_date asc, frequency desc
end
View 1 Replies
View Related
Aug 23, 2007
Hi
I have a SP that works on SQL 2000 but not on 2005
It is just suppose to step through my code and insert values into tables where it finds the "ticked" values
here is apiece of my code. I hope it Helps.
Code Snippet
INSERT INTO Members (ClientID, Name, Surname, Email, Username, Password, Active, WlcSent)
SELECT ClientID, [Name], Surname, Email, Username, Password, Active, [Welcome Sent]
FROM StageMemberUploading
WHERE ID = @numValues
SET @CurrentValue = (SELECT SCOPE_IDENTITY())
IF @ClientID IS NOT NULL BEGIN
INSERT INTO MemberUsergroup (MemberID, ClientID, UsergroupID)
VALUES (@CurrentValue, @ClientID, @UsergroupID)
END
IF @DateOfBirth IS NOT NULL BEGIN
INSERT INTO _MemberProfileCharacterValues (MemberID, OptionID, OptionValue)
VALUES (@CurrentValue, 1, @DateOfBirth)
END
-------------------My Code Stops here ------------------------------
IF @Male = 'x' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@CurrentValue, 2, 1)
END
IF @Female = 'x' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@CurrentValue, 2, 3)
END
Any help would be greatly appreciated
Kind Regards
Carel greaves
View 5 Replies
View Related
Jul 7, 2015
I have two calls to stored procedures that in an SSIS package fails silently. They are simply not executed in production but works fine in test, nothing happens and the sql server agent reports that everything has gone just fine.
In test they have 1 server with db A and B. No issue here.
In prod they have 2 servers with db A and B. On server 1 sql server agent executes a job that includes an SSIS package that on server 2 runs a couple of sp's. That user is db owner on server 2 db B and yet nothing happens. The sp's are not executed.
If I in prod run the job manually then it works, but not when run with the sql server agent account that as said is even db owner.
View 2 Replies
View Related
Jun 4, 2007
Hello to all,
i have a problem with IN-Operator. I cann't resolve it. I hope that somebody can help me.
I have a IN_Operator sql query like this, this sql query can work. it means that i can get a result 3418:
declare @IDM int;
declare @IDO varchar(8000);
set @IDM = 3418;
set @IDO = '3430'
select *
from wtcomValidRelationships as A
where (A.IDMember = @IDM) and ( @IDO in (3428 , 3430 , 3436 , 3452 , 3460 , 3472 , 3437 , 3422 , 3468 , 3470 , 3451 , 3623 , 3475 , 3595 , 3709 , 3723 , 3594 , 3864 , 3453 , 4080 ))
but these numbers (3428 , 3430 , 3436 , 3452 , 3460 , 3472 , 3437 , 3422 , 3468 , 3470 , 3451 , 3623 , 3475 , 3595 , 3709 , 3723 , 3594 , 3864 , 3453 , 4080 ) come from a select-statement. so if i use select-statement in this query, i get nothing back. this query like this one:select *
from wtcomValidRelationships as A
where (A.IDMember = @IDM) and ( @IDO in (select B.RelationshipIDs from wtcomValidRelationships as B where B.IDMember = @IDM))
I have checked that man can use IN-Operator with select-statement. I don't know why it doesn't work with me. Could somebody help me? Thanks
I use MS SQL 2005 Server Management Stadio Express
Thanks a million and Best regards
Sha
View 2 Replies
View Related
Nov 9, 2006
Hello. I'm having troubles with a query that (should) return all therecords between two dates. The date field is a datetime type. The db isSQL Server 2000. When I try thisSELECT RESERVES.RES_ID, PAYMENTS_RECEIVED.PYR_ID,PAYMENTS_RECEIVED.PYR_VALUE, PAYMENTS_RECEIVED.PYR_DATE,CUSTOMERS.CUS_NAMEFROM RESERVES LEFT OUTER JOINPAYMENTS_RECEIVED ON RESERVES.RES_ID =PAYMENTS_RECEIVED.RES_ID LEFT OUTER JOINCUSTOMERS ON RESERVES.CUS_ID = CUSTOMERS.CUS_IDWHERE (PAYMENTS_RECEIVED.PYR_DATE >= '2006-03-20 00:00:00') AND(PAYMENTS_RECEIVED.PYR_DATE < '2006-03-27 00:00:00')on a "query builder" in visual studio, I get the results that I want.But when I use exactly the same query on an asp 3 vbscript script, Iget no results (an empty selection).I've done everything imaginable. I wrote the date as iso, ansi, britishformat using convert(,103) (that's how users will enter the dates),i've used cast('20060327' as datetime), etc. But I can't still get itto work. Other querys from the asp pages work ok. Any ideas?thanks a lot in advance
View 1 Replies
View Related
May 9, 2007
Hi There,
I am trying to set a variable with this default value using expression. This works in tsql but doesn't in ssis. Can anybody tell me what is wrong with this?
dateadd("dd", -1, datediff("dd", 0, getdate()))
Thanks.
View 8 Replies
View Related
Oct 23, 2006
works fine in designer but when i load the report services
I get the following error
anybody know what to do
there is one subreport with this report
maybe the passing value but what could be wrong ????
Item has already been added. Key in dictionary: '9' Key being added: '9'
View 2 Replies
View Related
Sep 11, 2005
Anyone live in seattle (meet on cap hill)? I have been stuck for weeks now and can't wait any longer wasting time - i will pay someone to set me up correctly quickly. I doubt this will be resolved online, but here goes... I need a datagrid to bring up simple database info (northwind) instead of showing the error 'login failed for mycomputeraspnet'. I am using windows authentication and my string is connectionstring = datasource=(local)\netsdk;initial catalog=Northwind;integrated security=sspi; persistent security=false;, I have seen online they say alter the authorization in the app virtual directory-- is that the C/inetpub/wwwroot or the web config of my project? I did so in the web config of my project, and also I added the impersonate=true to same already. I shared all the folders and made sure they are not read-only. I am flabbergasted.
Paul
XP pro 2004 sp 2
vis studio 2002 w updates
C# Asp web app project
View 2 Replies
View Related
Apr 2, 2008
Hi All,
I have to create a task, and in that task i have check exection time of package and if it more then specified time, send a mail with custom message to some specific users.
or
Can i write a event which occur after specific time and send a mail?
Please help me.
Thanks in advance....
From
Manish
View 6 Replies
View Related
Jan 21, 2002
We ran a cmd file which includes dtsrun ... The last line of the DOS window came up with "DTSrun: Package execution complete", but the cmd window could not close automatically.
View 2 Replies
View Related
Feb 8, 2008
I have searched extensively and not been able to find a solution to this problem.
The problem:
We have one SSIS package will sometimes 'finish' executing (or crash from a .NET exception) when it certainly has not made its way through all of the data flow components. There are no SSIS error messages, no warnings, and it never happens at the same location in the package's pipeline. The only thing that is instantly visible is a command window that flashes on the screen and disappears too quickly to see anything,.
Sometimes the package does actually complete without any problem, but most of the time, it does not.
What we see:
If the packages is being run through the "Execute Package Utility" (by double clicking the dtsx file), after a bit, a command window flashes on the screen and instantly disappears (no text is visible), then the €œExecute Package Utility€? disappears. The event viewer of the machine then shows:
Source: .NET Runtime 2.0 Error
Category: None
Event ID: 1000
Type: Error
Description: Faulting application dtexecui.exe, version 9.0.3042.0, stamp 45cd726d, faulting module dtspipeline.dll, version 2005.90.3042.0, stamp 45cd721f, debug? 0, fault address 0x00019a66.
If the package is running within visual studio, again the command window flashes on the screen, then the "Execution has completed" prompt appears, but any "running" component remains Yellow (no red), both within the data flow and control flow (we do not have any event handlers set up). Neither our SQL Log provider, nor the "Execution Results" tab in visual studio show any type of error message... all SSIS messages just stop right in the middle of the many OnPipelineRowsSent log events (so there is no PackageEnd log event when this happens). The event viewer on the machine contains no useful messages when running within visual studio.
And other packages:
Are fine. This is only the case for this one package... we have nearly a dozen other packages, all very similar in design, that complete without issue.
We have also tried re-creating this troublesome package from scratch with no avail.
<!--[if !supportLineBreakNewLine]-->
About the package:
The Data Flow is pulling rows from 3 different external SQL data sources (400k-500k rows total), sorting and merging the rows, performing some basic lookups, then SCD'ing the results. This Data Flow is executed multiple times within 2 nested for loops (these nested loops give us particular dates, i.e. years 2000 through 2008, then months 1 through 12 for each year). There is not a single script task in the package. The problem seems to happen most as the data is being pulled from the sources and merged together, but it is not limited to this area.
<!--[if !supportLineBreakNewLine]--><!--[endif]-->
The environment:
We€™ve tried to use multiple machines with the same result. The current machine specs are as follows:
SQL Server 9.0.3042 (SP2)
Windows Server 2003 R2, Enterprise x64 Edition, SP2
3.00GHz x 16 processors, all 64 bit
63.5 GB of RAM
Over 1 terabyte of hard disk space
.NET 2.0.50727.42
The package was designed using:
Visual Studio 2005 with SP1
Microsoft SQL Server Integration Services Designer - Version 9.00.3042.00
Anyone have an idea? Thanks in advance.
View 6 Replies
View Related
Jul 26, 2004
I've got a popular problem so i get a message that server acces denied! ..
But that what is different in my error.... When i use same setting same database and connection string (on MSDE server) there is no problem...
On SQL server i have got windwos authentication but i added all accounts as ASPNET and SA.... and when i try to connect by
RETTO - name of my server
server=RETTO;uid=sa;pwd=password;database=db1;
or by
Integrated Security=SSPIserver=RETTO;uid=RETTOASPNET;database=db1;
I CAN BROWSE RECORDS THERE ARE NO PROBLEMS WITH CONNECTION!!! but when i try to update or iinsert or delete something in database there becomame this error that access denied or server does not exist!!!
PLEASE HELP I'm FIGHTING WITH THAT FOR OVER 5 DAYS!!!
I MADE FOR MY ACCOUNTS (SA, ASPNET) ALL THINGS ALLOWED AS EXECUTING stored procedures.. OR ACCESING datatables with insert delete and update query WHERE IS THE PROBLEM!!!??
View 3 Replies
View Related
Apr 30, 2007
Hello everybody,
I'm developing a report using the following structure :
declare @sql as nvarchar(4000)
declare @where as nvarchar(2000)
set @sql = 'select ....'
If <conditional1>
begin
set @where = 'some where'
end
If <conditional2>
begin
set @where = 'some where'
end
set @sql = @sql + @where
exec(@sql)
I run it in query analyser and works fine, but when I try to run in Reporting Services, Visual studio stops responding and the cpu reaches 100 %.
I realize that when I cut off the if clauses, then it works at Reporting services.
Does anybody know what is happening?
Why the query works in query analyser and doesn't work in Reporting Service ?
Thanks,
MaurÃcio
View 2 Replies
View Related
May 20, 2008
I'm having a problem with a simple audit package.
I have a sub-select query with parameters in a SQL task. now from what I gathered we must do this as an expression so my SqlStatementSource expression is:
"UPDATE [Table1]
SET column1 = GetDate(), Success = 'Y'
WHERE
column2 = ? and column3 = ? and
Success = 'I' and column4 = (select Max(Column4) from [Table1] where column2 = ?)"
My package is saved on a network drive. When i execute this package from a RDC to another computer (the server) green lights all the way. When I execute the exact same package from my computer, RED RED RED!!!
I get the following:
Error: 0xC002F210 at [. . my query. . .] failed with the following error: "Parameter Information cannot be derived from SQL statements with sub-select queries. Set parameter information before preparing command.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
My query is already an expression so I dont know what else to do especially since it works one place and not the other. Am I missing something? (I don't want to use an SP)
View 5 Replies
View Related
Jun 7, 2004
I have a SQL DTS package that imports a text file as one wide column into a two column SQL table, one is an identity seed. So essentially I import then parse the data using the index key as it is 3 seperate lines of data. All works fine by running the DTS package using execute. When I schedule this as a job it fails. It indicates that I have additional white space after a column. Any thoughts on why this can ocurr?
View 4 Replies
View Related
May 3, 2007
Okay, can someone explain why when I execute children packages within a loop that only a couple of them work?
When I first started this I had Child Package 1 and Child Packge 2 working from the Parent Package 1. It would loop through twice (I had 2 organizations in my outer loop) and pass in the Parent Package variables correctly.
I am using a simple "Foreach Item Enumerator" with a collection of string enumerators that are the Names of the children packages - these are assigned to a variable that is scoped to the outer loop.
Now, when I add a new Child Package 3 and set it up the same as the other 2, when it goes to execute Child Package 3 it fails with the error:
"Error: The connection manager "[My Package Name].dtsx" is not found. A component failed to find the connection manager in the Connections collection. "
What in the world have I done? I was under the assumption that since the first 2 packages worked, that any other packages I added would work the same.
View 3 Replies
View Related
Dec 3, 2007
Hi,
I have a problem that's baffling me. I have a package that loads some files into the database. If I run it from BIDS, it works fine. But if I run the package from the job, I get this error:
Cannot open the datafile "D:myFoldermyFile.TXT".
It seems like a permissions issue, but the job runs under a local admin account, and not to mention, the very same package/job reads and loads files from this exact same directory, with no problems.
Halp.
View 1 Replies
View Related
Jun 13, 2007
This should be a very easy question.
I'm trying to develop an SSIS package that will eventually do many things, but I can't get it to do very basic things.
My current test package has 2 tasks in it:
1) File System Task ( FST )
2) Execute SQL Task ( EST )
When running just the SSIS package via the VS2005 IDE ( as startup project ), everything works fine. The FST moves a file from DIR_A to DIR_B and the EST inserts a test record into the test table.
If I set my C# app to the the startup project and execute the package from within the C#, it kinda works. The FST works fine, but the EST does not work and the package returns a "FAILURE" code to the C#.
The EST is incredibly basic. This is the SQL text:
insert into tmpssis ( tmpdata ) values ( 66 );
I'm using ADO.NET, Direct input, FALSE for IsQueryStoredProcedure, and it's using the only connection I've set up to the database.
The FST block runs - the file gets moved, but then it fails on the SQL block for some reason.
I'm open to any suggestions.
Thanks,
-BEP
View 6 Replies
View Related
Jun 20, 2007
I'm having a strange problem with this but I know (and admit) that the problem is on my PC and nowhere else. My firewall was causing a problem because I was unable to PING the database server, switching this off gets a successful PING immediately. The most useful utility to date is running netstat -an in the command window. This illustrates all the connections that are live and ports that are being listed to. I can establish a connection both by running
telnet sql5.hostinguk.net 1433 and
sqlcmd -S sql5.hostinguk.net -U username -P password
See below:
Active Connections
Proto Local Address Foreign Address State
TCP 0.0.0.0:25 0.0.0.0:0 LISTENING
TCP 0.0.0.0:80 0.0.0.0:0 LISTENING
TCP 0.0.0.0:135 0.0.0.0:0 LISTENING
TCP 0.0.0.0:443 0.0.0.0:0 LISTENING
TCP 0.0.0.0:445 0.0.0.0:0 LISTENING
TCP 0.0.0.0:1026 0.0.0.0:0 LISTENING
TCP 0.0.0.0:1433 0.0.0.0:0 LISTENING
TCP 81.105.102.47:1134 217.194.210.169:1433 ESTABLISHED
TCP 81.105.102.47:1135 217.194.210.169:1433 ESTABLISHED
TCP 127.0.0.1:1031 0.0.0.0:0 LISTENING
TCP 127.0.0.1:5354 0.0.0.0:0 LISTENING
TCP 127.0.0.1:51114 0.0.0.0:0 LISTENING
TCP 127.0.0.1:51201 0.0.0.0:0 LISTENING
TCP 127.0.0.1:51202 0.0.0.0:0 LISTENING
TCP 127.0.0.1:51203 0.0.0.0:0 LISTENING
TCP 127.0.0.1:51204 0.0.0.0:0 LISTENING
TCP 127.0.0.1:51206 0.0.0.0:0 LISTENING
UDP 0.0.0.0:445 *:*
UDP 0.0.0.0:500 *:*
UDP 0.0.0.0:1025 *:*
UDP 0.0.0.0:1030 *:*
UDP 0.0.0.0:3456 *:*
UDP 0.0.0.0:4500 *:*
UDP 81.105.102.47:123 *:*
UDP 81.105.102.47:1900 *:*
UDP 81.105.102.47:5353 *:*
UDP 127.0.0.1:123 *:*
UDP 127.0.0.1:1086 *:*
UDP 127.0.0.1:1900 *:*
Both these utilities show as establishing a connection in netstat so I am able to connect the database server every time, this worked throughout yesterday and has continued this morning.
The problem is when I attempt to use SQL Server Management Studio. When I attempt to connect to tcp:sql5.hostinguk.net, 1433 nothing shows in netstat at all. There is an option to encrypt the connection in the connection properties tab in management studio, when I enable this I do get an entry in netstat -an, see below:
TCP 81.105.102.47:1138 217.194.210.169:1433 TIME_WAIT
TCP 81.105.102.47:1139 217.194.210.169:1433 TIME_WAIT
TCP 81.105.102.47:1140 217.194.210.169:1433 TIME_WAIT
Amost as if it's trying the different ports but you get this time_wait thing. The error message is more meaningful and hopefull because I get:
A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.) (.Net SqlClient Data Provider)
I would expect this as the DNS has not been advised to encrypt the conection.
This is much better than the : Login failed for user 'COX10289'. (.Net SqlClient Data Provider) that I get, irrespective of whether I enter a password or not.
This is on a XP machine trying to connect to the remote webhosting company via the internet.
I can ping the server
I have enabled shared memory and tcp/ip in protocols, named pipes and via are disabled
I do not have any aliases set up
No I do not force encryption
I wonder if you have any further suggestions to this problem?
View 7 Replies
View Related
Jul 23, 2005
I'm pulling my hair out. After several attempts I got the sp_OAMethodto execute without error. Unfortunately the DTS package isn'texecuting. It also isn't returning any error. What could I be doingwrong? Any help would be appreciated.This is theEXEC @hr=sp_OACreate 'DTS.Package', @oPKG OUTPUTIF @hr<>0BEGINEXEC sp_OAGetErrorInfo @oPKG,@src OUT, @desc OUTSELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@descRAISERROR (@desc,16,1)RETURNENDEXEC @hr=sp_OAMethod@oPKG,'LoadFromSQLServer',NULL,@ServerName='CAMDEV 0',@PackageName='TestPkg',@Flags=256IF @hr<>0BEGINEXEC sp_OAGetErrorInfo @oPKG,@src OUT, @desc OUTSELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@descRAISERROR (@desc,16,1)RETURNEND--Execute the pkgEXEC @hr=sp_OAMethod @oPKG,'Execute'IF @hr<>0BEGINEXEC sp_OAGetErrorInfo @oPKG,@src OUT, @desc OUTSELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@descprint @descRAISERROR (@desc,16,1)RETURNEND
View 1 Replies
View Related
Feb 26, 2007
Hi all,
I've installed SP2 on my server. If I run the package on the server, the package worked there before the installation of SP2. Now with SP2 it doesn't work anymore. In VS2005 on my computer the package works before and after the installation of SP2.
He gives an error with the execution of a sql-task on an oracle server:
Error :Executing the query "insert into cube_content values (trim(?), trim(?), trim(?), trim(?), trim(?), sysdate) failed with the following error: "ORA-01401: inserted value too large for column". Possible failure reasons: Problems with the query, "ResultSet property not set correctly, parameters not set correctly, or connection not established correctly.
Anybody an idea?
Thanks,
Dennis
View 3 Replies
View Related
May 27, 2004
I have created a package that do a file search on an AS400 box using activex scriptand UNC path to do the file search. When I run it locally, it's fine. When I run it on the server, it fails. The login setup for the sql server agent service and the job is the same and they both have admin rights.
In addition, I also have another package pointing to the same path, but the job is to create a text file to the UNC path. It works even when I schedule it.
May someone please help me to solve this problem ?
Thanks!
View 2 Replies
View Related
May 2, 2007
I have been working with this for about a month now, and no similar problems to date. Today I am trying to introduce 4 configuration flags that control whether optional ETL stage feeds are executed. I did this by adding a do-nothing script component. The precedent and constraint is used, and it checks the boolean variable flag. The first package executes fine. But it never returns from there. This precedent has nothing fancy on it either. It simply does not run any more of the package, make any more conditional checks, nor the common completion tasks. It just seems to think it is done.
The optionals all fire execute package tasks. One thing that might be tripping it up is that I attempt to run one package twice, each time with varying parent package variable set to control it to use a different destination database for each run. Should this not be OK to do?
Any hints would be greatly appreciated.
View 2 Replies
View Related
