SSIS Export different tables to different files in one SSIS Package

  • rayan98

    Mr or Mrs. 500

    Points: 577

    Hi All,

    thanks for looking at my question .

    at the moment, i need to do something at work which specified tables (100+) from a database to CSV Files :

    that's my rough idea (other than creating 100+ data flow tasks)

    i was thinking to use the for each loop task(s) to loop through all the table i need to export :

    here are the example sql code i am trying to use

    create table listoftable

    (

    id int identity(1,1) primary key,

    ObjectName varchar(255),

    SQLStatment varchar(512),

    DestinationFileName varchar(512)

    )

    go

    insert into listoftable

    select 'DimDate','select * from DimDate','C:\DimDate.txt'

    union all

    select 'DimProperty','select * from DimProperty','C:\DimProperty.txt'

    go

    Select * from listoftable -- run in my sql statment task

    something similar to this structure

    but i am getting the error complaining about the meta data :

    [OLE DB Source [45]] Warning: The external columns for OLE DB Source are out of synchronization with the data source columns. The column "PropertyKey" needs to be added to the external columns.

    The column "PropertyID" needs to be added to the external columns.

    The column "PropertyName" needs to be added to the external columns.

    The column "LocationKey" needs to be added to the external columns.

    The column "PropertyTypeKeyID" needs to be added to the external columns.

    The OLE DB Source.Outputs[OLE DB Source Output].ExternalColumns[FiscalSemester] needs to be removed from the external columns.

    The OLE DB Source.Outputs[OLE DB Source Output].ExternalColumns[FiscalYear] needs to be removed from the external columns.

    The OLE DB Source.Outputs[OLE DB Source Output].ExternalColumns[FiscalQuarter] needs to be removed from the external columns.

    The OLE DB Source.Outputs[OLE DB Source Output].ExternalColumns[CalendarSemester] needs to be removed from the external columns.

    The OLE DB Source.Outputs[OLE DB Source Output].ExternalColumns[CalendarYear] needs to be removed from the external columns.

    The OLE DB Source.Outputs[OLE DB Source Output].ExternalColumns[CalendarQuarter] needs to be removed from the external columns.

    The OLE DB Source.Outputs[OLE DB Source Output].ExternalColumns[MonthNumberOfYear] needs to be removed from the external columns.

    The OLE DB Source.Outputs[OLE DB Source Output].ExternalColumns[FrenchMonthName] needs to be removed from the external columns.

    The OLE DB Source.Outputs[OLE DB Source Output].ExternalColumns[SpanishMonthName] needs to be removed from the external columns.

    The OLE DB Source.Outputs[OLE DB Source Output].ExternalColumns[EnglishMonthName] needs to be removed from the external columns.

    The OLE DB Source.Outputs[OLE DB Source Output].ExternalColumns[WeekNumberOfYear] needs to be removed from the external columns.

    The OLE DB Source.Outputs[OLE DB Source Output].ExternalColumns[DayNumberOfYear] needs to be removed from the external columns.

    The OLE DB Source.Outputs[OLE DB Source Output].ExternalColumns[DayNumberOfMonth] needs to be removed from the external columns.

    The OLE DB Source.Outputs[OLE DB Source Output].ExternalColumns[FrenchDayNameOfWeek] needs to be removed from the external columns.

    The OLE DB Source.Outputs[OLE DB Source Output].ExternalColumns[SpanishDayNameOfWeek] needs to be removed from the external columns.

    The OLE DB Source.Outputs[OLE DB Source Output].ExternalColumns[EnglishDayNameOfWeek] needs to be removed from the external columns.

    The OLE DB Source.Outputs[OLE DB Source Output].ExternalColumns[DayNumberOfWeek] needs to be removed from the external columns.

    The OLE DB Source.Outputs[OLE DB Source Output].ExternalColumns[FullDateAlternateKey] needs to be removed from the external columns.

    The OLE DB Source.Outputs[OLE DB Source Output].ExternalColumns[DateKey] needs to be removed from the external columns.

    [SSIS.Pipeline] Error: "OLE DB Source" failed validation and returned validation status "VS_NEEDSNEWMETADATA".

    I am wondering is what am i doing is possible or somewhere i can force it to refresh the flat file destination to make it dynamic ?

    any suggestion or pointers will be great .

    many thanks

    Ray

  • Koen Verbeeck

    SSC Guru

    Points: 258955

    You cannot change the metadata of a data flow dynamically.

    Each different table structure needs a different data flow.

    Maybe you need to take another approach. You could try bcp to dump the tables into flat files.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • rayan98

    Mr or Mrs. 500

    Points: 577

    Thanks man,

    learn something new today 🙂

    i thought i can do something like that .

  • Koen Verbeeck

    SSC Guru

    Points: 258955

    Not with SSIS out of the box.

    There are some 3rd party components that can do this though.

    Alternatives are programmatically creating SSIS packages, using .NET or maybe BIML.

    (if you know a bit of BIML basics, this would be pretty basic)

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • liteswitch

    SSCommitted

    Points: 1764

    Either that or cheat with your SELECT statement and output.

    Let's say you want to output each table to a CSV file. Rather than doing

    Select

    Col1,

    Col2

    From

    dbo.SomeTable

    Try building up all columns into a single array, like

    Select

    Col1 + ',' + Col2 + ',' + Col3

    From

    dbo.someTable

    This will mean the SSIS package will read it as a single column ouptut, i.e. treating it as a single VARCHAR(MAX) datatype almost. You could then do the same on the destination CSV metadata, specifying a single column. This would then work in a loop through a number of tables.

    However, the performance may absolutely tank on this because of the string manipulation, but it might be faster than hand writing all those data flow tasks..

Viewing 5 posts - 1 through 5 (of 5 total)

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