Excel Inserts Null Values

Tue, 31 May 2005 22:57:44 GMT

PROBLEM: Why does Excel Insert NULLs into my table when I know there is good data in there?.

When you import data from Excel the driver looks at the first 8 rows of data in a column and makes the decision that all data is to be of that type. So for example say I have the following data in an Excel spreadsheet column

Multiple Paths in Workflow

Mon, 01 Nov 2004 07:00:00 GMT

...

Read File Transformation

Mon, 02 Aug 2004 07:17:13 GMT

...

Global variable diagnostic script

Thu, 08 Jul 2004 19:34:45 GMT

There is often some confusion about the real value of a global variable at run-time, or the current execution location and security context. This simple script will illustrate what is really going on. . When trying to debug a package and isolate the problem, it is not always practical to use a full debugger, and sometimes it is just simpler to use a more manual approach to isolating...

SQLDTS File Transfer Protocol Task

Tue, 06 Jul 2004 15:18:17 GMT

The SQLDTS FTP Task is designed to give you greater control over FTP operations compared to the task supplied with SQL Server 2000, as well as supporting SQL Server 7.0. . The task supports both upload and download operations, as well as exposing options such as port, connection type and transfer type. . Other features include wildcard support, file filters with date parameters and the...

Bulk Export Task

Thu, 22 Apr 2004 16:51:34 GMT

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 DataPump due to it's simplicity, for example it doesn't require you to...

How to export all tables in a database

Tue, 17 Feb 2004 20:58:57 GMT

This is a very short article as it is really a demonstration of concepts explained elsewhere, although quite a useful implementation in it's own right. The export itself is done with the Bulk Export Task, and this is wrapped in a recordset driven loop as covered in the article How to loop through a global variable Rowset. The key to the...

Global Variables and Stored Procedure Parameters

Mon, 16 Feb 2004 20:35:36 GMT

Input Parameters. 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. . Create a new package, adding a SQL Server connection pointing to the database where you have created the sample stored procedure. Next...

Getting Syntax Help for DTSRun

Mon, 19 Jan 2004 20:15:17 GMT

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, including settings for global variables and logging, then use the generate function to...

Execute a package from a package

Mon, 12 Jan 2004 22:16:00 GMT

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. As well as being very simple to use it supports transactions, the benefits of which should be...

DTSBackup 2000

Tue, 30 Sep 2003 17:55:35 GMT

DTSBackup 2000 is a designed to help with both backup and transfer of DTS packages. Whilst it is not recommended as a substitute for your normal file and database backup routines, it is an additional layer of protection, with the benefit of package level granularity. See the article Backing Up Packages for additional information. . Whilst the name is backup, it is...

How to loop through a global variable Rowset

Mon, 08 Sep 2003 18:54:30 GMT

The Execute SQL Task for SQL Server 2000 allows both input and parameters to be specified. In this article we will be focusing on the Rowset output parameter functionality, and what we can then do with the Recordset it returns. The Rowset parameter takes the result set of a SELECT statement and stores it in an ADO Recordset object within a global variable you choose. You can then retrieve...

DataPump truncates delimited fields to 255 characters

Sat, 06 Sep 2003 12:04:01 GMT

There is a known issue where DTS truncates delimited columns at 255 characters when exporting to a text file. The problem is not with the DataPump itself, it is due to the default properties of text file connection provider as used within the DTS designer and the Import/Export wizard. The Wizard has been fixed (FIX: DTS May...