problem when selecting dynamic connection setting

Hello All,

I have a SSIS package, desgined in such a way that, there is a table called connection which hold the connection name i.e similar to my DSN connection name & a ID for each DSN there is a unique ID. Now i want if there is a 10 record in the table & i have only 5 DSN connection then i'll work for 5 & for other 5 it 'll skip the process. my task is running success fully for 1 connection. i have used foeach loop container & 3 varibable for that 2 for holding the table value & 1 for refereing the recordset

thanks

So are you saying that the table holds 10 records but only 5 of them are valid for the particular package that you are currently executing?

Then you need to filter the list of connections that you want to "use". Can you not hardcode that filter per package?

Sorry, I don't really understand the requirement.

-Jamie

|||

take a example

i have a table table1(id,DsnConnectionName,.) table has 20 records. that means there is a information for 20 DSN connection. But my machine has only 5 DSN Connection. That is in that table. i have first i'm collecting these record in a record set then connect that data flow task with foreach loop. i have 2 variable 1 hold the ID & another DSNConnectionName.

in that foreach loop container i have placed a script task which is setting the connection according to the DSN name assign in that variable. but when the dsn connection with that name is not available in my system then i gives error. for that i use try catch but due to this it;'s running & inserting data each time.

|||

Why can you not just create all the required DSNs?

Why would you try andrun something against a DSN that does not exist?

If you're not going to create the DSNs then, like I said before, you need some way of filtering the list of connections that you iterate over.

-Jamie

|||

Thanks for your keen interest in my problem.

I got the solution,

solution is very simple i used try catch mathod & set dts.taskresult = dts.results.failure for error condition

Problem w/ Dynamic Query

I'm having problems constructing a dynamic query on the Data tab in SQL Reporting Services. When I try to use one of the reporting parameters it fails with "The expression for the query â'Dataâ' contains an error: [BC30648] String constants must end with a double quote." I want to be able to append the value of the chain the user selected to the end of the query. Anyone have any ideas.
My query is:
="SELECT {{[Product].[All Product]}*{Descendants([Market].[All Market].[Bob Jones])}} on rows,
{{[Time].[All Time].[2003-01-01 00:00:00].[2003-05-16 00:00:00],
[Time].[All Time].[2004-01-01 00:00:00].[2004-05-16 00:00:00],
[Time].[All Time].[2003-01-01 00:00:00].[2003-06-16 00:00:00],
[Time].[All Time].[2004-01-01 00:00:00].[2004-06-16 00:00:00]}
*{[Dollar Volume]}} on columns FROM " & Parameters!Chain.Value
If I get rid of the parameter and just end the query with a hard coded chain it works.Did you try appending a CStr(Parameters!Chain.Value) instead?
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Karch" <Karch@.discussions.microsoft.com> wrote in message
news:A1A5AB61-FFAC-49C3-A300-290A7C85906C@.microsoft.com...
> I'm having problems constructing a dynamic query on the Data tab in SQL
Reporting Services. When I try to use one of the reporting parameters it
fails with "The expression for the query â'Dataâ' contains an error:
[BC30648] String constants must end with a double quote." I want to be able
to append the value of the chain the user selected to the end of the query.
Anyone have any ideas.
> My query is:
> ="SELECT {{[Product].[All Product]}*{Descendants([Market].[All
Market].[Bob Jones])}} on rows,
> {{[Time].[All Time].[2003-01-01 00:00:00].[2003-05-16 00:00:00],
> [Time].[All Time].[2004-01-01 00:00:00].[2004-05-16 00:00:00],
> [Time].[All Time].[2003-01-01 00:00:00].[2003-06-16 00:00:00],
> [Time].[All Time].[2004-01-01 00:00:00].[2004-06-16 00:00:00]}
> *{[Dollar Volume]}} on columns FROM " & Parameters!Chain.Value
> If I get rid of the parameter and just end the query with a hard coded
chain it works.|||Parameterized MDX queries are not supported by the OleDB provider for OLAP
8.0. Therefore you have to use an expression-based query (in the text-based
generic query designer).
Since you cannot execute expression-based queries directly in the query
designer, you should first design your report based on a MDX query _without_
parameters, detect the fields and design the report.
When you are done, you would convert the MDX query into an expression-based
MDX query (as you tried initially). Before doing that, you might want to add
a textbox somewhere in your report and experiment with the expression till
it evaluates to a valid MDX query and then copy the expression into the
query designer.
You might also want to check out a sample available for download:
http://www.microsoft.com/downloads/details.aspx?FamilyID=f9b6e945-1f4c-4b7c-9c83-c6801f0576ff&DisplayLang=en
Additional information on the integration of RS 2000 and AS 2000 is provided
at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/olapasandrs.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Karch" <Karch@.discussions.microsoft.com> wrote in message
news:AC5B0D16-5C07-4258-AF6C-EC6925180DE0@.microsoft.com...
> I looked at it again. I think the problem is that parameters are not
supported with the OLE DB provider. The report I'm generating is hitting a
cube.
> Query:
> SELECT {{[Product].[All Product]}*{Descendants([Market].[All
Market].[East - Bob Jones])}} on rows, {{[Time].[All Time].[2003-01-01
00:00:00].[2003-05-18 00:00:00], [Time].[All Time].[2004-01-01
00:00:00].[2004-05-16 00:00:00], [Time].[All Time].[2003-01-01
00:00:00].[2003-06-15 00:00:00], [Time].[All Time].[2004-01-01
00:00:00].[2004-06-13 00:00:00]}*{[Dollar Volume]}} on columns FROM @.Chain
> @.Chain is mapped to "=Parameters!Chain.Value" in the DataSet.
> Error:
> Could not generate a list of fields for the query. Check the query syntax
or click Refresh Fields on the query toolbar. The ICommandWithParameters
interface is not supported by the 'MSOLAP.2' provider. Command parameters
are unsupported with the current provider.
>
> "Ravi Mumulla (Microsoft)" wrote:
> > Did you try appending a CStr(Parameters!Chain.Value) instead?
> >
> > --
> > Ravi Mumulla (Microsoft)
> > SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> > "Karch" <Karch@.discussions.microsoft.com> wrote in message
> > news:A1A5AB61-FFAC-49C3-A300-290A7C85906C@.microsoft.com...
> > > I'm having problems constructing a dynamic query on the Data tab in
SQL
> > Reporting Services. When I try to use one of the reporting parameters
it
> > fails with "The expression for the query â?~Dataâ?T contains an error:
> > [BC30648] String constants must end with a double quote." I want to be
able
> > to append the value of the chain the user selected to the end of the
query.
> > Anyone have any ideas.
> > >
> > > My query is:
> > > ="SELECT {{[Product].[All Product]}*{Descendants([Market].[All
> > Market].[Bob Jones])}} on rows,
> > > {{[Time].[All Time].[2003-01-01 00:00:00].[2003-05-16 00:00:00],
> > > [Time].[All Time].[2004-01-01 00:00:00].[2004-05-16 00:00:00],
> > > [Time].[All Time].[2003-01-01 00:00:00].[2003-06-16 00:00:00],
> > > [Time].[All Time].[2004-01-01 00:00:00].[2004-06-16 00:00:00]}
> > > *{[Dollar Volume]}} on columns FROM " & Parameters!Chain.Value
> > >
> > > If I get rid of the parameter and just end the query with a hard coded
> > chain it works.
> >
> >
> >|||First Prize.
Thanks for the assistance. Sure helped me alot
"Robert Bruckner [MSFT]" wrote:
> Parameterized MDX queries are not supported by the OleDB provider for OLAP
> 8.0. Therefore you have to use an expression-based query (in the text-based
> generic query designer).
> Since you cannot execute expression-based queries directly in the query
> designer, you should first design your report based on a MDX query _without_
> parameters, detect the fields and design the report.
> When you are done, you would convert the MDX query into an expression-based
> MDX query (as you tried initially). Before doing that, you might want to add
> a textbox somewhere in your report and experiment with the expression till
> it evaluates to a valid MDX query and then copy the expression into the
> query designer.
> You might also want to check out a sample available for download:
> http://www.microsoft.com/downloads/details.aspx?FamilyID=f9b6e945-1f4c-4b7c-9c83-c6801f0576ff&DisplayLang=en
> Additional information on the integration of RS 2000 and AS 2000 is provided
> at:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/olapasandrs.asp
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Karch" <Karch@.discussions.microsoft.com> wrote in message
> news:AC5B0D16-5C07-4258-AF6C-EC6925180DE0@.microsoft.com...
> > I looked at it again. I think the problem is that parameters are not
> supported with the OLE DB provider. The report I'm generating is hitting a
> cube.
> >
> > Query:
> > SELECT {{[Product].[All Product]}*{Descendants([Market].[All
> Market].[East - Bob Jones])}} on rows, {{[Time].[All Time].[2003-01-01
> 00:00:00].[2003-05-18 00:00:00], [Time].[All Time].[2004-01-01
> 00:00:00].[2004-05-16 00:00:00], [Time].[All Time].[2003-01-01
> 00:00:00].[2003-06-15 00:00:00], [Time].[All Time].[2004-01-01
> 00:00:00].[2004-06-13 00:00:00]}*{[Dollar Volume]}} on columns FROM @.Chain
> >
> > @.Chain is mapped to "=Parameters!Chain.Value" in the DataSet.
> >
> > Error:
> > Could not generate a list of fields for the query. Check the query syntax
> or click Refresh Fields on the query toolbar. The ICommandWithParameters
> interface is not supported by the 'MSOLAP.2' provider. Command parameters
> are unsupported with the current provider.
> >
> >
> >
> > "Ravi Mumulla (Microsoft)" wrote:
> >
> > > Did you try appending a CStr(Parameters!Chain.Value) instead?
> > >
> > > --
> > > Ravi Mumulla (Microsoft)
> > > SQL Server Reporting Services
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > > "Karch" <Karch@.discussions.microsoft.com> wrote in message
> > > news:A1A5AB61-FFAC-49C3-A300-290A7C85906C@.microsoft.com...
> > > > I'm having problems constructing a dynamic query on the Data tab in
> SQL
> > > Reporting Services. When I try to use one of the reporting parameters
> it
> > > fails with "The expression for the query â?~Dataâ?T contains an error:
> > > [BC30648] String constants must end with a double quote." I want to be
> able
> > > to append the value of the chain the user selected to the end of the
> query.
> > > Anyone have any ideas.
> > > >
> > > > My query is:
> > > > ="SELECT {{[Product].[All Product]}*{Descendants([Market].[All
> > > Market].[Bob Jones])}} on rows,
> > > > {{[Time].[All Time].[2003-01-01 00:00:00].[2003-05-16 00:00:00],
> > > > [Time].[All Time].[2004-01-01 00:00:00].[2004-05-16 00:00:00],
> > > > [Time].[All Time].[2003-01-01 00:00:00].[2003-06-16 00:00:00],
> > > > [Time].[All Time].[2004-01-01 00:00:00].[2004-06-16 00:00:00]}
> > > > *{[Dollar Volume]}} on columns FROM " & Parameters!Chain.Value
> > > >
> > > > If I get rid of the parameter and just end the query with a hard coded
> > > chain it works.
> > >
> > >
> > >
>
>