How do I pass a variable value to a query?
-
I'm trying to copy data from one server to another. The volume has got so large that I now need to limit it by month & year. On the target server, there is a table which contains the month/year required, so I can do a query like
select month,year from mgtdate -- both INTsWhat I'd like to do is then put these values into variables so I can then pass then the data flow source object and use a query likeselect field1, field2, field2 from datatablewhere recdate >= convert(datetime,convert(char(8),@year*10000*@month*100+1))I currently don't have access to create a procedure on the source server, so I'd prefer not to use stored procedures there, hence parameters don't sem to be useful.
How can I get variable values substituted into the query?
Or do I have to get another step to create the SQL string as a variable and use that for the data source? If I follow this route, how do I map the fields?
As you may gather, I have limited experience of SSIS and have mainly used it with fixed values, i.e. just copy everything doing a few transforms along the way!
Derek
-
You can either simply declare the variables in you script and then SET the values or you can use sqlcmd.exe with variables
Save your query as yourscript.sql like this:
select field1, field2, field2 from datatable
where recdate >=$(startdate)
and then run:
sqlcmd -v startdate = 20090211 -i c:\yourscript.sql
More info about this approach at http://msdn.microsoft.com/en-us/library/ms188714(SQL.90).aspx
[font="Verdana"]Markus Bohse[/font]
-
Sorry. But I don't see how this fits with accessing SSIS variables in tasks in a package. Could you explain further?
The data flow source task (where the SQL query is currently defined) is an OLE DB Source task. If I set the data access mode to SQL command, the Parameters button is enabled, but it's not clear how to pass SSIS variables to the query if it's not a procedure.
How would Iinvoke SQLCMD from SSIS and return the result set as input to the destination?
Derek
-
Oops, I didn't see that this was posted under SSIS. But I think you could still call sqlcmd using the Exec Process Task in SSIS.
But probably the better approach would be using variables in the SQL task. Here you can find a good example http://www.sqlis.com/post/The-Execute-SQL-Task.aspx
[font="Verdana"]Markus Bohse[/font]
-
I've come up with a solution using the Execute SQL Task but it seems a bit of a kludge.
I've changed the SQL query in the task to something like
select year,month,'select f1,f2,f2 '+' from datatable '+
' where scandate > '+convert(char(8),year*10000+month*100)+
' and scandate < '+convert(char(8),year*10000+(month+1)*100) as 'SQL'
from mgtdate
I then pass Year, Month and SQL back as SSIS variables and have changed the source object in the data flow taak to use 'SQL Command from variable' using the value set from SQL above.
I just thought there should be a better way to insert variables into an SQL query used as a data source.
Derek
-
Derek Dongray (2/11/2009)
I've come up with a solution using the Execute SQL Task but it seems a bit of a kludge.I've changed the SQL query in the task to something like
select year,month,'select f1,f2,f2 '+' from datatable '+
' where scandate > '+convert(char(8),year*10000+month*100)+
' and scandate < '+convert(char(8),year*10000+(month+1)*100) as 'SQL'
from mgtdate
I then pass Year, Month and SQL back as SSIS variables and have changed the source object in the data flow taak to use 'SQL Command from variable' using the value set from SQL above.
I just thought there should be a better way to insert variables into an SQL query used as a data source.
Derek,
Yes it is possible. Check this article[/url].
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply