I kick off a DTS package with ASP.NET. I'm getting an error stating:
The Microsoft Jet database engine cannot open the file <path>. It is already opened exclusively by another user, or you need permission to view its data.
The DTS package is pushing data from Excel to Access, and then Access to SQL. When running the process, I run it directly and it works fine. I haven't tested it through scheduling yet. All of the information on the web says security permissions, but I impersonate the ASP.NET page to use another account, which has full access to the access database and dbo to SQL Server.
I am having problems executing a child package from a parent package using the Execute Package Task. I am attempting to run the master package through a SQL Server Agent job.
The SQL Server Agent job is owned by sa. The step that runs the parent package is configured to load the package from the SSIS Package Store on the same server that the job is running.
I have the Execute Package Task configured as follows:
Location: SQL Server ExecuteOutOfProcess: True Connecting as a SQL Server login (let's say TestEtl)
I have added the db_dtsoperator database role to both the TestEtl login and the login that SQL Server Agent connects through. I have also configured the child package's reader role to include db_dtsoperator. Per http://msdn2.microsoft.com/en-US/library/ms141053.aspx, this should allow these logins to run the child package.
I have enabled logging of all events in both the parent and child packages. I see the following in the logs when the Execute Package Task executes (omitted portions unrelated to the execution of the child package task):
450939 OnPreExecute ChildPackage 2007-06-08 13:35:17.000 2007-06-08 13:35:17.000 450940 OnPreValidate ChildPackage 2007-06-08 13:35:17.000 2007-06-08 13:35:17.000 450941 OnPostValidate ChildPackage 2007-06-08 13:35:17.000 2007-06-08 13:35:17.000 450942 User: Diagnostic ETL 2007-06-08 13:35:17.000 2007-06-08 13:35:17.000 ExternalRequest_pre: The object is ready to make the following external request: 'IDataInitialize::GetDataSource'.450943 User: Diagnostic ETL 2007-06-08 13:35:17.000 2007-06-08 13:35:17.000 ExternalRequest_post: 'IDataInitialize::GetDataSource succeeded'. The external request has completed.450944 User: Diagnostic ETL 2007-06-08 13:35:17.000 2007-06-08 13:35:17.000 ExternalRequest_pre: The object is ready to make the following external request: 'IDBInitialize::Initialize'.450945 User: Diagnostic ETL 2007-06-08 13:35:17.000 2007-06-08 13:35:17.000 ExternalRequest_post: 'IDBInitialize::Initialize succeeded'. The external request has completed.450946 User: Diagnostic ETL 2007-06-08 13:35:17.000 2007-06-08 13:35:17.000 ExternalRequest_pre: The object is ready to make the following external request: 'IDBCreateSession::CreateSession'.450947 User: Diagnostic ETL 2007-06-08 13:35:17.000 2007-06-08 13:35:17.000 ExternalRequest_post: 'IDBCreateSession::CreateSession succeeded'. The external request has completed.450948 OnError ChildPackage 2007-06-08 13:35:17.000 2007-06-08 13:35:17.000 Error 0x80070005 while preparing to load the package. Access is denied. . 450949 OnError ParentPackage 2007-06-08 13:35:17.000 2007-06-08 13:35:17.000 Error 0x80070005 while preparing to load the package. Access is denied. . 450950 OnTaskFailed ChildPackage 2007-06-08 13:35:17.000 2007-06-08 13:35:17.000 450951 OnPostExecute ChildPackage 2007-06-08 13:35:17.000 2007-06-08 13:35:17.000 450952 OnWarning ParentPackage 2007-06-08 13:35:17.000 2007-06-08 13:35:17.000 SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. 450953 OnPostExecute ParentPackage 2007-06-08 13:35:17.000 2007-06-08 13:35:17.000 450954 PackageEnd ParentPackage 2007-06-08 13:35:17.000 2007-06-08 13:35:17.000 End of package execution.
I am sure that what I am doing is quite common, and I obviously have something misconfigured somewhere - but I'm not sure what my misconfiguration is. Can anyone enlighten me?
I am trying to execute a DTS package that updates a SQL database from a Microsoft Access form using a command button. The computer I created the access app on is the same computer that hosts the SQL Server and when I run it form there it works fine. However when I run it from another machine across the network the package does not work and I am having login failure problems with other users even though I have added them to the database. I need to use Windows Authentication for this, here is the code I am using in VB on the MSFT Access form. Do I need to specify username & password if specifying a trusted connection?
Private Sub Command0_Click()
Dim dtsp As New DTS.Package dtsp.LoadFromSQLServer "MYSERVER", "SERVER_USER_NAME", _ "USER_NAME_PWD",DTSSQLStgFlag_UseTrustedConnection, "", _ "", "", "TGImport" dtsp.Execute
I am somewhat new to SSIS, so please forgive my confusion.
I have a situation and I have two possible scenarios that will work, but I'm not sure if either of them are feasible. I have a database that is updated using about 8 text files extracted from the company's main software package. I have set up a SSIS package that takes these 8 files and updates the appropriate tables. This package works pretty well. My question surrounds the automation of the extraction of the files from our software. I am able to do the extraction in one of two ways - a) manually opening up an instance of the tenet program used to run the program with a script built into it or b) using VBA in an Access application to open up the program to run the built in script. In the past, when the platform for the database was Access, I ran the extraction and database update from the same VBA module, but now I'm not sure how to do it.
Is it possible to either:
A) Intiate the run of a SSIS package from Microsoft Access or
B) Somehow open up the program that run's the company's software using SSIS. And this brings another question, if this is a step in an SSIS package, will the package wait to move to the next step until the entire extraction process is complete?
I have a MS Access front-end (not web) that creates a Comma Separated Values (.CSV) file. I need to dlete all records in a temp table in the SQL SERVER (2000) backend, and then import the CSV file. Once that is done, I need to open the temp table (linked from Access ODBC) for the user and let them update records.
I already created a DTS package that deletes the records in the temp table and imports the records from the CSV.
What I don't know is how to execute that existing DTS package from VBA in Access. Any ideas?
Also, once the temp table is re-populated by manually executing the DTS package, I haven't been able to figure out how to "refresh" the temp table link. If it originally had 100 records (examined via the Access link) and I manually run the DTS package to upload 50 records, when the process completes, the Access linked table is still showing 100 records. I then go into the Access linked table manager and refresh the link. It then shows 50 records, but it won't let me update them.
Hi All, My name is kanishk. I am facing some issue in calling the DTS package from Visual Basic code. The brief history of issue is : We have a application which uses Ms-Access as front end and MS-Sql server 2000 as backend. Perviously we has MS-Access 97 version and MS-Sql 2000 .In this comination the DTS package was working fine. Now We have changes the fornt end to MS-Access 2002. Here the DTS package is not working .
Can you please help me out to get the proper syntax to call the DTS package from VB code.
I have defined a Data Transformation Services (DTS) package in MS SQL Server. In VB I know that the DTS execution can be done thru VB codings. But what about Delphi?
Can anybody suggest any Delphi components or codings for this purpose.
Hi We have a list of 40-50 SSIS packages and could grow up to 100. Each one loads 5-20 tables. I’d like to write a program (probably another package utilizing "Script task/component") to:
1- List all the packages by name 2- List input and output tables and/or file systems for each package 3- List child packages and procedures that are referenced in each package So I need to have access to the Package properties.
I would like to hear your idea/clue/tips and or any helpfull direction.
Hi MS NewsgroupI am using SQL 2000 SP3 + latest hot fixes and I am editing a DTSpackage. When I save the package I get a SQL Server Access Violationand cannot save the package. This does not crash SQL nor EnterpriseManager but no matter what I do I cannot save the package. This occursfor only 1 package on the server (others are ok) and for all users ofany privilaged access level. I have tried to save to file, VB, etc butall fail similarly.I have read MS KB and have tried all options they suggest but to noavail! I'd like not to have to rewrite the DTS again from scratch.Ideas anyone?Thanks
Hi,I made a DTS-package and it works when I execute it manually, but when it isrun by the SQL Server Agent, it fails.I have use the guide to create a maintenance plan. That doesn't work so goodeither. It runs Optimizations, but not integrity checks or backups (probablybecause integrity checks failed).The following from the log file:Executed as user: HT-DOMAININTRAB-SQL. ...:DTSStep_DTSExecuteSQLTask_2 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_2DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish:DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_1DTSRun OnStart: DTSStep_DTSDataPumpTask_3 DTSRun OnError:DTSStep_DTSDataPumpTask_1,Error = -2147024891 (80070005) Error string: Access is denied.Error source: Microsoft Data Transformation Services (DTS) PackageHelp file: sqldts80.hlp Help context: 1100 Error Detail Records:Error: -2147024891 (80070005); Provider Error: 0 (0) Error string:Access is denied. Error source: Microsoft Data TransformationServices (DTS) Package Help file: sqldts80.hlp Help context:1100 Error: -2147024891 (80070005); Provider Error: 0 (0)Error string: Cannot open a log file of specified name. Access is denied.Error source: Micr... Process Exit Code 1. The step failed.Log-file endeth here.....My "data-sources"/Connections is set up to use sql-serverauthentication where I enter superuser name+password, not windowsauthentication.If I browse around using SQL-EM:Under Security - Logins HT-DOMAINintrab-sql:Tab General: Grant AccessTab Server Roles: System AdministratorsTab Database Access: checkmark at Mbestil, user=dbo; database rolesfor mbestil = public + db_ownerI also have som problems identifying where the package fails. I have giventhe individual "steps"/"transformations" some pretty good names, but in thelog-file it still shows the "old" names.The DTS package empties a table, then fills it by copying data from anothertable in another database on another server.Anyone with some useful tips ?/jim
Response.Write("The following exception occurred: " & ex.Message)
End Try
End If
End Sub
I've tried both "sql" and "dts" with all the various permutations that are listed (and commented out) above. I either get an error that says: The top level folder "VisBridges" is not found. ("dts" "VisBridges") -- or -- Invalid package name or location: MSDBVisBridgesExportTime ("dts" "MSDBVisBridges") -- or -- Cannot find folder "MSDBVisBridgesExportTime" ("sql" "MSDBVisBridges") -- or -- The specified package could not be loaded from the SQL Server database ("sql" "VisBridges")
Hi all, I have created an SSIS package to export rows of data from SQL to Access using SSIS package. The package is executed from asp.net web application. Below is what i want to achieve: -User enters a date range -SSIS package will export data between the date range from SQL to Access database. -When user enter another date range, I want to clear the contents of the Access database. (Im using Execute Sql Task--- Delete tablename) The problem is that when I look at the table after the second user request, the fields will show #deleted. Only after i click refresh will the new data appear. How can I make the data appear without manually refreshing the Access table. Thks alot.
I am trying to get a handle on the new SQL Server Integration Services in SQL Server 2005. There is a legacy DTS package that I need to get working on our new server (using SQL Server 2005) but I can't seem to get it to work.First, I migrated it and attempted to run the package. It gave me many errors. So, I thought I would just rebuild it in integrated services. Everything was going smooth until I got to the real data transformation part.I added a data flow task and it took me to the Data Flow tab. Then I added a DataReader Source. I am trying to read from a dBase III file and I assumed I would be using an ODBC driver for that. Well... it has been hell trying to get it to see the dBase III file and access it.I have tried two solutions from the internet.This one and this one (by Wenyang) with no positive results.No matter what I do I still get an error in the bottom of the Advanced Editor for DataReader Source that reads: "Error at Sales Transformation [DataReader Source [81]]: Cannot acquire a managed connection from the run-time connection manager."Has anyone attempted anything like this before? Are there any SSIS experts around here that can drop me a few pearls of wisdom?Thanks in advance... I'm dying here.
I have a master package which calls two package one after an other.
1) I will be passing parameters to master package using dtexec with /set property to set the variables in the master package. I would like to know how can child package access these variables.
I created a script task that uploads a file to a HTTPs site. The script task works well and now I want to turn this script task into a custom component...
I'm having problems on how to call the variables I have in the package, I already locked them for reading, but how am I suppose to access them and how can I put their values into strings so I can easily call them in the WebClient.UploadFile method?
Also, I am very new to custom tasks in SSIS.. and would appreciate if anyone of you can send me something (code, link, etc.) of a very simple custom task without any form or properties, that I can try to study.
Basically, what I want my custom task to do is,
1. Read the varialbes in the package. 2. use the variables values as input in my WebClient.UploadFile("varURLstring", "PUT", "varFilePath")
as you can see, my custom task is really simple... but I'm a noob here and I don't know where to go.
My current project requires me to both rename the MDB file for an Access database and rename the table it contains. The Access files comes in with random names, each containing one table with a specific name. Based on the table name it contains, I rename both the file and the interior table to a standard name which a later package in the process references.
A foreach container loops through all the mdb files in the applicable directory, containing a script task and a file system task. The script task uses GetOleDbSchemaTable to extract the table name, then loops through an array of table names from the client's configuration, comparing it to a similar array of constant names and getting the matching one. The file system task then uses that found name (or the original table name if a conversion is not found) to rename the file to match that standard name. So far, so good.
Now I have to rename the table within the file as well. All of the examples of code I'm finding on the 'net refernce ADOX, but I haven't been able to figure out how to use that in a script task, assuming that's what I want to do in the first place.
Anyone have any experience with doing things like this?
Hello, I have a package variable called LatestTableFileDate that is typed to DateTime. I have an Execute SQL Task that executes the following SQL statement against an MS Access 2000 database table through the OLEDB Jet provider;
SELECT MAX(FileDate) AS MaxFileDate FROM CollectionData_Access
The statement parses fine. At the moment, the returned value is NULL. I have 'Result Set' set to 'Single Row'. The FileDate column in the Access table is 'Date/Time', and the Format is 'Short Date'.
In the result set properties, I attempt to set the result to the LatestTableFileDate user variable. When I run the task, the task fails, and the following appears on the 'Progress' tab;
[Execute SQL Task] Error: An error occurred while assigning a value to variable "LatestTableFileDate": "Unsupported data type on result set binding 0.".
I searched the forum for this problem, and didn't find anything. Do I need to convert the date in MS Access to a string and set the package variable to a string type, or is there some other way to handle this?
Hello, For packages where an MS Access database is the destination, what are some ways to detect whether or not the file is 'in use'? I know there is a lock file associated with Access databases. Could it be as simple as using something from the FileSystem Object to discover whether or not there is a lock file with the same name as the destination database? I'd like to stop the package if the file is in use.
If I create a job with an OS step with the text below
"c:Program Files (x86)Microsoft SQL Server90DTSBinn"dtexec /DTS "MSDBew Import" /SERVER HAYDN /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V
I keep getting the following error:
Message Executed as user: XYZAdministrator. The process could not be created for step 1 of job 0x2161C39C2C34C54AA850602A482E82DF (reason: Access is denied). The step failed.
HOWEVER...
If I take put the above text within in a batch file (foo.cmd); and chanage the step to execute "c:foo.cmd" it works fine.
What am I missing here? It's seems wierd that it works one way, and not the other.
I need to run a make-table query against an Access database out of an SSIS package. I tried to do this with an OLE DB Command Task but it fails to create the table even though the task execution comes back successful. Any thoughts???
I'm fairly new to SSIS, and I'm looking for some general guidance on the best approach to take to a particular issue. Essentially, I've built a web application, running on a remote server, and I'm looking to take the contents of an Excel spreadsheet, load it into memory, run a whole mess of calculations against it, and then load the results into a table on MS-SQL 2005. The calculations are in a class library, written in C#, so my sense was that the best approach would be to a) load the data into a datareader, b) run the calculations against the data and c) load it to SQL. Of these steps, a) seems like the place where SSIS would come into play.
My question is, once I've got the data into a datareader, how can it be accessed from my code? I'd been playing around with DtsClient until having the realization that it was intended to be used locally. Is there a similar method for doing this sort of thing remotely?
I have an SSIS package that has an "execute process task" that executes a batch file. The package has been deployed to the msdb database, and is called from a stored procedure using xp_cmdshell dtexec ...
I can execute the package just fine if I'm logged onto the server as a system administrator, by running the stored procedure from a query window.
However, if I log on to the server as a non-admin user, the package attempts to run, but breaks at the file system task, with "Access Denied". It can't run the batch file. It seems to be a permissions issue at the file system level.
I am finishing a port of a project that was done with RS2000/AS2000/DTS2000. The cube process is triggered remotely by a Korn Shell script from Services for Unix that runs a DTS package:
I have finished porting the AS and RS parts to their 2005 equivalents and now I am trying to get the cube processing converted. I have built a SSIS package that processes the cube and I have deployed it to the SQL Server in MSDB. When I am logged on to the SQL Server box directly, I can execute the package via the Management Studio and from the command line via:
When I try to register the SQL Server in Integration Services from my client PC Management Studio it fails with "Access Denied". When I try to run the dtexec from my client PC it fails with "Access Denied".
I have walked through Kirk Haselden's instructions at:
(adding myself to Distributed COM Users, noting the correct DCom Config settings for MsDtsServer and restarting the Integration Services service) and the problem is still not resolved. I have posted to the DTS group on Technet and now I am posting here. This can not be run scheduled by SQL Agent as it needs to be triggered by other load/postload activities. Are there any other fixes for this problem that I can try?
Thanks,
Keehan
By the way, being able to have all the parts of this project in one dev environment is great. The previous application was developed in 3 separate locations which was pretty clunky.
Error 0x80070005 while loading package file "Package.dtsx". Access is denied. .
The package is executed from the main package via an 'Execute Package Task'. The strange thing is that the error comes after 'Package.dtsx' has run successfully. I am logging the PackageStart/PackageEnd and error events and I see that Package.dtsx ends successfully and then the "Access is denied" error occurs. The main package is launched by executing dtsexec via a SQL Server Agent Job. The packages have 'SaveCheckpoints' set to True. Any ideas are welcome. Thanks.
I'm creating a SSIS in the designer view of SQL Server BI Dev. Studio (SQL Server 2005)
I need to import a whole table from MS Access into my local SQL Server.(this task will be performed weekly, so once working I'll schedule a job for it)
I've created a 'FILE' connection to MS Access in the 'Connection Managers'.
When I'm on the 'Data Flow' tab I can't find a Data Flow Item to use as a MS Access connection. (available on the 'Data Flow Sources' are only: DataReader, Excel, Flat File, OLE DB, Raw File and XML Sources)
I have an error when a package is trying to execute a SubPackage using the "Execute Package Task". I have this problem for all packages that are running sub packages.
The packages are stored in the DTSPackages folder of the SQL Server installation folder.
The master package is called from an Event Handler on a document library in MOSS 2007.
The account from the Sharepoint Application Pool has Full Control on the DTSPackages folder.
I'm trying to simplify the deployment process of my project. I already had some troubles with the config files but lets say I solved that issue. I'm going to read a flat file and set the variables of my packages from this file. I was thinking to use a Script Task to do that but I will need to copy this task in every package (I have at least 30). So if I want to make some change this will be painful.
Then, I came up with the idea of creating a Custom Task called Config File Task. I'm working on this but I got stuck trying to get the variables from the package that is running my Config File Task.
This is the code I had in the Script Task:
Dim streamReader As New StreamReader(Dts.Variables("ConfigFilePath").Value.ToString) Dim line As String Dim lineArray As String() Dim variableName As String Dim variableValue As String Dim readConfigurations As Boolean = False
While (streamReader.Peek() <> -1) line = streamReader.ReadLine() If line = "[CONFIGURATIONS]" Then readConfigurations = True ElseIf line = "[/CONFIGURATIONS]" Then readConfigurations = False Else If readConfigurations And line <> "" Then lineArray = line.Split("|".ToCharArray()) variableName = lineArray(0).Trim() variableValue = lineArray(1).Trim()
If Dts.Variables.Contains(variableName) Then Dts.Variables(variableName).Value = variableValue End If End If End If End While Dts.TaskResult = Dts.Results.Success
All I want to do is set the variables that exists in each package from the config file. In my UI Class (ConfigFileTaskUI.cs) I can have access to the variables via the TaskHost which is passed as an argument of Initialize() method.
S: Running SQL Server Express V: Running SSIS package in VS.Net F: Shared folder host excel files
And an openrowset SQL statement: select * from openrowset(..... \Fexcel.xls....). This statement can be run in SS management studio connecting to S using my Windows logon(integration security) without any problem.
However, the same SQL running inside SSIS package (integration security using my Windows account) get the following error:
Error: 0x0 at Check headers: OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "The Microsoft Jet database engine cannot open the file '\Fexcel.xls'. It is already opened exclusively by another user, or you need permission to view its data.".
Error: 0xC002F210 at Check headers, Execute SQL Task: Executing the query "....openrowset....." failed with the following error: "Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
(My Windows account is administrator of Windows and sysadmin or SQL Sever Express on S)
