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?
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.
|||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.
|||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
|||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.
|||Why not have 1 package containing many data-flows?
-Jamie
|||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 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.
any guides to SSIS will also be appreciated. Thanks!
i tried using 1 ole db source + file A
and 2 ole db source + file B separated in the Diagram but when i execute it , it doesnt run :(
Ah ok. You can't do this, as Simon explained earlier!
-Jamie
|||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?
|||
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
|||Be aware that even if you have 20 sources and 20 destinations they may not all run at once. SSIS has a process that determines the threads to use and the amount of concurrency. If running on a 1 proc machine you will get very different results that running on a 4 way machine.|||hmm so simon...what do u recommend?|||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
|||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
|||brohans wrote:
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....
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.
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......
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.
SSIS is really a pain ...arghhhhhhh
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
No comments:
Post a Comment