I have a simple ssis package that grabs data from sql server 2005 then puts that into a flat file and then sends it to my webserver. This package currently just pulls data from one db.
I would like it to do this for 10 dbs now therefore creating 10 flat files. Is there an easy way to do this or am I better off creating 10 ssis packages.
I guess the immediate question is, how conversant are you with SSIS?
If you're pretty good, you can use a ForEach Loop Container, iterate through a collection of variables and change the source server (or database name) with each iteration. Then inside the container, create a single DataFlow. Inside the DataFlow, create a single OLE DB Source and use the variable from the ForEach Loop to specify the server/database for this iteration, and construct the output file name for each one (if you actually need 10 output files). If you want to get fancy, you can even put the server/database pairs in a config file so you can easily move from prod to test.
If you have no idea what I'm talking about, try this:
If all the databases are on the same server, you can create a single Connection Manager for the server. Then, create a single data flow container. Inside the Data Flow, create separate OLEDB Sources for each database you want. Then use the OLE DB Source Editor to specify data access mode "SQL Command". Write a SQL statement to point to the proper database.schema.table (that way you can get away with the non-database-specific Connection Manager). Now copy the OLE DB Source, and paste it a bunch of times (each should get it's own "clone name"). Go into each source and change the database name to point to the various different databases. Now create a Union All box and drag the green arrow from each OLE Db source to the Union All box. Now you can drag the green arrow from the Union All box to Flat File Destination. Voila! You have one file that contains all your data, ready to ship to your web server.
If you still don't know what I'm talking about, then you can just create a separate package for each one.
I hope this rather long-winded set of suggestions was helpful. Good luck!
Sorry I was out of town but thanks it worked like a charm!
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.