Hi,

I have a problem because I'm new in DTS. I want to export table to many txt
files
I have a two tables, Employee and Location joined by Emp_ID, I need to set
up a DTS package where I can export to different text files
ext:
table 1
Empid EmpName
1 John
1 Richard
2 Peter

Table 2
Empid Location
1 USA
2 ENGLAND

I want John & Richard into USA.txt and Peter into ENDLAND.txt
How Can I do that with DTS Wizard?or Script?

If you simply want to export two tables to two text files can you not
set up two data pump tasks to do that ir is there more to this?

Allan


"Andry Cahyadi" <Andry Cahyadi (AT) discussions (DOT) microsoft.com> wrote in
message news:1058778A-DE9B-444E-8698-BAD2743E153D (AT) microsoft (DOT) com:

that's just example, i want to make many txt files, not 2 txt files.from 1 or
2 table into many txt files.

Use an ActiveX Script...
Here is an example...
hth,
bob mcclellan...

'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************


Function Main()


Dim sqlText
sqlText = "select " _
& "a.[addr#], " _
& "a.[LastName], " _
& "u.[class], " _
& "qd.OnRentDate, " _
& "a.lastname cust, " _
& "qd.rentalperiod, " _
& "JobsiteName, " _
& "qj.City, " _
& "qj.State, " _
& "qj.Zip, " _
& "u.[unit#], " _
& "u.[description], " _
& "make, model " _
& "from qrcDetail qd " _
& "inner join qrcHeader qh on " _
& "(qd.division = qh.division and qd.[qrc#] = qh.[qrc#]) " _
& "inner join addresses a on " _
& "qh.[addr#] = a.[addr#] " _
& "inner join units u on " _
& "qd.[unit#] = u.[unit#] " _
& "left join qrcJobSite qj on " _
& "qh.jobSiteSiteCode = qj.jobsitecode " _
& "where " _
& "qd.active = 'Y' and " _
& "qh.active = 'Y' and " _
& "qh.status = 'C' and " _
& "Lastname = 'MARTIN INC' " _
& "order by OnRentDate"


dim con
dim rs

set con = createobject("ADODB.Connection")
set rs = createobject("ADODB.recordset")

con.open = "Provider = SQLOLEDB.1; data source = (local); initial catalog
= EIS; user id = 'BobMcC'; password = '******';"
rs.open sqltext, con

'Initialize file system object and file.
Dim fso
Dim a

'Create the text file.
Set fso = CreateObject("Scripting.FileSystemObject")
Set a =
fso.CreateTextFile("D:\Common\Mailers\testMailer\W rite.To.TxtFile.txt")

'Write a line to the text file.
a.WriteLine("Cust Name | JobSiteName | OnRentDate| Unit
do until rs.eof
a.WriteLine(rtrim(rs.fields("LastName")) & " | " &
rs.fields("JobSiteName") & " | " & rs.fields("OnRentDate") & " | " &
rs.fields("Unit#") & " | " & rs.fields("Make") & " | " & rs.fields("Model")&
" | " & rs.fields("Description"))
rs.movenext
loop

a.Close
Main = DTSTaskExecResult_Success

End Function



"Andry Cahyadi" <Andry Cahyadi (AT) discussions (DOT) microsoft.com> wrote

So what are you saying?

You want to take 1 table and produce n text files of the same data?

You could do that using a loop and change the name of the file on each
iteration.

Let us know if I have the requirement correct. If not can you give us
some more details?


Allan




"Andry Cahyadi" <AndryCahyadi (AT) discussions (DOT) microsoft.com> wrote in
message news:6705FFCC-E6FC-4C91-9071-2749776DDA43 (AT) microsoft (DOT) com:

after we run activeX script we got this error :

Error Code: 0
Error Source=Microsoft OLE DB Provider for SQL Server
Error Description: [DBNETLIB][ConnectionOpen(Connect()).]SQL Server Does not
exist or access denied.

Error on Line 26
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server doest not exist or access
denied

my script is

'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************

Function Main()

Dim sqlText
sqlText = "select " _
& "qd.EmpId, " _
& "qd.EmpName, " _
& "qh.Orgname" _
& "from employee qd " _
& "inner join organization qh on " _
& "qd.emporg = qh.orgcode" _
& "where " _
& "qd.empdateend is null" _
& "order by EmpId"


'Initialize file system object and file.
Dim fso
Dim a

set con = createobject("ADODB.Connection")
set rs = createobject("ADODB.recordset")

con.open = "Provider = SQLOLEDB.1; data source = Testing; initial catalog =
EIS; user id = 'Sa'; password = 'proint';"
rs.open sqltext, con

'Create the text file.
Set fso = CreateObject("Scripting.FileSystemObject")
Set a = fso.CreateTextFile("C:\Documents and Settings\andry\My
Documents\Write.To.TxtFile.txt")

'Write a line to the text file.
a.WriteLine("EmpId | EmpName | OrgName")
a.WriteLine("")

do until rs.eof
a.WriteLine(rtrim(rs.fields("EmpId")) & " | " & rs.fields("EmpName") & " | "
& rs.fields("OrgName"))
rs.movenext
loop

a.Close

Main = DTSTaskExecResult_Success
End Function



"John 3:16" wrote:

This is my Table

EmpId EmpName OrgName
-----------------------------------------------------------------------------------------
D.50.96.0013 I Putu Eka Arsana Bengkel & Kendaraan BI
D.12.03.0085 Agus Chotib Yahya Bengkel & Kendaraan BN
D.12.04.0004 L.Kristiyanto Eka Yanuar Bengkel & Kendaraan BN
D.10.02.0020 Jumaedi Bengkel & Kendaraan DK
D.10.04.0025 Herdi Bengkel & Kendaraan DK
D.10.97.0002 Rubiyatun Bengkel & Kendaraan DK
D.10.98.0030 Antonius Lei Boro Bengkel & Kendaraan DK
D.10.99.0004 Nasokhi Bengkel & Kendaraan DK
D.20.01.0063 Aam Nurakhman Bengkel & Kendaraan JB
D.20.96.0012 Cucun Rukmana Bengkel & Kendaraan JB
D.40.02.0302 Heru Purnawan Bengkel & Kendaraan JI
D.40.97.0029 Tontowi Jauhari Bengkel & Kendaraan JI
D.30.02.0114 Amin Sustiyo Mulyo Bengkel & Kendaraan JT
D.30.91.0003 Agus Supriyanto Bengkel & Kendaraan JT
D.30.99.0007 Much. Asrul Bahrudin Bengkel & Kendaraan JT
D.11.03.0039 Samino Bengkel & Kendaraan RD
D.11.03.0040 Utari Bengkel & Kendaraan RD
D.11.97.0004 Nana Suryana Bengkel & Kendaraan RD
D.90.70.0001 Hamid Djojonegoro BOARD OF COMMISSIONER
D.90.70.0002 Husain Djojonegoro BOARD OF COMMISSIONER
D.90.70.0003 Pudjiono Djojonegoro BOARD OF COMMISSIONER
D.20.05.0040 Syaiful Bakhri Sanusi Business BD
D.11.05.0003 Lim Robbet Business D190
D.90.02.0014 Hasan Lim Business D190
D.91.04.0029 Derrick Surya Saputra Business D190
D.20.02.0220 Agus Setianto Business PSL
D.50.04.0071 Putu Supertama Checker BI
D.50.91.0001 Tan Wie Piauw Checker BI
D.93.04.0007 I Nyoman Muliartha Checker BI
D.10.80.0001 Bun Kim Nyan Checker BN
-------
-------
-------
-------

I want to create textfiles according to ORGNAME from that table.The Result i
want to is :
1. txtfile name : Bengkel&Kendraan.txt
Details :
D.50.96.0013 I Putu Eka Arsana
D.12.03.0085 Agus Chotib Yahya
D.12.04.0004 L.Kristiyanto Eka Yanuar
D.10.02.0020 Jumaedi
D.10.04.0025 Herdi
D.10.97.0002 Rubiyatun
D.10.98.0030 Antonius Lei Boro
D.10.99.0004 Nasokhi
D.20.01.0063 Aam Nurakhman
D.20.96.0012 Cucun Rukmana
D.40.02.0302 Heru Purnawan
D.40.97.0029 Tontowi Jauhari
D.30.02.0114 Amin Sustiyo Mulyo
D.30.91.0003 Agus Supriyanto
D.30.99.0007 Much. Asrul Bahrudin
D.11.03.0039 Samino
D.11.03.0040 Utari
D.11.97.0004 Nana Suryana

2.txt filename : BOARDOFCOM.txt
details :
D.90.70.0001 Hamid Djojonegoro
D.90.70.0002 Husain Djojonegoro
D.90.70.0003 Pudjiono Djojonegoro

3.txt filename: Busines.txt
details :
D.20.05.0040 Syaiful Bakhri Sanusi
D.11.05.0003 Lim Robbet
D.90.02.0014 Hasan Lim
D.91.04.0029 Derrick Surya Saputra
D.20.02.0220 Agus Setianto

4. txt filename : Checker.txt
details :
D.50.04.0071 Putu Supertama
D.50.91.0001 Tan Wie Piauw
D.93.04.0007 I Nyoman Muliartha
D.10.80.0001 Bun Kim Nyan

5. etc...

i want to create that txtfiles automatic create everyday inf my folder,
because the table always update everyday too. Can u help me?





"Allan Mitchell" wrote:

Ahhhh

This is certainly obtainable yes.

Right.


Grab the distinct list of Orgnames in your data. Assign to a GV rowset

How to loop through a global variable Rowset
(http://www.sqldts.com/default.aspx?298)

You will loop over this

On each iteration of the loop you need to fire a DataPump task .You now
need on each iteration of the loop to change the SourceSQLStatement of
the DataPump task using the value stored in the rowset


Global Variables and SQL statements in DTS
(http://www.sqldts.com/default.aspx?205)


You also need to change the name of the connection that pertains to the
destination text file on each iteration


How can I change the filename for a text file connection?
(http://www.sqldts.com/default.aspx?200)



Once you have all this together you should have what you want.

Allan




"Andry Cahyadi" <AndryCahyadi (AT) discussions (DOT) microsoft.com> wrote in
message news:48CDC379-F94F-446A-8CC3-457440AD5143 (AT) microsoft (DOT) com:

Thanks for your advice
when i run this script the error show :

Error Source : Microsoft Data Transformation Services (DTS) Package

Error Description :Error Code :0
Error Source=Microsoft VBScript runtime error
Error Description: Object required:'DTSGlobalVariabels(...).Value'

Error on line 9

the script i run is

'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************

' BuildMsgBoxString - Task Script
Option Explicit

Function Main()

Dim oRS
Set oRS = DTSGlobalVariables("RSTables").Value

' Get row value and add it to the MsgBoxString global variable
DTSGlobalVariables("MsgBoxString").Value = _
DTSGlobalVariables("MsgBoxString").Value & _
oRS.Fields(0).Value & vbCrLf

' Move to the next row in preparation for loop iteration
oRS.MoveNext

Set oRS = Nothing

Main = DTSTaskExecResult_Success
End Function


"Allan Mitchell" wrote:

Was the error exactly as it appears?

If yes then this "DTSGlobalVariabels" gives it away, it should be
"DTSGlobalVariables"

Allan



"Andry Cahyadi" <AndryCahyadi (AT) discussions (DOT) microsoft.com> wrote in
message news:CF99F3DE-D870-4C96-9752-44EAFFACB730 (AT) microsoft (DOT) com: