Post reply

Import data into .csv file

  • November 23, 2010 at 5:35 am

    #226920

    Hi All ,

    I am creating a package for migrating data from one database to another database with different schema , based on some rules.

    For migrating data in one column , i have a case statement .Now when none of the case satisfies i have to insert value 0 and also create a report for this . Like this will be the wrong data which is inserted but needs to be reported.

    I am using execute sql task which inserts data in table.

    Now i have to find the value in that particular column inserted as 0 and insert that whole row in a .csv file.

    So after execute sql task , can i use any other task to find these rows and insert in .csv file.

    Hope you are getting my point ...

    Thanks in advance

  • November 23, 2010 at 5:45 am

    #1254369

    there are a number of ways to get data out to a CSV...

    In SSIS you can:

    1. In the data flow, delete the existing flat file destination and connection manager, if any (for example, if you are using the sample file as a starting point)

    2. Create a new flat file destination (drag it from the toolbox, or double-click it in the toolbox)

    3. Edit the flat file destination just created (double click it)

    4. Click "New..."

    5. Make sure that "Delimited" is selected and click OK

    6. Enter a connection manager name and filename. It doesn't matter what you call the file or where you put it--the name will be overridden at run time.

    7. Verify the encoding and format options

    8. Click OK to create the connection manager

    9. Click OK to finish editing the destination

    Or as a SQL statement:

    Exec xp_cmdshell 'bcp "SELECT ''ConfigInfoID'', ''Name'', ''Value''" queryout "C:\test.csv" -S -T -c'

    Exec xp_cmdshell 'bcp "SELECT * FROM [ConfigurationInfo]" queryout "C:\test.csv" -S -T -c'

  • November 23, 2010 at 6:04 am

    #1254380

    Assuming you're using a dataflow to get data from A to B, you can use a Conditional Split to redirect your '0' data out to a CSV file while your other data continues unimpeded.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • December 6, 2010 at 1:07 am

    #1258910

    Thanks a lot.

    My problem is solved 🙂

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply