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,

Ciphon · 10 Apr 2011 at 10:39

So, you've set up a Data Flow task, with an OLE DB Source. Connected the Source to your SQL Server.

You've created a view in the same SQL server your Source is connected to, which pulls the data you want to report on via a linked server, then when you selected "Table Or View" as your Data Access Mode, select your view, you get a syntax error?

Sorry - Just trying to understand the problem a little more, tell me if I'm way off..

Can you run a SELECT * FROM "View" without getting an error?

What happens if you grab the SQL from the view, change your Data Access Mode to "SQL Command" and paste it in?

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!

Ciphon · 13 Apr 2011 at 12:13

Instead of creating a Data Source View, try creating an SSIS Package.

Then you should be able to use a Data Flow task to pull the information you want.

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,

Ciphon · 13 Apr 2011 at 18:23

I'm no pro to be honest! I've only been using this technology for a couple of months to do fairly basic things..

I don't use Data Source Views - I've never even tried to use them.

I simply create an SSIS, drag a data flow on from the toolbox, go into the data flow then drag on a OLE DB source, select your connection and SQL command - put your SQL in.

Drag an OLE DB destination on, link them up with the green arrow (green meaning "Do this on success of OLE DB source", you can change this by right clicking on the arrow). Then simply select the table you want to import your data into, map your columns.

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?