August 15, 2017 at 9:06 am
Hello, I have a single Teradata statement query, in a SSIS package, with a single Execute SQL Task. In that task, I've tried to use in the General Tab, the ODBC, but have lots of connection issues. So now I'm using OLE DB, my Connection to my SQL Server, SQL Source Type is Direct Input, SQL Statement is a single simple Teradata Query as that's the source to extract.
I do not know what to select or key into the Parameter Mapping or Results, I've tried a few things but It's all error prone. I've also not made at target table yet on SQL Server, as I don't know how the result set is held and what object I should use in the SSIS package to connect, should I have that first?
I'm looking for some help/advice on best approach. Also, is there a better way, like use a script to wrap the SQL to better execute on Teradata to bring back results to load to a target table, especially if I have multiple linear SQL statements to execute, something like Python?
Thank you,
JQ
August 15, 2017 at 9:15 am
quinn.jay - Tuesday, August 15, 2017 9:05 AMHello, I have a single Teradata statement query, in a SSIS package, with a single Execute SQL Task. In that task, I've tried to use in the General Tab, the ODBC, but have lots of connection issues. So now I'm using OLE DB, my Connection to my SQL Server, SQL Source Type is Direct Input, SQL Statement is a single simple Teradata Query as that's the source to extract.I do not know what to select or key into the Parameter Mapping or Results, I've tried a few things but It's all error prone. I've also not made at target table yet on SQL Server, as I don't know how the result set is held and what object I should use in the SSIS package to connect, should I have that first?
I'm looking for some help/advice on best approach. Also, is there a better way, like use a script to wrap the SQL to better execute on Teradata to bring back results to load to a target table, especially if I have multiple linear SQL statements to execute, something like Python?
Thank you,
JQ
You'll need to use a data flow, not an ExecuteSQL task, to do this.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 15, 2017 at 9:47 am
Phil Parkin - Tuesday, August 15, 2017 9:15 AMYou'll need to use a data flow, not an ExecuteSQL task, to do this.
When I do this, it works, for one script, and though I created a table in a database to hold the results, when I place the object Teradata Source for the SQL, and then the object SQL Server destination, I don't get to point it to the table. It ends up creating a Destination Table in the Systems Tables area on the server. A little bigger issue, is if I have more than one script to execute, and don't want to chain a bunch of SQL Execute objects, but want it to be executed on the source system in one big step? And, also direct it to my target table on SQL Server that I created? Thanks
August 15, 2017 at 11:30 am
quinn.jay - Tuesday, August 15, 2017 9:47 AMPhil Parkin - Tuesday, August 15, 2017 9:15 AMYou'll need to use a data flow, not an ExecuteSQL task, to do this.When I do this, it works, for one script, and though I created a table in a database to hold the results, when I place the object Teradata Source for the SQL, and then the object SQL Server destination, I don't get to point it to the table. It ends up creating a Destination Table in the Systems Tables area on the server. A little bigger issue, is if I have more than one script to execute, and don't want to chain a bunch of SQL Execute objects, but want it to be executed on the source system in one big step? And, also direct it to my target table on SQL Server that I created? Thanks
I don't know why your SQL Server destination is failing. I've never had a problem with that component before, so that's difficult to diagnose.
Executing a big script on the source – sounds like you need to create a stored proc (or whatever the Teradata equivalent is) on the source and execute that.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 15, 2017 at 12:03 pm
Phil Parkin - Tuesday, August 15, 2017 11:30 AMI don't know why your SQL Server destination is failing. I've never had a problem with that component before, so that's difficult to diagnose.Executing a big script on the source – sounds like you need to create a stored proc (or whatever the Teradata equivalent is) on the source and execute that.
I'm able to get the proper target table name now from the SQL Server Dest load.
I thought that on the SSIS side, there was a way to place say 35 SQL statements within a wrapper of another scripting language, and you have one object that gets executed that would go to the source, sign in, and execute the Queries in linear fashion, then disconnect. All that instead of moving the queries to a Unix Shell wrapper outside of SQL Server, or creating a SPROC on Teradata, or even 35 objects executing each individual query.
August 15, 2017 at 12:14 pm
quinn.jay - Tuesday, August 15, 2017 12:03 PMPhil Parkin - Tuesday, August 15, 2017 11:30 AMI don't know why your SQL Server destination is failing. I've never had a problem with that component before, so that's difficult to diagnose.Executing a big script on the source – sounds like you need to create a stored proc (or whatever the Teradata equivalent is) on the source and execute that.
I'm able to get the proper target table name now from the SQL Server Dest load.
I thought that on the SSIS side, there was a way to place say 35 SQL statements within a wrapper of another scripting language, and you have one object that gets executed that would go to the source, sign in, and execute the Queries in linear fashion, then disconnect. All that instead of moving the queries to a Unix Shell wrapper outside of SQL Server, or creating a SPROC on Teradata, or even 35 objects executing each individual query.
Like Script Task Editor, it appers it can take MS VB or VC# '15 to execute a task, would this be an appropriate location for wrapping Query statements into to be executed once as an object? It reminds me of how BTEQ on Teradata.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply