I'm not sure if I posted this in the right forum, but here goes.
I need to load (basically copy) tables from a SQL Server application and load those tables to Teradata. I can access the tables thru SQL Assistant now, but am not sure how I should set up this "copy" from one platform to another. There are 100+ tables that I need to copy, and this copy will be done on a regular basis (weekly or monthly) so a manual process is not ideal.
Someone suggested using OLE Load to do this copy. Is this the best way? Can OLE Load be set up to run in batch mode? If so, how?
Someone also mentioned SSIS. I know nothing about that, so don't even know where to begin reseraching this option.
Is there a better way?
My customer is extremly limited in technical ability, so I will have to come up with the solution. I will not have the ability to do anything with the SQL Server other than just read the tables. The solution must be done on my side in the Teradata world.
SSIS is SQL Server Integration Service, a kind of ETL Tool of Microsoft. If you just would like to load data from SQL Server to Teradata, it is not a bad choice. There are some available SSIS packages which you can just reconfig the connection and ready to use. Here is a very good projects from Microsoft that you can download all the packages: http://www.microsoft.com/sqlserver/2005/en/us/project-real.aspx
Another solution in pure Teradata environment is using TPT script Teradata Parallel Transporter which allow several parallel thread of loading data to Teradata.
Hope you can offer the best solution to customer :)
I'm working on the same migration project from SQL SERVER to TERADATA , i have some errors from the SSIS packages using Attunity TD connectors.
Let me know if you have faced this kind of problem
[Teradata Transaction Destination ] Error: TPT Import failed to insert row. Fast/MLoad or FastExport has already been terminated.
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Teradata Transaction Destination" (35) failed with error code 0x80004005 while processing input "Teradata Destination Input" (58). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
[Transaction ] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Transaction" (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
[Teradata Transaction Destination ] Error: TPT Import error encountered during Apply phase. Session forced off by PMPC or gtwglobal.