SSIS and Progress databases

cotton31 · 8 Apr 2011 at 12:35

Hi all,

Does anyone have any experience with this?

I've created a linked server in SQL Server 2005 using the Progress ODBC driver and this works fine (i.e. I can run queries using OPENQUERY with no problems)

I now want to extract data from this Progress db into a SQL Server db (for use with reporting services). I've started an Integration Services project and have created the data source with no problem, but creating the data source view gives me an SQL error (incorrect syntax) after selecting which tables to include in the view. I'm not sure where this error occurs though, as I haven't had to enter any SQL at this point...

I'm not even sure I'm on the right track, I'm a complete novice when it comes to SSIS, although I've got a lot of experience with SSRS so I think I should be able to do it...

Cheers,

cotton31 · 13 Apr 2011 at 11:31

Initially I created a linked server in management studio. I can run select * from anything without getting errors in this.

I want to warehouse data from this linked progress db, to a new SQL server db. So in Visual Studio, I've created a new Integration Services project and in Solution Explorer, it shows sections for data source, data source views, ssis packages and miscellaneous.

I can create the data source fine, but creating the data source view where I pick which tables to use throws an error about syntax (I haven't entered any code at this point).

I haven't added any tasks at this point - should I have?

Thanks for the reply!

cotton31 · 13 Apr 2011 at 13:02

Cheers mate, I'll try that. Any chance of a mini how to guide? As I say I'm a bit of a novice!

Cheers,

cotton31 · 14 Apr 2011 at 09:25

Thanks for your help mate - that's more or less what I'd done, except I'd used a SQL Server Destination. This caused an error, which apparently is common when the machine the package is running on is not the same as the database machine. The workarounds are to either run the package locally, or use an OLE DB destination as you suggested. Either way it seems to be working now - I assume I just keep adding these to the package for each table I want to import?