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.
Thannks in advance.
Hello,the following problem:I use Delphi 6 to access a MS Access Database.In short, the Delphi Code looks like this:ADOConnection1.Open;ADOQuery1.Close;ADOQuery1.SQL.Text := 'SELECT * FROM database1 WHEREvalue1=1 AND value2= ' + inttostr(array[3]);ADOQuery1.Open;Label5.Caption:=ADOQuery1.Fiel*dByName('value4').A sString;Now I only want to select these rows, where the Integer value3 ends on1. So I need the Modulo-Function. I found in the Internet these twofunctions:MOD(x,y) und x % yBut I get an error if I want to use one.For example, If I changeADOQuery1.SQL.Text := 'SELECT * FROM database1 WHEREvalue1=1 AND value2= ' + inttostr(array[3])+' AND MOD(value3,10) = 1;Delphi shows me a Syntax Error in this statement.Thx for help!
View 2 Replies View RelatedHi,
How can i connect to an sql server db file. I have the server's ip number.
I'm using Delphi 7.
Thanks...
:)
Hi,
I'm looking at using SQL Server CE as a replacement for MS Access in a WIN32 Delphi Application. This limits my database options to Providers that is usable from ADO.
I see that the OLE DB Interfaces are implemented, but I can't connect to a SQL Server CE database from Delphi. I've cut and paste a sample connection string (replacing the data source bit) but no go??
I've installed the Runtime Environment, VS2005 bit, Connectivity Tools and the SDK :)
Any help? Anything that I might have missed?
Thanks,
Dawid
I have a Client/Server app built with SQL Server + Delphi as Front-end.
I have maybe 15 TTable components (active tables in the app.) and when runnning the app, each table uses 1 connection to the server. I had to boost the # of user connections from 15 to 25 just to run 1 instance of the app.
If i try running another instance of the prog. the server needs 15 x 2 connections. I`m wondering if this is NORMAL? Would it be possible to have only one connection per instance instead of 15 !?!
Thanks in advance
Hello,
Is there a way to import data from a text file with DELPHI toward a SQL Server table.
I heard about BCP ...?
Does someone know?
THANKS.
Can you let me know whether the performance of SQL Server 7.0 and Delphi 4.0 Client/Server is better or SQL Server 7.0 and Paradox?
What testing procedures have you done on this issue?
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 exporting 350 tables data from SQL Server 2005 to Access 2003.and getting the below error.
SSIS package "Package2.dtsx" starting.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.
Error: 0xC0202009 at Package2, Connection manager "DestinationConnectionOLEDB": SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Unspecified error".
Error: 0xC020801C at Data Flow Task, Destination 64 - CLIMBINGEXP [8065]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "DestinationConnectionOLEDB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Error: 0xC004701A at Data Flow Task, DTS.Pipeline: component "Destination 64 - CLIMBINGEXP" (8065) failed the pre-execute phase and returned error code 0xC020801C.
Any clue?
Can anyone please help me on how to access SQL7 through Delphi 2 or greater?
Any help at all is appreciated
I try to connect (locally) to SQLServer Express from Delphi5. On my developer computer, which has Windows XP, SQLServer 2000, VisualStudio2005 (and with that, SQLExpress 2005), it is no problem. However, when I transfer my program and the database to other computers with Windows 2000/WindowsServer2003 and a more recent standalone-version of SQLExpress, I get error messages like:
'Db-Library error 10004: No connection possible: SQL Server not available or not existing'
It does not matter which protocols I activate at the Server or whether the Server Browser service is running or not.
Does anybody have an idea how this might be fixed? Or whether it is a problem of the operating system?
I am using Delphi 7 and need to save mulitple documents that range from 2K KB to 200K KB. These need to be stored in the database for inclusion in the database backup.
When i am working on the 200K KB file i continusely run out of memory, how can i resolve this.
Hello,I have a situation where I have to run a Delphi 1.0 application onWin95 via ODBC to MS-SQL 6.5 server. This is working for me onseveral workstations.I always install 16-bit BDE and make appropriate changes to odbc*.INIfiles, etc.I have a machine which has just now been setup for this situation -and at the same time converted to a wireless network card.Everything works great as far as network connection, running querys,etc. But when get to the point of saving a transaction - I am gettingthe message:"Error - EDBEngineError(General SQL error. [Microsoft]{ODBC SQL ServerDriver][SQL Server]Line 2: Incorrect syntax near 'DATE_TIME')Here is the sql statement and partial source code:sql.Add('INSERT INTO INVOICES (INVOICE_NO, TOTAL, TAX, VOID_CODE,TYPE_CODE,');SQL.Add('invoices."DATE_TIME", TERM_NUMB, USER_NUMB, CUSTOMERID,BATCH)');SQL.Add('VALUES (:xINVOICE_NO, :xTOTAL, :xTAX, :xVOID_CODE,:xTYPE_CODE,');SQL.Add(':xDATE_TIME,'+':xTERM_NUMB, :xUSER_NUMB, :xCustomerID,:xBATCH)');ParamByName('xINVOICE_NO').AsString := InvoiceTotals.InvoiceNumber;ParamByName('xTOTAL').AsFloat := InvoiceTotals.Total;ParamByName('xTAX').AsFloat := InvoiceTotals.Tax;ParamByName('xVOID_CODE').AsString := vcFinalized;ParamByName('xTYPE_CODE').AsString := InvoiceTotals.TypeCode;ParamByName('xDATE_TIME').AsDateTime := InvoiceTotals.DateTime;ParamByName('xTERM_NUMB').AsInteger :=FUseStation;{InvoiceTotals.StationNumb;}ParamByName('xUSER_NUMB').AsInteger := InvoiceTotals.UserNumb;ParamByName('xCUSTOMERID').AsString := InvoiceTotals.CustomerID;ParamByName('xBATCH').AsString := DateToFieldStr(FUseBatch);ExecSQL;I realize DATE_TIME is some sort of reserved word but in this context(ie. table name and quotes) it works on all other stations. This isthe first part of a two part SQL transaction.What could be wrong?Thanks - Scott Murray.
View 3 Replies View RelatedHi everyone,
I'm making an application in Delphi that use an SQL database, i want the user can import a table in DBF format (that its located in another computer) to the SQL Server (in the database that i want) by one click of a button.
I try with the import wizard but its not what i want because it requiere to do the process manually.
Its there anyway of achive this (Stored Procedure or something?)
Ernesto Cisneros Sanchez
Senior Software Engineer
Credit and Trade Bank
Havana, Cuba
Hi, I would really appreciate some help on this one as I know nothing about triggers to be quite honest.
I'm trying to add a feature to my Delphi 2007 application to start a SQL package whenever a load button on my Delphi form is clicked. Assume I'll need SQL code to perform this.
Can anyone please assist in this matter - sample code will also be much appreciated. Thanks a million!
Dear friends I have a problem with the use of DELPHI and MSSQLServer!
I have installed in a PC a MSSQLServer Express. The PC is connected
with a other via a small network. I have developed an application in DELPHI
which runs also in the two computers. I have regulated SQLServer so as
to is accessible via TCP/IP, make that I tryed after in the other PC I
installed SQL Server Management Studio Express and had access in the
database via the network. While in the beginning both applications
worked normaly and were connected regularly in the base suddenly
one day they couldn't be connected giving as message the following
"DataBase Server Error: SQL State: 08001, SQL Error Code: 17 ", as there was no server. That is to say without I have teased the
properties the access to DataBase was impossible via the application but possible via the SQL
Server Management Studio Express and the environment of Borland Delphi
2005 from both PC's. That is to say
suddenly only the application simply could not be connected. I tryed
a lot of solutions but no one it did not work. When I installed the
MSSQLServer Express in the other PC the application could be connected
from this but no from the other via network always giving the same
message. Has anyone any idea why I am in impasse from solutions!!!!
Thak you in advance!
I use Delphi 2006 to connect MS SQL Express 2005. When I save record, it shows the following error message:
Database Server Error : Cannot create new transaction because capacity was exceeded.
Does anyone know what happen and how to solve?
Thanks!
Since there is no native support for 64bit integers in dbExpress, itis not trivial to fetch a BIGINT from SQL 2k.If it is possible, how can a BIGINT be fetched using TParam or TField?Any help appriciated
View 2 Replies View RelatedHello,
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.
Any ideas?
Brian
Hi,
Does anyone know if it is possible to run a DTS Package from MS-Access database (.mdb)? If yes, could you please specify how to do that? Thanks!
Regards,
av
Hello,
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
End Sub
Thanks Much!!
Hello,
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?
Thanks so much!
Christine
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.
Any ideas on solving this multi-part problem?
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.
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.
Thanks so much.
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
View 1 Replies View RelatedHi,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
View 5 Replies View RelatedHI
how to access package configuration files of a particular package during runtime.
I (actually my DBA did it) have created an SSIS package in MS SQL Server 2005. It can be found under Integration Services under:
Stored Packages
MSDB
VisBridges
ExportTime
Using the Execute Package Utility, it runs perfectly.
I used the article at http://msdn2.microsoft.com/en-us/library/ms403355.aspx to build an application to run it.
My code includes the following (written in VB.Net):
a Web Service Function called LaunchPackage:
Public Function LaunchPackage(ByVal sourceType As String, _
ByVal sourceLocation As String, _
ByVal packageName As String) As Integer ' DTSExecResult
Dim packagePath As String
Dim myPackage As New Package()
Dim integrationServices As New Application
' combine path and file name
packagePath = Path.Combine(sourceLocation, packageName)
Select Case sourceType
Case "file"
' package is stored as a file.
' add extension if not present.
If String.IsNullOrEmpty(Path.GetExtension(packageName)) Then
packagePath = String.Concat(packagePath, ".dtsx")
End If
If File.Exists(packagePath) Then
myPackage = integrationServices.LoadPackage(packagePath, Nothing)
Else
Throw New ApplicationException("Invalid file location: " & packagePath)
End If
Case "sql"
' Package stored in MSDB.
' Combine logical path and package name.
If integrationServices.ExistsOnSqlServer(packagePath, <servername>, <UserID>, <password>) Then
myPackage = integrationServices.LoadFromSqlServer(packageName, <servername>, <UserID>, <password>, Nothing)
Else
Throw New ApplicationException("Invalid package name or location: " & packagePath)
End If
Case "dts"
' package is managed by SSIS Package Store.
' Default logical paths are File System and MSDB.
If integrationServices.FolderExistsOnDtsServer(packagePath, <servername>) Then
myPackage = integrationServices.LoadFromDtsServer(packagePath, <servername>, Nothing)
Else
Throw New ApplicationException("Invalid package name or location: " & packagePath)
End If
Case Else
Throw New ApplicationException("Invalid sourceType argument: valid values are 'file', 'sql', and 'dts'.")
End Select
Return myPackage.Execute()
End Function
My TestPage includes the following:
Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
Dim launchPackageService As New LaunchSSISPackageService
Dim packageResult As Integer
Dim sourceType As String = "dts"
'Dim sourceLocation As String = "M:mssqlssiconfig" ' for "file"
'Dim sourceLocation As String = "MSDBVisBridges" ' for "sql"
Dim sourceLocation As String = "VisBridges" ' for "sql & dts"
'Dim sourceLocation As String = "MSDB"
If rdoCreateTimeTrans.Checked Then
Try
packageResult = launchPackageService.LaunchPackage(sourceType, sourceLocation, "ExportTime")
Catch ex As Exception
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")
Any assistance would be greatly appreciated.
Many thanks,
Michell
I have some applications in Access and I execute a DTS from there, Can I execute a SSIS package fron access?
Regards
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.
View 3 Replies View Related
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.