Hi,
I am in a huge crisis. I have to export more than 400 code tables into
csvfiles. Each table should be exported in to seperate file. I have to
create a package to execute all these tables into files. All the code
tables are in different layout.
I have two questions
1)where do i write the script?(inside the transformation or just a
script which creates the source and the destination).If I write inside
the transformation what will be the initial source and destination?

2) Do I need an ADO to gothrough these tables?

Here is my non working code:
Function Main()
Dim oPackage As New DTS.Package
Dim oConnection As DTS.Connection
Dim oTask As DTS.Task
Dim oCustomTask As DTS.CustomTask
Dim oStep As DTS.Step

Dim oExecuteTask As DTS.ExecuteSQLTask
Dim oSrcColumn AS dts.columns
Dim oDestColumn AS dts.columns
Dim oPumpTask As DTS.DataPumpTask
Dim oTransForm As DTS.Transformation
Dim oTransProps As DTS.Properties

Dim adoConn As New ADODB.Connection
Dim adoRS As New ADODB.Recordset

Dim sSQL As String
Dim sPAckageName As String
Dim srcColInfo As DTS.Columns
Dim destColInfoas As DTS.Columns
Dim fieldInfo As String
Dim SourceID As String
Dim DestId As String
Dim sServerName As String
Dim dServerName As String
Dim sUserID As String
Dim dUserID As String
Dim sPassword As String
Dim dPassword As String
'Creating an excel object
Dim xlApp
Set xlApp = CreateObject("Excel.Application")

'Setup ADO Connection and recordset
Set adoConn = CreateObject("ADODB.connection")
adoConn.ConnectionString = "Driver={SQL
Server};Server="";Uid="";pwd="";database="""
adoConn.Open adoConn

'SQL statement to retrive table name
sSQL = "select tableName from temptable"
'open the resultset
adoRS.Open sSQL, adoConn, adOpenDynamic, adLockPessimistic
'Create Step
Set oStep = oPackage.Steps.New
With oStep
.Name = "DTSStep_DTSDataPumpTask"
.Description = "Transfer File"
.TaskName = "DTStask_DTSDataPumpTask"
End With
oPackage.Steps.Add oStep
'Create Task
Set oTask = oPackage.Tasks.New("DTSDataPumpTask")
'Column Transformations
adoRS.MoveFirst
Dim n
adoRS.MoveFirst
While Not adoRS.EOF
n = n + 1
adoRS.MoveNext
Wend
adoRS.MoveFirst
For i = 0 To n - 1
fieldInfo = Trim(adoRS.Fields(0).Value)

xlApp.Workbooks.Open (("\\& fieldInfo & (".csv"))
xlApp.Application.Visible = True
Set oPumpTask = oTask.CustomTask
oPumpTask.SourceConnectionID = 1
oPumpTask.DestinationConnectionID = 2
oPumpTask.SourceSQLStatement = "Select * from" & fieldInfo
oPumpTask.DestinationObjectName = xlApp
'Source Transform


Set oTransForm =
oPumpTask.Transformations.New("DTS.DataPumpTransfo rmCopy")
oTransForm.Name = "Transformation" & i + 1

'Source Transform
set oSrcColumn=oTransForm.SourceColumns.New(adoRS.Fiel ds(i).Name,1)
oTransform.SourceColumns.ADD oSrcColumn

'Destination Transform
set oDestColumn=oTransForm.DestinationColumns.New(adoR S.Fields(i).Name,1)
oTransform.DestinationColumns.ADD oSrcColumn

Set oTransProps = oTransForm.TransformServerProperties
Set oTransProp = Nothing

oPumpTask.Transformations.Add oTransForm
Set oTransForm = Nothing
'Add New Task
oPackage.Tasks.Add oTask
'Execute Package
oPackage.Execute
Next


Main = DTSTaskExecResult_Success
End Function

Thanks in Advance,
Any help will be appreciated
laka

This sounds like a job for a BULK Exporter custom task

DTS Bulk Export Task
http://www.sqldts.com/default.aspx?6,102,237,0,1


Also of you want to get into it more yourself look at SQLDMO

If you want a quick and cheerful method then why not look to do something
like

Use <your DB name>
GO
SELECT 'EXEC master..xp_cmdshell "BCP DATABASENAME.dbo.' + TABLE_NAME + '
OUT DIRECTORYFORFILES' + TABLE_NAME + '.txt -SYOURSERVER -T -c -t, -r\n"'
from information_schema.tables where table_type = 'BASE TABLE'


copy and paste the output from this

--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"laka" <lekha_anil (AT) hotmail (DOT) com> wrote

This is the first time I am using SQL server DTS. I don't know
anything about that. As u said I tried to include the Bulk Export
Custom task. It is giving me some errors. Do I need to create a
transformation and include the custom task or just create a package
and include the task.
Help me!!!!!
thanks,
laka

What are the errors you are getting.?

--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"laka" <lekha_anil (AT) hotmail (DOT) com> wrote

Error:Tablename is not in the tables collection.

I took the Bulk export icon from the task and selected the table name
and file name. Evenif it works it exports one file only. I want to
export more than 400 files with same package just by variying the
source and destination programatically.
Thank you for your help,
laka

Hi Allen,
Thanks,
I wrote the script to loop through the tables and it suppose to write
to the spreadsheet. But it is not. Script is working without any
errors, it is not writing to the spread sheet.

my code:
Set adoConn = CreateObject("ADODB.Connection")
adoConn.ConnectionString = "Driver={SQL Server};
Server="";Uid=sa;pwd=tt;database=gi"
adoConn.Open adoConn

sSQL = "select * from temptable"

adoRS1.Open sSQL, adoConn, adOpenDynamic, adLockPessimistic

adoRS1.MoveFirst

While Not adoRS1.EOF
n = n + 1
adoRS1.MoveNext
Wend

adoRS1.MoveFirst
For i = 0 To n - 1
fieldInfo = Trim(adoRS1.Fields(1).Value)
adoRS2.Open "select * from" & fieldInfo, adoConn, adOpenStatic,
adLockPessimistic
strColName = ""
strColCount = 0

For j = 0 To adoRS2.Fields.Count - 1
If strColName = "" Then
strColName = strColName + adoRS2.Fields(j).Name
Else
strColName = strColName + "," + adoRS2.Fields(j).Name
End If
strColCount = adoRS2.Fields.Count
Next
'

Set goPackage = goPackageOld
goPackage.Name = fieldInfo
goPackage.Description = "DTS package description"
goPackage.WriteCompletionStatusToNTEventLog = False
goPackage.FailOnError = False
goPackage.PackagePriorityClass = 2
goPackage.MaxConcurrentSteps = 4
goPackage.LineageOptions = 0
goPackage.UseTransaction = True
goPackage.TransactionIsolationLevel = 4096
goPackage.AutoCommitTransaction = True
goPackage.RepositoryMetadataOptions = 0
goPackage.UseOLEDBServiceComponents = True
goPackage.LogToSQLServer = False
goPackage.LogServerFlags = 0
goPackage.FailPackageOnLogFailure = False
goPackage.ExplicitGlobalVariables = False
goPackage.PackageType = 0



'---------------------------------------------------------------------------
' create package connection information
'---------------------------------------------------------------------------
Dim oConnection As DTS.Connection2

'------------- a new connection defined below.
'For security purposes, the password is never scripted

Set oConnection = goPackage.Connections.New("SQLOLEDB")

oConnection.ConnectionProperties("Persist Security Info") =
True
oConnection.ConnectionProperties("User ID") = "sa"
oConnection.ConnectionProperties("Initial Catalog") = "gi"
oConnection.ConnectionProperties("Data Source") = ""
oConnection.ConnectionProperties("Application Name") = "DTS
Import/Export Wizard"

oConnection.Name = "Connection 1"
oConnection.ID = 1
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = ""
oConnection.UserID = ""
oConnection.ConnectionTimeout = 60
oConnection.Catalog = ""
oConnection.UseTrustedConnection = False
oConnection.UseDSL = False
oConnection.Password = ""

goPackage.Connections.Add oConnection
Set oConnection = Nothing

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
xlBook.SaveAs (("C:\") & fieldInfo & (".csv"))


Set oConnection = goPackage.Connections.New("DTSFlatFile")

oConnection.ConnectionProperties("Data Source") = "C:\" &
fieldInfo & ".csv"
oConnection.ConnectionProperties("Mode") = 3

oConnection.ConnectionProperties("File Format") = 1
oConnection.ConnectionProperties("Column Delimiter") = ","
oConnection.ConnectionProperties("File Type") = 1
oConnection.ConnectionProperties("Skip Rows") = 0
oConnection.ConnectionProperties("Text Qualifier") = """"
oConnection.ConnectionProperties("First Row Column Name") =
True
oConnection.ConnectionProperties("Column Names") = strColName
oConnection.ConnectionProperties("Number of Column") =
strColCount
oConnection.ConnectionProperties("Text Qualifier Col Mask:
0=no, 1=yes, e.g. 0101") = "111"
oConnection.ConnectionProperties("Max characters per delimited
column") = 8000
oConnection.ConnectionProperties("Blob Col Mask: 0=no, 1=yes,
e.g. 0101") = "000"

oConnection.Name = "Connection 2"
oConnection.ID = 2
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "C:\" & fieldInfo & ".csv"
oConnection.ConnectionTimeout = 60
oConnection.UseTrustedConnection = False
oConnection.UseDSL = False
oConnection.Password = ""

goPackage.Connections.Add oConnection
Set oConnection = Nothing

'---------------------------------------------------------------------------
' create package steps information
'---------------------------------------------------------------------------

Dim oStep As DTS.Step2
Dim oPrecConstraint As DTS.PrecedenceConstraint

'------------- a new step defined below

Set oStep = goPackage.Steps.New

oStep.Name = "Copy Data from " & fieldInfo & " to C:\" &
fieldInfo & ".csv Step"
oStep.Description = "Copy Data from " & fieldInfo & " to C:\"
& fieldInfo & ".csv Step"
oStep.ExecutionStatus = 1
oStep.TaskName = "Copied data in table C:\" & fieldInfo &
".csv"
oStep.CommitSuccess = False
oStep.RollbackFailure = False
oStep.ScriptLanguage = "VBScript"
oStep.AddGlobalVariables = True
oStep.RelativePriority = 3
oStep.CloseConnection = False
oStep.ExecuteInMainThread = False
oStep.IsPackageDSORowset = False
oStep.JoinTransactionIfPresent = False
oStep.DisableStep = False
oStep.FailPackageOnError = False

goPackage.Steps.Add oStep
Set oStep = Nothing

'---------------------------------------------------------------------------
' create package tasks information
'---------------------------------------------------------------------------


Call Task_Sub1(goPackage)

'---------------------------------------------------------------------------
' Save or execute package
'---------------------------------------------------------------------------
goPackage.Execute
'tracePackageError goPackage
goPackage.UnInitialize
'to save a package instead of executing it, comment out the executing
package lines above and uncomment the saving package line
Set goPackage = Nothing

Set goPackageOld = Nothing
adoRS1.MoveNext
adoRS2.Close
Next

End Function


''-----------------------------------------------------------------------------
'' error reporting using step.GetExecutionErrorInfo after execution
''-----------------------------------------------------------------------------
'Public Sub tracePackageError(oPackage As DTS.Package)
'Dim ErrorCode As Long
'Dim ErrorSource As String
'Dim ErrorDescription As String
'Dim ErrorHelpFile As String
'Dim ErrorHelpContext As Long
'Dim ErrorIDofInterfaceWithError As String
'Dim i As Integer
'
' For i = 1 To oPackage.Steps.Count
' If oPackage.Steps(i).ExecutionResult =
DTSStepExecResult_Failure Then
' oPackage.Steps(i).GetExecutionErrorInfo
ErrorCode, ErrorSource, ErrorDescription, _
' ErrorHelpFile,
ErrorHelpContext, ErrorIDofInterfaceWithError
' MsgBox oPackage.Steps(i).Name & " failed" &
vbCrLf & ErrorSource & vbCrLf & ErrorDescription
' End If
' Next i
'
'End Sub
'
Public Sub Task_Sub1(ByVal goPackage As Object)

Dim oTask As DTS.Task
Dim oLookup As DTS.Lookup

Dim oCustomTask1 As DTS.DataPumpTask2
Set oTask = goPackage.Tasks.New("DTSDataPumpTask")
oTask.Name = "Copied data in table C:\" & fieldInfo & ".csv"
Set oCustomTask1 = oTask.CustomTask

oCustomTask1.Name = "Copied data in table C:\" & fieldInfo &
".csv"
oCustomTask1.Description = "Copied data in table C:\" &
fieldInfo & ".csv"
oCustomTask1.SourceConnectionID = 1
oCustomTask1.SourceSQLStatement = "select " & strColName & "
from " & fieldInfo
oCustomTask1.DestinationConnectionID = 2
oCustomTask1.DestinationObjectName = "C:\" & fieldInfo &
".csv"
oCustomTask1.ProgressRowCount = 1000
oCustomTask1.MaximumErrorCount = 0
oCustomTask1.FetchBufferSize = 1
oCustomTask1.UseFastLoad = True
oCustomTask1.InsertCommitSize = 0
oCustomTask1.ExceptionFileColumnDelimiter = "|"
oCustomTask1.ExceptionFileRowDelimiter = vbCrLf
oCustomTask1.AllowIdentityInserts = False
oCustomTask1.FirstRow = 0
oCustomTask1.LastRow = 0
oCustomTask1.FastLoadOptions = 2
oCustomTask1.ExceptionFileOptions = 1
oCustomTask1.DataPumpOptions = 0

Call oCustomTask1_Trans_Sub1(oCustomTask1)


goPackage.Tasks.Add oTask
Set oCustomTask1 = Nothing
Set oTask = Nothing

End Sub

Public Sub oCustomTask1_Trans_Sub1(ByVal oCustomTask1 As Object)

Dim oTransformation As DTS.Transformation2
Dim oTransProps As DTS.Properties
Dim oColumn As DTS.Column


Set oTransformation =
oCustomTask1.Transformations.New("DTS.DataPumpTran sformCopy")
oTransformation.Name = "DirectCopyXform"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4
For j = 0 To adoRS2.Fields.Count - 1
Set oColumn =
oTransformation.SourceColumns.New(adoRS2.Fields(j) .Name, 1)
oColumn.Name = adoRS2.Fields(j).Name
oColumn.Ordinal = 1
oColumn.Flags = 24
oColumn.Size = 2
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn =
oTransformation.DestinationColumns.New(adoRS2.Fiel ds(j).Name, 1)
oColumn.Name = adoRS2.Fields(j).Name
oColumn.Ordinal = 1
oColumn.Flags = 24
oColumn.Size = 2
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
Next

Set oTransProps =
oTransformation.TransformServerProperties


Set oTransProps = Nothing

oCustomTask1.Transformations.Add oTransformation
Set oTransformation = Nothing

End Sub
Do u have any idea?
thanks,
laka

So what is it you want to do

Export from 400 tables to csv ? then into Excel ?

The exporting to csv would use the SQLDMO library not DTS (Although as
Darren has done you can write a custom task to do this)
The importing back into Excel may be more tricky as it will expect a table
to exist that can take the data.

Are all these tables the same structure ?

You can do it even if each table is different it is just that you will need
to dynamically build 400 datapump definitions

I do a simliar if not as large thing in a package I have. It reads from a
text file a Query and exports that data from SQL Server to an Excel
spreadsheet.
The query can vary each time.

It is pretty basic but you can have a look at it if you want



--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"laka" <lekha_anil (AT) hotmail (DOT) com> wrote

---
---

hi Allan,
Thanks. I did that using the script. Loopthrough the tables , get the
data and then copied into csv file. That is less complicated than
making datapump for each table.
Thank you so much for your help
laka