Prev: Correct Way to Insert into Multiple Tables -Materialised
Next: SQL2005 Express - where is the problem?
From: Gloria on 16 Jul 2008 11:55
To All,

I am trying to get a parameterized query to run in a ssis package.
I ran the dtsx file with hard-coded values and the query and package work.
The package exports data from a database into an Excel file.

My hope is that users can double-click on the .dtsx file and get the Execute
Package Utility Dialog Box. They can set the value of the parameter in the
'Set Values' option and run the query.

If anyone knows of any documentation to do this or has done it themselves, I
would appreciate some info.

Thanks,
Gloria
From: jonock14 on 16 Jul 2008 13:03
On Jul 16, 10:55 am, Gloria <Glo...(a)discussions.microsoft.com> wrote:
> To All,
>
> I am trying to get a parameterized query to run in a ssis package.
> I ran the dtsx file with hard-coded values and the query and package work..
> The package exports data from a database into an Excel file.
>
> My hope is that users can double-click on the .dtsx file and get the Execute
> Package Utility Dialog Box.  They can set the value of the parameter in the
> 'Set Values' option and run the query.
>
> If anyone knows of any documentation to do this or has done it themselves, I
> would appreciate some info.
>
> Thanks,
> Gloria

Gloria,

It sounds like there are two parts to the problem. One is trying to
run a parameterized query in an SSIS package. I'm going to assume
that you're using an Execute SQL Task. So running the parameterized
query in the SQL task would mean you had something like this in the
SQLStatement: select * from dbo.Tbl_Example where active_date = ?

Then, you would parameterize this by substituting the "?" with a
variable. To do that, select the Parameter Mapping option on the
left, add a variable, and select a variable from the drop down list.
Make sure the "Parameter Name" option gets set to 0 for the first
parameter in a SQL query, 1 for the second, and so on.

There I assumed that you have a variable created to serve as the
parameter. Now in order to set this variable from the Exec Package
Utility, you'd add a like like this in the "Set Values" option:
\Package.Variables[PassInDate].Value under Property Path, and the
correspoding value.

Hope this helps!
From: Gloria on 16 Jul 2008 14:29


"jonock14(a)gmail.com" wrote:

> On Jul 16, 10:55 am, Gloria <Glo...(a)discussions.microsoft.com> wrote:
> > To All,
> >
> > I am trying to get a parameterized query to run in a ssis package.
> > I ran the dtsx file with hard-coded values and the query and package work..
> > The package exports data from a database into an Excel file.
> >
> > My hope is that users can double-click on the .dtsx file and get the Execute
> > Package Utility Dialog Box. They can set the value of the parameter in the
> > 'Set Values' option and run the query.
> >
> > If anyone knows of any documentation to do this or has done it themselves, I
> > would appreciate some info.
> >
> > Thanks,
> > Gloria
>
> Gloria,
>
> It sounds like there are two parts to the problem. One is trying to
> run a parameterized query in an SSIS package. I'm going to assume
> that you're using an Execute SQL Task. So running the parameterized
> query in the SQL task would mean you had something like this in the
> SQLStatement: select * from dbo.Tbl_Example where active_date = ?
>
> Then, you would parameterize this by substituting the "?" with a
> variable. To do that, select the Parameter Mapping option on the
> left, add a variable, and select a variable from the drop down list.
> Make sure the "Parameter Name" option gets set to 0 for the first
> parameter in a SQL query, 1 for the second, and so on.
>
> There I assumed that you have a variable created to serve as the
> parameter. Now in order to set this variable from the Exec Package
> Utility, you'd add a like like this in the "Set Values" option:
> \Package.Variables[PassInDate].Value under Property Path, and the
> correspoding value.
>
> Hope this helps!
>

YES...That was very helpful.
I got as far as the 'Set Values' part.
The Property Path and Values inputs are not accepting my inputs.
If the package name is PackName and my variable that I created is Parameter
is 0 and under 'Variables' in my Integration Service project is User::VarName,
then the Property Path should be:
\PackName.Variables[User::VarName].Value ?
(I tried several variations of this and still no luck...such as
\Package.Variables[VarName.VarName]
\PackName.Variables[User::VarName].
The Value is the value I want to pass in.
I always get the error: Cannot not set value.

Thanks,
Gloria
From: Gloria on 16 Jul 2008 15:06


"Gloria" wrote:

>
>
> "jonock14(a)gmail.com" wrote:
>
> > On Jul 16, 10:55 am, Gloria <Glo...(a)discussions.microsoft.com> wrote:
> > > To All,
> > >
> > > I am trying to get a parameterized query to run in a ssis package.
> > > I ran the dtsx file with hard-coded values and the query and package work..
> > > The package exports data from a database into an Excel file.
> > >
> > > My hope is that users can double-click on the .dtsx file and get the Execute
> > > Package Utility Dialog Box. They can set the value of the parameter in the
> > > 'Set Values' option and run the query.
> > >
> > > If anyone knows of any documentation to do this or has done it themselves, I
> > > would appreciate some info.
> > >
> > > Thanks,
> > > Gloria
> >
> > Gloria,
> >
> > It sounds like there are two parts to the problem. One is trying to
> > run a parameterized query in an SSIS package. I'm going to assume
> > that you're using an Execute SQL Task. So running the parameterized
> > query in the SQL task would mean you had something like this in the
> > SQLStatement: select * from dbo.Tbl_Example where active_date = ?
> >
> > Then, you would parameterize this by substituting the "?" with a
> > variable. To do that, select the Parameter Mapping option on the
> > left, add a variable, and select a variable from the drop down list.
> > Make sure the "Parameter Name" option gets set to 0 for the first
> > parameter in a SQL query, 1 for the second, and so on.
> >
> > There I assumed that you have a variable created to serve as the
> > parameter. Now in order to set this variable from the Exec Package
> > Utility, you'd add a like like this in the "Set Values" option:
> > \Package.Variables[PassInDate].Value under Property Path, and the
> > correspoding value.
> >
> > Hope this helps!
> >
>
> YES...That was very helpful.
> I got as far as the 'Set Values' part.
> The Property Path and Values inputs are not accepting my inputs.
> If the package name is PackName and my variable that I created is Parameter
> is 0 and under 'Variables' in my Integration Service project is User::VarName,
> then the Property Path should be:
> \PackName.Variables[User::VarName].Value ?
> (I tried several variations of this and still no luck...such as
> \Package.Variables[VarName.VarName]
> \PackName.Variables[User::VarName].
> The Value is the value I want to pass in.
> I always get the error: Cannot not set value.
>
> Thanks,
> Gloria
When I preview the SQL query in the OLE DB Source Editor, I get the error:
No value for one or more required parameters.
I set a value in for the parameter. It is a string. I tried using single
quotes and none at all and still get the error.
 | 
Pages: 1
Prev: Correct Way to Insert into Multiple Tables -Materialised
Next: SQL2005 Express - where is the problem?