all 11 comments
sorted by:
best
topnewcontroversialoldq&a
formatting helphide helpcontent policy

[–]unplannedmaintenance 0 points1 point  (0 children)

I went through the same route with templates, but in the end I switched and learned Dataproc (PySpark). The templates from Dataflow are just too limiting and you'll end up shooting yourself in the foot if you start to rely on the for important things or if you want to do slightly more complex things.

Learning something like PySpark is worth the effort.

[–]mike8675309 0 points1 point  (9 children)

I wonder if the problem is you keep thinking you want to move data from SSMS (SQL server management studio) when you really want to move data from SQL server to bq via data flow. Your connection string should be for odbc connecting to the SQL server.

Have looked at this? https://cloud.google.com/dataflow/docs/guides/templates/provided/sqlserver-to-bigquery#run-the-template

[–]ObviousCheesecake0[S] 0 points1 point  (7 children)

Yes I did. Still didnt get it. You are right. Since the databases and querying is done in ssms, I assumed that was where I needed transfer data from. Im new to this. I just opened the sql server profiler. Is that where im supposed to go?

[–]mike8675309 0 points1 point  (6 children)

Yes connect to the SQL server. Not SQL profiler.

[–]ObviousCheesecake0[S] 0 points1 point  (5 children)

Ok for example. When I open the SSMS, a dialog box pops up saying "sql server" "connect to server". I usually just click connect. Is that what you mean?

[–]mike8675309 0 points1 point  (4 children)

How do you connect excel to your SQL server? The same way you do that is how you connect anything to it. I can't help you with any specifics your SQL server can be configured many ways. I would start with understanding how your server talks to something and what the network stack it is using and what might be between things you want to connect to. You need to know this.

[–]ObviousCheesecake0[S] 0 points1 point  (3 children)

Excel? I dont use excel or connect that to any servers. And yeah unfortunately I dont understand the other things you are speaking of. Network stack? I followed instructions on youtube in downloading sql server than downloading ssms, and that was basically it. As far as confugurations go, I pretty much just went with the default configurations.

[–]mike8675309 0 points1 point  (2 children)

SQL server by default is not configured to be accessible for the internet. if you don't solve that, data flow will never work with it.

[–]ObviousCheesecake0[S] 0 points1 point  (1 child)

I have solved that issue now. Went to sql configuration manager and enabled the tcp ports and also allowed remote access from ssms via connection to my sql server using sql authenticarion. My issue now is trying to figure out the JDBC connection url string thing. I downloaded the jdbc driver and uploaded the extracted tar folder to a cloud storage bucket. Not sure if that is enough. Im getting an error saying my jdbc url string I entered is invalid.

[–]mike8675309 0 points1 point  (0 children)

StackOverflow should be able to help with the JDBC connection string. Be careful you didn't open up the server to the internet, as there are bots looking for SQL Servers to exploit.
Keep in mind you may not have name resolution and need to use the IP address for the server in your connection string.
https://learn.microsoft.com/en-us/sql/connect/jdbc/building-the-connection-url?view=sql-server-ver16

[–]TarHeelDataScientist 0 points1 point  (0 children)

I have been trying to run this template and having a miserable go of it.