June 19, 2012 at 4:22 pm
Hello there,
I'm currently in the process of designing a SSIS package that needs to run in both DEV and Prod. As such, I've setup a database table that contains all connection strings for all of my participating data sources.
The main issue I have is the inability to define/set a connection dynamically for any SSAS processing tasks. When I run my package, I can see that the SSIS variables I've defined at server level get the correct values. However, the actual processing steps fails with a "invalid connection format" message.
Has anyone been able to solve this in the past? I'm running this against SQL Server 2008 R2...
I can attach screenshots or more relevant information upon request.
Thanks guys!
June 22, 2012 at 4:48 am
this isn't really solving your problem...
but I use "localhost" as the database name in my SSAS processing tasks...
June 22, 2012 at 6:54 am
I worked around it - you can set the connection name inside the package to a dynamic setting. The downside to that is that you have to hard-code all SSAS connections in your SSIS package.
June 25, 2012 at 9:04 am
jchernev (6/22/2012)
I worked around it - you can set the connection name inside the package to a dynamic setting. The downside to that is that you have to hard-code all SSAS connections in your SSIS package.
jc
That is not strictly true.
You can set runtime variables using expressions in ssis for pretty much most control flow items including the Analysis services Processing Task. On the Analysis Services Processing Task Editor ==> Click on the Expressions ==> then you get a dialogbox that presents the options you can set as expressions including the 'ConnectionName'.:-)
June 25, 2012 at 9:08 am
Hey Akin,
Thanks for your reply.
Have you actually gotten a SSIS package containing a "SSAS Processing Task" that gets its either "Connection String" or "Data Source" parameter dynamically assigned to work with said suggestion? I'd be curious to see an actual package that does that...
Thanks!
June 25, 2012 at 9:50 am
See attachment.
This is a screenshot of a package (thanks denglishbi) that attempts to dynamically process only dimensions that have modified attributes. So you use a 'SSAS EXECUTE DDL TASK'. Using this control flow item you will then set your DDL source as a variable. All elements of the DDL can be set as a runtime expressions stored in vaiables that are fed into the DDL code.
the below code uses 4 user defined variables that are automatically set at runtime and even change dynamically during the execution of the DDL task using a 'ForEach loop' container. it's simple enough to modify this to change connections using this method. 🙂
Example:
"<Batch xmlns=\"http://schemas.microsoft.com/analysisservices/2003/engine\">
<Parallel>
<Process xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\">
<Object>
<DatabaseID>" + RTRIM(LTRIM(@[User::ASDatabase])) + "</DatabaseID>
<CubeID>" + RTRIM(LTRIM(@[User::ASCube])) + "</CubeID>
<MeasureGroupID>" + RTRIM(LTRIM(@[User::ASMeasureGroup])) + "</MeasureGroupID>
<PartitionID>" + RTRIM(LTRIM(@[User::ASPartition])) + "</PartitionID> </Object>
<Type>ProcessDefault</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>
</Parallel>
</Batch>
"
June 25, 2012 at 9:52 am
akin.akinwumi (6/25/2012)
See attachment.This is a screenshot of a package (thanks denglishbi) that attempts to dynamically process only dimensions that have modified attributes. So you use a 'SSAS EXECUTE DDL TASK'. Using this control flow item you will then set your DDL source as a variable. All elements of the DDL can be set as a runtime expressions stored in vaiables that are fed into the DDL code.
the below code uses 4 user defined variables that are automatically set at runtime and even change dynamically during the execution of the DDL task using a 'ForEach loop' container. it's simple enough to modify this to change connections using this method. 🙂
Example:
"<Batch xmlns=\"http://schemas.microsoft.com/analysisservices/2003/engine\">
<Parallel>
<Process xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\">
<Object>
<DatabaseID>" + RTRIM(LTRIM(@[User::ASDatabase])) + "</DatabaseID>
<CubeID>" + RTRIM(LTRIM(@[User::ASCube])) + "</CubeID>
<MeasureGroupID>" + RTRIM(LTRIM(@[User::ASMeasureGroup])) + "</MeasureGroupID>
<PartitionID>" + RTRIM(LTRIM(@[User::ASPartition])) + "</PartitionID> </Object>
<Type>ProcessDefault</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>
</Parallel>
</Batch>
"
At last see attachement: 😉
June 25, 2012 at 9:57 am
Thanks for the detailed post - this solution solves a different problem.
The one stated in this thread is how to dynamically set steps to use different connections. In your screenshot, that would be the "Connection" parameter (currently statically assigned to 'AS Server').
I know how to dynamically assign connection names (because that seems to work). The only limitation here is that you need to statically define that connection manager in you SSIS package with the DataSource parameter explicitly stated. It doesn't seem that you can modify this connection parameter on the fly with any of the SSAS steps/tasks. You could however use AMO and script everything dynamically and that would work. The problem? This isn't as straight-forward for less-technical people and the learning curve is somewhat higher.
Compare/Contrast this limitation to OLEDB connections where you could retrieve the full connection string from a database table and have your connection be completely defined outside of the scope of your SSIS package.
June 26, 2012 at 1:09 am
jchernev (6/19/2012)
Hello there,I'm currently in the process of designing a SSIS package that needs to run in both DEV and Prod. As such, I've setup a database table that contains all connection strings for all of my participating data sources.
The main issue I have is the inability to define/set a connection dynamically for any SSAS processing tasks. When I run my package, I can see that the SSIS variables I've defined at server level get the correct values. However, the actual processing steps fails with a "invalid connection format" message.
Has anyone been able to solve this in the past? I'm running this against SQL Server 2008 R2...
I can attach screenshots or more relevant information upon request.
Thanks guys!
Try to use package configurations.
Please follow below URL
http://msdn.microsoft.com/en-us/library/ms141682.aspx
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply