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
olaayoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.