DTS fails at customer site with "Too many columns", works locally

I am having the most baffling problem with DTS... :confused:

I have a set of ActiveX transforms that execute on my customers flat transaction data files, destination a single database table. Since they switched to a new method of generating the flat file using SAS, the DTS package mysteriously will fail at a couple select records. The error is always the same, and turning on error logging in DTS yielded this:

Step 'DTSStep_DTSDataPumpTask_1' failed

Step Error Source: Microsoft Data Transformation Services Flat File Rowset Provider
Step Error Description:Too many columns found in the current row; non-whitespace characters were found after the last defined column's data.
Step Error code: 80043013
Step Error Help File: DTSFFile.hlp
Step Error Help Context ID:0

Step Execution Started: 11/16/2004 6:37:51 PM
Step Execution Completed: 11/16/2004 6:39:39 PM
Total Step Execution Time: 107.415 seconds
Progress count in Step: 515000

The exact same file parses all the way through on my laptop, with the same DTS package. Tests have revealed no strange characters or whitespaces in the data file, not at that record (running a Test... on any of the active x transforms will fail at row 515186 always, until that row is deleted and it fails on some subsequent row - this iteration went on at the customer site until about 5 rows were deleted this month and it finally worked), not at any other records. My database and the customer database are both using the same, default character set.

The only microsoft KB article referencing anything resembling my problem is
http://support.microsoft.com/default.aspx?scid=kb;en-us;292588
but this does not hold because I am not specifying fixed width, but rather comma delimited.

If anyone has any ideas about what other environmental variables are coming into play here, please let me know - I'm at the end of my rope. I believe we are both patched up to SQL 2000 SP3. They have an XP client connecting to a 2003 server; I have an XP client/server. Neither machine has the NLS_LANG environment variable set.This may not be helpful...but have you considered just using a stored procedure instead?|||What happens to that row when you try to import the file into access? If you create an extra column at the top of the flat file, it should insert whatevers in that column for the five offending rows right? Once you get it into a table query it with a NOT NULL. It might give you a clue as to what the offending characters are.

If your stuck with the file then you might just have to use the insertfail phase to make the pump task skip to the next record when it finds an offending row. Read up on multiphase to find out exactly how you'd do this.

Sorry can't help you more.|||Modify the DTS package to use an Execute Process Task and then use bcp.

-or-Use Execute SQL Task and the Bulk Insert Transact-SQL command.|||...the file imports fine here with the exact same DTS package, so I don't want to modify it to address a problem that isn't really the problem. IN other words, there is nothing to indicate there is anything actually wrong with the data itself - no whitespaces, no bad characters or problem causing characters, no datatype mismatch, nothing; it looks just like the last row. Here are the rows before and after as well as the one that failed:

737,10/15/2004,09:11:39,114,15536,1
737,10/15/2004,09:11:49,114,18408,1
737,10/15/2004,09:11:54,714,18024,1

I am not using column 5, but all the others. From last month to this month the number of offending rows increased from 1 to 7, so I don't want to start throwing away data that for all other intensive purposes looks good automatically in case it starts multiplying.

Since it works here but fails there, it has to be something environmental, maybe with character sets or??|||Generating files from SAS...Like from a mainframe?

I betcha you got some low values [CHAR('00') ] going on...

I know you don't want to alter your process, but I ALWAYS create a staging environment and load the data to it, then audit the data to look for problems...then I move the data in after I verify it...

And it's all done with a stored procedure|||Thanks for the tip. I am looking into how these "low values" occur and how these EBCDIC to ASCII conversions can get hung up. I'm sure the answer lies somewhere in there.

Well, the front end application will run a custom DTS package, but not a custom SP. At least the staging need is moot, because it rolls the whole thing back if one record fails...

DTS execution account

hi all,

I hace a DTS package that contains a transformation task and an ActiveX Script Task (the last task accesses the registry in order to read some values). So first, does anybody knows under what user account the DTS package will run? and second what permissions should the account have in order to execute the DTS?.

Thanks in advance.

God Bless.The answer is that it depends (you knew I was going to say that).

If it is run interactively, it will run under the login of whoever is logged in (it will also run in the client context of your login, so if you are using EM from a client workstation and are not using it through Terminal Services, watch out!)

If it is run via a SQL Server job, then it will run in the context of the SQL Agent Service.

If you schedule it using NT Scheduled tasks, you can specify the user when setting up the task.

If ou execute it from an SP, I think you can specify the user context (though I don't swear to that -- it might pick up the user context of the person executing the SP).

Originally posted by mvargasp
hi all,

I hace a DTS package that contains a transformation task and an ActiveX Script Task (the last task accesses the registry in order to read some values). So first, does anybody knows under what user account the DTS package will run? and second what permissions should the account have in order to execute the DTS?.

Thanks in advance.

God Bless.

DTS Error (ActiveX)

Hello,

I have a DTS package that has an ActiveX task. This task works with access to create databases and push out data to them. The problem is, I'm getting an error trying to establish a connection to the SQL Server database as such:

set objConn = Server.CreateObject("ADODB.Connection")

I get this as a resolution:

http://support.microsoft.com/default.aspx?scid=kb;en-us;201740

But I wasn't sure since I'm not dealing with ASP. I'm trying to run the task as myself manually, not scheduled also, for testing purposes. Any ideas?

Thanks.

Try this url for sample code. Hope this helps.

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

DTS connection

Hi
I'm using DTS to transform Data between 2 SQL server .My package uses Activex transformation with lookups.first I used 2 connections one for the Source and another for the destination and I used the destination connection to fetch the data for thr lookup but while executing I faced an error saying Connection is busy with results from another command. I creaed another connection for the lookups and it worked. I started the profiler while the package was running and I noticed the the lookup connection is opend and closed once per each lookup (in my case I use up to for lookups per row) which leaks my performance down .so i have these questions which I hope any one thankfully answer:
1- why can't I use the Destination connection for the lookup?
2- why the connection is open and closed each time it looks-up?

Thanks in advanceHi Eisa,

This is a forum for the next version of DTS, called Integration Services, so you probably won't get enough qualified eye balls looking at your question to answer it.
Try the microsoft.public.sqlserver.dts newsgroup instead, that should have more activity on it.

thanks,
ashsql

DTS activex task to query soap web service ?

Hello there
I wanted to know if anybody has an example of an activex task in a dts that
will query a soap service ?
I never queries a soap service from vbscript so I guess i need to see an
example to start ? please
ThanksSimo Sentissi wrote:
> Hello there
> I wanted to know if anybody has an example of an activex task in a dts tha
t
> will query a soap service ?
> I never queries a soap service from vbscript so I guess i need to see an
> example to start ? please
> Thanks
>
There have been various toolkits released and the .Net tools have good
support for SOAP. Trying to write that by hand in VBS would seem like
very hard work. I would look to develop something in .Net that does the
work and wrap it either as a custom task or as a COM DLL for use from
the ActiveX Script.
Darren
http://www.sqldts.com
http://www.sqlis.com

DTS activex task to query soap web service ?

Hello there
I wanted to know if anybody has an example of an activex task in a dts that
will query a soap service ?
I never queries a soap service from vbscript so I guess i need to see an
example to start ? please
Thanks
Simo Sentissi wrote:
> Hello there
> I wanted to know if anybody has an example of an activex task in a dts that
> will query a soap service ?
> I never queries a soap service from vbscript so I guess i need to see an
> example to start ? please
> Thanks
>
There have been various toolkits released and the .Net tools have good
support for SOAP. Trying to write that by hand in VBS would seem like
very hard work. I would look to develop something in .Net that does the
work and wrap it either as a custom task or as a COM DLL for use from
the ActiveX Script.
Darren
http://www.sqldts.com
http://www.sqlis.com

DTS activex task to query soap web service ?

Hello there
I wanted to know if anybody has an example of an activex task in a dts that
will query a soap service ?
I never queries a soap service from vbscript so I guess i need to see an
example to start ? please
ThanksSimo Sentissi wrote:
> Hello there
> I wanted to know if anybody has an example of an activex task in a dts tha
t
> will query a soap service ?
> I never queries a soap service from vbscript so I guess i need to see an
> example to start ? please
> Thanks
>
There have been various toolkits released and the .Net tools have good
support for SOAP. Trying to write that by hand in VBS would seem like
very hard work. I would look to develop something in .Net that does the
work and wrap it either as a custom task or as a COM DLL for use from
the ActiveX Script.
Darren
http://www.sqldts.com
http://www.sqlis.com

DTS activex task to query soap web service ?

Hello there
I wanted to know if anybody has an example of an activex task in a dts that
will query a soap service ?
I never queries a soap service from vbscript so I guess i need to see an
example to start ? please
ThanksSimo Sentissi wrote:
> Hello there
> I wanted to know if anybody has an example of an activex task in a dts that
> will query a soap service ?
> I never queries a soap service from vbscript so I guess i need to see an
> example to start ? please
> Thanks
>
There have been various toolkits released and the .Net tools have good
support for SOAP. Trying to write that by hand in VBS would seem like
very hard work. I would look to develop something in .Net that does the
work and wrap it either as a custom task or as a COM DLL for use from
the ActiveX Script.
--
Darren
http://www.sqldts.com
http://www.sqlis.com

DTS activeX script...please help begginner

Hi there, im using this vb script.

'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************

' Copy each source column to the destination column
Function Main()
DTSDestination("SES_STATUS") = DTSSource("Session Status")
DTSDestination("SIT_ID") = DTSSource("Year/Sitting")
DTSDestination("TUT_ID") = DTSSource("Tutor Code")
DTSDestination("SLC_ID") = DTSSource("Sub Location")
DTSDestination("SES_STREAM") = DTSSource("Stream")
DTSDestination("SES_TYPE") = DTSSource("Subject Type")
DTSDestination("SES_DATE") = DTSSource("Start Date")
DTSDestination("SUB_ID") = DTSSource("Subject Code")
DTSDestination("SES_ID") = DTSSource("Start Date")
Main = DTSTransformstat_InsertQuery
End Function

what I would Like to know is how can I complete this script so that it knows when to run an update statement and when to run a insert statement (I have already created the update and insert statements) as this dts package will run every night and the source database tables are updated and inserted into quite regualar. So something like this is needed:

if record exists in destination
do update statement

if record not exist
do insert startement

many thanks in advance.

You can use an execute SQL task with two statements, one that inserts if the record does not exist and one that updates if the record does exist.

-Sue

|||Thanks for your reply, could you please give me a code example of this.

Many thanks|||

thanks for that. I've abandoned the activeX and have decided to use an execute SQL task using this tsql:

INSERT INTO dbo.Target
SELECT *
FROM dbo.Source AS s
WHERE NOT EXISTS
(
SELECT 1
FROM dbo.Target AS t
WHERE t.title_id = s.title_id
)

however my source and target tables are on different database, so when I choose a connection in the execute sql task I get an error as each database table exists different database, how could I over come this?

many thanks

|||

Im using this activeX vb script to perform updates or inserts depending if the primary key already exists in the destination table, however the it never updates when it should, infact it never updates it always inserts, which leads to a violation of the primary key. Can somebody help me see where Im going wrong...thanks

'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************

' Copy each source column to the destination column
Function Main()
Dim sSessCode
sSessCode = DTSDestination("SES_ID")
DTSDestination("SUB_ID") = DTSSource("Subject Code")
If DTSSource("Start Date") = "01/01/1753" Then
DTSDestination("SES_DATE") = "01/01/1980"
Else
DTSDestination("SES_DATE") = Cdate(DTSSource("Start Date"))
End If
DTSDestination("SES_TYPE") = DTSSource("Subject Type")
DTSDestination("SES_STREAM") = DTSSource("Stream")
DTSDestination("SLC_ID") = DTSSource("Sub Location")

DTSDestination("TUT_ID") = DTSSource("Tutor Code")
DTSDestination("SIT_ID") = DTSSource("Year/Sitting")
DTSDestination("SES_STATUS") = DTSSource("Session Status")

Select Case sSessCode
Case DTSSource("Session Code")
' MsgBox("Update")
DTSDestination("SES_ID") = DTSSource("Session Code")
Main = DTSTransformstat_UpdateQuery
Case Else
' MsgBox("Insert")
DTSDestination("SES_ID") = DTSSource("Session Code")
Main = DTSTransformstat_InsertQuery
End Select
End Function

DTS ActiveX Script Task..vanished

I have no idea what's going on.. but when I open the SQL DTS designer.. there's no ActiveX Script task on the left toolbar anymore. It used to be there. How do I get it back?? I remember playing around with some dts dll files on my machine... maybe I did something that made it go away (note: the DB server is not on my machine.. I just run Enterprise Manager on my machine to access it). Should I reinstall Enterprise Manager?OK, weird. I registered a new task, I pointed to axscphst.dll, having a feeling that was the ActiveX stuff I needed. It was.. ActiveX Script Task is back, along with many other tasks.. but they all have a strange page icon now. Not a huge issue really, but how do I get these tasks to their original appearance ?

DTS ActiveX Script error

The 1st 4 lines of code were copied verbatim from a VBS file that works
perfectly.
Why does it fail when running from a DTS ActiveX script?
Function Main()
set objShell = WScript.CreateObject("Wscript.Shell")
wait = true
strEXEC = "C:\FTPTest\FTPSNAP.BAT"
objShell.run strEXEC, 1, wait
Main = DTSTaskExecResult_Success
End Function
Debugging is not an option until I know how to make JIT debugging work...
(see my other posts on JIT Debugging)
Regards,
John> set objShell = WScript.CreateObject("Wscript.Shell")
The WScipt object is available from a Windows Scripting Host environment but
not from a DTS ActiveX script task.
It looks to me like you should use a DTS ExecuteProcess task instead.
Hope this helps.
Dan Guzman
SQL Server MVP
"John Keith" <JohnKeith@.discussions.microsoft.com> wrote in message
news:FF140623-C03B-4DB6-BE33-852C933E0849@.microsoft.com...
> The 1st 4 lines of code were copied verbatim from a VBS file that works
> perfectly.
> Why does it fail when running from a DTS ActiveX script?
> Function Main()
> set objShell = WScript.CreateObject("Wscript.Shell")
> wait = true
> strEXEC = "C:\FTPTest\FTPSNAP.BAT"
> objShell.run strEXEC, 1, wait
> Main = DTSTaskExecResult_Success
> End Function
> Debugging is not an option until I know how to make JIT debugging work...
> (see my other posts on JIT Debugging)
> --
> Regards,
> John|||http://www.codeproject.com/useritems/DTS__VBNET_.asp|||Yep, thats what what needed.
Thanks!
--
Regards,
John
"Dan Guzman" wrote:

> The WScipt object is available from a Windows Scripting Host environment b
ut
> not from a DTS ActiveX script task.
> It looks to me like you should use a DTS ExecuteProcess task instead.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "John Keith" <JohnKeith@.discussions.microsoft.com> wrote in message
> news:FF140623-C03B-4DB6-BE33-852C933E0849@.microsoft.com...
>
>|||Thanks for the reply!
I am not using VBNET, yet.
All my VB experience comes from MS Office with VBA.
Do you have a link that covers the same or similar topics from the
standpoint of using VB6 projects or an excel macro with VBA code?
From looking at the code samples on your link... I don't recognize the "Try"
and "End Try" statements nor the "Catch exc" I assume those are some new NE
T
commands?
Regards,
John
"vipinjosea" wrote:

> http://www.codeproject.com/useritems/DTS__VBNET_.asp
>

DTS ActiveX Script

Hi All,
I have the following NewBie Question:

If I have the sources:
StartDate: e.g. 23/3/2002
ApproveDate: e.g. 26/7/2002

And I want the destination be count in no.of days
(TotalDays), what should I write in the ActiveX Script?

Please give me some hints, thanks!

RickSolved.

DTS ActiveX BCP file manipulation

Does anyone know the best way to handle this type of situation?

A file coming into a directory based on the date filename042707
I use the fileexist stored procedure to check for the existence.
I use xp_cmdshell(sp)... stored procedure to rename the file so that
it just has filename instead of the date... I can not use
variables within the xp_cmdshell to replace the date...
everyday the filename would change to filename042807,filename042907 etc...

Basically I know how to copy the file to another directory if it exists...
then I want to strip the right 8 characters off... rename just to that
but with the rename i have to know what the file will be named for that day ...
excuse the grammar just somewhat tired...
any suggestions... please thanks time for sleep"DTS ActiveX BCP file manipulation"

what on earth are you doing with ActiveX in SQL Server?|||not sure where r u facing problem

I can not use variables within the xp_cmdshell to replace the date...

u can use variables withing xp_cmdshell like below

declare @.cmd varchar(100)
set @.cmd = 'ren d:\file042807.xls file' + replace(convert(varchar ,getdate(),1),'/','') + '.xls'
exec master..xp_cmdshell @.cmd

what on earth are you doing with ActiveX in SQL Server?

talking about ActiveX of DTS...

DTS : Format Problem !

I have a statement in the DTS ( Activex Script ) like this :

TomorrowDate = Format$(Now, "yyyy-mm-dd")

When i am running the package, i am getting the following error :

ActiveX Script Task: undefined

Error Code: 0

Error Source= Microsoft VBScript compilation error

Error Description: Invalid character

Error on Line 8

Can anyone tell me what is the solution to this error ?

Thanks in advance.

Remove the $ in Format$

-Vijay

|||

Hi vijay,

Thanks. Even when i removed $ from format, still an error :

Telling that :

Type Mismatch "Format"

DTS - How to make ActiveX script open a connection in the diagram?

I have a simple question but I'm having a really hard time finding an answer.

Let's say I'm designing a DTS package, and I have a connection to a SQL Server called ConnSQL. How do I reference this connection in my ActiveX script? (so I can run SQL queries and such)

ThanksThere is no way to reference such connections. This is because you can only reference COM objects from VBScript, and the DTS package is not a COM object, but itself a script. Scripting is included to allow you to access objects in the operating system environment.

If you want to run SQL queries against a DTS connection, you want to use a SQL Query task, which operates against whatevery connection you attach it to.

If your goal is moving data between files and/or tables, you want to use a transformation task between two DTS connections (select the source, holding SHIFT, select the second, then click the Transformation Task button on the toolbar.

DTS

Hi,
(i).I wanted to transfer data from SQL server to access through code.
or Activex Script.
(ii) how can i devide a table in two parts means one table but
vertually two tables.
from
Killer
doller
www.sqldts.com
"doller" <sufianarif@.gmail.com> wrote in message
news:1125895448.595557.6480@.g44g2000cwa.googlegrou ps.com...
> Hi,
> (i).I wanted to transfer data from SQL server to access through code.
> or Activex Script.
> (ii) how can i devide a table in two parts means one table but
> vertually two tables.
>
> from
> Killer
>
|||Hi Uri,
Canu pls tell me the exact link to that page where sqldts.com have
informagtion about me question.
And what about my second question.
from
doller
|||(i).I wanted to transfer data from SQL server to access through code.
or Activex Script
"doller" <sufianarif@.gmail.com> wrote in message
news:1125988732.096701.123580@.z14g2000cwz.googlegr oups.com...
> Hi Uri,
> Canu pls tell me the exact link to that page where sqldts.com have
> informagtion about me question.
> And what about my second question.
> from
> doller
>

DTS

Hi,
(i).I wanted to transfer data from SQL server to access through code.
or Activex Script.
(ii) how can i devide a table in two parts means one table but
vertually two tables.
from
Killerdoller
www.sqldts.com
"doller" <sufianarif@.gmail.com> wrote in message
news:1125895448.595557.6480@.g44g2000cwa.googlegroups.com...
> Hi,
> (i).I wanted to transfer data from SQL server to access through code.
> or Activex Script.
> (ii) how can i devide a table in two parts means one table but
> vertually two tables.
>
> from
> Killer
>|||Hi Uri,
Canu pls tell me the exact link to that page where sqldts.com have
informagtion about me question.
And what about my second question.
from
doller|||(i).I wanted to transfer data from SQL server to access through code.
or Activex Script
"doller" <sufianarif@.gmail.com> wrote in message
news:1125988732.096701.123580@.z14g2000cwz.googlegroups.com...
> Hi Uri,
> Canu pls tell me the exact link to that page where sqldts.com have
> informagtion about me question.
> And what about my second question.
> from
> doller
>

DTS

Hi,
(i).I wanted to transfer data from SQL server to access through code.
or Activex Script.
(ii) how can i devide a table in two parts means one table but
vertually two tables.
from
Killerdoller
www.sqldts.com
"doller" <sufianarif@.gmail.com> wrote in message
news:1125895448.595557.6480@.g44g2000cwa.googlegroups.com...
> Hi,
> (i).I wanted to transfer data from SQL server to access through code.
> or Activex Script.
> (ii) how can i devide a table in two parts means one table but
> vertually two tables.
>
> from
> Killer
>|||Hi Uri,
Canu pls tell me the exact link to that page where sqldts.com have
informagtion about me question.
And what about my second question.
from
doller|||(i).I wanted to transfer data from SQL server to access through code.
or Activex Script
"doller" <sufianarif@.gmail.com> wrote in message
news:1125988732.096701.123580@.z14g2000cwz.googlegroups.com...
> Hi Uri,
> Canu pls tell me the exact link to that page where sqldts.com have
> informagtion about me question.
> And what about my second question.
> from
> doller
>