• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 592
  • Last Modified:

ssis to out put file to CSV with yyyymmddhhss_

Please does anyone knwo how I can export a file  ussing SSIS package or scripts
with YYYYDDMMHHSS_1000.csv Extension
basically i want and out put of tow files one at 10:00 and the other at 14:00 hours and keep them for 7 days before deleting them
I also want a semiphone or a flag  to say the out put for 1000 has completed and 1400 file shas completed
0
olaayo
Asked:
olaayo
  • 5
  • 5
1 Solution
 
carsRSTCommented:
Where's the data coming from?  Do you have Excel installed where the SSIS script will run?
0
 
olaayoAuthor Commented:
Not I do not have excel installed on the Server
0
 
carsRSTCommented:
OBVIOUSLY MAKE CHANGES AS NEEDED BUT THIS CODE WORKS IN A "SCRIPT TASK"



Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

      ' The execution engine calls this method when the task executes.
      ' To access the object model, use the Dts object. Connections, variables, events,
      ' and logging features are available as static members of the Dts class.
      ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
      '
      ' To open Code and Text Editor Help, press F1.
      ' To open Object Browser, press Ctrl+Alt+J.

      Public Sub Main()
            '

        '
        Dim objFile As System.IO.File
        Dim objWrite As System.IO.StreamWriter


        Dim dateTimeInfo As DateTime = DateTime.Now
        Dim strDate As String = dateTimeInfo.ToString("G").Replace("/", "").Replace(":", "").Replace(" ", "")


        objWrite = objFile.CreateText("C:\temp\" & strDate & "_1000.csv")

        objWrite.WriteLine("...data...")
        objWrite.Close()

        Dts.TaskResult = Dts.Results.Success
    End Sub

End Class
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
olaayoAuthor Commented:
Thanks for your help can I use this script to export to a file?
0
 
carsRSTCommented:
Yep.

If you want to test this as is, just change this line:

objWrite = objFile.CreateText("C:\temp\" & strDate & "_1000.csv")

(it will run as is if you have a "C:\temp\" directory)
0
 
olaayoAuthor Commented:
Hello how do I change the date format to be yyyymmddhhss because waht I a getting is
22042010111613_1000.csv(which iss ddmmyyyyhhss


Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)


        Dim objFile As System.IO.File
        Dim objWrite As System.IO.StreamWriter


        Dim dateTimeInfo As DateTime = DateTime.Now
        Dim strDate As String = dateTimeInfo.ToString("G").Replace("/", "").Replace(":", "").Replace(" ", "")


        objWrite = objFile.CreateText("E:\MIDS\" & strDate & "_1000.csv")

        objWrite.WriteLine("...data...")
        objWrite.Close()


    End Sub


End Class
0
 
carsRSTCommented:
Change this line:
 Dim strDate As String = dateTimeInfo.ToString("G").Replace("/", "").Replace(":", "").Replace(" ", ""

To this line:
Dim strDate As String = dateTimeInfo.ToString("yyyyMMddhhss")
0
 
olaayoAuthor Commented:
Thanks yo for your help
The issue I am having now is the databse is not coming out I am am getting ...data.. in the files and not the rows from the table
 I am obviously not doing something write

Doc2.doc
0
 
carsRSTCommented:
0
 
olaayoAuthor Commented:
Thanks for your help I can to seem to work it out may be cause
The point is I have as sql server 2005 DECLARE @Date datetime
SET @Date = getdate()
begin tran
update Tempdev
Set SENT_DATETIME = @date
where SENT_DATETIME  is Null and Audit = 'A'
or SENT_DATETIME  is Null and Audit = 'U'
or  SENT_DATETIME  is Null and Audit = 'D'
which I have put in an execute SQL task and its is running fine all I want to do is link it to this scripts
Public Overrides Sub CreateNewOutputRows()



        Dim objFile As System.IO.File
        Dim objWrite As System.IO.StreamWriter


        Dim dateTimeInfo As DateTime = DateTime.Now
        Dim strDate As String = dateTimeInfo.ToString("yyyyMMddhhss")
        ' Dim strDate As String = dateTimeInfo.ToString("G").Replace("/", "").Replace(":", "").Replace(" ", "")


        objWrite = objFile.CreateText("E:\MIDS\" & strDate & "_1000.csv")

        objWrite.WriteLine("...data...")
        objWrite.Close()


    End Sub


End Class
and make it save a anout put file on the e drivesupplied from this scripts
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now