Results 1 to 7 of 7

Thread: Sql Dts

  1. #1
    Join Date
    Dec 2003

    Question Unanswered: Sql Dts

    Hello E'body

    I have to automate the process of transferring the data of some tables, strucuture of those table and some storedprocedures in the server to textfiles.
    1) how to transfer the data of 30 tables(just a number) in the server to 30 different text files at one shot , is it possible??
    2) How can I automate to generate a script for a specified table (I know how to generate it using the enterprise manager) ?
    3) How can I transfer the contents of 30 SP's to 30 different text files?

    Can I use a DTS package to do the above??
    Using a SQLDMO object with VB as frontend comes as a second option. Please advise on that too.

    Please Help. Its urgent.


  2. #2
    Join Date
    Apr 2003
    Washington DC area

    Sql Dts

    Why use a text file. Let DTS do it for you. Just go thru the setup processes. You can clean up the data as it is processed in.

  3. #3
    Join Date
    May 2007

    I need to transfer CSV files to SQL. I want to use DTS code in VBA. Please help. I have written the code but I am having a few problems. Anyone want to have a look?

  4. #4
    Join Date
    Apr 2003
    Washington DC area

    Cool Dts

    I'll be your hucleberry, What do you got?

  5. #5
    Join Date
    Jan 2007
    There are several solutions from simple calling bcp, osql etc. from your code or using SQLDMO, Integration/Transformation Services and other COM-based technologies.
    May be just enough that will do:

  6. #6
    Join Date
    Jan 2007
    Provided Answers: 12
    DTS transfer to text files is really simple - you simply use the "text file (destination)" in your DTS package designer - repeat as many times as needed!

    If however you want to use VBA then you cna use the Docmd.TransferText method to export tabels to text files

    Home | Blog

  7. #7
    Join Date
    May 2007

    Problems with csv to SQL


    I have managed to get the data to loop through the fields i want to upload and loop through the files I want to upload.

    However I have a few issues:
    1. There may be some duplicate columns in the source file. it crashes because of these and does not upload anything.
    2. After uploading one file there may be a field in the next file which is not there. I want the SQL table to have nulls here. However it crashes because it cannot find the source column.
    3. The files I am uploading are all ".rpt" files. They are comma delimited. I need the use the "First row has column names" function. But in my files the first row is blank and then it has the headings. If I open it and resave it then the blank line disappears. I have too many files to upload so cannot do this for all of them.
    4. I want to populate a field with the value from a cell in the excel workbook (I am running this in VBA). So instead of the "hello" below I want to read a cell in excel.

    Public Sub oCustomTask1_Trans_Sub3(ByVal oCustomTask1 As Object)

    Dim oTransformation As DTS.Transformation2
    Dim oTransProps As DTS.Properties
    Dim oColumn As DTS.Column
    Set oTransformation = oCustomTask1.Transformations.New("DTSPump.DataPump TransformScript")
    oTransformation.Name = "DTSTransformation__2"
    oTransformation.TransformFlags = 63
    oTransformation.ForceSourceBlobsBuffered = 0
    oTransformation.ForceBlobsInMemory = False
    oTransformation.InMemoryBlobSize = 1048576
    oTransformation.TransformPhases = 4

    Set oColumn = oTransformation.DestinationColumns.New("Product", 1)
    oColumn.Name = "Product"
    oColumn.Ordinal = 1
    oColumn.Flags = 120
    oColumn.Size = 20
    oColumn.DataType = 129
    oColumn.Precision = 0
    oColumn.NumericScale = 0
    oColumn.Nullable = True

    oTransformation.DestinationColumns.Add oColumn
    Set oColumn = Nothing

    Set oTransProps = oTransformation.TransformServerProperties

    oTransProps("Text") = "'************************************************ **********************" & vbCrLf
    oTransProps("Text") = oTransProps("Text") & "' Visual Basic Transformation Script" & vbCrLf
    oTransProps("Text") = oTransProps("Text") & "'************************************************ ************************" & vbCrLf
    oTransProps("Text") = oTransProps("Text") & "' Copy each source column to the destination column" & vbCrLf
    oTransProps("Text") = oTransProps("Text") & "Function Main()" & vbCrLf
    oTransProps("Text") = oTransProps("Text") & " DTSDestination(""Product"") = ""hello""" & vbCrLf
    oTransProps("Text") = oTransProps("Text") & " Main = DTSTransformStat_OK" & vbCrLf
    oTransProps("Text") = oTransProps("Text") & "End Function"
    oTransProps("Language") = "VBScript"
    oTransProps("FunctionEntry") = "Main"

    Set oTransProps = Nothing

    oCustomTask1.Transformations.Add oTransformation
    Set oTransformation = Nothing

    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts