Hi,
I have to execute package in a Batch. I have done adding SSIS packages to SQL Server Agent and it is working. Now, my requirement is such that, when a user clicks "Transfer to batch" button in Windows application, i have to add all the packages to batch automatically without need for user to manually open SQL Server Agent and add packages. Is there any code available to do this?
Please help!
Thanks,
HELP,
I need to take a variable from a tabel in SQL Server pass to a Batch file and execute the batch file. Right now I can exec the batch file with XP_CMDSHELL but how can I pass the variable to the batch file and loop through all the variables.
Please help
Phil
Hi all,
I am executing a backup batch job scheduled daily. It runs
successfully but havent backed up any of the databases. The message
is
"The name specified is not recognized as an
internal or external command, operable program or batch file."
I am trying this for a full day and i gave up..please can anyone help
me or suggest me what to do?..The same batch file is executed in all
servers and is working fine..
Thank you in advance
Rani.
If I have an application running on MSSQL is it possible that if an action occurs on the application side that a stored procedure can execute a batch script?
View 4 Replies View RelatedSomeone asked me a curious question.Can I execute a query to sql server from a bat file? how?If not is there a simple scripting laguage that this person might use todrive his process that is similar to a dos bat file?
View 5 Replies View RelatedHello
I have a script ,which runs with osql
The script is :
osql -E
declare @cmd nvarchar(1000)
declare @cmd2 nvarchar(1000)
declare @state1 varchar(100)
declare @message varchar(100)
set @message = ''
-- Build command to determine state of SQLSERVERAGENT service on Master Server
SET @CMD = 'create table #state (state varchar(2000))' + char(10) +
'declare @cmdx varchar(1000)' + char(10) +
'insert into #state EXEC master..xp_servicecontrol ''''QueryState'''', ''''SQLSERVERAGENT''''' +
+ char(10) + 'select @state=state from #state' + char(10) +
'drop table #state'
-- Build command to execute command that determines state of service being monitored
set @cmd2 = 'declare @state varchar(100)' + char(10) +
'exec ' + rtrim(@@servername) + '.master.dbo.sp_executesql N''' + @CMD + ''',' +
'N''@state varchar(100) out'',' +
'@state out' + char(10) +
'set @state1 = @state'
-- Execute command and return state of service being monitored
exec master.dbo.sp_executesql @cmd2,N'@state1 varchar(100) out',@state1 out
-- Is the service that was monitored not
IF (UPPER(@state1) <> 'RUNNING.')
--if @state1 <1 'Running.'
begin
-- Display message that primary monitor is down
select @message = @message+char(13)+ @@servername + ' -' + 'Sql Server Agent Not Running'+char(13)
print 'Master server "' + rtrim(@@servername) + '" for monitoring is not available.'
exec master.dbo.xp_smtp_sendmail
It works fine when I run it on the command line prompt.
And I receive a mail , if the server agent is running.
But when I save it as bat file and try to run , it stops and doesnot even give an error.
Can anyone let me know what I can do.
Thanks
Hello,
I have an "execute process" task which executes a perl script. When I run the task, it shows a prompt with the message "the publisher can not be verified". It gives the option to continue the task or to cancel.
The problem for me is that I want to schedule this package to run automatically, and I don't want the automated process to show this dialog. I need to override the security setting and have SSIS execute my batch scripts without prompting. Assuming I don't want to rewrite the Perl code into a script task, is there a way to do acomplish this?
Thanks!
Arkadiy
Hi,
I have developed an SSIS package for ETL purpose. I am invoking the SSIS package through .Net console application by referencing the ManagedDTS Assembly. I am able to execute the package in Sql Server 2005 Developer Edition and it runs fine till completion.
But when i try to execute the packahe in Sql Server 2005 Standard edition, by invoking the package through .Net console application the status of the package is failure.
Can any one help me how to over come this problem.
Hi,
I am planning to develop a single package that will download files from ftp server, move the files to internal file server and upload it in the database. But I want to run this package for multiple ftp file providers. For each provider the ftp server might be different and the transformation to upload the files into a database table might be different.
So can I create a single package and then multiple configuration files (xml), which will contain the details fo the ftp file providers and then pass the xml file as a parameter while executing the package. The reason being that the timings of fetching the files is different for each ftp file provider and hence cannot be combined into one.
Is this possible?
Thanks for your help.
$wapnil
I would like to fetch the data flow component name while package is executing. Since system variable named [System::SourceName] only fetches name of the control flow tasks? Is there a way to capture them?
View 5 Replies View RelatedGot this error running a query with ssms2014
on SqlServer2014
but db has Compatibiliy level 100 (sql2008)
no sql error code in message and no error found in eventviewr (sqlserver log, windows log, application log ecc)when i run that query i got this message in "messages" tab, and in results tab i got a strange result, indeed the value returned doesn't exists in table.
ex table1.field1 possible values= 2,3
it returns 1113344
we got that error thru jdbc too...that query use a lot of joins, unfortunatley i can't post it here. today got this error, so i make a copy of 1 of involved table into another db in the same instance, re run the query on the original db, and it works well.
Hi all,
I have the "Northwind" database in my Sql Server Management Studio Express.
In my C:ProSSEAppsSamplesForChapter02Chapter02 folder, I have the following 2 files:
(1) ListColumnValues (MS-DOS Batch File)
sqlcmd -S .sqlexpress -v DBName = "Northwind" CName = "CompanyName" TName =
"Shippers" -i c:prosseappschapter02ListListColumnVales.sql -o
c:prosseappschapter02ColumnValuesOut.rpt
(2) ListColumnValues (Microsoft SQL Server Query File)
USE $(Northwind)
GO
SELECT $(CompanyName) FROM $(Shippers)
GO
When I ran the following SQLcmd:
C:ProSSEAppsSamplesForChapter02Chapter02>ListColumnValues.bat
I got the following "ColumnValuesOut.rpt" with error messages:
'Northwind' scripting variable not defined.
Msg 102, Level 15, State 1, Server L1P2P3SQLEXPRESS, Line 1
Incorrect syntax near '$'.
'CompanyName' scripting variable not defined.
'Shippers' scripting variable not defined.
Msg 102, Level 15, State 1, Server L1P2P3SQLEXPRESS, Line 1
Incorrect syntax near 'CompanyName'.
I copied these T-SQL statements from a book and I do not know how to correct them.
Please help and tell me how to correct these errors.
Thanks in advance,
Scott Chang
I am getting this error
An error occurred while executing batch. Error message is: The directory name is invalid."
in sql management studio.
i found that if i switch to results to text i don't get the error but with results to grid I get it no matter what i query in any database on the server.
Trying to run a SSIS package from a SQL job, and the package itself has a step that calls a process task that runs a batch file. The syntax in the process task I have is the following:
executable: c:windowssystem32cmd.exe
Arguments: /C e:SungardPTAencryptfile.bat
Working Directory: e:sungardpta
I keep getting the following in my log:
PackageStart,MIMKEIMC11N,MI rustserviceadmin,PTADailyTransactionExtract,{46F7381F-B345-47DC-BFC0-17CCF02A935A},{F82C7944-D28C-4F70-8CB7-F0BD7ED748D2},1/29/2008 1:59:11 PM,1/29/2008 1:59:11 PM,0,0x,Beginning of package execution.
OnError,MIMKEIMC11N,MI rustserviceadmin,EncryptFiles,{FCF5B653-CC05-4183-981B-F5EF4906DD09},{F82C7944-D28C-4F70-8CB7-F0BD7ED748D2},1/29/2008 1:59:12 PM,1/29/2008 1:59:12 PM,-1073573551,0x,In Executing "c:windowssystem32cmd.exe" "/C e:SungardPTAencryptfile.bat" at "e:sungardpta", The process exit code was "1" while the expected was "0".
OnError,MIMKEIMC11N,MI rustserviceadmin,PTADailyTransactionExtract,{46F7381F-B345-47DC-BFC0-17CCF02A935A},{F82C7944-D28C-4F70-8CB7-F0BD7ED748D2},1/29/2008 1:59:12 PM,1/29/2008 1:59:12 PM,-1073573551,0x,In Executing "c:windowssystem32cmd.exe" "/C e:SungardPTAencryptfile.bat" at "e:sungardpta", The process exit code was "1" while the expected was "0".
OnTaskFailed,MIMKEIMC11N,MI rustserviceadmin,EncryptFiles,{FCF5B653-CC05-4183-981B-F5EF4906DD09},{F82C7944-D28C-4F70-8CB7-F0BD7ED748D2},1/29/2008 1:59:12 PM,1/29/2008 1:59:12 PM,0,0x,(null)
PackageEnd,MIMKEIMC11N,MI rustserviceadmin,PTADailyTransactionExtract,{46F7381F-B345-47DC-BFC0-17CCF02A935A},{F82C7944-D28C-4F70-8CB7-F0BD7ED748D2},1/29/2008 1:59:12 PM,1/29/2008 1:59:12 PM,1,0x,End of package execution.
Any help would be greatly appreciated
Hello,
I have a web application that I kick off a DTS package. I tested the package and it works fine. However, when running the package through ASP.NET, I get the following error:
Type mismatch. (mscorlib (80004005): Missing parameter does not have a default value. Parameter name: parameters)
I've traced it to a dynamic task step. I have this as the first step in the workflow, but for some reason, it is executing last. Which is definitely not the place where I want it. Is there anyway I can force it, or why is it doing that?
Any ideas?
Brian
Hi Friends
I am trying to execute the DTS package from the T-Sql
Its basically Exporting the table from Sql Server to tab delimited text file
DECLARE @Command varchar(1000)
declare @partcode varchar(100)
set @command = 'DTSRun /S "servername" /U "username" /P "password" /N "TabFormat" /G "{459784D8-0170-400C-8919-AC25423D7F34}" /W "0" '
EXEC master..xp_cmdshell @Command
It Comes up with the Following Error
output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSDataPumpTask_1
DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005)
Error string: Error opening datafile: The system cannot find the path specified.
Error source: Microsoft Data Transformation Services Flat File Rowset Provider
Help file: DTSFFile.hlp
Help context: 0
Error Detail Records:
Error: 3 (3); Provider Error: 3 (3)
Error string: Error opening datafile: The system cannot find the path specified.
Error source: Microsoft Data Transformation Services Flat File Rowset Provider
Help file: DTSFFile.hlp
Help context: 0
DTSRun OnFinish: DTSStep_DTSDataPumpTask_1
DTSRun: Package execution complete.
NULL
(22 row(s) affected)
Vic
Hi,
I am having trouble executing a package as a job in SQL Server 2005. I can run the package fine manually from sql server but when I create a job for that package and run the job I get an error that says
The package execution failed. The step failed.
I am completely lost at this point. Any help would be greatly appreciated.
Thanks
Brian
Is it possible to execute a package in a trigger or stored procedure ?
View 3 Replies View RelatedI apologize in advance for posting yet another connection failure issue. I went through quite a few posts and could not find the actual answer so here is the issue. I have a main SSIS package to call five other packages. This seems to work fine in my BIDS workstation; however, when I copied it, including the bat file that was created by dtexecui utility, to the production environment (which runs on 64 bit - probably has nothing to do with the 64 bit platform), the main package executes fine but the child packages failed with this error:
Description: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult:
0x80004005 Description: "Login timeout expired".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult:
0x80004005 Description: "An error has occurred while establishing a connection
to the server. When connecting to SQL Server 2005, this failure may be caused by
the fact that under the default settings SQL Server does not allow remote conne
ctions.".
It seems that the child packages are still using the old connection strings from my workstation. My question is how can I pass the connection string from the batch file to all the child packages that the parent (main) package is using.
Thanks,
Andrew
Can some one point me in the right direction? I need to execute a DTS package after a set of T-SQL statements run in a stored procedure. The starting of the DTS package needs to be the last thing executed/called from the stored procedure.
Thanks
When run in query analyzer, the procedure below returns no errors however it doesn't execute the dts package I am passing to it.
The package works beautifully if you run it directly in Enterprise Manager.
Can anyone help me with a possible causes or perhaps help me improve my error trapping to help me resolve this?
create procedure sp_ExecuteDTSPackage
@login varchar(15),
@userpwd varchar(15),
@svr varchar(15),
@packagename varchar(255),
@pkgPwd varchar(15)
as
declare @hr int
declare @object int
--create a package object
EXEC @hr = sp_OACreate 'DTS.Package', @object OUTPUT
if @hr <> 0
Begin
Print 'Package Creation Failed'
RETURN
end
declare @flag int
select @flag = 0 -- 0 = sql svr authentication, 256 = windows authentication
EXEC @hr = sp_OAMethod @object, 'LoadFromSqlServer',NULL, @ServerName=@svr, @ServerUserName=@login,
@PackageName=@packagename, @Flags=@flag, @ServerPassword = @userpwd, @PackagePassword = @pkgPwd
if @hr <>0
begin
Print 'Package load failed'
RETURN
end
-- execute the package
EXEC @hr = sp_OAMethod @object, 'Execute'
IF @hr <> 0
BEGIN
print 'Package execution failed'
RETURN
END
--return the step errors as a recordset
--
EXEC @hr = sp_OAMethod @object, 'UnInitialize'
IF @hr <> 0
BEGIN
print 'UnInitialize failed'
RETURN
END
-- release the package object
EXEC @hr = sp_OADestroy @object
if @hr <> 0
Begin
RETURN
end
hi, I have created a DTS package that export data to another database. I always click on the package itself to run it. Now I need to be able to create an asp page to execute that DTS pakcage how can I do that.
Thanks for your help
Al
Hi All,
I hav a strange problem. I created a SSIS package on my machine with protection level as "EncryptAllWithPassword".
When I deploy this package on a SQL Server on another machine (either in File System or SQL Server)and add it as a job in SQL Server Agent and try to run it through a scheduler or manually the job fails with the following message -
Executed as user:IBM-P4-1976SYSTEM. The package could not be loaded. The step failed.
However if I deploy it on my own machine the job runs successfully.
Also if I run the package using dtexec utility on the another machine it runs successfully.
It is not running as a job on another machine. All the logged in users have administrative rights. My databases are altogether on a different server.
Can any one help me out with this issue...Pleassssseeee...
I'm looking for an example of how to execute an existing DTS* packagefrom anASP (VB)script and would appreciate any and all response. *I don'tevenknow if it's possibleThanks- Chuck GattoDan Guzman Apr 27 2000, 12:00 am show optionsNewsgroups: comp.databases.ms-sqlserverFrom: "Dan Guzman" <DGuz...@nospamplease-earthlink.net&g*t; - Findmessages by this authorDate: 2000/04/27Subject: Re: Executing a DTS Package using an ASP (VBScript) ScriptReply to Author | Forward | Print | Individual Message | Show original| Report AbuseThis VBScript example loads and executes an existing DTS pac*kage fromSQLServer.Option ExplicitConst PackageName = "PackageName"Const ServerName = "ServerName"Const UserName = "UserName"Const Password = "Password"Dim DTSPkSet DTSPk = CreateObject("dts.package")DTSPk.LoadFromSQLServer ServerName, UserName ,Password,,,,,*PackageNameDTSPk.ExecuteIf DTSPk.Steps(1).ExecutionResult = 0 ThenResponse.Write "Package execution completed"ElseResponse.Write "Package execution failed"End IfSet DTSPk = NothingYou can also create the entire package from scratch from wit*hin yourasp andexecute it.Hope this helps.Chuck Gatto <cga...@anchorsystems.com> wrote in messagenews:8eapo8$frj$1@slb7.atl.mindspring.net...- Hide quoted text -- Show quoted text -[color=blue]> I'm looking for an example of how to execute an existing D*TS[/color]package froman[color=blue]> ASP (VB)script and would appreciate any and all response.* I don't[/color]even[color=blue]> know if it's possible> Thanks> - Chuck Gatto[/color]Chuck Gatto May 1 2000, 12:00 am show optionsNewsgroups: comp.databases.ms-sqlserverFrom: "Chuck Gatto" <cga...@anchorsystems.com> - Find messages by thisauthorDate: 2000/05/01Subject: Re: Executing a DTS Package using an ASP (VBScript) ScriptReply to Author | Forward | Print | Individual Message | Show original| Report AbuseBelow code works 100% in VB but the load fails in ASP. I g*et..."Microsoft OLE DB Provider for SQL Server. Login failed for *user "".error.I think the IIS (server a) is set for NT auth. and sql7 (on *server b)aswell but I can't be sure.Any idea what I should look for.Thanks"Dan Guzman" <DGuz...@nospamplease-earthlink.net> wrote in m*essagenews:sgi3hi617qo89@corp.supernews.com...- Hide quoted text -- Show quoted text -[color=blue]> This VBScript example loads and executes an existing DTS p*ackage[/color]from SQL[color=blue]> Server.[/color][color=blue]> Option Explicit> Const PackageName = "PackageName"> Const ServerName = "ServerName"> Const UserName = "UserName"> Const Password = "Password"> Dim DTSPk> Set DTSPk = CreateObject("dts.package")> DTSPk.LoadFromSQLServer ServerName, UserName ,Password[/color],,,*,,PackageName[color=blue]> DTSPk.Execute> If DTSPk.Steps(1).ExecutionResult = 0 Then> Response.Write "Package execution completed"> Else> Response.Write "Package execution failed"> End If> Set DTSPk = Nothing[/color][color=blue]> You can also create the entire package from scratch from w*ithin[/color]your aspand[color=blue]> execute it.[/color][color=blue]> Hope this helps.[/color][color=blue]> Chuck Gatto <cga...@anchorsystems.com> wrote in message> news:8eapo8$frj$1@slb7.atl.mindspring.net...[color=green]> > I'm looking for an example of how to execute an existing* DTS[/color][/color]packagefrom[color=blue]> an[color=green]> > ASP (VB)script and would appreciate any and all respons*e. I[/color][/color]don't even[color=blue][color=green]> > know if it's possible> > Thanks> > - Chuck Gatto[/color][/color]Dan Guzman May 1 2000, 12:00 am show optionsNewsgroups: comp.databases.ms-sqlserverFrom: "Dan Guzman" <DGuz...@nospamplease-earthlink.net&g*t; - Findmessages by this authorDate: 2000/05/01Subject: Re: Executing a DTS Package using an ASP (VBScript) ScriptReply to Author | Forward | Print | Individual Message | Show original| Report AbuseYou can specify a trusted connection with flag 256 instead o*fusername andpassword. For example:DTSPk.LoadFromSQLServer ServerName, , , 256,,,,PackageNa*meAssuming this is an intranet application running under NT 4.*0 and youwantto execute the package under the invoking user's account, yo*u can dothis asfollows:Specify 'clear text' for the IIS Directory Securityauth*enticationRemove 'Everyone' from the access list on the files (req*uiresNTFS) andgrant permissions to the usersGrant logins access to the database serverWith this method, users must enter their DomainUserName and* passwordwhenprompted.NT authentication presents a challenge when multiple servers* areinvolvedbecause NT 4.0 does not support delegation. Seehttp://msdn.microsoft.com/workshop/...re/security.asp fordetails.I understand Windows 2000 provides delegation capabilities b*ut thiscan be abit tricky to implement.BTW, if your DTS package does not access SQL Server, you can* save itto afile and use the LoadFromStorageFile method instead.Hope this helps.If you need to useChuck Gatto <cga...@anchorsystems.com> wrote in messagenews:8ekrkd$pmq$1@slb7.atl.mindspring.net...- Hide quoted text -- Show quoted text -[color=blue]> Below code works 100% in VB but the load fails in ASP. I* get...> "Microsoft OLE DB Provider for SQL Server. Login failed fo*r user[/color]"".error.[color=blue]> I think the IIS (server a) is set for NT auth. and sql7 (o*n server[/color]b) as[color=blue]> well but I can't be sure.> Any idea what I should look for.> Thanks[/color]Chuck Gatto May 6 2000, 12:00 am show optionsNewsgroups: comp.databases.ms-sqlserverFrom: "Chuck Gatto" <cga...@anchorsystems.com> - Find messages by thisauthorDate: 2000/05/06Subject: Re: Executing a DTS Package using an ASP (VBScript) ScriptReply to Author | Forward | Print | Individual Message | Show original| Report AbuseHey DanThanks again. I really appreciate your input and help.I actually solved the problem by calling dtsrun...I apologize for the delay getting back w/you but thee recent* virusatacksidetracked me.Thanks again."Dan Guzman" <DGuz...@nospamplease-earthlink.net> wrote in m*essagenews:sgsfh8spoog87@corp.supernews.com...- Hide quoted text -- Show quoted text -[color=blue]> You can specify a trusted connection with flag 256 instead* of[/color]username and[color=blue]> password. For example:[/color][color=blue]> DTSPk.LoadFromSQLServer ServerName, , , 256,,,,Package*Name[/color][color=blue]> Assuming this is an intranet application running under NT *4.0 and[/color]you want[color=blue]> to execute the package under the invoking user's account, *you can[/color]do thisas[color=blue]> follows:[/color][color=blue]> Specify 'clear text' for the IIS Directory Security[/color]au*thentication[color=blue]> Remove 'Everyone' from the access list on the files (r*equires[/color]NTFS)and[color=blue]> grant permissions to the users> Grant logins access to the database server[/color][color=blue]> With this method, users must enter their DomainUserName a*nd[/color]password when[color=blue]> prompted.[/color][color=blue]> NT authentication presents a challenge when multiple serve*rs are[/color]involved[color=blue]> because NT 4.0 does not support delegation. See> http://msdn.microsoft.com/workshop/...re/security.asp for[/color]details.[color=blue]> I understand Windows 2000 provides delegation capabilities* but this[/color]can bea[color=blue]> bit tricky to implement.[/color][color=blue]> BTW, if your DTS package does not access SQL Server, you c*an save[/color]it to a[color=blue]> file and use the LoadFromStorageFile method instead.[/color][color=blue]> Hope this helps.[/color][color=blue]> If you need to use> Chuck Gatto <cga...@anchorsystems.com> wrote in message> news:8ekrkd$pmq$1@slb7.atl.mindspring.net...[color=green]> > Below code works 100% in VB but the load fails in ASP. * I get...[/color][/color][color=blue][color=green]> > "Microsoft OLE DB Provider for SQL Server. Login failed *for user[/color][/color]"".[color=blue]> error.[color=green]> > I think the IIS (server a) is set for NT auth. and sql7 *(on[/color][/color]server b) as[color=blue][color=green]> > well but I can't be sure.> > Any idea what I should look for.> > Thanks[/color][/color]chris.duni...@agwsha.nhs.uk Jan 31, 10:05 am show optionsNewsgroups: comp.databases.ms-sqlserverFrom: chris.duni...@agwsha.nhs.uk - Find messages by this authorDate: 31 Jan 2005 10:05:00 -0800Subject: Re: Executing a DTS Package using an ASP (VBScript) ScriptReply | Reply to Author | Forward | Print | Individual Message | Showoriginal | Report AbuseHi,I've tried adding this function to my ASP pages and get the *followingerror message;Microsoft VBScript runtime error '800a01ad'ActiveX component can't create object: 'DTS.Package'/asp/pages/dts.asp, line 21Does anyone have any idea what I need to fix to get the DTS *to work?I'm v. new to SQL Server and ASP so any help would be apprec*iated.Many thanks, Chris Dunigan
View 4 Replies View Relatedhi!
I am able to run the pacakge in command line but when i try running the package through agent . The it starts the pacakge it says that it has been started by me local user and fails in next step saying that it has been execute by administrator.
I understand that there is a miss match between the permissions. Cos the SQL server Agent uses my login in but the SQL server agent on the Server that i am trying to execute has the Administrator log in. How to solve the issue?
Thanks in advance
jasmine
Can someone tell me the best way to "automatically" execute a SSIS package. I have approximately 100 computers that I need to execute packages on. Here is the scenario:
I have some text files that are coming from our AS400 to a local computer (the package will NOT be used to bring files from our AS400. I will be using Client Access for that). The text files will be stored in a specified directory on the local computer. I will be importing these text files into a SQL Database on all 100 machines. Once I create the Import Package, what is the best way to execute this package on the devices?
I thought maybe after creating the package, I can put an EXE on the desktop that will execute the SSIS package. This way all I would have to do is click a icon on the desktop. I don't know if this is feasible or not. After building a package, do you make it a EXE or what? Pease advise. Thank in advance.
Hi,
I'm having another go at attempting to call an SSIS package from a web service. The Web Service is set up on the same server as the sql server and SSIS package deployment. When i attempt to run the pacage from the web service it starts and then inmediately fails. I can see this in both the Integration Service Logs and the event viewer.
If i click on the package itself and run it using the DTExecUI, it runs without a problem. No logs are output by the package when called by the web service.
Can anyone suggest anything that i could to see if i can diagnose where the problem stems from.
Many thanks in advance,
Grant
Hi,
Simplification of setup:
- SQL Server Box with Enterprise Edition of SQL Server 2005
- Workstation with the client tools (Managment Studio, Business Intelligence Studio, etc) SQL Server 2005 installed running Windows XP
1. Am I right in saying that I need to install the SSIS service in order to run this package on my workstation from the command line, the Execute Package Utility tool, or through Management Studio?
2. I can however execute the package on my workstation in the Business Intelligence Studio without the SSIS service installed, correct?
3. Assuming I need to install the SSIS service on my workstation, if I execute the package via the command line or the Execute Package Utility utility, will it run on my local machine or on the server (i.e. which gets the load)?
4. Does Service Pack 1 address these issues?
Thanks a lot for any help you could provide!
John
Hi
I've a doubt. I don't know whether it is possible or not.
I've a very less knowledge in DTS packages.
When I execute a package in C#. it is displaying predefined GUI for showing process.
Can I've my own UI when executing DTS.
Thanks in advance.
Hi all,
I have a dts package that wil load data from excel file to sql server 2005 database tables deployed in our sql server. I have a asp.net application in my machine. I need to call execute that package from my asp.net page. i need to pass values for 2 varaibles in the package from my asp.net code based on that the package will choose the excel file to load. When i directly call the package from the server it works fine. but when i call it from the asp.net page the dts package cant access the excel file. I dont know the reason for this. please help me to solve the problem. if u still have any doubts plz let me know.
Sathish.N
Hi again
First I explain my environment:
- SSIS Packages are stored on 64bit Server (Win 2003 64bit, SQL 2005 64bit)
- .NET (C#) application (on Win 2003 32bit Server) which loads and executes the SSIS Packages.
I can execute my SSIS Package localy on the 64bit Server and they work fine.
When I execute the SSIS Package from the Application I get following exeption:
Retrieving the COM class factory for component with CLSID {697AC67E-FDD5-46D1-90D7-F3D0DF1B2A47} failed due to the following error: 80040154. ...
at Microsoft.SqlServer.Dts.Runtime.Package..ctor()
The code looks like:
Microsoft.SqlServer.Dts.Runtime.Package local_Package = new Microsoft.SqlServer.Dts.Runtime.Package();
Microsoft.SqlServer.Dts.Runtime.DTSExecResult local_DTSExecResult = new Microsoft.SqlServer.Dts.Runtime.DTSExecResult();
Microsoft.SqlServer.Dts.Runtime.Application local_Application = new Microsoft.SqlServer.Dts.Runtime.Application();
local_Package = local_Application.LoadFromDtsServer("PackageName", "64bitServer", null);
local_DTSExecResult = local_Package.Execute();
Other question would be: how can I execute a package directly on the server??
I mean without "LoadPackage"?? I guess the problem on top is because on the
32bit Server there is only Framework installed but not Integration Services Tools ...
Would be happy for any comment!
Best regards
Frank Uray
Hi,
when I run the following from the command prompt I am getting an error saying that can not set the variable Status as int.
dtexec /File "C:workSSIS PackagesDataflowControlRoom.dtsx" /SET
Package.Variables[ChannelCode].Value;"test" /SET
Package.Variables[Status].Value;1
both variables as you can see are in the top level.
the problem is that ChannelCode is string but Status is Integer.
if I take out the /SET Package.Variables[Status].Value;1 part from the command line it will work fine. I think th eproblem is down to diffenrt types of variable as there are two types of String and Interger.
any ideas on what the problem migth be.
p.s. I am runnning SQL server 2005 Dev edition (without any SP)
Cheers
I wrote package using Integration Services and tried execute it on Visual Studio 2003 (Visual Basic). Package was loaded, but execute method has failed without any error messages. When I tried to run it on Visual Studio 2005, execution was successful.
Codes:
Dim App As DTSLib.IDTSApplication90 = New DTSLib.Application
Dim p As DTSLib.IDTSPackage90 = App.LoadPackage("d: empPackage.dtsx", True, Nothing)
p.Execute()
What can be the reason of the trouble?
Thanks,
Alexander