I'm running a large series of not-too-complex SSIS packages locally from within a .Net 2.0 process (a Windows Service). After trolling PerfMon for some time I'm convinced that the unmanaged code invoked by the SSIS API is still leaking. I'm running SQL2005 SP2 (3042) and .Net 2.0.50727.42.
I've read a few other threads that indicated there was a known issue, but they're dated some time ago, so I figure they'd have been addressed by the SQL service pack.
I migrated a few stored procedures from SQL 2000 to SQL 2005. When the packages are run from the box where the SSIS and SQL Server (Windows server) is installed it is running fine without any issues. When run from my local machine (client) where I have installed the SQL Server Client, the same package would not execute and gives me some weird error messages. I am a local admin on the box. Is this a issue with SSIS? Do we have to log onto the box (server) and then execute the packages?
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.
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 am a newby to SSIS and it has been around 2 weeks since I have started with SSIS in my project.
Now, I have been able to successfully build the SSIS package for my project need which is to export all the data from excel source file to the destination database tables after certain validations and modifications. So, when I run the package from the Visual Studio, it runs fine, but actually we have to call the SSIS package from the Webpage through ASP.NET. I have used following code, for loading and executing the package on the page load event.
// Load package from file system Package package = app.LoadPackage("C:\Monitor 3\EEObjectImportSSISPackage\ImportCustomPointObject.dtsx", null);
Variables vars = package.Variables; vars["selectedArea"].Value = 1050;
DTSExecResult result = package.Execute(null, vars, null, null, null); }
All the connections and the log details are configured in the package itself. I am not much expert on the how to configure the package properly, as this is my first hands on effort at SSIS.
Unfortunately, I am not able to execute the package from the above piece of code. I have tried referring to various blogs, but none could come to my rescue. On the page load, it generates the log which notes below errors.
OnError,PRODSK0418,PRODSK0418ASPNET,Filling table from Excel file after validation,{81E15B75-B9B8-48EB-9B46-936C240EF9E0},{A0D5ECDF-E6CA-41AD-A9A5-1FF66E027FA1},12/13/2007 4:20:48 PM,12/13/2007 4:20:48 PM,-1071611876,0x,SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
OnError,PRODSK0418,PRODSK0418ASPNET,Filling table from Excel file after validation,{81E15B75-B9B8-48EB-9B46-936C240EF9E0},{A0D5ECDF-E6CA-41AD-A9A5-1FF66E027FA1},12/13/2007 4:20:48 PM,12/13/2007 4:20:48 PM,-1073450985,0x,component "Excel Source for CustomPoint object" (1) failed validation and returned error code 0xC020801C.
OnError,PRODSK0418,PRODSK0418ASPNET,Filling table from Excel file after validation,{81E15B75-B9B8-48EB-9B46-936C240EF9E0},{A0D5ECDF-E6CA-41AD-A9A5-1FF66E027FA1},12/13/2007 4:20:48 PM,12/13/2007 4:20:48 PM,-1073450996,0x,One or more component failed validation.
OnError,PRODSK0418,PRODSK0418ASPNET,Filling table from Excel file after validation,{81E15B75-B9B8-48EB-9B46-936C240EF9E0},{A0D5ECDF-E6CA-41AD-A9A5-1FF66E027FA1},12/13/2007 4:20:48 PM,12/13/2007 4:20:48 PM,-1073594105,0x,There were errors during task validation.
Can any one please give me your expert advice on this matter and help me in solving the problem? I am in real need to get this solved.
Hope to get your quick reply n this matter. Please feel free to let me know in case of any questions in this matter.
I am having the same problems as those in another post. SSIS package works fine when executed in BIDS and through execute package utility but it doesnt work when executed as a step in a job.
The other problem is that the logging also doesnt work when i try executing it as a job. So I have no clue about what to do without knowing what error it is. When I run the job it simply says the step has failed.
I have tried most of the solutions posted in other websites most of them to do with using proxies with credentials but havent hit a solution. I would love to get any input on what to do.
I have a very anoying problem with SSIS. I've done new packages into same project, almost identical compared to old ones. They work well in visual studio, but I can't execute them using procedure. Old packages works just fine, but none of the new. Error message is in the end of my story.Visual Studio version is 9.0.30729.1 and SQL Server version is 10.0.4000.0. Is it possible, that these not not updated versions could cause this problem?Package ProtectionLevel is DonSaveSensitive.
Error messages, when package is executed by procedure: Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. NULL Started:Â 10:36:48 AM Error: 2015-03-31 10:36:48.48 Â Â Code: 0xC0016016
I created DTS a while ago and placed in job to run once a day (it worked fine for 3 months) 2 days ago I changed sa password and now job fails with error (Login failed for user 'sa'.), but it run fine from DTS !!!
1. My DTS created with domain Account DomainSVCSQL2000( sa rights and local admin) 2. SVCSQL service use DomainSVCSQL2000 to run 3. SVCSQL agent use DomainSVCSQL2000 to run 4. DTS use 'osql -E
Where should look for reference to sa ?
Executed as user: MONTREALsvcsql2000. DTSRun: Loading... Error: -2147217843 (80040E4D); Provider Error: 18456 (4818) Error string: Login failed for user 'sa'. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0. Process Exit Code 1. The step failed.
I have seen several problems posted where an SSIS package writes a file successfully when executed manually, but fails when executed via SQL Agent job..I have the opposite problem. I'll try to lay it out succinctly: SSIS writes to a file on a shared folder, specified as HostAShare, for example. I created the share and gave full control to Everyone (out of frustration).I'm working from HostA via RDP, connected to the DB on DBHost via SSMS.If I kick off a SQL Agent job that executes the package, it works fine. (SQL job is running under SQL Server Agent Service Account).If I execute the job interactively (logged in to SSMS with Windows Auth), it fails with "Error: Cannot open the datafile "HostASharefilename.ext""We did find that if I RDP directly to the DBHost, I am able to execute manually.Also, if I try executing an xp_command shell command to write a file to the share, it works. (When RDP's into HostA with an SSMS connection to DBHost under my windows domain auth, as above.)The problem is the same when I RDP to any remote host.
I have a simple SSIS package that reads a flat file and copies it into a SQL Server table.
When the flat fiel is on the C drive I have no problem runnign this package from SQL Server Agent, but as soon as I update the path to a network location the package only works when I run it manually, but fails when is executed via the SQL Server agent job.
The error says "cannot open the datafile", while the datafile location is valid.
Is this a kind of limitation of a SQL Server Agent that only local files are allowed to be processed?
Just wonder whether is there any indicator or system parameters that can indicate whether stored procedure A is executed inside query analyzer or executed inside application itself so that if execution is done inside query analyzer then i can block it from being executed/retrieve sensitive data from it?
What i'm want to do is to block someone executing stored procedure using query analyzer and retrieve its sensitive results. Stored procedure A has been granted execution for public user but inside application, it will prompt access denied message if particular user has no rights to use system although knew public user name and password. Because there is second layer of user validation inside system application.
However inside query analyzer, there is no way control execution of stored procedure A it as user knew the public user name and password.
Looking forward for replies from expert here. Thanks in advance.
Note: Hope my explaination here clearly describe my current problems.
I'm having a hard time to getting back an xml data back from a stored procedure executed by an Execute SQL task.
I'm passing in an XML data as a parameter and getting back resulting XML data as a parameter. The Execute SQL task is using ADO connection to do this job. The two parameters(in/out) are type of "string" and mapped as string.
When I execute the task, I get the following error message.
[Execute SQL Task] Error: Executing the query "dbo.PromissorPLEDataUpload" failed with the following error: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 2 ("@LogXML"): Data type 0xE7 has an invalid data length or metadata length.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I also tried mapping the parameter as XML type, but that didn't work either.
If anyone knows what's going on or how to fix this problem please let me know. All I want to do is save returning XML data in the parameter to a local package variable.
I have created SSIS (.dtsx) files and have stored in different servers. Now my query is I want to move all dtsx files from filesystem to Sqlserver2005 database how should i do it.
I need to create the ssis package in business intelligence developement studio i am need to sqlserver 2005.When i opened the BID studio i am not able to see the integration services packages type.. Please help the steps to design the package.
I have experience of using the 2000 in dts designer mode.
I upgraded to Microsoft SQL Server 2005 Service Pack 2 and now when I run the master SSIS package( that has several packages in it), all the packages run twice.
After removing SP2, they work fine. Any ideas how to make this work with SP2?
I am writing a vb application that is supposed to let the users set the connection string for the datasources in the package. After new connection strings are entered the application is supposed to run 8 packages in a certain order, but I haven't been able to set a new connection string successfully. Is there a way to programmatically modify the connection string of a package's datasource? (the packages are moving data from a D3 database to sql server 2005)
Here is what I have tried so far:
A. Dim pkgLocation As String Dim app As Application = New Application() pkgLocation = "c:Package1.dtsx" Dim pkg As Package = app.LoadPackage(pkgLocation, Nothing) Dim myConns As Connections = pkg.Connections
MessageBox.Show(myConns(0).ID.ToString) Dim myConnMgr As ConnectionManager = myConns(0) Dim connProperties As DtsProperties = myConnMgr.Properties
I am connecting to a DB2 mainframe to pull data into SQL 2005. Very simple import. SSIS package works fine on 32 bit. However, once deployed to the 64 bit machine, I get "invalid product license" on the Acquire Connection method.
I've worked with IBM support. I have the correct version of the DB2 Connect client installed. The license is there and in the right place. I can connect to the mainframe from the 64 bit server using the DB2 client tools. I just can't seem to execute the package from Integration Services or run a job in SQL Server that executes the package.
According to BOL, the package should automatically detect the 64 client I installed. It and the 32 bit client I developed with share the same name/id.
I read in Kirk Haselden's book "Microsoft SQL Server 2005 Integration Services" that if SQL Serfver 2005 and 2000 are installed on the same machine as seperate instances then you can view the SQL Server 2000 DTS packages in 2005 Management Studio under the Management tree, Legacy, Data Transformation Services node.
But in my case, I am not able to see DTS packages in Management Studio. Is there a property or a setting that we need to configure for that?
I'm still new to SSIS packages and I'm NOT a developer. I am in the process of doing preliminary/prepatory work for migrating our SQL 2000 platforms to SQL 2005.
I am having a REAL headache with migrating/moving DTS packages from SQL 2000 to SQL 2005. Here are things that I know :
1. I know that some packages cannot be migrated due to ActiveX issues and other issues. Fine.
2. I know that I can install DTS backwards compatibility components on the server in order to be able to edit the DTS packages using a SQL 2000 DTS GUI. Fine.
3. I know that I can use the Migration wizard to migrate packages (and that some of them can't be migrated this way). Fine.
Here's what I don't know/or am conjecturing:
1. In a clustered environment, I have to edit the <%Install Path%>/90/DTS/Bin/MsDtsSrvr.ini.xml file to set the <ServerName> property to the Virtual Server name. Correct? Why can't M$ do this for me?
2. Do I HAVE to export the SSIS package to a .DTSX file in order to be able to edit it with Visual Studio? Is there ANY way around this?
3. If I am running in a clustered environment and I use the File System for storing packages, then the pacakges must be stored on a shared volume, right?
4. I did not find SQL Server Integration services on the B- (Passive) node. Do I have to install it separately onto the B server (much like having to install the Client Tools)?
If anyone has some guidance or tips on running SSIS in this brave, new, wonderful world, I would sure appreciate it.
And yes, I am going to go out right now and order a new book on SSIS.
Hello, When I try to save modifies in packages with many components the system show me a information dialog telling me that there was a System.OutOfMemoryException
Anyone knows how to solve this problem without divide the package in 2 or more packages?
Hi All, I've been assigned a task by one of the programmer in my team to create packages from some of the databases(One from SQL2005&Other one from SQLEXPRESS) I've created and saved the packages using the export wizard.I saved the Packages as Integration Services Packages(On file System). Now he wants me to execute the packages using SSIS But in different time,like maybe after 5min.Other package runs. I really have no clue how to do that,I've added Both packages in SSIS,But i really dont know how to run them in different time. If anyone could help please do so!
I am in a situation where we are redesigning our Datawarehouse. Currently we have our Datawarehouse in SQL 2000 and we are rebuilding from scratch in SQL 2005. This means that even though we will get the same tables but we are planning to rename each & every attribute to make it more meaningful.
The question is like this:
We can migrate the current DTS packages to SSIS but since all the ODBC connections , field names(attributes) will change is it worth it to leverages the DTS packages ?
Also we convert the julian date to gregorian date in our DTS packages but since SSIS has a feature to convert julian date it would be redundant to migrate the packages and my feeling is to create new packages in SSIS and start on a clean slate.
I am asking something very basic and theoretical, here.
Are there any design tools available in the market for designing SSIS packages? By "design tools" I mean tools which enable us to "plan" or "design" the architecture of a SSIS Solution that will be implemented later, using SSIS, of course.
We have several design tools available for designing a Web Application solution, for example. Similarly do we have something for SSIS?
Are there any design approaches, best practices and/or design techniques published for designing a SSIS solution?
Please note that I am not talking about the SSIS Designer or the BIDS. I am talking about a tool/approach for designing the SSIS solution which can be delivered as a project artifact before the actual coding phase starts?
Well, I have tried to express myself as best as I could. If someone can help me with this, it will be really great!
