June 28, 2011 at 11:16 am
Basic situation:
We need to get data from DB2 into SQL Server. We do this via OPENQUERY and an SSIS data flow task.
However, I'd like to parametrize the query so we don't have to rewrite the SP every time we want to change the WHERE clause data range (and no, pulling everything over and filtering in SQL Server is not a viable option as the dataset is too large).
This means using EXEC() with a string version of the OPENQUERY statement.
But this "breaks" the SSIS data flow task because it can no longer validate the columns in the select statement.
What I tried:
1) Create a temp table to hold the data
2) generate my dynamic OPENQUERY statement to insert into said temp table
3) select from the temp table
SSIS fails to read the columns from this, even though they show up in preview.
Any other ideas? I guess I could dump the data into a preliminary persistent table but I'd rather not. Oh and we have to do quite a bit of data transformation and filtering so need to keep this as an SSIS data flow task.
June 28, 2011 at 11:28 am
You could try returning the columns from a stored proc and using that as the source for your data.
You'll need to include a dummy select as the first line of the proc to trick SSIS into thinking it knows what is being returned.
I mean this sort of thing
Select 'x' col1, 'y' col2
Where 1=2
Remember to get the datatypes etc matching and it might work...
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
June 28, 2011 at 11:38 am
Ugh, that won't really work due to the nature of the (garbage) data I have to deal with. Rather stick with persistent tables than do that, I think.
But of course new I have a new wrinkle. Can't seem to get the SQL Command data flow component to take parameters.
Maybe I'll just go back to hard-coding the date range. At least that works...
June 29, 2011 at 11:13 am
Phil Parkin (6/28/2011)
You could try returning the columns from a stored proc and using that as the source for your data.You'll need to include a dummy select as the first line of the proc to trick SSIS into thinking it knows what is being returned.
I mean this sort of thing
Select 'x' col1, 'y' col2
Where 1=2
Remember to get the datatypes etc matching and it might work...
Tangential Phil, but here is how I do what you're talking about:
IF 1 = 0 -- no-op
BEGIN
-- SSIS OLE DB source will still "find" this SELECT and use it as shape of metadata
SELECT CAST(NULL AS VARCHAR(100)) AS col1,
CAST (NULL AS INT) AS col2 ;
END
This way it will not return an empty resultset and potentially affect normal operation of the proc when called from another app. I sought out the technique because I needed to call a shared "report" proc from SSIS and just adopted it as a standard way to do it. Note: I have not tried it in SSIS 2008 with an ADO.NET source.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 29, 2011 at 11:20 am
Pam Brisjar (6/28/2011)
Basic situation:We need to get data from DB2 into SQL Server. We do this via OPENQUERY and an SSIS data flow task.
However, I'd like to parametrize the query so we don't have to rewrite the SP every time we want to change the WHERE clause data range (and no, pulling everything over and filtering in SQL Server is not a viable option as the dataset is too large).
This means using EXEC() with a string version of the OPENQUERY statement.
But this "breaks" the SSIS data flow task because it can no longer validate the columns in the select statement.
What I tried:
1) Create a temp table to hold the data
2) generate my dynamic OPENQUERY statement to insert into said temp table
3) select from the temp table
SSIS fails to read the columns from this, even though they show up in preview.
Any other ideas? I guess I could dump the data into a preliminary persistent table but I'd rather not. Oh and we have to do quite a bit of data transformation and filtering so need to keep this as an SSIS data flow task.
Have you looked into the EXEC()...AT construct? Unlike OPENQUERY you can pass a variable into EXEC(), so something along these lines (untested):
DECLARE @sql NVARCHAR(MAX) ;
CREATE TABLE #tmp (col1 VARCHAR(100),col2 INT,etc INT) ;
SET @sql = N'SELECT col1, col2, etc FROM remote_table WHERE ' ;
IF @some_proc_input = N'something'
BEGIN
SET @sql = @sql + N'other_col = ''' + @some_proc_input + N'''' ;
END
INSERT INTO #tmp (col1,col2,etc)
EXEC (@sql) AT LINKED_SERVER_NAME ;
EDIT: Just noticed this was for 2005, updated code to be 2005-compliant
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 29, 2011 at 1:58 pm
opc.three (6/29/2011)
Phil Parkin (6/28/2011)
You could try returning the columns from a stored proc and using that as the source for your data.You'll need to include a dummy select as the first line of the proc to trick SSIS into thinking it knows what is being returned.
I mean this sort of thing
Select 'x' col1, 'y' col2
Where 1=2
Remember to get the datatypes etc matching and it might work...
Tangential Phil, but here is how I do what you're talking about:
IF 1 = 0 -- no-opBEGIN
-- SSIS OLE DB source will still "find" this SELECT and use it as shape of metadata
SELECT CAST(NULL AS VARCHAR(100)) AS col1,
CAST (NULL AS INT) AS col2 ;
END
This way it will not return an empty resultset and potentially affect normal operation of the proc when called from another app. I sought out the technique because I needed to call a shared "report" proc from SSIS and just adopted it as a standard way to do it. Note: I have not tried it in SSIS 2008 with an ADO.NET source.
Oh yeah - that's the way to do it. I wrote my response on a BlackBerry so it wasn't quite up-to-scratch!
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply