|
|
 |
Error : When I try to backup to a Network share
|
| Total Hit (2876) |
I get the following error message: «b»"Can't open dump device ... device error or
device off line. Please consult the SQL Server error log for more details."«/b»
«b»«u»Resolution:«/u»«/b»
To perform SQL Backup on network share SQL Executive service needs to run under an account that has permis
....Read More |
Rating
 |
|
|
|
How do I enumerate tables in an Access Database
|
| Total Hit (1161) |
If you need to enumerate the tables in an Access database, the easiest method is through the ActiveX Data Objects Extensions for Data Definition Language and Security. The ADOX library, as it is better known, can be used just like other COM objects from within ActiveX script, such as a task or workf
....Read More |
Rating
 |
|
|
|
Working with files and the FileSystemObject
|
| Total Hit (1110) |
The ability to work with files gives us great flexibility in how we use DTS. It allows us to add some intelligence into a package, and cope with unexpected situations automatically. The key to working with files is the Scripting Run Time as this gives us the FileSystemObject, which can be fully expl
....Read More |
| Rating
|
|
|
|
How do I open a file package or template
|
| Total Hit (946) |
In the save package dialog screen there are several Locations available. The Structured Storage File option is useful if you wish to keep the package as a file, perhaps so that it can be added into a source control repository, or for manually copying to another server or transfer media. Whilst it is
....Read More |
| Rating
|
|
|
|
Create an Access Database
|
| Total Hit (974) |
When exporting data to a text file, if the file does not exist it will be created, or if it does exist it will be overwritten. In contrast, when exporting to an Access database the file and table must exist already. This article will show you how to create an empty Access database ready to receive y
....Read More |
| Rating
|
|
|
|
Open File Dialog Custom Task
|
| Total Hit (1138) |
If you run packages manually and want the ability to select a file through a GUI, this is for you. This is a simple custom task that pops up a standard windows open file dialog box when executed. It assigns the chosen filename to a global variable for use within the package.
....Read More |
| Rating
|
|
|
|
Coping with text files and abnormal column or row delimiters
|
| Total Hit (1241) |
Sometimes you may receive a text file to import that has a row or column delimiter not available in the standard drop-down boxes of the Text File Properties dialog.
The first thing to try in these cases is to type directly into the Row Delimiter or Column Delimiter box. This will work fine if th
....Read More |
| Rating
|
|
|
|
Find an Access Database (from anywhere on the file system)
|
| Total Hit (638) |
It may be that we have built a DTS package to synchronise data held in an Access database and our SQL Server. The Access databases all have a consistent name but due to the fact that they are used by travelling salesmen on their laptops we cannot guarantee where the database will be.
For this we
....Read More |
| Rating
|
|
|
|
Deleting an Excel WorkSheet from within a package
|
| Total Hit (1893) |
Here is a simple and quick way of deleting a worksheet in an Excel spreadsheet. It uses two global variables, one for the Excel file name and one for the sheet to delete. As this uses OLE automation of the Excel application object, you will need Excel installed on any machine that this package execu
....Read More |
| Rating
|
|
|
|
Importing a Named Range From Microsoft Excel
|
| Total Hit (1725) |
On the newsgroups recently there has been a great deal of interest in importing data from Microsoft Excel into SQL Server using DTS. One of the questions that caught my eye was "How do I import values from cells from anywhere on the page?". You can do this using Named Ranges in the Excel spreadsheet
....Read More |
| Rating
|
|
|
|
How can I dynamically set the Access System Database
|
| Total Hit (669) |
You can easily change the filename for an Access database connection using the Dynamic Properties Task in SQL Server 2000. Unfortunately the property selection dialog does not allow access to the system database property (Jet OLEDB:System database) which you need to specify correctly when accessing
....Read More |
| Rating
|
|
|
|
How to check the file date
|
| Total Hit (603) |
This sample script was written to check the age of a file. If you import files according to a schedule, you may wish to check that the file is recent to prevent the potential re-import of an old file. The script reads the filename from an existing package connection and uses the scripting FileSystem
....Read More |
| Rating
|
|
|
|
Connecting to an AS400 with DTS
|
| Total Hit (563) |
DTS does not support AS400 connectivity natively, but by obtaining a third party ODBC or OLE-DB provider you can connect quite easily. In no particular order, some links for suppliers of such providers:
|
| Rating
|
|
|
|
How can I change the filename for a text file connection?
|
| Total Hit (619) |
It is a common scenario to have a scheduled package that imports a new file each day. Conversely you may wish to produce a uniquely named file each day. You can change the filename (DataSource) property of a connection at run-time from within an ActiveX Script Task.
....Read More |
| Rating
|
|
|
|
How can I change the filename for an Access Connection?
|
| Total Hit (696) |
You can change the Access MDB file of a connection at run-time using an ActiveX Script Task. The following example reads the new filename from a global variable and updates the connection called "Accounts".
|
| Rating
|
|
|
|
|
|
Getting Syntax Help for DTSRun
|
| Total Hit (579) |
If you are having problems with the syntax for the dtsrun command line, or just want an easy way of generating a valid command line first time, try using the dtsrunui utility. As the name indicates it is very similar to dtsrun, but with a user interface. This allows you to easily select your package
....Read More |
| Rating
|
|
|
|
Execute a package from a package
|
| Total Hit (598) |
You can use a parent or master package to control the execution of one or more child packages. This is often used in a looping scenario, or when there are a number of individual packages that make up a process.
This can be achieved very simply in SQL Server 2000 by using the Execute Package Task
....Read More |
| Rating
|
|
|
|
|
|
DTS Global Variables and Visual Basic .Net
|
| Total Hit (704) |
1. In order to change the value of a DTS Global Variable of type String from VB.Net code, the variable must be programmatically removed from the collection and added back with its new value.
2. In order to change the value of a DTS Global Variable of type Date, the new value must be explictly cas
....Read More |
| Rating
|
|
|
|
Execute a package from Visual Basic (VB)
|
| Total Hit (695) |
To execute a package from Visual Basic is a relatively simple task using the DTS object model. Before you start using the object model you must add a the appropriate reference to your project. From the Project menu select References and check the "Microsoft DTSPackage Object Library".
....Read More |
| Rating
|
|
|
|
Global Variables and Stored Procedure Parameters
|
| Total Hit (600) |
One of the benefits of the SQL Server 2000 is that the SQL tasks support mapping of global variables to parameter placeholders within the SQL. This means you can use the global variable as an input parameter for your stored procedure.
|
| Rating
|
|
|
|
Bulk Export Task
|
| Total Hit (675) |
The DTS Bulk Export Task was originally conceived in response to a problem with the OLE-DB provider for text files. For more details see DataPump truncates delimited fields to 255 characters. Whilst this problem can be fixed quite easily the Bulk Export Task still offers some benefits over the DataP
....Read More |
| Rating
|
|
|
|
DTS How to...Skip Rows during Import
|
| Total Hit (848) |
Firstly you can skip header rows by using the First Row property of the DataPump Task, see Advanced properties sheet. The Text File Connection also has a Skip Rows property, see file Properties of the Connection.
|
| Rating
|
|
|
|
|
|
DTS How to...Transfer Packages between Servers
|
| Total Hit (1056) |
There are two ways to transfer packages between servers. The simple method is to save packages as COM Structured Storage Files, copy them to the new location, then open and save them to the new server. Whilst this works, it is very tedious when you have a lot of packages. You can automate much of th
....Read More |
| Rating
|
|
|
|
DTS Errors
|
| Total Hit (1042) |
«LI»"Could not create component categories manager" Or "Could not create an instance of the DTS package"
«LI»"DTS WIZARD ERROR: CoCreateInstance - Class not Registered"
«LI»"The parameter is incorrect" Or "Invalid class string" Or "The system cannot find the file specified"
«LI»"The license fo
....Read More |
| Rating
|
|
|
|
|
|
Implementing Application Roles
|
| Total Hit (1157) |
SQL Server 7.0, as well as SQL Server 2000, support several technological feature enhancements that allow for a faster time to completion for database and application development. Application roles exist as SQL Server database objects that allow developers and database administrators to assign SQL S
....Read More |
| Rating
|
|