Link to home
Start Free TrialLog in
Avatar of olaayo
olaayo

asked on

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
Avatar of carsRST
carsRST
Flag of United States of America image
Where's the data coming from?  Do you have Excel installed where the SSIS script will run?
Avatar of olaayo
olaayo

ASKER

Not I do not have excel installed on the Server
ASKER CERTIFIED SOLUTION
Avatar of carsRST
carsRST
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of olaayo
olaayo

ASKER

Thanks for your help can I use this script to export to a file?
Avatar of carsRST
carsRST
Flag of United States of America image
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)
Avatar of olaayo
olaayo

ASKER

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
Avatar of carsRST
carsRST
Flag of United States of America image
Change this line:
 Dim strDate As String = dateTimeInfo.ToString("G").Replace("/", "").Replace(":", "").Replace(" ", ""

To this line:
Dim strDate As String = dateTimeInfo.ToString("yyyyMMddhhss")
Avatar of olaayo
olaayo

ASKER

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
Avatar of olaayo
olaayo

ASKER

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