March 7, 2017 at 12:46 pm
I'm trying this using SQL Server 2008. The DB is an SQL 2008 DB. The version of SQL Server 2012 I have is the express version. I'm unable to create SSIS within the express version.
How do I determine the BIDS version?
March 7, 2017 at 1:29 pm
You'd be far better served using SQL 2012 Developer Edition than SQL 2012 Express. Express has too many limitations. I strongly suspect that SQL 2008 is having problems dealing with SQL 2012. You'd be far better off pulling this data from 2008 to 2012 using the 2012 instance.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 7, 2017 at 1:55 pm
I've tried using SQL Server 2014, but it will not work with the 2008 DB. I'm stuck in the middle.
March 7, 2017 at 2:09 pm
That is strange... I know I've connected to SQL 2005 from a SQL 2016 SSMS version without any problems. mind you that isn't for a data import/export.
I'd still recommend making an SSIS package in visual studio and using that to migrate the data around. If you have a license for SQL 2012 standard or higher you can build that into an SSIS catalog and just run it with a job step. If you don't, then it is more tricky. If you have a license for SLQ Prompt, you can quickly and easily script the output from a query to an insert statement, but that isn't really a reusable solution.
Is there any reason you cannot upgrade your 2008 database? SQL 2008 is fast approaching end of extended support.
Or any reason not to set up a linked server connection and pull data across that way? I personally don't like linked servers, but your options are limited when you are running express.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 7, 2017 at 2:10 pm
crowegreg - Tuesday, March 7, 2017 1:55 PMI've tried using SQL Server 2014, but it will not work with the 2008 DB. I'm stuck in the middle.
Maybe there is a way to still get it working with SSIS but I'd probably try using openrowset instead at this point. That could just be me as SSIS just pisses me off sometimes.
Sue
March 7, 2017 at 3:10 pm
Sue_H - Tuesday, March 7, 2017 2:10 PMThat could just be me as SSIS just pisses me off sometimes.Sue
I'm sensing a kindred spirit here. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
March 7, 2017 at 3:34 pm
Jeff Moden - Tuesday, March 7, 2017 3:10 PMSue_H - Tuesday, March 7, 2017 2:10 PMThat could just be me as SSIS just pisses me off sometimes.Sue
I'm sensing a kindred spirit here. 😉
heh... me too... until 2016... the SSIS catalog in 2016 has fixed most of my issues with SSIS. Well, for now... I imagine I'll find some other thing that bugs me about it. But for the moment, SSIS 2016 makes me a lot less angry than working with SSIS in 2008 R2 and older. 2012 made good improvements with the catalog but I've blown out a project by accident when uploading a single dtsx instead of the entire ispac... that was frustrating...
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 7, 2017 at 4:01 pm
Jeff Moden - Tuesday, March 7, 2017 3:10 PMSue_H - Tuesday, March 7, 2017 2:10 PMThat could just be me as SSIS just pisses me off sometimes.Sue
I'm sensing a kindred spirit here. 😉
Yeah but you maybe suffer a bit more OFS than I do.
Old Fart Syndrome.
Sue
March 7, 2017 at 4:09 pm
bmg002 - Tuesday, March 7, 2017 3:34 PMheh... me too... until 2016... the SSIS catalog in 2016 has fixed most of my issues with SSIS. Well, for now... I imagine I'll find some other thing that bugs me about it. But for the moment, SSIS 2016 makes me a lot less angry than working with SSIS in 2008 R2 and older. 2012 made good improvements with the catalog but I've blown out a project by accident when uploading a single dtsx instead of the entire ispac... that was frustrating...
It has some great features, no doubt. But sometimes you see people messing with it for way to long when you could use bcp or bulkinsert and be done with it in a fraction of the time. That's the part that gets to me the most.
Sue
March 7, 2017 at 10:36 pm
Sue_H - Tuesday, March 7, 2017 4:09 PMbmg002 - Tuesday, March 7, 2017 3:34 PMheh... me too... until 2016... the SSIS catalog in 2016 has fixed most of my issues with SSIS. Well, for now... I imagine I'll find some other thing that bugs me about it. But for the moment, SSIS 2016 makes me a lot less angry than working with SSIS in 2008 R2 and older. 2012 made good improvements with the catalog but I've blown out a project by accident when uploading a single dtsx instead of the entire ispac... that was frustrating...It has some great features, no doubt. But sometimes you see people messing with it for way to long when you could use bcp or bulkinsert and be done with it in a fraction of the time. That's the part that gets to me the most.
Sue
Those and OPENROWSET with a healthy dose of the ACE Drivers. You can do some absolute magic there. Throw in some additional magic with HTML, spreadsheet templates, and refreshable spreadsheets and I have no need for SSRS either. It's a real shame they did away with sp_MakeWebTask. Oh, the things you could do with that baby.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 8, 2017 at 7:43 am
Sue_H - Tuesday, March 7, 2017 4:09 PMbmg002 - Tuesday, March 7, 2017 3:34 PMheh... me too... until 2016... the SSIS catalog in 2016 has fixed most of my issues with SSIS. Well, for now... I imagine I'll find some other thing that bugs me about it. But for the moment, SSIS 2016 makes me a lot less angry than working with SSIS in 2008 R2 and older. 2012 made good improvements with the catalog but I've blown out a project by accident when uploading a single dtsx instead of the entire ispac... that was frustrating...It has some great features, no doubt. But sometimes you see people messing with it for way to long when you could use bcp or bulkinsert and be done with it in a fraction of the time. That's the part that gets to me the most.
Sue
Ah... that is a good point. SSIS is a bit nicer to a non-DBA developer though. Where I work, we use SSIS to move data form various databases into a data warehouse and it works OK and is pretty easy to maintain. Mind you we do end up with some messes of SSIS that take 5 minutes to load. But we do a bunch of transforms on the data before dumping it into the final database and it is nice being able to poke at the values and see what is where to make sure all the calculations are correct. If I need to build up an SSIS pacakge, I still often use TSQL to get the basic logic sorted out and then use SSIS to do the heavy lifting. I've done some messy SSIS pacakges before though too. Lots of repeated things like update column1. Now based on the value of column1, update column2. and repeat up to column 63. SSIS was not the right tool for that job, but once I had started I was determined to finish. It works, runs in not horrible time (roughly a minute, but it does a bunch of odd calculations along the way... think there are 150 transforms roughly plus 2 data pulls and 2 data dumps), but I bet TSQL in a SQL job would have got the same thing done in less time. It does use bulkinsert for the dumps but I still think it could be optimized a bit.
EDIT - guess I should clarify that I am the DBA where I work, but more of a DBA by neccessity. I quite enjoy it though. I've read about bcp and openrowset, but haven't really used them much.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 8, 2017 at 8:37 am
Jeff Moden - Tuesday, March 7, 2017 10:36 PMSue_H - Tuesday, March 7, 2017 4:09 PMbmg002 - Tuesday, March 7, 2017 3:34 PMheh... me too... until 2016... the SSIS catalog in 2016 has fixed most of my issues with SSIS. Well, for now... I imagine I'll find some other thing that bugs me about it. But for the moment, SSIS 2016 makes me a lot less angry than working with SSIS in 2008 R2 and older. 2012 made good improvements with the catalog but I've blown out a project by accident when uploading a single dtsx instead of the entire ispac... that was frustrating...It has some great features, no doubt. But sometimes you see people messing with it for way to long when you could use bcp or bulkinsert and be done with it in a fraction of the time. That's the part that gets to me the most.
Sue
Those and OPENROWSET with a healthy dose of the ACE Drivers. You can do some absolute magic there. Throw in some additional magic with HTML, spreadsheet templates, and refreshable spreadsheets and I have no need for SSRS either. It's a real shame they did away with sp_MakeWebTask. Oh, the things you could do with that baby.
If you work at different places or with different dev groups, you do end up needing to know the alternative ways things are done. I just prefer doing things more with SQL even if I have to do or support things using other tools. Some of the other options are useful if and when they are used in appropriate places or for necessary business reasons. It seems that too often the wrong tools end up being used for the job.
Dang...I hope we aren't reminiscing years down the road of how we used to do backups with BACKUP DATABASE commands 🙂
Sue
March 8, 2017 at 8:51 am
Sue_H - Wednesday, March 8, 2017 8:37 AMJeff Moden - Tuesday, March 7, 2017 10:36 PMSue_H - Tuesday, March 7, 2017 4:09 PMbmg002 - Tuesday, March 7, 2017 3:34 PMheh... me too... until 2016... the SSIS catalog in 2016 has fixed most of my issues with SSIS. Well, for now... I imagine I'll find some other thing that bugs me about it. But for the moment, SSIS 2016 makes me a lot less angry than working with SSIS in 2008 R2 and older. 2012 made good improvements with the catalog but I've blown out a project by accident when uploading a single dtsx instead of the entire ispac... that was frustrating...It has some great features, no doubt. But sometimes you see people messing with it for way to long when you could use bcp or bulkinsert and be done with it in a fraction of the time. That's the part that gets to me the most.
Sue
Those and OPENROWSET with a healthy dose of the ACE Drivers. You can do some absolute magic there. Throw in some additional magic with HTML, spreadsheet templates, and refreshable spreadsheets and I have no need for SSRS either. It's a real shame they did away with sp_MakeWebTask. Oh, the things you could do with that baby.
If you work at different places or with different dev groups, you do end up needing to know the alternative ways things are done. I just prefer doing things more with SQL even if I have to do or support things using other tools. Some of the other options are useful if and when they are used in appropriate places or for necessary business reasons. It seems that too often the wrong tools end up being used for the job.
Dang...I hope we aren't reminiscing years down the road of how we used to do backups with BACKUP DATABASE commands 🙂Sue
BACKUP DATABASE commands? don't you just use the GUI?
I'm just kidding.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 8, 2017 at 8:56 am
bmg002 - Wednesday, March 8, 2017 8:51 AMBACKUP DATABASE commands? don't you just use the GUI?I'm just kidding.
No just some SMO apps I wrote in C# and PowerShell.
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply