July 16, 2009 at 1:21 pm
Going through a SSIS tutorial at http://www.accelebrate.com/sql_training/ssis_tutorial.htm
Do a search for the string "Figure 16-6" with no quotes. Right above that line, you will see step 10:
"10.Select the File System task in the designer. Use the Properties Window to set properties of the File System Task. Set the Source property to DepartmentList. "
Thing is, they say "Datalist" because this tutorial is based on some sample files they have you download, and the Datalist source is actually a flat file. I am using the tutorial to do an actual task, copying data from selected tables on one server to another database on another server. I could do this in easier ways, but I have reason to believe I will do this more than once, so I am using SSIS.
Anyway, here's my question: when I try to do step 10 above, the Source property is a free-text field. Not a dropdown, like I'd hoped. So what do I type in there? The name of my connection (which is in the form servername.databasename)?
I have the same question about my Destination.
July 16, 2009 at 1:36 pm
The source would be your connection. Just to be sure: It sounds like you're using a file system object where you should be using a database connection. The type of source you want is OLEDB. Those usually have a drop-down of the connections you have defined, plus an option to create a new connection.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 16, 2009 at 2:13 pm
I am using OLE, but the tutorial is using a flat file. So I get it now, and have made the correct connections.
However, I have an unrelated follow-up question. When I get down the tutorial a bit, it shows how to select a single table. However, I want to select multiple tables. Nowhere near all the tables, though, so I cannot select the entire database. Not sure how to do this, and make sure they get to their intended destination on the new server.
July 16, 2009 at 2:56 pm
Something that might save you a bunch of work, and clarify a few things at the same time: Open up the Import wizard in Management Studio, set up the import you want to do, and save a copy of the import to the file system. That'll create an SSIS package for you.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 17, 2009 at 7:30 am
OK, I can do that. Two questions, though:
How can I run this saved package again if needed (and it almsot certainly will be)
It gives me a choice of saving to file system or SQL Server (whatever that means). What's the difference?
July 17, 2009 at 7:37 am
If you save it, it's an SSIS package, just like any other. You'd run it the way you'd run any SSIS package.
If you save it to SQL Server, you can schedule it in SQL Agent. I save them to the file system, because then you can edit them and so on. You can always save to SQL Server later, from the file. Just open it in Dev Studio and use the Save a Copy option.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 17, 2009 at 7:42 am
Yes, but having never touched SSIS before yesterday, the "run it like you would any other" idea leaves me hanging.
And is there a way to do this with views, too?
July 17, 2009 at 7:48 am
You can run SSIS packages from Dev Studio, or you can schedule them in jobs, or you can run them from the command line. It depends on what you want to do. If you're going to be running them manually, saving them as files and then opening them in Dev Studio and hitting the Execute button works just fine. If you want to run them on a scheduled interval (daily, montly, hourly, etc.), then save them into SQL Server and create a job and schedule for them.
Not sure what you mean about doing this with views. Views don't hold data, they just select data from tables. If you import the data into the tables, the views will have the data in them.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 17, 2009 at 8:52 am
I meant that I was now looking for ways to copy several views over rather than re-create them one at a time. Now that I have the tables copied.
thanks
July 17, 2009 at 9:09 am
Will you need to copy views more than once?
If so, it's possible to set something up to look at object definitions and then execute those in the target database. I can't imagine a scenario where that would need to be done, but it is theoretically possible.
If not, right-click the source database, select Generate Scripts, and step through the wizard on that to generate the scripts you need to create views (and anything else you want to copy over), save the scripts to a file and to a connection in Management Studio. Connect to the target database, and run the scripts.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 17, 2009 at 1:48 pm
Sheesh, I should have known that. Thanks!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply