I am doing a repointing of the target table in SQL server to another ODBC destination. The problem is, if i were to specify a new target ODBC destination, i have to do the repointing of each column(in the transformation job) to the new target column. There are about 200 jobs and each tables has many columns.
Besides this manual and tedious process, would like to seek your expertise or by experience, is there any other simpler yet automated way to perform this task?
You didn't give near enough detail as to what you are doing and what you need. What is being repointed to the table, and which technology are you using?
Is the pointing done in a c# or vb6 app, or something internal to SQL server like a view to a linked server's table or a DTS package?
It sounds like you have jobs which execute DTS packages. You mention transforming in the job, but I'm wondering if you mean the job starts the DTS package, which then performs the transformation. Or does the job actually perform a transformation using TSQL code?
Which technology is used for the new odbc datasource which is the new home for the table? Another SQL Server of a different version? MySQL? Access?
So many questions that need answering first, then we can help you come up with a plan to solve this.
Thanks for replying me. Sorry for the lack of details.
Its a dts job, retrieving data from source systems , transforming it and mapped into tables in the sql server. Now, all remains, except the final mapping to the tables that resides in sql server shall be changed to greenplum server.
If i were to do it manually, changing the final target odbc connection, remapping each fields, it would be very tedious.
Do you have any brilliant idea to perform this task in an automated way?
I'm afraid not. If there were any tools that auto-remapped these, they most certinly wouldn't work with SQL7 since it is so old. Most tools easily work with 2005, and a lot support 2000. But almost none work on 7.0 or before.
This is a real problem for me since I still support a sql7 installation for a health care company with their main database being 325GB and 450 concurrent users. Although not a problem for newer versions of SQL, 325GB was pretty huge for SQL server back in 1998. So I need to care for this box the old fashioned way (perfmon and server side traces).
Maybe someone else knows of a tool for you.
Yeap. Its very old version of sql server. Thanks for your inputs anyhow. I will continue to search for better way and will let you know if i found better ways to do it.
Viewing 5 posts - 1 through 4 (of 4 total)