I got a OLE DB source pointing to 1 table
and 1 flat file destination.
currently this is how i export data from 1 table to 1 flat file.
To make things easier, I was wondering whether i can have only 1 OLE DB source pointing to few tables pointing to few file destinations so I dun need to create 1 SIS project for each table data exporting.
anyone can help me
is this possible in SSIS?
Juan Manuel CR
ok Jamie, lets look at it the other manner
I got a table on database server -> export to text file -> import to my local database....
I m doing this task several times...
can this be run consecutively in a data flow diagram i tried but its not working.....cos of concurrency issues i guess.
can some expert enlighten me
Anshim
JuanSa
lkurts
no tats not what i want....
I trying to backup many tables into many text files using 1 SSIS package project. Is that possible
trying to reduce the no. of SIS packages file i need to maintain.
indra bayu
You can direct rows to 1 of multiple destinations based on characteristics of the row. This is done usnig the Conditional Split transform. You should look into that and see whether it will do what you require.
I don't know why you used the word "loop". There is no notion of looping in a data-flow.
-Jamie
Cromwell
Unfortunately not because the metadata i.e. the columns involved in the transform need to be the same. So you can't have 1 data flow in a loop that is reconfigured for different tables and destinations.
If the source is SQL you could use BCP to produce the flat files but this then really isn't SSIS. Although you could run the bcp from within SSIS.
Merzhin
i was wondering if there can be a conditional loop in between the OLE DB source and the flat file.....
like if the table name is A then go to File destination A
if table name is B then go to File destination B etc....
hope someone understands what i m saying.
Erratis
Ah ok. You can't do this, as Simon explained earlier!
-Jamie
Jean Fuertes
and 1 flat file destination.
currently this is how i export data from 1 table to 1 flat file.
To make things easier, I was wondering whether i can have only 1 OLE DB source pointing to few tables pointing to few file destinations so I dun need to create 1 SIS project for each table data exporting.
anyone can help me by posting a screenshot of how this can be done in SSIS....the data flows diagram i m not very sure...cos i just started using SSIS in SQL Server 2005.
floatping
Why not have 1 package containing many data-flows
-Jamie
Beast Forever
If you want to execute things in a defined order then put everything in seperate data-flows and make sure they execute in that defined order using precedence constaints. There is no need to have more than 1 package.
The file path can be made dynamic through the use of expressions. For example, put an expression on the connection string of the flat file connection manager.
Why is it a pain So far you haven't got a requirement that cannot be achieved.
Is it SSIS that is a pain or the fact that you're still learning how to use it Its a hugely powerful tool but because of that there is a learning curve - I am confident you'll like it when you know how to fully leverage it. Like any technology it takes time to learn it properly.
-Jamie
Jaffi
great, my boss says he wants the individual packages which i just make to be used in
creating 1 entire database......like program them in sequence so that the data gets into
just nicely into tables which has foreign keys constraints....
i just beginning to figure out SSIS....how do i configure the file path for the data in each of these packages and how do i link these packages together......
SSIS is really a pain ...arghhhhhhh
BorbaTheGeek
Brohans,
In this scenario its really hard to make a recommendation. These are your options where you have N tables that you have to move data from:
1) Have 1 data-flow that contains N source adapters going to N destination adapters
2) Have N data-flows, 1 for each table. Run them all in the same package
3) Have N packages
Its generally accepted that option #1 will be quicker when N is fairly small (e.g. 4 or 5 tables. I wouldn't like to speculate as to what will be quicker when (e.g.) N>25, I would guess at option #2 but that's only a guess. Option #3 probably isn't a goer. The amount of hardware will come into play here whatever you do. Perhaps this will help: http://blogs.conchango.com/jamiethomson/archive/2005/10/02/2227.aspx
To be honest, the only person that can answer this is yourself. Test and measure,Test and measure, Test and measure...
And let us know how it goes cos this could be really interesting.
-Jamie
NetQuery
You can run them all in the same data-flow (in which case there will be as many source and destination adapters as there are tables you are moving data from) or concurrently in seperate data-flows.
-Jamie