Problem With MSAccess And Executing A DTS Package Via VBA - Returns Column Headings Only
Jan 31, 2007
I wonder if anyone can help with this. I have searched around and not found much on the topic which can resolve my problem.
History on the reason for using VBA and not C# or VB or VB.net etc. Whn I arrived at this company, there were no development tools so had to resort to Access and VBA. Of course, the routines I wrote are now set in production and I haven't had much time to convert them to c# and ASP.NET (which I have learnt over the past couple of years).
Anyway the problem is that I found on the SQLDTS.com site that I could in fact create and execute DTS packages within VB and followed the examples and got this working in VBA. Of course I created the DTS first and then saved it as VB, then used the resulting code with the VBA project etc etc. This worked well and we were happy.
The DTS packages I create and execute are to export data from SQL2000 tables to tabs in Excel spreadsheets.
Now I find that on my machine, when the objPkg.execute command runs, the Excel file is created but only the query column headings are populated into the excel file. No data is returned.
If I run this on a different machine it works (although having said that, the other machine is now starting to display similar behaviour on some routines).
we created a version of the Access 2000 MDB in Access 2003 some time ago and the Access 2000 still behaves correctly. The strange thing is that my machine has only started doing this incorrectly a little while ago (not sure how long now but could be as little as a couple of months). The routines are monthly processes so it's only going to show up as a problem once a month so the fact that it used to work on my machine and has only failed the last couple of times means that something must have changed on my setup. I am runningXP-Pro SP2 - fully patched, Office Pro 2003 and talking to SQL 2000 SP3a
is it possible that some windows updates or office updates are clobbering something which used to work?
If I allow the VBA to create the DTS but not execute it, I can execute the created DTS manually from SQLEM and it outputs data correctly.
No security access to the SQL databases/table has changed, nor have there been any changes to security on the file system where the files are written.
It's weird.
Any help would be gratefully accepted.
Annabel
I'm really lost and not sure whether I should look at driver versions, ODBC versions blah blah
This means, I am using the Application object and the Package object to actually load the package using the path to it.
So my code looks something like this:
-----
pkgLocation = @"<package_path>/Package1.dtsx";
app = new Application();
pkg = app.LoadPackage(pkgLocation, null);
pkgResults = pkg.Execute();
Console.WriteLine(pkgResults.ToString());
Console.ReadKey();
---
My package reads in a flat file (located on another server) and transforms it and saves it to a database (on that same server). And mind you, I can execute this package just fine when I do it manually from within BIDS.
But when I try to execute the above mentioned code in my C# solution (compiled to a command line executable), I always get a "Failure".
Can somebody point out what I am doing wrong here?
If the SOURCE doesn't have column headings in a txt file (the format will be pipe delimited), how can I make it work (Through DTS) to load the source into my designed SQL Table? Thanks in advance!
This may be a stupid question, but I have a table of orders that consists of various data. I want to take by date all orders that have been completed but have not had a previous order in 180 days. I have this part done. What gets returned is Date, Count, Status. What I want to get is the various status's returned as headings. So I get Data, Total Order Count, Completed Count, Abandoned Count, etc. I'm returning this data to Excel for display. My original thought was seperate queries, but some of the data will not have a count so the ordering would be off. Any ideas?
Hi, I've got an IS package which reads a lot of records from a text file and loads that into the database. The text file has column such as Firstname, Lastname, phone number etc and same as the database table.
The problem: IS works fine if I have the text file columns in the same order as the database columns but for example if have phone number in the place of firstname (in the text file) IS puts the phone numbers as firstname in the database and moves all the columns dow the order.
Is there anyway I could force IS to use the heading names in the text file and put it in the appropriate database columns?
I have generated a report that requires the user to scroll down the page since it is fairly long. I want to be able to show the column headings as the report is being scrolled. Much like excel locking the column headings as you would scroll down the spreadsheet so you know what values correspond to what heading. How do I configure this report to do this? Any suggestion is greatly appreciated. Thanks!
I am relatively new to SQL and have experience with relatively simple queries and data manipulation. I have just finished creating a fully normalized database, which, of course, is presenting me with a few problems retrieving data. I'm not sure if I'm even taking the right approach. Anyway, I am trying to create a web search with a filter. I want my filter to be based upon different columns in a view, rather than rows in one column. I have the following columns:
I want to populate a drop list with these column headings. So I thought I would need a way to make a dummy column named Category or something, and then make each of these columns a row in the the Category colum. Can that be done?
we'd like our users to have the option of choosing the language (eg French) for column headings (maybe report heading too). I'm sure I'd have to provide the translations somehow but I'd like to know what feature, if any in RS2005 comes the closest to doing/supporting this, even if it is something like making column headings themselves dependent on a variable?
I'm using isqlw to generate delimited text files from scripts. There are several SET options available so that you can restrict the output to just the data in the script, for example SET NOCOUNT ON.
I can't find a similar SET option to suppress the column headings, although there is a tickbox that allows you to do this in Query Analyzer (Query -> Current Connection Options -> Advanced -> Print Headers)
DTS packages appear to support this, yet I can't find the setting from a dts file.
When I am using a SQL Query I have an ability to control whether or not I am able to Include the Column Headers when copying or saving results.
The control exists in the Options > Query Results > Results to Grid > Include column headings etc.
My question is how to get this same ability when attempting to copy the results of a VIEW vs. a Query. The idea is that when I setup a view it€™s a drag/drop type of query building (query building for dummies if you will). Once I have a view and click the Execute icon it will return all the records selected by the View. However, when I click the upper left/top box to select all rows and column and then try to copy/paste the records into Excel all the data copies just fine but the field name/column headers are not there. How can I get the header fieldname date to copy/paste from View result set that I'm able to copy from a Query result set? Thank you, Mike
I have a simple enough task to complete that I can€™t seem to find the answer to.
The task is this €“
Select table x from the database and write it to a flat file complete with that tables column headings.
Now I€™ve managed to set up an ole db datasource and selected the table and I€™ve also linked it to the flat file output. So now I can generate a flat file from the database. However no column headings appear in the flat file.
I can€™t seem to find anywhere (like a checkbox) that will also output the column headings to the flat file.
Now I can add in Headings manually in the properties of the Flat File Destination object but the columns that appear in the flat file don€™t appear to be in the order that I requested them in the SQL.
So the question is how do I automatically have the column headings appear for flat file output (ideally without me having to manually add them in).
If it can€™t be done and I have to use a vb.net script instead then would anyone have an example script of how to do it?
Thanks in advance for anyone who manages to answer this.
com.microsoft.sqlserver.jdbc.SQLServer Exception: A server cursor cannot be opened on the given statement or statements . Use a default result set or client cursor.
If a SP doesnt have a temp table, then there is no issue, SP executes perfectly, but if a SP has a temp table, this error occurs.
SP :
create proc testProcedure @countrCode varchar(3) as
select countryname INTO #TMPCOU from country where countryCode = @countrCode SELECT COUNTRYNAME FROM #TMPCOU
I have developed an SSIS package for ETL purpose. I am invoking the SSIS package through .Net console application by referencing the ManagedDTS Assembly. I am able to execute the package in Sql Server 2005 Developer Edition and it runs fine till completion.
But when i try to execute the packahe in Sql Server 2005 Standard edition, by invoking the package through .Net console application the status of the package is failure.
Can any one help me how to over come this problem.
I am planning to develop a single package that will download files from ftp server, move the files to internal file server and upload it in the database. But I want to run this package for multiple ftp file providers. For each provider the ftp server might be different and the transformation to upload the files into a database table might be different.
So can I create a single package and then multiple configuration files (xml), which will contain the details fo the ftp file providers and then pass the xml file as a parameter while executing the package. The reason being that the timings of fetching the files is different for each ftp file provider and hence cannot be combined into one.
I would like to fetch the data flow component name while package is executing. Since system variable named [System::SourceName] only fetches name of the control flow tasks? Is there a way to capture them?
we are running 2014 enterprise. A couple of column headings (even with smaller font) are a little tall, as much as 6 "lines".   All headings in the respective matrices are aligned vertically as "bottom". When I save the report to excel, these taller headings get chopped off visually in their excel cell.  The cell contains the entire heading. It appears that the second "line" of the heading is the one that is most visible in excel.Â
How do I get these to be saved so user will see entire heading when opening excel doc? In the first matrix there is a text box directly above the matrix and only one row of column headings. In the second there is a higher column group heading containing month name above the problem headings.Â
Hi people,I?m trying to alter a integer field to a decimal(12,4) field in MSACCESS 2K.Example:table : item_nota_fiscal_forn_setor_publicofield : qtd_mercadoria integer NOT NULLALTER TABLE item_nota_fiscal_forn_setor_publicoALTER COLUMN qtd_mercadoria decimal(12,4) NOT NULLBut, It doesn't work. A sintax error rises.I need to change that field in a Visual Basic aplication, dinamically.How can I do it? How can I create a decimal(12,4) field via script in MSACCESS?Thanks,Euler Almeida--Message posted via http://www.sqlmonster.com
I have a web application that I kick off a DTS package. I tested the package and it works fine. However, when running the package through ASP.NET, I get the following error:
Type mismatch. (mscorlib (80004005): Missing parameter does not have a default value. Parameter name: parameters)
I've traced it to a dynamic task step. I have this as the first step in the workflow, but for some reason, it is executing last. Which is definitely not the place where I want it. Is there anyway I can force it, or why is it doing that?
I am having trouble executing a package as a job in SQL Server 2005. I can run the package fine manually from sql server but when I create a job for that package and run the job I get an error that says
The package execution failed. The step failed.
I am completely lost at this point. Any help would be greatly appreciated.
Can some one point me in the right direction? I need to execute a DTS package after a set of T-SQL statements run in a stored procedure. The starting of the DTS package needs to be the last thing executed/called from the stored procedure.
hi, I have created a DTS package that export data to another database. I always click on the package itself to run it. Now I need to be able to create an asp page to execute that DTS pakcage how can I do that. Thanks for your help
Hi All, I hav a strange problem. I created a SSIS package on my machine with protection level as "EncryptAllWithPassword".
When I deploy this package on a SQL Server on another machine (either in File System or SQL Server)and add it as a job in SQL Server Agent and try to run it through a scheduler or manually the job fails with the following message -
Executed as user:IBM-P4-1976SYSTEM. The package could not be loaded. The step failed.
However if I deploy it on my own machine the job runs successfully. Also if I run the package using dtexec utility on the another machine it runs successfully. It is not running as a job on another machine. All the logged in users have administrative rights. My databases are altogether on a different server.
Can any one help me out with this issue...Pleassssseeee...
I'm looking for an example of how to execute an existing DTS* packagefrom anASP (VB)script and would appreciate any and all response. *I don'tevenknow if it's possibleThanks- Chuck GattoDan Guzman Apr 27 2000, 12:00 am show optionsNewsgroups: comp.databases.ms-sqlserverFrom: "Dan Guzman" <DGuz...@nospamplease-earthlink.net&g*t; - Findmessages by this authorDate: 2000/04/27Subject: Re: Executing a DTS Package using an ASP (VBScript) ScriptReply to Author | Forward | Print | Individual Message | Show original| Report AbuseThis VBScript example loads and executes an existing DTS pac*kage fromSQLServer.Option ExplicitConst PackageName = "PackageName"Const ServerName = "ServerName"Const UserName = "UserName"Const Password = "Password"Dim DTSPkSet DTSPk = CreateObject("dts.package")DTSPk.LoadFromSQLServer ServerName, UserName ,Password,,,,,*PackageNameDTSPk.ExecuteIf DTSPk.Steps(1).ExecutionResult = 0 ThenResponse.Write "Package execution completed"ElseResponse.Write "Package execution failed"End IfSet DTSPk = NothingYou can also create the entire package from scratch from wit*hin yourasp andexecute it.Hope this helps.Chuck Gatto <cga...@anchorsystems.com> wrote in messagenews:8eapo8$frj$1@slb7.atl.mindspring.net...- Hide quoted text -- Show quoted text -[color=blue]> I'm looking for an example of how to execute an existing D*TS[/color]package froman[color=blue]> ASP (VB)script and would appreciate any and all response.* I don't[/color]even[color=blue]> know if it's possible> Thanks> - Chuck Gatto[/color]Chuck Gatto May 1 2000, 12:00 am show optionsNewsgroups: comp.databases.ms-sqlserverFrom: "Chuck Gatto" <cga...@anchorsystems.com> - Find messages by thisauthorDate: 2000/05/01Subject: Re: Executing a DTS Package using an ASP (VBScript) ScriptReply to Author | Forward | Print | Individual Message | Show original| Report AbuseBelow code works 100% in VB but the load fails in ASP. I g*et..."Microsoft OLE DB Provider for SQL Server. Login failed for *user "".error.I think the IIS (server a) is set for NT auth. and sql7 (on *server b)aswell but I can't be sure.Any idea what I should look for.Thanks"Dan Guzman" <DGuz...@nospamplease-earthlink.net> wrote in m*essagenews:sgi3hi617qo89@corp.supernews.com...- Hide quoted text -- Show quoted text -[color=blue]> This VBScript example loads and executes an existing DTS p*ackage[/color]from SQL[color=blue]> Server.[/color][color=blue]> Option Explicit> Const PackageName = "PackageName"> Const ServerName = "ServerName"> Const UserName = "UserName"> Const Password = "Password"> Dim DTSPk> Set DTSPk = CreateObject("dts.package")> DTSPk.LoadFromSQLServer ServerName, UserName ,Password[/color],,,*,,PackageName[color=blue]> DTSPk.Execute> If DTSPk.Steps(1).ExecutionResult = 0 Then> Response.Write "Package execution completed"> Else> Response.Write "Package execution failed"> End If> Set DTSPk = Nothing[/color][color=blue]> You can also create the entire package from scratch from w*ithin[/color]your aspand[color=blue]> execute it.[/color][color=blue]> Hope this helps.[/color][color=blue]> Chuck Gatto <cga...@anchorsystems.com> wrote in message> news:8eapo8$frj$1@slb7.atl.mindspring.net...[color=green]> > I'm looking for an example of how to execute an existing* DTS[/color][/color]packagefrom[color=blue]> an[color=green]> > ASP (VB)script and would appreciate any and all respons*e. I[/color][/color]don't even[color=blue][color=green]> > know if it's possible> > Thanks> > - Chuck Gatto[/color][/color]Dan Guzman May 1 2000, 12:00 am show optionsNewsgroups: comp.databases.ms-sqlserverFrom: "Dan Guzman" <DGuz...@nospamplease-earthlink.net&g*t; - Findmessages by this authorDate: 2000/05/01Subject: Re: Executing a DTS Package using an ASP (VBScript) ScriptReply to Author | Forward | Print | Individual Message | Show original| Report AbuseYou can specify a trusted connection with flag 256 instead o*fusername andpassword. For example:DTSPk.LoadFromSQLServer ServerName, , , 256,,,,PackageNa*meAssuming this is an intranet application running under NT 4.*0 and youwantto execute the package under the invoking user's account, yo*u can dothis asfollows:Specify 'clear text' for the IIS Directory Securityauth*enticationRemove 'Everyone' from the access list on the files (req*uiresNTFS) andgrant permissions to the usersGrant logins access to the database serverWith this method, users must enter their DomainUserName and* passwordwhenprompted.NT authentication presents a challenge when multiple servers* areinvolvedbecause NT 4.0 does not support delegation. Seehttp://msdn.microsoft.com/workshop/...re/security.asp fordetails.I understand Windows 2000 provides delegation capabilities b*ut thiscan be abit tricky to implement.BTW, if your DTS package does not access SQL Server, you can* save itto afile and use the LoadFromStorageFile method instead.Hope this helps.If you need to useChuck Gatto <cga...@anchorsystems.com> wrote in messagenews:8ekrkd$pmq$1@slb7.atl.mindspring.net...- Hide quoted text -- Show quoted text -[color=blue]> Below code works 100% in VB but the load fails in ASP. I* get...> "Microsoft OLE DB Provider for SQL Server. Login failed fo*r user[/color]"".error.[color=blue]> I think the IIS (server a) is set for NT auth. and sql7 (o*n server[/color]b) as[color=blue]> well but I can't be sure.> Any idea what I should look for.> Thanks[/color]Chuck Gatto May 6 2000, 12:00 am show optionsNewsgroups: comp.databases.ms-sqlserverFrom: "Chuck Gatto" <cga...@anchorsystems.com> - Find messages by thisauthorDate: 2000/05/06Subject: Re: Executing a DTS Package using an ASP (VBScript) ScriptReply to Author | Forward | Print | Individual Message | Show original| Report AbuseHey DanThanks again. I really appreciate your input and help.I actually solved the problem by calling dtsrun...I apologize for the delay getting back w/you but thee recent* virusatacksidetracked me.Thanks again."Dan Guzman" <DGuz...@nospamplease-earthlink.net> wrote in m*essagenews:sgsfh8spoog87@corp.supernews.com...- Hide quoted text -- Show quoted text -[color=blue]> You can specify a trusted connection with flag 256 instead* of[/color]username and[color=blue]> password. For example:[/color][color=blue]> DTSPk.LoadFromSQLServer ServerName, , , 256,,,,Package*Name[/color][color=blue]> Assuming this is an intranet application running under NT *4.0 and[/color]you want[color=blue]> to execute the package under the invoking user's account, *you can[/color]do thisas[color=blue]> follows:[/color][color=blue]> Specify 'clear text' for the IIS Directory Security[/color]au*thentication[color=blue]> Remove 'Everyone' from the access list on the files (r*equires[/color]NTFS)and[color=blue]> grant permissions to the users> Grant logins access to the database server[/color][color=blue]> With this method, users must enter their DomainUserName a*nd[/color]password when[color=blue]> prompted.[/color][color=blue]> NT authentication presents a challenge when multiple serve*rs are[/color]involved[color=blue]> because NT 4.0 does not support delegation. See> http://msdn.microsoft.com/workshop/...re/security.asp for[/color]details.[color=blue]> I understand Windows 2000 provides delegation capabilities* but this[/color]can bea[color=blue]> bit tricky to implement.[/color][color=blue]> BTW, if your DTS package does not access SQL Server, you c*an save[/color]it to a[color=blue]> file and use the LoadFromStorageFile method instead.[/color][color=blue]> Hope this helps.[/color][color=blue]> If you need to use> Chuck Gatto <cga...@anchorsystems.com> wrote in message> news:8ekrkd$pmq$1@slb7.atl.mindspring.net...[color=green]> > Below code works 100% in VB but the load fails in ASP. * I get...[/color][/color][color=blue][color=green]> > "Microsoft OLE DB Provider for SQL Server. Login failed *for user[/color][/color]"".[color=blue]> error.[color=green]> > I think the IIS (server a) is set for NT auth. and sql7 *(on[/color][/color]server b) as[color=blue][color=green]> > well but I can't be sure.> > Any idea what I should look for.> > Thanks[/color][/color]chris.duni...@agwsha.nhs.uk Jan 31, 10:05 am show optionsNewsgroups: comp.databases.ms-sqlserverFrom: chris.duni...@agwsha.nhs.uk - Find messages by this authorDate: 31 Jan 2005 10:05:00 -0800Subject: Re: Executing a DTS Package using an ASP (VBScript) ScriptReply | Reply to Author | Forward | Print | Individual Message | Showoriginal | Report AbuseHi,I've tried adding this function to my ASP pages and get the *followingerror message;Microsoft VBScript runtime error '800a01ad'ActiveX component can't create object: 'DTS.Package'/asp/pages/dts.asp, line 21Does anyone have any idea what I need to fix to get the DTS *to work?I'm v. new to SQL Server and ASP so any help would be apprec*iated.Many thanks, Chris Dunigan
I have to execute package in a Batch. I have done adding SSIS packages to SQL Server Agent and it is working. Now, my requirement is such that, when a user clicks "Transfer to batch" button in Windows application, i have to add all the packages to batch automatically without need for user to manually open SQL Server Agent and add packages. Is there any code available to do this?
I am able to run the pacakge in command line but when i try running the package through agent . The it starts the pacakge it says that it has been started by me local user and fails in next step saying that it has been execute by administrator.
I understand that there is a miss match between the permissions. Cos the SQL server Agent uses my login in but the SQL server agent on the Server that i am trying to execute has the Administrator log in. How to solve the issue?
Can someone tell me the best way to "automatically" execute a SSIS package. I have approximately 100 computers that I need to execute packages on. Here is the scenario:
I have some text files that are coming from our AS400 to a local computer (the package will NOT be used to bring files from our AS400. I will be using Client Access for that). The text files will be stored in a specified directory on the local computer. I will be importing these text files into a SQL Database on all 100 machines. Once I create the Import Package, what is the best way to execute this package on the devices?
I thought maybe after creating the package, I can put an EXE on the desktop that will execute the SSIS package. This way all I would have to do is click a icon on the desktop. I don't know if this is feasible or not. After building a package, do you make it a EXE or what? Pease advise. Thank in advance.
I'm having another go at attempting to call an SSIS package from a web service. The Web Service is set up on the same server as the sql server and SSIS package deployment. When i attempt to run the pacage from the web service it starts and then inmediately fails. I can see this in both the Integration Service Logs and the event viewer.
If i click on the package itself and run it using the DTExecUI, it runs without a problem. No logs are output by the package when called by the web service.
Can anyone suggest anything that i could to see if i can diagnose where the problem stems from.
Simplification of setup: - SQL Server Box with Enterprise Edition of SQL Server 2005 - Workstation with the client tools (Managment Studio, Business Intelligence Studio, etc) SQL Server 2005 installed running Windows XP
1. Am I right in saying that I need to install the SSIS service in order to run this package on my workstation from the command line, the Execute Package Utility tool, or through Management Studio? 2. I can however execute the package on my workstation in the Business Intelligence Studio without the SSIS service installed, correct? 3. Assuming I need to install the SSIS service on my workstation, if I execute the package via the command line or the Execute Package Utility utility, will it run on my local machine or on the server (i.e. which gets the load)? 4. Does Service Pack 1 address these issues?
