Dtsrun To Start A Package - Cannot Find Xp_cmdshell
Jul 23, 2005
Newbie here.
In my database I'm needing to automate some data imports. I have the
import set up as a DTS package and it works wonderfully. But I'm
having trouble kicking it off as a stored procedure, or even from the
Query Analyzer. I used dtsrunui to get a proper connection string, but
when I enter
EXEC xp_cmdshell 'dtsrun /S "(local)" /N "MyPackage" /A
"KeyNum":"19"="19687627" /W "0" /E'
I get an error that says "Could not find stored procedure
'xp_cmdshell'". xp_cmdshell is indeed there, under Master, Extended
Procedures. I tried calling it dbo.xp_cmdshell, but that didn't help.
I'm guessing that I need to point the command to the location of the
SP, but I have no idea how to do that. Anyone willing to shed a little
light would get my eternal gratitude. :)
Thanks in Advance, maddman
View 2 Replies
ADVERTISEMENT
Feb 16, 2001
Hi,
i am running the xp_cmdshell 'dtsrun ...' from Query Analyzer to call my DTS and the process seem to stuck there. It say "Executing" in the bottom of the screen since 3 hours. The DTS take 10 seconds to run when i start it manually. This is working fine on my other server but not on this one.
Any help will be appreciated
Martin
View 1 Replies
View Related
Apr 21, 2005
Here is the error message that I'm getting
Error string: Error opening datafile: The system cannot find the path specified.
The file it's bombing out on is the text file that Im importing into one of my tables through a dts package (which is getting called by the dtsrun statement -- that is giving off this message.) If anyone would know what may be sending this message off to me let me know.
Thanks in advance.
RB
View 2 Replies
View Related
Sep 20, 2007
Hi all.
I've working around this one. I have a package that receives a parameter in YYYYMMDD format. I've tried from dtsexecgui and pass parameter. Tried 20070101 and worked fine. Tried convert(varchar,getdate(),112) and the whole string is passed, not the result as I thought.
So, I google it, so I can do this convert. Find out that this can only be done by, for example, a stored procedure that runs a cmdshell (DTSRUN.exe).
I've deployed my package (LIXO) by the manifest (also tried in VS save copy as - think this is the same thing). And the package is under MSDB in SSIS.
But now, I trie to run the package with all I could, for example:
DTSRUN /S "." /N "LIXO" /E
DTSRUN /S "(localhost)" /N "LIXO" /E
Also tried with user and password. I think I tried everything. And the error is always the same.
Says
"Loading..."
but then it breaks telling that could not find the package...
I connect into SSIS, run the package and it works just fine.
Can't discover what is missing around here.
Thanks in advance.
Marco Francisco
View 6 Replies
View Related
Nov 12, 2006
A developer needs to execute an SSIS package from a stored procedure and I do not want to enable xp_cmdshell on SQL 2005.
One suggestion is to have the application invoke dtexec on the client PC, but this would cause the package to be executed on the client and also requires some SSIS components be installed on each client.
Another idea is to configure a Windows Service to execute dtexec, but I do not know the risks of this approach. Any thoughts?
Thanks, Dave
View 1 Replies
View Related
Jan 17, 2008
am trying to execute a dtsx package using xp_cmdshell
when testing, this works fine
DECLARE @returncode int
EXEC @returncode = master..xp_cmdshell 'dtexec /f "C:WorkWarehouseDev.ETLLoadGroup_Daily.dtsx"'
PRINT @returncode
then change it to look at the live one
DECLARE @returncode int
EXEC @returncode = master..xp_cmdshell 'dtexec /f "C:WorkWarehouse.ETLLoadGroup_Daily.dtsx"'
PRINT @returncode
doesnt work - it tries to execute the WarehouseDev version
any ideas ??
n.b. didnt know if this should be in this forum or a t-sql forum - apologies if its in wrong place !!
View 4 Replies
View Related
Mar 19, 2008
How to execute SSIS package from CMD or using xp_cmdshell?
SSIS package is saved in the folder of the integration services Db.
Thanks in advance.
View 2 Replies
View Related
Oct 22, 2007
How can I execute an SSIS package from TSQL without using xp_cmdshell?
I have a web-app which calls some SQL which executes my SSIS package (a DTSX file, but stored in the server). But the security policy for my application won't permit me use to xp_cmdshell.
I want to do this:-
DECLARE @returncode int
EXEC @returncode = xp_cmdshell 'dtexec /sq pkgOne"'
Is there another way for executing a Package without going to the command line (e.g. is there some other system stored proc)?
Thanks
View 1 Replies
View Related
Jul 20, 2005
HelloI am trying to execute ‘xp_cmdshell' from within a DTS package thatwas created by another person. When I try to execute that ‘SQL Task'selectively from within the package, I get the following errormessage:Error Title: Package ErrorError Details:Error Source: Microsoft OLE DB Provider for SQL ServerError Description: xpsql.cpp: Error 87 from GetProxyAccount on line604Why is this error message popping up? When I create a new package(myself) and create an exact same SQL task as above and run it, theSQL task runs fine.Appreciate any help / feedback.Thanks in AdvanceJagannathan Santhanam
View 1 Replies
View Related
Oct 22, 2007
How can I execute an SSIS package from TSQL without using xp_cmdshell?
I have a web-app which calls some SQL which executes my SSIS package (a DTSX file, but stored in the server). But the security policy for my application won't permit me use to xp_cmdshell.
I want to do this:-
DECLARE @returncode int
EXEC @returncode = xp_cmdshell 'dtexec /sq pkgOne"'
Is there another way for executing a Package without going to the command line (e.g. is there some other system stored proc)?
Thanks
View 14 Replies
View Related
Feb 10, 2006
I have a package that executes fine in BIDS. However, when calling it using xp_cmdshell it cannot find the path to the source flat file. Anyone have any suggestions?
Here is the statement:
xp_cmdshell 'dtexec /SQ NatlAcctsImport /SER CR1-SQL-01 /CONN SourceConnectionFlatFile;Q:BINATNLACCTS'
Here is the result:
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
NULL
Started: 9:50:56 AM
Progress: 2006-02-10 09:50:57.40
Source: Data Flow Task
Validating: 0% complete
End Progress
Progress: 2006-02-10 09:50:57.40
Source: Data Flow Task
Validating: 50% complete
End Progress
Progress: 2006-02-10 09:50:57.42
Source: Data Flow Task
Validating: 100% complete
End Progress
Progress: 2006-02-10 09:50:57.43
Source: Data Flow Task
Validating: 0% complete
End Progress
Progress: 2006-02-10 09:50:57.43
Source: Data Flow Task
Validating: 50% complete
End Progress
Progress: 2006-02-10 09:50:57.43
Source: Data Flow Task
Validating: 100% complete
End Progress
Progress: 2006-02-10 09:50:57.43
Source: Data Flow Task
Prepare for Execute: 0% complete
End Progress
Progress: 2006-02-10 09:50:57.43
Source: Data Flow Task
Prepare for Execute: 50% complete
End Progress
Progress: 2006-02-10 09:50:57.43
Source: Data Flow Task
Prepare for Execute: 100% complete
End Progress
Progress: 2006-02-10 09:50:57.43
Source: Data Flow Task
Pre-Execute: 0% complete
End Progress
Warning: 2006-02-10 09:50:57.43
Code: 0x80070003
Source: Data Flow Task Source - NATNLACCTS [1]
Description: The system cannot find the path specified.
End Warning
Error: 2006-02-10 09:50:57.43
Code: 0xC020200E
Source: Data Flow Task Source - NATNLACCTS [1]
Description: Cannot open the datafile "Q:BINATNLACCTS".
End Error
Error: 2006-02-10 09:50:57.43
Code: 0xC004701A
Source: Data Flow Task DTS.Pipeline
Description: component "Source - NATNLACCTS" (1) failed the pre-execute phase and returned error code 0xC020200E.
End Error
Progress: 2006-02-10 09:50:57.43
Source: Data Flow Task
Pre-Execute: 50% complete
End Progress
Progress: 2006-02-10 09:50:57.43
Source: Data Flow Task
Cleanup: 0% complete
End Progress
Progress: 2006-02-10 09:50:57.43
Source: Data Flow Task
Cleanup: 50% complete
End Progress
Progress: 2006-02-10 09:50:57.43
Source: Data Flow Task
Cleanup: 100% complete
End Progress
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 9:50:56 AM
Finished: 9:50:57 AM
Elapsed: 0.985 seconds
NULL
View 3 Replies
View Related
Oct 28, 2004
Hello,
I am having some probelms getting script to give me the first and last date a customer had an outstanding item. The data is as follows:
Customer StartDate EndDate
A 4/1/04 4/15/04
A 4/15/04 5/1/04
A 5/1/04 5/15/04
A 5/16/04 5/28/04
A 5/28/04 6/5/04
B 5/1/04 5/15/04
B 5/16/04 5/20/04
The results I am looking for would be as follows:
Customer A : Outstanding 4/1/04 - 5/15/04
Customer A : Outstanding 5/16/04 - 5/28/04 (Theres a one day gap between prior sting, so this would be a new string )
Customer B : OUtstanding 5/1/04 - 5/15/04
Customer B : Outstanding 5/16/04 - 5/20/04
I want to include any strings where the start of one item and the end of another are on the same day as one continuis string. Any ideas on how to do this??
Thanks in advance!!
View 4 Replies
View Related
Apr 11, 2007
Hi,
This is the situation:
There are around 300 jobs on the server. The server shutsdown abnormally and is not functional for let's say 2 hours. After the server is back on, I need to find what jobs had to start during the server was down.
Is there a way to get this list? I tried to write an SQL script that tells me the needed information, but the job schedule sheme is so complex that it would take too much time in developing such SQL script. Maybe anybody has a better solution, or has already made a similar script?
ThankYou.
View 2 Replies
View Related
Oct 13, 2007
I have two SSIS packages in a project, one calling the other. The parent package works fine in my local mechine. After they are deployed to the production, I schedeul jobs to run the packages in the SqlServer. The child package works fine if I run it alone, but the parent package could not find its child package if I run the parent package . As I checked, all xml config files and the connection string pointing to the child package were set correctly. It seems the parent package did not use the xml config file. Can someone help me? Thanks in advance.
View 9 Replies
View Related
Apr 23, 2007
Sorry if this is a simple question, I've not seen any similar posts.
I'm just finished installed SQL 2005 on Vista Business edition (first time for both). I subsequently installed SP2 for 2005.
Upon completing the SP2 installation, the SQL Server Surface Area Configuration manager opened asking me to grant admin rights to the account I used to install the software. The account I used is the local admin account on the machine (only account on the machine as this is my dev box) .
Nothing shows in the two boxes below on this dialog, "Available Privelages" is blank and "Privelages granted is blank".
In addition, if I go to SQL Server Configuration Manager, and click on SQL Server 2005 Services, nothing shows in the right pain. It's as if the software installed, and yet no instance of the database exists or is running.
My thought was that the admin account I used to install the software doesn't have sufficient privelages to to start the SQL Server or SQLBrowser. So I went to computer management, Local Users and groups (machine isn't on a network yet), and made sure this same account is part of the administrators group.
Any ideas on how I should solve this?
View 1 Replies
View Related
Sep 24, 2015
I have a date dimension set in the SSAS Cube. I have been trying get quarter,year,month,semester start and end dates using ClosingPeriod() and OpeningPeriod() functions but not getting the exact value. How the get correct dates for a given date.
View 2 Replies
View Related
Aug 27, 2015
I need to find the missing months in a table for the earliest and latest start dates per ID_No.  As an example:
create table #InputTable (ID_No int ,OccurMonth datetime)
insert into #InputTable (ID_No,OccurMonth)Â
select 10, '2007-11-01' Union all
select 10, '2007-12-01' Union all
select 10, '2008-01-01' Union all
select 20, '2009-01-01' Union all
select 20, '2009-02-01' Union all
select 20, '2009-04-01' Union all
select 30, '2010-05-01' Union all
select 30, '2010-08-01' Union all
select 30, '2010-09-01' Union all
select 40, '2008-03-01'
For the above table, the answer should be:
ID_No OccurMonth
----- ----------
20 2009-02-01
30 2010-06-01
30 2010-07-01
1) don't include an ID column,
2) don't use the start date/end dates in the data or
3) use cursors, which are forbidden in my environment.
View 9 Replies
View Related
Dec 6, 2006
I have a SSIS package which copies data from Excel file to the database.
As soon as the file is copied to a specific location on the file system I insert an entry in the database table, which should kick off the above mentioned SSIS package.
I tried to read on WMI Event Watcher Task to do the above thing. I am not sure that I can do something like that.
Can anybody please help me out on this?
View 4 Replies
View Related
Sep 22, 2006
Hi,
I am getting the following error message, when I was trying to execute an existing SSIS Package which was working properly before.
"Failed to Start Package
Cannot communicate with the debug host process. Failed to obtain child process active object. (Microsoft.DataTransformationServices.VsIntegration)"
So I tried creating a new package and execute it, still I am getting the same error message.
If anyone have come across this problem and rectified it, pls let me know.
Thanks in advance for your help.
Regards,
Prakash Srinivasan
View 6 Replies
View Related
Apr 15, 2008
I truncate some tables before refreshing the data and that is one of the last steps shown in the package execution progress window when watching the package run.
Both in Visual Studio and when I use the Execute package utility once I have put the package on the server?
Is there a problem with having multiple truncate statements in one execute T-SQL statement task?
View 11 Replies
View Related
Sep 24, 2007
I have a Stored Procedure preparing data, which then are exported (flat files) using 2 SSIS packages. What is the best way to execute those 2 SSIS packages (sp,job, other) ?
Every advise is appreciated!
View 5 Replies
View Related
May 26, 2001
I expect to create a trigger to post updated data from GoldMine hosted in MS-SQL to my migration MS-SQL database in the appropriate tables mirroring the destination PICK data tables.
Then, start an ActiveX DTS package to migrate the data via a PICK DSN to data tables in a PICK database.
Currently the dba has been able to use VB6.0 with ADO to push data into PICK. He also was able to do similar using MS-Access.
However, PICK (RainingData) is of the opinion that he must script a PICK server side Basic (RealBasic) insert script to receive the data from a VB6.0 application triggered by MS-SQL.
I think that I could skip the Basic script and go direct with ADO in DTS as he has before with VB6.0 with a user form.
Can I have the trigger start the DTS or should I just schedule it to run as often as necessary to update the PICK database?
FYI, this is a one-way data flow into PICK.
TIA
Anyone within the L.A. CA area that has experience with PICK and MS-SQL can get some well paid consulting hours. I'm just the GoldMine GMT whose been enlisted to get the job done, but would appreciate an expert with PICK to join the project.
jEfFp...
View 1 Replies
View Related
May 31, 2007
This is a repeat listing - third time - of this problem.
Here's the deal:
If I turn on logging on an SSIS package in Development Studio, when the package executes it will log all the events I choose to the sysdtslog90 table in the MSDB database - INCLUDING the PACKAGESTART and PACKAGEEND events.
When I create my own custom logging, however, those two events ARE NOT being logged, even though I explicitly state in my script I want those two logged. Everything else in the script (OnWarning, OnPreExecute, OnPostExecute, etc.) is being logged.
In my reading, it states that the PACKAGESTART and PACKAGEEND events are defaults and are always logged and cannot be excluded.
If this is the case, can someone explain why they aren't getting logged?
I've seen other people have run across the same issue...
View 8 Replies
View Related
Mar 27, 2007
HELLO,
I want to create a package which start and stop the SQL server's services... i know i can achive this via NET COMMAND.... but i coudnt find in which task (SSIS) I can place that command?..
I also came across that I can achieve this using Execute Process task but for this I have to define executable file.... actually i dont want ne thing outside from my SSIS package
CAN I ACHIEVE THIS WITH IN SSIS PACKAGE?
is there ne other alternative?
regards,
Anas
View 9 Replies
View Related
Nov 15, 2007
I would like to create a log of what happens with each step in the package, at least start & start time and email that at the end of the package
Is this possible?
Thanks
JPS
View 3 Replies
View Related
Jun 19, 2006
I'm trying to gather information from within a SSIS package for benchmarking, reconciliation, and reporting purposes in regards to cube processing, which I'm initiating using the AS processing task.
What is the easiest way to capture this information?
The only way I've been able to come up with is to use a profiler trace. If this is really the only way, what is the easiest way to execute and read the trace from within SSIS?
Also, if a script task has to be used, does anyone have a code sample?
Thanks in advance!
View 3 Replies
View Related
May 7, 2014
I have problems sometimes with a package.
There is and SQL Agent job that starts a package (from a file system using cmd command). Usually job takes 8-10 minutes. But sometimes it get stuck for a long time (1+ hour).
DTexec process can be found with procmon, but it seems it just not doing anything (And package is not logging to file Start of the execution) After long wait it just runs a package quickly.
I've moved a package to SSIS catalog to try to get more detailed logging, but with no luck.
Job starts at 1 PM, package execution starts at 1:49 PM. Without any messages about the execution in SSISDB log.
First I've thought it might be long validation problem, but when package executes validation messages are there and they perform quick.
View 3 Replies
View Related
May 16, 2008
Hi,
I have a package designed as bring data tables over to SQL Server. There are 9 data flow tasks that runs parallel, to bring 9 datatables over. In BIDS, when I execute the package, it runs like 8 minutes. Or if I start the scheduled job manually, it runs around 8 minutes too. But it runs about 30 minutes at the scheduled time at midnight.
I wonder what I can do to speed up the scheduled job.
Thanks
View 13 Replies
View Related
Oct 10, 2006
hi all,
i searched and all i found is questions, not answers.
maybe it's a silly question, but i really can't find any documention / posts about this.
i have a scheduled job in sqlagent that executes a SSIS package that runs every minute. As a result, my application log in eventviewer gets filled very quickly.
i tried using "/REPORTING E" option with no luck.
i tried enabling logging on the package, and then select only the OnErro Event, no luck.
does anyone have this problem ?
does anyone have a solution ?
TIA
View 4 Replies
View Related
Mar 21, 2007
Can't an SSIS package run "in the background", so to speak, without having either the cmd.exe or dtexecui windows open while executing? I'd obviously rather not have to have a window open when the thing is running right?
View 2 Replies
View Related
Apr 19, 2015
My requirement is to get the earliest start date after a gap in a date column.My date field will be like this.
Table Name-XXX
StartDate(Column Name)
2014/10/01
2014/11/01
2014/12/01
[code]...
 In this scenario i need the latest start date after the gap ie. 2015/09/01 .If there is no gap in the date column i need 2014/10/01
View 10 Replies
View Related
Apr 23, 2015
My requirement is to get the latest start date after a gap in a month for each id and if there is no gap for that particular id minimum date for that id should be taken….Given below the scenario
ID Â Â Â Â Â StartDate
1 Â Â Â Â Â Â 2014-01-01
1 Â Â Â Â Â Â 2014-02-01
1 Â Â Â Â Â Â 2014-05-01-------After Gap Restarted
1 Â Â Â Â Â Â 2014-06-01
1 Â Â Â Â Â Â 2014-09-01---------After last gap restarted
1 Â Â Â Â Â Â 2014-10-01
1 Â Â Â Â Â Â 2014-11-01
2 Â Â Â Â Â Â 2014-01-01
2 Â Â Â Â Â 2014-02-01
2 Â Â Â Â Â Â 2014-03-01
2 Â Â Â Â Â Â 2014-04-01
2 Â Â Â Â Â Â 2014-05-01
2 Â Â Â Â Â Â 2014-06-01
2 Â Â Â Â Â Â 2014-07-01
For Id 1 the start date after the latest gap is  2014-10-01 and for id=2 there is no gap so i need the minimum date  2014-01-01
My Expected Output
id       Startdate
1 Â Â Â Â Â Â Â 2014-10-01
2 Â Â Â Â Â Â Â 2014-01-01
View 4 Replies
View Related
Oct 5, 2007
Hi
As the dts package is a scheduled job, how i can find out the dts package is currently executing or not?
View 5 Replies
View Related
