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 ?
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.
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()
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.?
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.
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,
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.Â
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.
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.....
I have a Problem with my SSIS and it is that when you open the package it says "Validating <Data Flow>" and it takes a loooooong time to finaly open ( by long I mean like 3 or 4 hours to just for open!!!). In 3 of the DF, I have conections to a Unisys server and in exactly in this DF is where it takes its time. This Unisys system its kind a slow but not that slow. I am running on a server that have 4 G and 8 processors, so it cant be the machine.
my question is, How can I make it validate faster ? I have tried to work Offline but when I run the package I need to be online.
I have a MsAccess db containing a table called Employees which i am transforming to a staging table in Sql server 2005. Everything is working fine. I am using Foreach File enumerator and uploading the files one by one.However I now plan to validate the schema of MsAccess before uploading it. For eg: My employee table in msaccess is as follows :
Code Snippet Employees empId int, empName varchar(60), empAge int
Since the files come from different vendor, while looping, i want to perform a check if the empid or empAge are not of type long or string etc. If they are of type smallint,i have no problem.
However if they are larger datatypes than the the ones kept in Sql server, then the file needs to be logged in the db with the reason and moved to the error folder. In short, if the datatypes in access tables are smaller than those in Sqlserver, allow it, otherwise reject it. THe schema of Sqlserver table is same as of that of Employees in msaccess.
I want to compare the schema of the incoming access tbl fields with my desired schema and all mdb's having data types that are higher or incompatible with the desired schema should be moved to the error.
I'm stuck here when I try to open an existing package which contains several data flows, SSIS tries to validate each data flow and after a while a Visual Studio error message pops up and I can't do anything.
The error message says : "Unable to cast COM object of type 'System._ComObject' to interface type 'Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSObject90'. This operation failed because the QueryInterface call on Com component for the interface with IID '...GUID...' failed due to the following error : The application called an interface that was marshalled for a different thread. (Exception from HResult: RPC_E_WRONG_THREAD)"
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.
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.
I have created a functioning DTS package inside Enterprise Manager andnow I want to be able to execute it outside of EM. The package importsdata from an SQL dbase to a Visual FoxPro dbase. I would like this DTSpackage to execute everytime an Insert(of certain critera) is made onthe SQL dbase. I am thinking that using an Insert Trigger on the SQLtable is the way to go about this. I am seeking advice as to... is thisthe 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 or2. setup a COM object to run the DTS packageI have tried both in SQL query analyzer and am having troubles with thesyntax. Could anyone possibly send the code for both ways. Thanks.Good Day to all,Brett
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:
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: -----------------------------------------------
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
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()
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?
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.
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.
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
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?
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.
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.
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.
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.
