December 15, 2011 at 8:51 am
Hi Everybody,
Hope all is well
I have created an SSIS package that pulls a column from a Sql Server 2008 database and outputs it to a flat file and attaches the flat file and sends out an email. I have created a SQL Server job to execute the package. So far so good. But now I want to pass input parameters to the select statement in OLEDB source.
The data access mode is: SQL Command. This is my query:
select Column from DBName.dbo.Tablename with (nolock) where Timestamp between ? and ?
and in variables tab of the parameters button I have both the variables as
User::Variable
User::Variable1
Now, when I execute the package I am getting an error. Can someone please tell me how to pass variables to this package as I am running this through a sql job.
Thanks much for your help.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
December 15, 2011 at 9:01 am
The link below should give you a good idea of how to resolve things:
_____________________________________________________________________
Disclaimer - The opinions expressed by the mouth are not necessarily those of the brain
December 15, 2011 at 10:17 am
What ParameterNames are you using for the variables?
December 15, 2011 at 10:54 am
What ParameterNames are you using for the variables?
fromtimestamp, totimestamp they are of type datetime.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
December 15, 2011 at 11:04 am
Also, at the time of adding variables in the "set query parameters" dialogue box for the type datetime, the "Add variable" dialogue box requests me to enter a value for the datetime type. But if I specify a value it returns me the output for these values only, Instead I want to specify the 2 input parameters when I run the SQL Job.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
December 15, 2011 at 12:01 pm
Sapen (12/15/2011)
What ParameterNames are you using for the variables?
fromtimestamp, totimestamp they are of type datetime.
That's your problem. The Execute SQL task doesn't allow actual string names to be used for ParameterName. It wants the position of the parameter within the SQL string.
So "Between ? and ?" is positions 0 and 1. If I'm reading your names correctly, the variable that maps to fromtimestamp should map to 0 and the variable that maps to totimestamp should map to 1.
Change your ParameterNames to 0 and 1 accordingly and it should work.
EDIT: Hang on. I just realized I had the wrong task. Give me a moment and I'll verify my reply.
December 15, 2011 at 12:05 pm
Okay, the Parameters box of the OLE DB Source has Parameters and Variables. Parameter0 should map to the variable that is the first date and Parameter1 should map to the variable with the last date.
And yes, you have to specify the date when you create the variables. That's normal. But they can be made dynamic. You just have to decide what part of the process changes or determines the dates.
Can you tell me, in English, how these dates should be determined?
December 15, 2011 at 12:06 pm
I am not using the execute sql here. The package selects the data and outputs to a flatfile. I am using the OLEDB Source and Flat file destination. I am able to get the output. But right now I have to change the xml config file with the 2 new timestamp inputs and then run the sql job. I was looking for a way if we can pass the input from the sql job itself instead of editing the XML config.
Thanks much for your input.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
December 15, 2011 at 12:07 pm
Can you tell me, in English, how these dates should be determined?
its on demand basis.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
December 15, 2011 at 12:48 pm
Sapen (12/15/2011)
..right now I have to change the xml config file with the 2 new timestamp inputs and then run the sql job. I was looking for a way if we can pass the input from the sql job itself instead of editing the XML config.
That's part of what I what I was looking for in the "English" explanation.
Now, how do you derive these "on demand" dates? Is one date always 2 weeks after the other? Is "today" or "yesterday" always used as the end date?
December 15, 2011 at 1:19 pm
That depends again. The difference could be anytime between a day to 15 minutes. So Parameter1 takes 2011-12-15 10:30:00 and parameter2 takes 2011-12-15 15:00:00. In this case the difference is 4 and half hours. Let me know if I am clear.
Thanks again
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
December 16, 2011 at 5:08 am
Then my answer is no. You have to use (and update) some sort of configuration setting (XML, Table Config, etc.) for the package in order to get this to work.
If there were a constant, you could put it into an expression and make it automatically dynamic. But it doesn't sound like that's the case here.
December 16, 2011 at 8:01 am
Thanks. I am using a XML config for now. Everytime i get a request i am entering the new values and running the job.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply