Data export to individual files

Hello,
I am looking to T-SQL extract records from a table and have each record
stored in individual .eml files. A query based DTS doesn't let me
specify what extension my files will have AND I'm unsure how to have
each record written to a separate file? Any ideas on how I can pull
this off?
Much appreciated,
Prpryan75 wrote:
> Hello,
> I am looking to T-SQL extract records from a table and have each record
> stored in individual .eml files. A query based DTS doesn't let me
> specify what extension my files will have AND I'm unsure how to have
> each record written to a separate file? Any ideas on how I can pull
> this off?
> Much appreciated,
> Pr
>
A few possibilities:
1. Use OSQL to run the query and pipe the output to the desired file
2. Use xp_cmdshell to issue DOS "ECHO" commands to write the desired file
3. Use a combination of xp_cmdshell and OPENROWSET to create and then
write the desired file
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Data export to individual files

Hello,
I am looking to T-SQL extract records from a table and have each record
stored in individual .eml files. A query based DTS doesn't let me
specify what extension my files will have AND I'm unsure how to have
each record written to a separate file? Any ideas on how I can pull
this off?
Much appreciated,
Prpryan75 wrote:
> Hello,
> I am looking to T-SQL extract records from a table and have each record
> stored in individual .eml files. A query based DTS doesn't let me
> specify what extension my files will have AND I'm unsure how to have
> each record written to a separate file? Any ideas on how I can pull
> this off?
> Much appreciated,
> Pr
>
A few possibilities:
1. Use OSQL to run the query and pipe the output to the desired file
2. Use xp_cmdshell to issue DOS "ECHO" commands to write the desired file
3. Use a combination of xp_cmdshell and OPENROWSET to create and then
write the desired file
Tracy McKibben
MCDBA
http://www.realsqlguy.com

data driven query DTS activeX script

Hi guys, 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.

As far as I remember this is / was not possible. You should use an executeSQL tasks to UPDATE first the rows which are existing in both tables (Using linked servers or OPENDATASOURCE) and then Insert the remaining ones which are not in the destination table yet.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Data Driven Query - Milliseconds

DTS
Transferring data from one table to another using 'Data Driven Query' task
and transformations in VB script with
DTSDestination("MyDate) = DTSSource("MyDate")
both fields are with datatype 'DATETIME'
After the transfer, I noticed - Destination table field (MyDate) is WITHOUT
milliseconds , whereas my Source table field(MyDate) is WITH milliseconds.
Any Thoughts '
Thanks
SOn Wed, 7 Apr 2004 15:07:54 -0500, "MS User" wrote:
>DTS
>Transferring data from one table to another using 'Data Driven Query' task
>and transformations in VB script with
>DTSDestination("MyDate) = DTSSource("MyDate")
>both fields are with datatype 'DATETIME'
>After the transfer, I noticed - Destination table field (MyDate) is WITHOUT
>milliseconds , whereas my Source table field(MyDate) is WITH milliseconds.
The problem is caused by the conversion of the datetime to a Variant in
VBScript. The conversion chops off the milliseconds:
http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q297463&
Discussed in reference to DTS here:
http://groups.google.com/groups?threadm=036f01c3bdc0%2494bbc510%24a101280a%40phx.gbl
( http://tinyurl.com/3f63m )
cheers,
Ross.
--
"There is more to life than simply increasing its speed." - Mahatma Gandhisql

data conversion in during DTS import

Hi,
I have a text file in which one field is a date but in
file it is in '20030818' format and I am tring to import
that file to a table which has a column datetime.
is it possible to convert '20030818' to datetime during
DTS import?
if yes please let me know how ?
Thanks.use
1 .vbscript tranformation(activex)
2 .select convert(datetime, '20030818')
3. dts datetime conversion facility
rohan
>--Original Message--
>Hi,
>I have a text file in which one field is a date but in
>file it is in '20030818' format and I am tring to import
>that file to a table which has a column datetime.
>is it possible to convert '20030818' to datetime during
>DTS import?
>if yes please let me know how ?
>Thanks.
>.
>|||use
1 .vbscript tranformation(activex)
2 .select convert(datetime, '20030818')
3. dts datetime conversion facility
rohan
>--Original Message--
>Hi,
>I have a text file in which one field is a date but in
>file it is in '20030818' format and I am tring to import
>that file to a table which has a column datetime.
>is it possible to convert '20030818' to datetime during
>DTS import?
>if yes please let me know how ?
>Thanks.
>.
>|||use
1 .vbscript tranformation(activex)
2 .select convert(datetime, '20030818')
3. dts datetime conversion facility
rohan
>--Original Message--
>Hi,
>I have a text file in which one field is a date but in
>file it is in '20030818' format and I am tring to import
>that file to a table which has a column datetime.
>is it possible to convert '20030818' to datetime during
>DTS import?
>if yes please let me know how ?
>Thanks.
>.
>

Data checking?

Hey all,
prolly a simple solution, but why isn't the following string working in
my execute sql step within DTS? It produces results, just not the ones
I want... What am I doing wrong?

select x from new_files where x like '%[^0-9]%' and x like '%[^a-z]%'

It's displaying all the records? It should only be displaying those
records that do *not* contain letters or numbers.
Thanks in advance!
-RoyOn 6 Jan 2005 06:13:08 -0800, roy.anderson@.gmail.com wrote:

> Hey all,
> prolly a simple solution, but why isn't the following string working in
> my execute sql step within DTS? It produces results, just not the ones
> I want... What am I doing wrong?
>
> select x from new_files where x like '%[^0-9]%' and x like '%[^a-z]%'
> It's displaying all the records? It should only be displaying those
> records that do *not* contain letters or numbers.
> Thanks in advance!
> -Roy

Your clause is selecting rows where the x column contains at least one
character that is not a digit and also contain at least one character that
is not a letter. If you had a row where x was all letters, all digits, or
maybe all letters plus punctuation but no digits, etc., then it would not
be included.

The clause you want is probably

WHERE NOT (x LIKE '%[0-9a-z]%')

(parenthesis optional)|||Thanks much Ross, after some toying around, the end product that works
is:

WHERE (x LIKE '%[^0-9a-z]%')

I'm unsure why having the "NOT" specified beforehand produces no
results, but it doesn't. I'm assuming it's because sqlserver perceives
the NOT as referring to the wildcards too, ergo, it's only looking for
blank fields.

Thanks much for the help!!!|||On 6 Jan 2005 09:02:41 -0800, Roy wrote:

> Thanks much Ross, after some toying around, the end product that works
> is:
> WHERE (x LIKE '%[^0-9a-z]%')
> I'm unsure why having the "NOT" specified beforehand produces no
> results, but it doesn't. I'm assuming it's because sqlserver perceives
> the NOT as referring to the wildcards too, ergo, it's only looking for
> blank fields.
> Thanks much for the help!!!

It looks to me like your query is requesting those rows that contain at
least one non-letter, non-digit character. I thought you wanted rows that
contained no letters and contained no digits... maybe I'm still confused
... but if you've got what you want, great.

Daft DTS Data Driven task question

Hi, I'm trying to get a Data Driven DTS task to update a table based on a .csv source file. I've got a numeric ID Identity column (unique) to give me a record number, and I'm using the following code on the update query to try and update the relevant record:

UPDATE [DR-TestDB].dbo.[Test - CustAddress]
SET
County = ?
WHERE (ID = ?)

The problem I'm getting is a conversion error from VarChar to Numeric when I run the task. I've tried using a type conversion in the transformation, but that doesn't appear to help.

Anyone know the answer - I'll bet it's simple :)

Cheers,

MenthosHmmm... ok, I seem to have resolved this - just rebuilt the task from scratch and it appears to have worked fine.

Very strange.