SSIS Export different tables to different files in one SSIS Package
-
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
-
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 -
Thanks man,
learn something new today 🙂
i thought i can do something like that .
-
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 -
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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply