SSRS with Amazon Redshift as DWH

Question

Dear All,

We are planning to migrate our DWH from SQL Server to Amazon Redshift and analyzing all the options for our existing SSRS Reports.

We would like to know whether SSRS is compatible with Redshift as a DWH and also share your experiences what changes would be required.

Please let me know if you require any other information.

Thank you.

Best Regards,
Santhosh

Answer 1

Hi,

I did some research in internet, and I believe this should be possible.

Since I don't have Redshift test environment, I could not try the methods myself, but I would suggest you to try two ways :

The first road could be that to try to use Redshift ODBC to be connected to SSRS . You could see : Configuring an ODBC connection

Another way is to try this approach which is accept by previous users as described in this thread : Redshift to SSRS Connectivity issue

You could try these two see if this works for you.

Regards,
Lukas

Answer 2

hey folks - here's my smallish rant on this topic

TL:DR = SSRS and Redshift does and can work together, but in a red wine and fish sort of way....unless you are happy to remove the use of user friendly parameters, expect it to hurt a lot.

this doesn't help the OP, but wanted to add my part, since i am going through this right now, i have around 100 SSRS reports over various Solutions, that need to be "converted" to Redshift since our IT have moved our reporting database over to Redshift from SQL Server. Connecting SSRS to Redshift is the easy part, you can simply use the AWS redshift driver - its easy. i have few issues with Redshift itself aside from having to learn a new dialect of SQL, its not that hard though, Redshift has good syntax documentation although the examples given in the official documentation are vague and not very helpful, they don't mimic real life situations. but otherwise, query writing seems to be ok...

in terms of query editors, the AWS Query editor is not the best. given that coding is so keyboard intensive, there is a lot of clicking required within the AWS query editor. that said it is workable, and does the job...

we opted to go with DBeaver as a decent alternative that mimics most of what SSMS does, it is adequate for what we need and this is our preference over the AWS query editor

but then comes the hard part, getting the query to run in SSRS. here are the things you should know, that i wish i had known a few months back:

SSRS and ODBC are not friends - working with single value parameters in MSSQL is easy, over ODBC it is more difficult. any quick search will reveal that you replace @paramname with '?', which is fine for single value things like dates.

![User's image](/api/attachments/79111b6b-2da5-40c2-93b6-0432a5f12544?platform=QnA)

the fun starts with **multivalue parameters**. you will get the error " *cannot add multi value query parameter ..... because it is not supported by the data extention*"......ODBC doesn't support multi value parameters, there is a workaround which is detailed nicely by this guy [https://www.youtube.com/watch?v=a4Frj8vG4as](https://www.youtube.com/watch?v=a4Frj8vG4as), but in order to make this work you also need to solve the lack of STRING_SPLIT function in redshift. there are various solutions to this, you can adapt/create your own string_split function. but the trick is basically to convert the parameter values to String and load them into a long comma separated list, using the technique in the video. you will find that this needs to be done via a stored procedure, called in your SSRS dataset via an expression. its tedious, but it works

![User's image](/api/attachments/12a4ba02-c325-45a3-8600-5a48ae14ac16?platform=QnA)

![User's image](/api/attachments/3e860099-0214-4416-ba4f-85691eda0eaa?platform=QnA)

![User's image](/api/attachments/4aee1a84-e2f1-4580-8ab3-2f8a8d01eeb7?platform=QnA)

![User's image](/api/attachments/93a6355b-7b56-4670-b9e6-f7b7b5818fec?platform=QnA)

the next issue i have come across is **trying to run a query in more than a single batch**.

for example if you use temp tables in your query you will be greeted by this error : *"Cannot insert multiple commands into a prepared statement"* because quite literally you are trying to process multiple commands.

![User's image](/api/attachments/1d3cbfa5-b890-4d86-b02c-af1262cf644e?platform=QnA)

The workaround so far has been to either use a CTE or stored procedure, again tedious, but it works

i'm sure i will find out more things that make this whole job of converting from SQL Server to Redshift an absolute misery, if i do i can reply as a comment so that others following in my path might spend fewer hours crying at the lack of tutorials/guidance/advice in this respect. all i can say to you is good luck, i feel your pain!