DTS for Dummies

Hi,
i am new to DTS and need to work with DTS.
Would you happen to have a Web link that contains a DTS for dummies online tutorial.
Is there a web site for step-by-step on how to use DTS ?
i will be thankful for your reply.
Regards,
Babbuhttp://www.sqldts.com/|||B user

Thank you for your reply.
i was looking for the web site.

Regards,
Babbu

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 DB2 Dates 0001-01-01

Hi,

I am trying to transfer a table from DB2 to SQLServer 2000 through a DTS package. The DB2 table contains fields with default dates of "0001-01-01". The DTS package errors out whenever it reads this date as "invalid data value". In SQLServer 2000, the date fields are of type ShortDateTime. I have searched the Internet but did not find a workable solution. Please, can anyone help me find a solution?

Older applications sometimes used dates like '9999-12-31' or '0001-01-01' to signify that the date was either NULL, invalid or not-entered. If DB2 is using that date as a valid point in time then the following would not work. However if it is a sentinal value used to represent NULL then you could transform such dates into NULL on-the-fly as you extract them from DB2; in pseudocode:

SELECT

NullIf(theDate, '0001-01-01') as theDate, <other fields>

FROM theTable

(You would need to find out the equivalent function for NullIf in DB2 parlance) This would have the effect of leaving all dates alone except for '0001-01-01' which would be translated to NULL, which would keep SSIS happy.

If you translated it to NULL then you would have to be careful that you did not break other business rules.

|||Thanks but I don't have any control over the creation of DB2 tables hence I was looking for DTS to do the trick. Can DTS handle this?|||In the source component of your DTS package, use the SELECT statement and conversion functions as detailed in my previous reply.|||

Thanks.

I am rather new to DTS and am trying to get an example of creating a package using vbscript. I have looked at Books on line but the examples are a little confusing for me right now. Do you by chance have any sample code that shows how to do something similar like this ?

Appreciate your help.

DTS - problem on after inserting .csv file into sql database

Hello,

I inserted a .csv file into the database using DTS and it get inserted successfully.

But my problem is, the csv file contains the datas that has double quotes on it (eg: 1/2" iron rod, 3/4" wooden rod).

When I checked the inserted datas, it was inserted as "1/2" iron rod, 3/4"""," 3/4" wooden rod"".

How do I eliminate these additional double quotes.

Raviraj Danasekaran

The pattern of what it did (per your example) doesn't make sense to me.

That said, one way would be to update the affected columns using a replace function.

update my_table

set my_problem_column = replace(my_problem_column,'""','"')

(that's a single quote followed by two double quotes followed by a single quote, then a comma, then a single quote followed by a double quote and a single quote.)

That's a brute force method, but it might work to fix the problem if it's a one-time thing.

You might need to adjust your dts packages, there might be some values you can set that would change the quote handling behavior.

DT_TEXT not supported while importing from an XML file?

I have an XML file that contains a field that has over 8000 characters in it. I cannot use a (n)varchar for it, I must use a Text for it (although as a last resort I could split the string into several (n)varchar columns). I want to set the external column of the XML column to a DT_TEXT and I receive and error message that states :

Error at Import XFFD Data [xffd [1]]: The SSIS Data Flow Task data type "DT_TEXT" on the external metadata column "reviewText" (32411) is not supported for the component "xffd" (1).

I've tried converting the nvarchar into a text stream with the use of a Data Conversion Transform, but I think the Validating steps are truncating the field.

HELP. I've been beating my head against this for a couple hours a day.

Thanks,
Scott

The XmlSrc only supports Unicode text. Set the output column type of the XML source to DT_NTEXT. If you need it to be DT_TEXT downstream, you would then need to use a data conversion transform to convert to DT_TEXT.

Thanks
Mark