Execute DTS Packages
Jul 23, 2005
I have created a functioning DTS package inside Enterprise Manager and
now I want to be able to execute it outside of EM. The package imports
data from an SQL dbase to a Visual FoxPro dbase. I would like this DTS
package to execute everytime an Insert(of certain critera) is made on
the SQL dbase. I am thinking that using an Insert Trigger on the SQL
table is the way to go about this. I am seeking advice as to... is this
the best way to go about this.
Also, and if so, in a trigger which route is the best to take...
1. use dtsrun command line utility or
2. setup a COM object to run the DTS package
I have tried both in SQL query analyzer and am having troubles with the
syntax. Could anyone possibly send the code for both ways. Thanks.
Good Day to all,
Brett
View 10 Replies
ADVERTISEMENT
Apr 22, 2015
We manage some SSIS servers, which has only SSIS and SSIS tools installed on them and not the sql server DB.
SSIS packages and configuration files are deployed on a NAS. We run the SSIS packages through DTEXEC by logging in to the server.
We want to allow developers to run their packages on their own on the server, but at the same time we dont want to give them physical access on the server i.e we do not want to add them into RDP users list on server properties. We want them to allow running their packages remotely on the server.
One way We could think of is by using powershell remoting and we are working on that. But is there any other way or any tool already present for the same.
View 4 Replies
View Related
Sep 12, 2007
I have couple of DTS Packages that I want to run on the server running SQL Server 2000, how do I go about running those? Any help will be greatly appreciated.
View 2 Replies
View Related
Sep 10, 2007
Does anybody have feedback on how to execute several packages using c#.
Thanks Sergio
View 1 Replies
View Related
May 22, 2001
Hi,
I'm using SQL 7.0 SP2 on NT SP6a and have a quick question.
I want to create a scheduled job to execute several DTS packages (all located on the same server). I can use Enterprise Manager to schedule an individual package but the command line of the step has something like:
DTSRun /~S 0x26CE410E3EFF5E8B738DC1C3527EEA1A /~N 0xCAAF8584582AA98C09F5CF132CB0BB5F296D1E9053DD6526 717A53AD4134C3463615FEF20330386927242B6CB7070036 /E
Is using the DTSRUN utility the only way to create several steps in a job to run DTS packages ?
Any help appreciated.
Thanks,
Tim
View 1 Replies
View Related
Mar 28, 2008
I have a schedule job that errors out on the first step when I attempt to run it. There are 3 packages that I created and I can run them all manually just fine.
I get this error when I try to run the job:
-----------------------------------------------
Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,
Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
03/28/2008 13:49:22,ST Customer Data,Error,0,Server01,ST Customer Data,(Job outcome),,The job failed.
The Job was invoked by User sa.
The last step to run was step 1 (config 1).
The job was requested to start at step 1 (config 1).,00:00:01,0,0,,,,0
03/28/2008 13:49:22,ST Customer Data,Error,1,Server01,ST Customer Data,config 1,,
Executed as user: Server01SYSTEM. ...9.00.3042.00 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 1:49:23 PM Error: 2008-03-28 13:49:23.23
Code: 0xC0016016 Source:
Description: Failed to decrypt protected XML node "DTS:Password" with
error 0x8009000B "Key not valid for use in specified state.".
You may not be authorized to access this information.
This error occurs when there is a cryptographic error.
Verify that the correct key is available. End Error
Error: 2008-03-28 13:49:23.57 Code: 0xC0202009
Source: Config 1 Customer Data Connection manager "SourceConnectionOLEDB"
Description: SSIS Error Code DTS_E_OLEDBERROR.
An OLE DB error has occurred. Error code: 0x80040E4D.
An OLE DB record is available.
Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D
Description: "Communication link failure".
An OLE DB record is available.
Source: "Microsoft SQL Native Client"
Hresult: 0x80...
The package execution fa...
The step failed.,00:00:01,0,0,,,,0
-----------------------------------------------
Any ideas?
Thanks,
John
View 2 Replies
View Related
Sep 13, 2007
All,
Could someone please tell me what to do with it and what might be causing it?
The packages were fine yesterday.
Here is the error message:
[Script Component 1 1 [3941]] Error: System.Runtime.InteropServices.COMException (0x80040154):
Retrieving the COM class factory for component with CLSID {A138CF39-2CAE-42C2-ADB3-022658D79F2F}
failed due to the following error:
80040154. at Microsoft.VisualBasic.Vsa.VsaEngine.CreateEngine()
at Microsoft.VisualBasic.Vsa.VsaEngine.CheckEngine()
at Microsoft.VisualBasic.Vsa.VsaEngine.set_RootMoniker(String value)
at Microsoft.SqlServer.VSAHosting.Runtime.CreateVsaEngine()
at Microsoft.SqlServer.Dts.Pipeline.ScriptRuntime..ctor(String projectName,
String moniker, String language, Boolean showErrorUI)
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.CreateUserComponent()
Thanks in Advance
View 1 Replies
View Related
Nov 8, 2007
Hi!
I was following this article http://www.codeproject.com/useritems/Execute_Package_by_C_.asp to execute list of SSIS packages via vb.net. I created a windows application in server A copied the exe file in server B. the SSIS package is in server B. I deployed the package and it runs file vis integration server. below are my codes. everytime i run the exe in server B. I can see the list of packages but when i hit execute button i get error msgs that says "failure" I am not sure how to get more error out of it. I am not sure whats causing this error.
----------------------------
Imports System.Data.SqlTypes
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Smo.Agent
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Management.Common
Public Class Form1
Dim app As New Microsoft.SqlServer.Dts.Runtime.Application()
Dim pInfos As PackageInfos = app.GetPackageInfos("\", "SQLKEV", "", "")
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
For Each pInfo As PackageInfo In pInfos
If pInfo.Name <> "Maintenance Plans" Then
ComboBox1.Items.Add(pInfo.Name)
End If
Next
End Sub
Private Sub btnExec_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExec.Click
Dim app As Microsoft.SqlServer.Dts.Runtime.Application
app = New Microsoft.SqlServer.Dts.Runtime.Application()
Dim pkgResults_Sql As DTSExecResult
Dim pkgIn As New Package()
Try
pkgIn = app.LoadFromSqlServer("" + ComboBox1.SelectedItem.ToString().Trim(), "SQLKEV", "", "", Nothing)
pkgIn.PackagePassword = "password21"
pkgResults_Sql = pkgIn.Execute()
MessageBox.Show(pkgResults_Sql.ToString())
Catch ex As Exception
MsgBox("this is an exception" + ex.ToString)
End Try
End Sub
End Class
----------------------------
please help
View 9 Replies
View Related
Jun 20, 2001
I have SQLServer 7, and have used Enterprise Manager to schedule local packages. The package that I'm trying to do is run a SQL script on a nightly basis. The problem that I'm having is that I need to be able to install the scheduled script customer box using an installer. I have access to execute command line programs in the installer. Do anyone know if you can schedule local packages (using SQL Scripts) from the command line? Or if a 3rd party application can do this. Any help or direction would be greatly appreciated. I've tried to use sp_add_job and sp_add_jobschedule, but haven't been able to get them to work.
Thanks
Brian
View 1 Replies
View Related
Apr 26, 2004
Hi, hoping someone may be able to shed some light on this.
I have a stored procedure that conditionally executes different DTS Packages. Everything seems to work, there's no errors at all, but the DTS Packages are never executed. Wondering if anyone has any ideas.
Here's the stored procedure....
CREATE PROCEDURE exportLists
AS
DECLARE @script VARCHAR(8000)
DECLARE @id INT
DECLARE @max INT
select regionid,count(distinct homephone) as phone
into #phone
from tbl_template
Where exportid is null
group by regionid
select officeid,listpath
into #office
from tbl_office
WHERE startdate <= GETDATE()
AND (enddate >= GETDATE() OR enddate IS NULL)
and listpath is not null
and listpath <> ''
Select case when phone > 2500 then '"exportLists" /A MDBFile="' + listpath + '" /A OfficeID ="' + convert(varchar(10),officeid) + '" '
Else '"resetTemplate" /A OfficeID="' + convert(varchar(10),officeid) + '" '
end as cmd
,Identity(int,1,1) as rowno
,phone
into #temp
from #office as O
inner Join #phone as p
on o.officeid=p.regionid
Set @max=@@rowcount
set @id = 1
While @id <=@max
Begin
Select @script='"Dtsrun /S "' + @@SERVERNAME + '" /E /N ' + cmd
+ case when phone > 2500
then '/A ExportID="' + convert(varchar(10),(Select coalesce(Max(listid),0) + 1 from tbl_sf3Lists)) + '" '
else ' ' end
, @id=@id+1
from #temp
Where rowno = @id
End
Return
GO
View 1 Replies
View Related
Feb 18, 2008
Hi
I have an SSIS package that wont run whne i try to run it in one go, but if i run it step by step it will succeed
as the reason its failing is in the 2nd step it references a table that doesn't exist (its created in the 1st step.
So at the start package is trying to validate, but it won't as the table is created in step 1.
So this is why it works if i execute this step by step, is there a way of turning off the pre run validation
on SSIS scripts ?
Thanks
View 1 Replies
View Related
Nov 30, 2006
HI, I have some parent parent packages that calls child packages. When I added a bunch of packages, I faced the buffer out of memory error. I then decided to set the child packages property ExecuteOutOfProcess to TRUE. I noticed that the execution time is longer now. Is this a good practice to set the ExecuteOutOfProcess to true? If so, is it normal that the execution time is longer?
Thank you,
Ccote
View 2 Replies
View Related
Dec 29, 1999
Please help,
I have a DTS package that consist of approximately 50 steps which pulls data from an as400 and populates a SQL 7.0 database. The package works perfect flawlessly when executed manually but won't run when scheduled.
The DTS package starts but hangs when it tries to connect to the as400, this is where the problem lies. The preceeding steps work fine but they execute against the SQL 7.0 database. I have client access installed on the server and configured. I checked and made sure the SQL server agent service was started and running also,I just can't figure why it works fine manually,but not when scheduled.
Any help would be welcome...
View 2 Replies
View Related
Aug 17, 2007
I am seeking a walkthrough for executing packages via a web service. All I have found so far are fragmented bits of information. This article is a good start but it leaves out some critical security setting information. http://msdn2.microsoft.com/en-us/library/ms403355.aspx#service.
Does anyone know of a good walkthrough that includes the security setup: Impersonation, Proxies, etc.?
View 6 Replies
View Related
Jan 26, 2006
I have 200+ plus packages that need to be flexible in how they are run. For example, an end user may choose to run packages 1,2,3 and the next end user may choose to run packages 2,3,7, etc. Prior ro running a package, I set an "instance id" inside the group of packages so I can tie them all together in the logfile - I know that packages 1,2,3 were all run as group and that's distinct from packages 2,3,7 that were run in a differnt group.
Initially I embarked on a scenario where I had a queue table that loaded up the packages to be run and then had a little c# app that read the queue, generated the "instance id" and ran all the packages (either thru dtexec.exe or the Microsoft.SqlServer.DTS.Runtime). But now I wonder if using a master package that uses the Execute Package Task is the way to go. My 200+ packages are all independent and run based on a single config file and it seems as though going the parent package route will destroy some of that independence because I'll now be relying on parent package variables.
Any comments or suggestions?
View 2 Replies
View Related
Mar 28, 2007
Question is in the subject.
The reason I'm asking is because I want a workaround to a problem that a guy is having here:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1404349&SiteID=1
-Jamie
View 1 Replies
View Related
May 4, 2015
I have 12 packages to execute in order. I made a table in my DB where i mentioned the name of each package and his order in execution.
I want to create a master package that get the name and order from my DB table to execute all packages.
View 3 Replies
View Related
Oct 14, 2008
I'm pretty new to SSIS but I've managed to cobble together a number of individual packages to refresh SQL tables from a 3rd-party database.
Now, what I'd like to do is have a single package that I can use to invoke each of the individual ones. Since it will run on a quad, I'd like to invoke them such they'll run in parallel.
View 2 Replies
View Related
Oct 27, 2015
I have 100 packages all of them stored in "Integration services catalog". Is there a way to execute packages using script task in ssis 2012.
View 5 Replies
View Related
May 27, 2008
I've been executing a package and passing a parent variable to the child using package configurations but I'd now like to do this using a script task. The script task would then programmatically load the package and execute it.
How do I do this and still use the parent variable?
I've found examples of how to load a package but I haven't been able to find out how to I load it specifying the parent variables.
I think it's possible. MSDN shows the available methods but the example is for the base method.
http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.package.execute.aspx
Cheers,
Ben
View 1 Replies
View Related
Jun 15, 2006
I am trying to migrate our processing from command line based scripts and foxpro to SQL so I need to run the SSIS packages using dtexec. I copied the dtexec file and a few dll's that are missing to our production servers but i cant execute the packages. I dont want to install the full client tools (particularly managment/business inteligence studio) on our production servers due to the overhead and limited system disk space.
Can somebody tell me what the minimum install would be so I would be able to run SSIS packages using the dtexec or dtexecui tools? I would also like to install some of the other command line client tools like osql etc.
View 8 Replies
View Related
Aug 28, 2007
Need some help...
When we tried to run mulitple packages one after the other from a windows service, first one succeeds but later ones are throwing below error :
"The script threw an exception: The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.
A deadlock was detected while trying to lock variable "System:ackageName, User::BusinessDate, User::Environment, User:ortfolioName" for read access. A lock could not be acquired after 16 attempts and timed out."
Later, we tried to create separate AppDomains for each package and execute via console application, but ended up with below error (The below expressions were defined in OnError Event) :
"The result of the expression "@[User::ReportErrorFrom]" on property "FromLine" cannot be written to the property. The expression was evaluated, but cannot be set on the property.
The result of the expression ""Error At :" + @[System:ourceName] + "" +
"Error Description : "+ @[System::ErrorDescription] + "" " on property "MessageSource" cannot be written to the property. The expression was evaluated, but cannot be set on the property."
At last, we tried to span a separate process (System.Diagnostics.Process) for each package. this seems working but taking very long time:
A package that normally takes 2 min, is taking 60 min.
We also tried creating an SSIS Package that executes mulitple packages. But only first package is getting executed, and second one is throwing below error (Here the variable it is trying to lock is of first package):
"Failed to lock variable "UniqueInstrumentsQuery1" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".
Please help us with some work around for this. Thanking you in advance,
View 17 Replies
View Related
Jun 26, 2015
I have created for each container to call all the packages in a folder like below, also created a variable.
Then I add execute package task inside of foreach container and selected file system in a location and in connection called currently creating package name finally in connection properties i added variable in expression which i created and mapped into for each loop container. I referred below link
[URL] ....
All the packages are running but its not ending once all the packages executed its re run and continue the running process, how to stop once all the packages execute.
View 24 Replies
View Related
Sep 4, 2015
We are building a dataload application where parameters are store in a table. And there are multiple packages for each load.There is a column IsChecked column if it is 1 then only the child package should execute.Created a master package. In which i have taken execute SQL task in that storing a results in variable and based on the result the child package should execute. But In executesql task i selected result set as full result set. I am getting the below error.
[Execute SQL Task] Error: Executing the query "SELECT isnull(ID ,0) AS ID FROM DataLoadParameter..." failed with the following error: "The type of the value (DBNull) being assigned to variable "User::LoadValue" differs from the current variable type (Int32). Variables may not change type during execution. Variable types are strict, except for variables of type Object.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
View 3 Replies
View Related
Sep 18, 2007
Hi all,
I have a problem while i create a proxy account.The situation is like this...There is a user who has an login in to the server.He has a stored procedure which calls some on the SSIS packages and XP_cmdshell...so this stored procedure basically load some data in to the tables .So for the login in order to execute the stored procedure as he is not a Sys admin I have created a proxy account in my account as Iam an SA and then in the proxies and in principals I selected his login name and this way I have created a credential and a proxy account.
Now the problem is if he logins with his id and password and try to execute the stored procedure it gives an error message
Server: Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1
The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information.
....so this mean the login is not able to see the proxy account.So what I did is I created a job and then in the job owner tab I have selected his login and then created a step with the type operating system (CmdExec) as I need to just execute the stored procedure and used the proxy account that I have created.
so I gave the command -- exec <stored procedure> --.
But this job fails and gives the error message as
[298] SQLServer Error: 536, Invalid length parameter passed to the SUBSTRING function. [SQLSTATE 42000]....
So now ....first My question is am I doing in a right way....if its right then why Iam not able to execute the stored procedure.
If there is any other way through which I can execute the stored procedure using a proxy account for the logins who are not sys admins....please do let me know.....
Thanks
Raja.V
View 2 Replies
View Related
Jan 9, 2007
I've run into a problem with SSIS packages wherein tasks that write or copy files, or create or delete directories, quit execution without any hint of an error nor a failure message, when called from an ASP.NET 2.0 application running on any other machine than the one where the package was created from. By all indications it appeared to be an identity/permissions problem.
Our application involves a separate web server and database server. Both have SQL Server 2005 installed, but the application server originally only had Integration services. The packages are file system-deployed on the application server, and are called using Microsoft.SqlServer.Dts.Runtime methods. For all packages that involve file system tasks, the above problem occurs.
When the above packages are run using the command prompt (either DTEXEC or DTEXECUI) the packages execute just fine. This is expected since we are using an administrative account. However when a ShellExecute of the same command is called from ASP.NET, the same problem occurs.
I've tried giving administrative permissions to the ASPNET worker process user to no avail.
I have likewise attempted to use the SQL Server Agent job approach but that approach might not be acceptable for our clients since it means installing SQL Server 2005 Database services on the application server.
I have read the relevant threads in this forum, namely http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1044739&SiteID=1 and http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=927084&SiteID=1 but failed to find any solution appropriate for our set up.
Anybody got any idea on how to go about this?
View 33 Replies
View Related
Aug 2, 2006
Hi all
Our data management system currently runs DTS packages using DTSPKG.dll.
I am currently looking at the possibliity of replacing the DTS packages and SQL 2000 with DTSX packages using SSIS in SQL 2005.
Do I need a new dll? or will the current dtspkg.dll handle the new DTSX packages?
Many thanks in advance!
View 1 Replies
View Related
Dec 6, 2006
Dear all:
I had got the below error when I execute a DELETE SQL query in SSIS Execute SQL Task :
Error: 0xC002F210 at DelAFKO, Execute SQL Task: Executing the query "DELETE FROM [CQMS_SAP].[dbo].[AFKO]" failed with the following error: "The transaction log for database 'CQMS_SAP' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
But my disk has large as more than 6 GB space, and I query the log_reuse_wait_desc column in sys.databases which return value as "NOTHING".
So this confused me, any one has any experience on this?
Many thanks,
Tomorrow
View 5 Replies
View Related
Apr 19, 2007
I'm looking for a way to refer to a package variable within any
Transact-SQL code included in either an Execute SQL or Execute T-SQL
task. If this can be done, I need to know the technique to use -
whether it's something similar to a parameter placeholder question
mark or something else.
FYI - I've been able to successfully execute Transact-SQL statements
within the Execute SQL task, so I don't think the Execute T-SQL task
is even necessary for this purpose.
View 5 Replies
View Related
Mar 6, 2008
Hi.
I have a master package, which executes child packages that are located on a SQL Server. The Child packages execute other child packages which are also located on the SQL server.
Everything works fine when I execute in process. But when I set the parameter in the mater package ExecutePackageTask to ExecuteOutOfProcess = True, I get the following error
Error: 0xC00470FE at DFT Load Data, DTS.Pipeline: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "Row Count" (5349).
Error: 0xC00470FE at DFT Load Data, DTS.Pipeline: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "SCR Custom Split" (6399).
Error: 0xC00470FE at DFT Load Data, DTS.Pipeline: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "SCR Data Source" (5100).
Error: 0xC00470FE at DFT Load Data, DTS.Pipeline: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "DST_SCR Load Data" (6149).
The child packages all run fine when executed directly, and the master package runs fine if Execute Out of Process is False.
Any help would be greatly appreciated.
Thanks
Geoff.
View 7 Replies
View Related
Jun 25, 2007
I have a SSIS package contains an "Execute SQL Task". The SQL will raise error or succeed. However, it sounds the package won't pick up the raised error?
Or is it possible to conditional run other control flow items according the the status of SQL task execution?
View 1 Replies
View Related
Jan 25, 2007
I am trying to execute a SP in the execute SQL task in SSIS 2005..
but I keep getting an error:
SSIS package "Package.dtsx" starting.
Error: 0xC002F210 at Load_Gs_Modifier_1, Execute SQL Task: Executing the query "exec Load_GS_Modifier_1 ?, ?" failed with the following error: "Could not find stored procedure 'exec Load_GS_Modifier_1 ?, ?'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Load_Gs_Modifier_1
SSIS package "Package.dtsx" finis
I have set up two user parameters: startdate and enddate.. I am not sure what I am doing wrong????
View 3 Replies
View Related
Apr 16, 2001
Can you use multiple tables in a DTS job?
I copy a large data table from one database to another every night, but I only want to copy the data since the last time the DTS job ran. I am going to create new job that writes the date of the update to a date table.
I then want the DTS job to compare the invoice date from the datawarehouse table to the last date in the Date table, and only write the data with a newer date.
I know the script if the values were in the same table - but how do I use two different tables?
View 2 Replies
View Related
