Just as new as everybody else
I'm a bit confused when to choose SSIS and when to choose T-SQL. I've worked with T-SQL for about 6years now. I have taken SSIS Hands-on-labs, read alot about SSIS, tutorials etc.
The cause for my question is actually very simple.
I've build an advanced (imho) extracting system in T-SQL. We have an urgent need for collecting data from alot af different servers spread out in our country. On each server there is located a lot of different named tables, but all with the same schema.
The collecting are managed by a central jobserver which knows only the names( and ofcause the servers are linked), but not where the tables could be or not could be. Therefore the procedure checks each and every server by asking to different systables. And if the table exists it's eglible for collecting.
The centralserver now builds a list with all valid tables and theres locations and starts to collect with a predefined priority.
The priority is only predefined in that understanding that a systemuser can change it along the way, thereby changing the running collecting-procedure.
Now comes one of my questions... Is it possible to build a similar scenario in SSIS
And another:
I've created (msdn-tutorial) SSIS-packages that can handle dynamic input. But can the input also be a tablename As you've probably guessed the T-SQL procedures running today, uses a lot of dynamic statements
SSIS instead of raw T-SQL
Amde
You can easily define a for each loop to iterate servers and within that another for each loop to loop through the tables you want to process and if the tables exist using a data flow move the data around.
It would reduce the need for linked servers, hardcoded security (I imagine) and the need for dynamic SQL.
Sean K. Campbell
Well I'll get started in a couple of days (actually I cant wait
Thank you both for your answers, now I can start with good confidence.
SergeDC
Have you tried basing a data source on a variable In my situation I'm loading from multiple source systems but they all have the same schema and matching table names, but I need to vary the where clauses based on a variety of factors between each source system. I set up variables for all of my tasks, created a script task to set the variable values, and then based the subsequent tasks datasources on the variables.
-Steve
DetBerMer
You can dynamically set the properties for your source and destination components (yes, input can be a table name), but the schemas must be identical (as you've indicated they are). A data flow's pipeline is inflexible at run-time, so you couldn't use a single Data Flow to collect multiple schemas.
I see two tasks:
1) A loop that goes through your list of servers and executes a Data Flow for each one. The Data Flow would connect to each remote server, pull the list of tables from your central server, do a Lookup into the systables of the remote server (ignoring lookup failures), then conditional branch the table names that were found on the remote server into a staging table.
2) Another loop to go through your staging table and execute another Data Flow to reconnect to valid remote servers and actually move the data from the tables you discovered.