Problem Running Ddq Task On Different Machines
Apr 6, 2001
Hi all. I'm having a strange problem occur - any help would be appreciated. I have a data driven query task that is a part of a much larger package that runs fine and completes successfully if I run it by right-clicking on it and running an 'execute task' on just that individual task. This completes successfully only when I run it from my local machine with the SQL 2000 box registered on it. The task does not complete - or at least runs for as long as I'm willing to wait - if I run it either as part of the entire package or alone, as described above, directly on the SQL 2000(running Win 2000 Advanced). Obviously this is not good because the larger overall package is an integral part of a nightly cycle. Any thoughts?????
View 1 Replies
ADVERTISEMENT
Oct 11, 2006
Hello
We have 1 machine running SQL Server 2005 x64 (64 bit). The other machine is backing this SQL Server up. It is a 32-bit machine, so it requires (?) a 32-bit version of SQL Server 2005.
Would this back-up machine work correctly on a database previously managed by a 64-bit machine and vice versa.
Thank you!
View 3 Replies
View Related
Dec 20, 2007
Hello Everyone,
I've seen many entries about trailing spaces but have not found one like this.
In the Control Flow I am using an "Execute SQL Task" to populate some SSIS local variables (type string) by: (1) executing a SQL stored proc with output variables (type varchar(100)) to (2) be mapped to the local variable name (the parameter mapping Data Type is VARCHAR).
One of these mapped outputs is used as a path for subsequent operation in the Control Flow. At execution the sproc fires, populating the local variable with the path but with trailing spaces out to 255. Later in the "Script Task" when that path is used I receive an error telling me that the path is too long, and something about 260 or 246 characters.
Here's the oddity. I have two desktop environments running XP and a server environment (server 2003). This package runs just fine on the server - no trailing space issue, no need to trim. But on both my desktops I get the errors. By adding trim statements I can get back the correct path, but varchars should not be including trailing spaces, and the sproc return variable is a varchar (100).
I know this soulds like numerous other posts which indicate the solution is to trim, but I think the question I am asking is why does it work on the server but not the desktop? Is the SSIS variable type string experiencing a bug on different OS's?
Not to further complicate the issue but it used to work on my laptop, but through a horrible sequence of events I had to reload the studio in which case the error started to happen on that too.
View 6 Replies
View Related
Jan 20, 2006
Stupid question but please be gentle and answer anyway please....
Background: We have SQL Server 2003 (32bit) running on our servers. Our .Net applications (from old release of VS) are still running on them and using the old databases. From what I understand there is no immediate plans to upgrade the servers. However the developers were just given this new upgrade (2005) SQL Server and VS (and fixing depreciated code etc in the .net apps).
Question: Can the applications and new stored procedures written via the 2005 environment be deployed successfully on the 2003 Servers? Same goes with Reporting Services?
View 1 Replies
View Related
Oct 5, 2007
Hi!
I've written an scheduled job for importing an excel file into a sql table.When I try to run the job from Sql server agent , I get the status 'Not running',and in the last run status,it's written 'failed'.I've tested the command before in the query analyzer,and it works.Can someone tell me why isn't this working as a scheduled job?
Thanks in advance!
View 4 Replies
View Related
Jul 3, 2006
Hi All,
I have a strange problem that I can't find any information about and was hoping someone could help me out.
Some background:
* I am calling an SSIS package from ASP.Net using the in-process, .net methods.
* I am importing a flat file into a staging SQL Server table using the OLE DB Destination.
* After I import the data, I call some Execute SQL Tasks that does some cleaning of the data.
* I then move the data from the staging table to a final table.
Here's the problem:
When the SQL Task like this:
WHILE @@rowcount > 0
UPDATE Staging_Table
SET [Test1] = REPLACE([Test1], SUBSTRING([Test1], PATINDEX('%[^a-zA-Z0-9 ]%', [Test1]), 1), ' ')
WHERE PATINDEX('%[^a-zA-Z0-9 ]%', [Test1]) <> 0
is supposed to be run, sometimes it will, sometimes it won't. The character replacement always works when I test the package through BIDS but when I call the package from code, sometimes the task doesn't seem to get called. Or, at least, it isn't updating the table correctly. The staging table is poplated and copied to the final table but the character replacement doesn't occur in-between.
Does anyone have any advice on the possible causes or where to investigate further?
Thanks,
s.
View 2 Replies
View Related
Jul 27, 2001
In a SQL Server 6.5 database a task appears in the Running Tasks tab as Active. I have tried to Stop it for 2 hours but it will not disappear. When I run dbcc opentran against the database it says there are no Active open transactions. I also can't see anything in Current Acitivity.
The task is a scheduled task that runs daily and it has run OK both before and after this Active task
Does anyone know how I can get rid of it
View 1 Replies
View Related
Mar 12, 2007
hi frnds,
Till last week my SSIS package was running fine.
now it is throwing the below mentioned error:
SSIS package "DNB_GMS_PO_Transition.dtsx" starting.
Error: 0xC0202009 at DNB_GMS_PO_Transition, Connection manager "CTSINNVLSDNB1SQLEXPRESS.cedprod.cedprod": 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 'cedprod'.".
Error: 0xC00291EC at Taking backup of the affected tables, Execute SQL Task: Failed to acquire connection "CTSINNVLSDNB1SQLEXPRESS.cedprod.cedprod". Connection may not be configured correctly or you may not have the right permissions on this connection.
Task failed: Taking backup of the affected tables
Warning: 0x80019002 at DNB_GMS_PO_Transition: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "DNB_GMS_PO_Transition.dtsx" finished: Failure.
i was unable to figure it out.but i had connected correctly to Database.
if i run those queries in the Query analyser with the same DB connection its running fine.
its urgent.
please tell me the soultion.
View 4 Replies
View Related
Mar 13, 2008
My problem is that I am trying to set up an Execute Process Task to run a C# executable. The .exe captures information from an ftp site and downloads it into a directory on our server. Here is the command line run:
C:SAXOEodinReleaseSaxoEod
I have set the Execute Process Task up as follows:
Require Full name: True
Executable: C:SaxoEODobjReleaseSaxoEod.exe
FailTaskIfReturnCodeIsNotSuccessValue: False
SuccessValue:0
TimeOut:0
WindowStyle:Normal
I have the FailTaskIfReturnCodeIsNotSuccessValue set to False because when it is set to true I get the following error:
Error: 0xC0029151 at Execute Process Task, Execute Process Task: In Executing "C:SaxoEODobjReleaseSaxoEod.exe" "" at "", The process exit code was "-532459699" while the expected was "0".
Setting FailTaskIfReturnCodeIsNotSuccessValue to False does not work either because the files are not downloaded from the FTP site.
I am relatively new to the SSIS arena. I have googled this thing to death and have not found an answer. Any help in the right direction is appreciated. Thanks.
Dave Christman
View 7 Replies
View Related
Jun 26, 2006
We are attempting to use Microsoft SQL Server Integration Services Designer
Version 9.00.2047.00 to run a BCP command from a Process task. We are able to do so without a problem when running it interactively, but when run as a SQL Server Agent job the bcp process never completes and the job hangs indefinitely.
We also tried using a Script task for this, with the same result.
Has anyone else run into this problem?
Thanks,
Ron
View 7 Replies
View Related
Jun 29, 2006
Dear folks,
I'm trying to call SMO classes from SSIS package but I don't know which reference to use..
Mighn't be Imports Microsoft.SqlServer.Management?? But Intellisense don't give me such info.
Let me know where I'm failing.
TIA
View 14 Replies
View Related
Mar 13, 2001
Hi
Is it possible in my DTS Package to check if a stored procedure which I'm executing from the Execute sql task icon can be tested for failure?
View 2 Replies
View Related
Jun 12, 2001
Hi!
I just wonder if it is possible to run a scheduled task from a SQL Server 6.5 Client. Whenever I would try to run a scheduled task from a SQL Server Client a would get the error message that says "The SQLExecutive service is not currently running on server 'POFDS1099'. This prevents task 'ACORS2 FS90 People Pull' from being run", but when I would try to go the server itself, the service is runnning. Can anybody please tell me what could be the possible cause of this or perhaps 6.5 doesn't really support running scheduled task using SQL Server client. Thanks.
By the way, I also have SQL 2000 Client running on my local.
Thanks again,
Lhot
View 2 Replies
View Related
Jun 1, 2007
Hi..
I'm running a package that has a XML Task in the control flow. This task tranforms a XML file with a XSLT.
The file is about 2 megs on a daily basis, but at the end of the month there is a full dump of data that makes the file to be around 400 megs. There is where my problem is.
I run this on my 2 GB memory workstation and when the memory gauge on the task manager reaches about 1.5gb the package fails with an "Out of memory exception".
I also run this package on a 8GB Ram server, and same applies.
Is there any way of making this package utilize all the available memory, I even increased the virtual memory to see if that helped my issue, but nothing.
Thanks
View 10 Replies
View Related
Feb 28, 2008
Forget it -- I decided to take to crappy way out --
Instead of executing a procedure and trying to get the output when running a SSIS package, I simply run the query in the first step of the SQL Agent Job and fill a table with the results.
All I have SSIS do is export the table to a flat file.
I'm getting the following when I try to run in BIDS:
[Execute SQL Task] Error: Executing the query "dbo.prcCreatePayExtract" failed with the following error: "Query timeout expired". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
It appears that the timeout hits at 30 seconds which I've captured in SQL Profiler but I can't see to find where on earth in the package I can set the timeout. The "Timeout" on the task is set to zero and the Connection Timeout and General Timeout are both zero.
Any help is greatly appreciated,
Sid
View 5 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 11, 2007
Hello,
I developed an SSIS package doing a nightly load into a data warehouse. We have an 8 hour loading window - currently the package takes 16 hours to complete.
I isolated the problem to a Data Flow task where +-35% of the time is spent. This task is pretty straight forward:
- OLE DB source, reading +- 800,000 rows from a SQL server database
- 13 Lookups in sequence, to get surrogate keys from dimension tables. Lookups are all on GUIDS.
- An aggregation
- OLEDB target, fact table in a SQL server database.
It seems unreasonable for the this task to take over 5 hours. It spends the majority of time on the lookups - not so much at target, source and aggregation.
Any comments and advice will be greatly appreciated.
Thanks.
(PS some machine details:
OS Name Microsoft(R) Windows(R) Server 2003, Standard Edition
Version 5.2.3790 Service Pack 1 Build 3790
Other OS Description Not Available
OS Manufacturer Microsoft Corporation
System Name ARK-SQL
System Manufacturer HP
System Model ProLiant DL380 G5
System Type X86-based PC
Processor x86 Family 6 Model 15 Stepping 6 GenuineIntel ~1866 Mhz
Processor x86 Family 6 Model 15 Stepping 6 GenuineIntel ~1866 Mhz
BIOS Version/Date HP P56, 9/18/2006
SMBIOS Version 2.3
Windows Directory C:WINDOWS
System Directory C:WINDOWSsystem32
Boot Device DeviceHarddiskVolume1
Locale United States
Hardware Abstraction Layer Version = "5.2.3790.1830 (srv03_sp1_rtm.050324-1447)"
User Name Not Available
Time Zone South Africa Standard Time
Total Physical Memory 3,327.30 MB
Available Physical Memory 938.20 MB
Total Virtual Memory 1.10 GB
Available Virtual Memory 2.78 GB
Page File Space 2.00 GB
Page File C:pagefile.sys)
View 6 Replies
View Related
Feb 24, 2007
I found this and have done everything it says to do, but I can't get the script to compile. Any ideas on how to run a access macro in SSIS??
Baiscally to execute an Access Macros in SSIS package we need to Download
Microsoft.Office.Interop.Access DLL from Office XP PIAs.
Download site
http://www.microsoft.com/downloads/details.aspx?FamilyId=C41BD61E-3060-4F71-A6B4-01FEBA508E52&displaylang=en
1) Extract the Microsoft.Office.Interop.Access DLL from Oxppia.exe
2) Drag and Drop Microsoft.Office.Interop.Access DLL to Global Assembley Directory(GAC) ie: C:WINNTassembly for Windows 2000 -- C:WINDOWSassembly for ( Win Xp and Win 2003)
3) Copy paste Microsoft.Office.Interop.Access to C:WINNTMicrosoft.NETFrameworkv2.0.50727 for Windows 2000 -- C:WINDOWSMicrosoft.NETFrameworkv2.0.50727 ( Win Xp and Win 2003)
4) Add DLL reference in the Script Task
5) Add the below Code
1) Create a New Project in SSIS
2) Drag and Drop Script Task
3) Copy Paste the code in script task editor
Imports Microsoft.Office.Interop.Access
Try
Dim objAccess As New Access.Application
objAccess.OpenCurrentDatabase("D:TestMacro.mdb", False) ' Add the Access File Path
objAccess.DoCmd.RunMacro("Macro1") ' Replace Macro1 with the name of your macro
objAccess.CloseCurrentDatabase()
objAccess.Quit(Access.AcQuitOption.acQuitSaveNone)
objAccess = Nothing
Catch ex As Exception
System.Windows.Forms.MessageBox.Show(ex.ToString())
End Try
Dts.TaskResult = Dts.Results.Success
View 7 Replies
View Related
Apr 22, 2000
I need to start a job task from the command line. Does anyone know of the correct command(s) to do this?
I also need to check to see if a specific job task is currently executing from the command line. Does anyone know of the correct command(s) to do this?
Thanks for any assistance.
Jim
View 4 Replies
View Related
Apr 24, 2007
Hi
What SSIS Task or process can i use to stop my Control Flow Process from running?
I created a SQL Task to do a count on a table to see if there is data, if the count is > 0 then the Control Flow task must continue, else it uses a RAISERROR statement which i use with the event handler, but i want to put something in the event handler to stop the process then and not continue?
Any help will be greatly appreciated.
Kind Regards
Carel Greaves
View 1 Replies
View Related
Apr 25, 2006
Hi all,
I have a class that utilizes the ThreadPool together with a ManualResetEvent to queue up work items (in this case, FTP downloads) and then waits until all work items have completed before proceeding.
I then use this class inside a Script Component. However, I am getting a "remote procedure call failed" every single time the FTP component starts a download. This same class works perfectly in a WinForm app that I created as a test harness. Also, the synchronized version of the FTP download component works perfectly well from the Script Task -- it's only when I "wrap" the synchronous version in a threaded version that it fails.
Also, when the "remote procedure call failed" exception occurs, sqldumper.exe runs for a split second and creates a minidump. I, unfortunately, cannot inspect the dump because VS2k5 throws a native error when I try to "debug" the dump.
Is it possible to utilize multithreaded code inside SSIS? I noticed that SQL Server prohibits the use of threading as part of its hosting requirements (http://msdn2.microsoft.com/en-us/library/ms172338.aspx) but I'm not sure how that would impact (if at all) the SSIS host.
Thanks,
Vitaly
View 8 Replies
View Related
Jun 22, 2007
I have a manager SSIS package that calls a generic loader SSIS package, via the 'Execute Process Task'. The manger package (amongst other things) creates a config file and and then executes DTExec passing the config specification in the Arguments property, i.e. '
'/FILE "D:AudiencePackagesUKDW_Core_Loader_Persist.dtsx" /CONFIGFILE "D:AudienceDataSCPmystery.dtsconfig" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF"
It works as expected, but, I always get the warning message 'File/Process "DTExec" is not in path.', I know that it is because the exe runs and loads the correct package which then performs the correct actions.
Have I dropped the ball here? I cannot see any reason for the warning as DelayValidation==True, I could set RequireFullFileName==True, but I do not know where SQL is installed on the various nodes.
Any input would be warmly received
Thanks
View 1 Replies
View Related
Sep 18, 2006
I try to convert a Procedure that join 8 tables with INNER AND OUTER JOIN, my understanding is that the Lookup task is the one to use and I should break these joins into smaller block, it takes a long time to load when I do this, since each of these tables had 10-40mill. rows and I have 8 tables to go thru, currently this Stored Procedure took 3-4min to run, by converting this to 8 Lookup tasks, it ran for 20min. has anyone run into this issue before and know a work around for this.
Thanks
View 4 Replies
View Related
Jul 11, 2014
I have an execute process task in SSIS that executes a .Net program.When this is running as a Sql Server Agent job in Sql Server 2005, I have no issues. The operating system of the server is Windows Server 2003 R2.
Now I'm trying to get this working as a Sql Server Agent job in Sql Server 2012. The operating system of the server is Windows Server 2012 R2. And now I have issues.
If I run the SSIS package in Visual Studio debug mode, I have no issues! It's only when I try running the same package from a Sql Server Agent job that there are problems. The .Net program errors out with a System.TypeInitializationException.
I thought this might be an issue running the .Net program as 64 bit so I changed the agent setting to 32 bit. That didn't work. I thought it might be an issue with authorization (since I can run it in debug with me as the user). I put in a class in the program that changes the user through impersonation. That didn't work either.
Since I don't have a full-blown version of Visual Studio on the database server, I haven't been able to capture the error and debug the program to see where it is blowing up.
why something like this would work in Sql Server 2005/Windows 2003 but not in Sql Server 2012/Windows 2012 when run as an agent job? And on top of that, why would it work when I run it manually in debug mode but not when run as an agent job?
View 5 Replies
View Related
Apr 29, 2015
I'm trying to call WinSCP in a SSIS Execute Process Task using a .bat file to automate. SSIS is returning a generic failure error message ("Error: 0xC0029151 at Execute Process Task, Execute Process Task: In Executing "MyServerC$Program Files (x86)WinSCPWinSCP.exe" "-script=MyNASShareWinSCPFTP.bat " at "", The process exit code was "1" while the expected was "0"."). So now I'm trying to run the .bat file by itself. Unfortunately, the command prompt rushes by so fast that I can't see what the server is doing.
I can't find anything on the WinSCP site that indicates how to slow down the .bat file processing or log the remote server responses. I do see how to use the /log switch when using the interactive command line console, but that's not what I want to do.
View 9 Replies
View Related
Nov 5, 2007
My goal is to run a bunch of select statements from different tables in one database and have them all insert to the same columns/table in the new database. Do I need a new data source for each statement, or is there a way to run all the statements in one set seeing as they all have the same destination. I keep receiving the SQL statement improperly ended error when trying.
View 5 Replies
View Related
Jul 16, 2015
I am new to the world of ETL. I recently started using SSIS and I have come across couple of questions on this.
1.Can we run a SSIS Package using windows task scheduler?
2. If the above answer is yes,I don't have the SSMS installed on my machine. Can a SSIS package run in the absence of SSMS?(I do have Visual studio and SQL server data tools installed)
3. Can a SSIS Package run in the absence of Visual studio and SQL Server Data Tools.Say for example, I create a SSIS package and then remove Visual studio and SQL server data tools from my machine. Will the Task scheduler still be able to run the SSIS package?
4. Does the SSMS free edition comes along with SQL server Agent?
View 5 Replies
View Related
Apr 8, 2008
Hi All,
We're trying to run an EXE from SSIS through "Execute Process" task.
The EXE folder contains other DLLs as well.
The EXE interacts with the database and reporting services and sends some e-mails(max 500 a day) out to customers.
My question is:
Is it ok to run this kind of EXE on the production SQL box?
If not, why?
(People argue that running EXEs is not advisable on production boxes)
Q: Why did Microsoft introduce "Execute Process" task when we cannot run EXEs on the production box?
If somebody can educate whether it's ok to run such EXEs on prod SQLs.
In either case, some explanation is greatly appreciated.
Thanks,
Siva.
View 8 Replies
View Related
Sep 13, 2006
I have a DTS package that I brought over from SQL server 2000 in to SQL Server 2005. I have installed all of the legacy components to run the DTS packages but I need to debug an ActiveX script task. In SQL Server 2000 I could turn on Just-In-Time debugging and use the stop operator (in my vbscript) to break the running script and launch the debugger.
I don't see how to do this in SQL Server 2005 Management Studio. Is it possible to debug a script object in a DTS package running in SQL Server 2005?
Jay Abbott
View 1 Replies
View Related
Apr 1, 2008
Hello
I'm trying to run a task that executes a script file (cmd). When i run it with in bids with my own users (domain admin) it works. When i start a cmd prompt and try to run the cmd file directly from the network location where it is it works (with my own rights and with the sql server agent user).
Now when i try to run in from smss > agent jobs > job and run job it never completes. Im not getting any error message either it just keeps on running on the step ??? It seems like a rights issue, but the account running the sql server agent is able to execute the cmd file directly from the command prompt.
There are no errors in any error logs anywhere and no error is displayed...
Ps. Im running the job step as a integration service pacgake.
View 8 Replies
View Related
Mar 12, 2008
This would actually be funny if I weren't under serious time constraints right now. I have an SSIS project with several script tasks in the control flow. I put breakpoints in one of them to allow me to step through and see whats going on. However, when I start the project, the IDE brings up the script editor for a different script task. The breakpoints in this task are actually on the same line numbers as the task I need to debug but the code is all wrong. I checked the task for which the code is being displayed and there are no breakpoints there.
Thanks for the assist!
Don
View 11 Replies
View Related
Nov 30, 2006
when I run a package from a command window using dtexec, the job immediately says success.
DTExec: The package execution returned DTSER_SUCCESS (0).
Started: 3:37:41 PM
Finished: 3:37:43 PM
Elapsed: 2.719 seconds
However the Job is still in th agent and the status is executing. The implications of this are not good. Is this how the sql server agent job task is supposed to work by design.
Thanks,
Larry
View 1 Replies
View Related
Feb 28, 2002
Question - is there anyone out there running SQL Server on machines in the 2GB-4GB of RAM Range who has quick sec for a ? The documentation I have found for running SQL Server(native 32bit 2 GB RAM max) on 2GB+ machines is a little confusing (to me) if anyone can answer my question that would be great. I am running Ent SQL Server on WIN2K Adv box with 8 Xeons and 4 GB of RAM. I have enabled the /3GB switch in the boot.ini file but have not enabled AWE memory management in SQL. I have set the MAX amount of RAM avialble to my SQL box at 3GB and SQL Serve appears to be using all of the 3 GB under load. Is this the right way to set this or is there another more efficient way? Any answer explenations would be great. Thanks in advance
PS The server is only a SQL box no other apps / servers are running it.
View 2 Replies
View Related
