The problem (or advantage, depending on your viewpoint) of DTS is that it often provides a half dozen different ways to do something.

Define a Connection object, and run an Execute...

An alternative to the variable columns problem is to use more T-SQL and less DTS.

eg:

SELECT *

INTO TempTableName

FROM OpenRowSet('MSDASQL', 'Driver={ [Insert DBF driver specifics here] }'

INSERT INTO PermanentTable

(FirstName, LastName, ....)

SELECT FirstName,...

>>In first SQl query task i have set the Output parameter as Rowset

This is the wrong setting. It should be "Row Value" if you want to assign individual Select statement...

Which implies some need to use row-at-a-time cursor like processing in VBScript, versus set-based T-SQL UPDATEs to the data ? What exactly are you trying to do ?

Any particular reason why you're doing all this in VBScript, versus using the DTS designer to add a connection and a couple of Execute SQL tasks ?

Have you verified that tables FIN and FinancialCSV contain those columns ?

Also, take a close look at this:

set @sql = N'if object_id(''.dbo.IKBFinStg'') is not null drop table .dbo.IKBFinStg'

exec sp_executesql @sql

What...

Yes. You use the expression in the SELECT list. The expression is the CASE ... WHEN ... END construct:

case SLPBXLT.SLBTYP when 7 then 'CR'

                    when 8 then 'CR'

                    when 9 then...

Why all the dynamic SQL ?

Are you sure of database context. I see a bunch of 2-part naming on objects, without a database context, so is it possible you're executing...

CREDIT is the last column in your INSERT list

The expression that you have added to add the logic of 7,8,9 = "CR" is the third column in your SELECT list.

Ordinal...

I'd say the design is flawed. The purpose of the ALTER TABLE is to add an IDENTITY column to data that has just been loaded. Don't fight the tool.

Create a...

Your original question listed 9 columns in the INSERT list, but many more in the SELECT list. The number of columns in both must match, so we should start by...

The Excel driver does not support deleting worksheet rows. Since you don't want to recreate the destination XLS file, your other option is to use an ActiveX script task, and...

What is your line terminator set to ? Have you opened the text file in a hex/binary editor and verified that you are getting the expected line terminator ?

eg: we've...

Actually, that's not the most efficient way. For a smalldatetime, it is stored internally as two 2-byte integers.

You can convert a date to a float, Floor() it to get the...

If it's just 1 package, open it up on 1 server and "Save As ..." on the other server.

If it's multiple packages, get the DTSBackup2000 tool:

http://www.sqldts.com/default.aspx?272