May 31, 2012 at 8:41 pm
All,
I know there's a forum specifically for SSIS but it's for 2005 and this is a 2008 R2 instance.
We have a US client who is running a commercial app on a local server. Our office is remote (in fact separated by a very big pond - the Pacific Ocean). Their other Prod systems (Oracle) are running out of a data center in a third location (also in the US).
We need to create some SSIS packages that will extract from Oracle Prod to their local commercial package. Clearly the SSIS packages will need to be installed in the SQL Server instance at their location. I'm not to concerned about running this in Prod as I'm fairly certain it can be done. We still need to do some analysis on the amount of data being sent over the wire, but that is probably manageable.
My question is about the proper strategy for creating a development environment. We have Oracle development instances in our office. I am concerned that if we did development connecting to a remote copy of their (SQL) Prod database, the performance will be poor while building and testing the packages.
Would it be better to ship us a copy of the database we need to load into, so we could attach it to a local server and build the SSIS packages here? Or is some other strategy recommended?
There is also another scenario at play. There needs to be data flowing back from SQL to the Oracle databases. Again, I'm fairly certain that the Prod scenario can be handled (I'm aware of the Oracle Connector required by SSIS). Where I get concerned is for the development environment. The SQL schema is actually different than the one we're pushing into and we may not be able to create a remote copy of it. So to clarify:
SQL Staging tables (using SSIS) to pull from an Oracle database
SQL Prod application (different schema than the staging tables) to push using SSIS to Oracle
Any other comments would be appreciated.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 1, 2012 at 12:36 am
There's an Integration Services forum in the Datawarehousing section more at the bottom 🙂
Every time you open a package or when you run it, the connection managers will be validated. If this takes a long time, it will make development a frustrating experience.
If data transfer takes too long, you can add TOP clauses (do they have that in Oracle?) to temporarily limit the data.
The second scenario is trickier. The SSIS dataflow is metadata static, so you need to have the correct schema in order to create the dataflow.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 1, 2012 at 12:58 am
Koen Verbeeck (6/1/2012)
There's an Integration Services forum in the Datawarehousing section more at the bottom 🙂Every time you open a package or when you run it, the connection managers will be validated. If this takes a long time, it will make development a frustrating experience.
If data transfer takes too long, you can add TOP clauses (do they have that in Oracle?) to temporarily limit the data.
The second scenario is trickier. The SSIS dataflow is metadata static, so you need to have the correct schema in order to create the dataflow.
Koen,
Thanks. I really wasn't sure where was best to post this question so I posted here looking for maximum exposure.
I believe Oracle does have a TOP clause but I'm not really an Oracle guy. I do understand how that would make development less frustrating.
Do you have any suggestions for alternate scenarios? You seem to be confirming that my suspicions were justified.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 1, 2012 at 1:17 am
dwain.c (6/1/2012)
I believe Oracle does have a TOP clause but I'm not really an Oracle guy. I do understand how that would make development less frustrating.Do you have any suggestions for alternate scenarios? You seem to be confirming that my suspicions were justified.
For what do you want alternate scenarios? Regarding the development or the different schema?
If the schema: you can perhaps develop against the real production environment but with a TOP 0 in the source query. That way no data is inserted but you still have the correct metadata. It will be pretty hard to test for truncation errors or invalid datatypes...
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 1, 2012 at 1:22 am
Koen Verbeeck (6/1/2012)
dwain.c (6/1/2012)
I believe Oracle does have a TOP clause but I'm not really an Oracle guy. I do understand how that would make development less frustrating.Do you have any suggestions for alternate scenarios? You seem to be confirming that my suspicions were justified.
For what do you want alternate scenarios? Regarding the development or the different schema?
If the schema: you can perhaps develop against the real production environment but with a TOP 0 in the source query. That way no data is inserted but you still have the correct metadata. It will be pretty hard to test for truncation errors or invalid datatypes...
Sorry to be unclear. I'm looking for a recommendation on how to best set up a development environment under the stated conditions. For either or both of the scenarios I described.
The idea is I want a productive dev environment.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 1, 2012 at 1:29 am
If you could get copies of the destinations for your local DEV environment, that would be the most ideal situation.
The most important part is the metadata. If schema's are different, you can't just deploy a package from one environment to another. SSIS is a littly crybaby when it comes to changing metadata.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 1, 2012 at 5:51 am
My recommendation. Make sure your SQL schema (as Koen said) is the same in the development environment. Get local copies of all dbs for development. Do some unit testing to make sure it works.
THEN set up a test environment that mirrors current production setup and test there before moving anything into Production. That way you know (before going into Production) if there are going to be problems.
June 1, 2012 at 6:13 am
Koen Verbeeck (6/1/2012)
SSIS is a littly crybaby when it comes to changing metadata.
Ha! So true...
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
June 1, 2012 at 6:27 am
jcrawf02 (6/1/2012)
Koen Verbeeck (6/1/2012)
SSIS is a littly crybaby when it comes to changing metadata.Ha! So true...
I hate the XML Source. Any changes to the XSD file, and you have to delete the damn source object and recreate it from scratch. Stupid crybaby SSIS.
June 1, 2012 at 7:47 am
Brandie Tarvin (6/1/2012)
jcrawf02 (6/1/2012)
Koen Verbeeck (6/1/2012)
SSIS is a littly crybaby when it comes to changing metadata.Ha! So true...
I hate the XML Source. Any changes to the XSD file, and you have to delete the damn source object and recreate it from scratch. Stupid crybaby SSIS.
And if you're creating XML and change the length of the path, you have to delete the object adn recreate it from scratch.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
June 1, 2012 at 7:49 am
I'd suggest getting the Attunity connector for Oracle. It seems to work better/faster for me than any of the standard objects for connecting to Oracle. They're free.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
June 2, 2012 at 2:38 pm
Stefan Krzywicki (6/1/2012)
I'd suggest getting the Attunity connector for Oracle. It seems to work better/faster for me than any of the standard objects for connecting to Oracle. They're free.
+1
And already available for SQL 2012.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 3, 2012 at 4:21 am
Develop locally and use package variables, you can then update the connections when deploying to live
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 3, 2012 at 1:08 pm
Perry Whittle (6/3/2012)
Develop locally and use package variables, you can then update the connections when deploying to live
It's not enough if the metadata changes during deployment (which is the issue here).
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 4, 2012 at 6:15 am
Oracle does not have a TOP clause, but they finagle it with a rownumber method. Attunity is the best way to go.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply