Home » MS Office

How to make up MDX with parameters of SSIS OLEDB data source

Hi everyone,

I want to make up a MDX query by using some parameters, just like:

{...}ON ROWS
FROM [CubeName]

I put this query in the 'sql command' field of OLEDB data source, and replaced the date parts with parameter token '?':

{...}ON ROWS
FROM [CubeName]

However, when I clicked 'parameter' button, the exception below showed:

Parameters cannot be extracted from the SQL command. The provider might not help to parse parameter information from the command. In that case, use the "SQL command from variable" access mode, in which the entire SQL command is stored in a variable. (Microsoft Visual Studio)
Error Code = 0x80040E51, External Code = 0x00000000:. (Microsoft OLE DB Provider for Analysis Services 2008.)


3 Answers Found


Answer 1


You can use a variable to store the MDX query and then pass parameters to it. Try these steps:

 Create three variables of type of your parameter.(V_Date,V_time,V_year)

Initialize these variables by using an Execute Sql task and use a result set to initialize these variables.

 Create a variable V_MDX of string datatype and then in the properties of the variable use expression and paste the MDX query inside it, something like

[Date].[Calender].[Day].&["+ ' V_Date '+ "]&["+ ' V_Time '+ "]&["+ ' V_Year '+ "]
{...}ON ROWS
FROM [CubeName]

Now use this variable as a source in the OLEDB source drop down.

Note: I haven't tested this myself but sure will work , the expression provided might have some syntax issues, check it out.

Answer 2


I have a similar task where : I want to retrieve top 25 author names with their count in previous month. I have written a query and in where clause



([Measures].[Author : Count]) ONCOLUMNS, TopCount(order([Author - Name].members, [Measures].[Author : Count],DESC),26) onrowsfrom [Cube] where ([Company].[Company].[CompName],[Date].[Year].[Year],[Date].[Month In Year].[Month In Year].&[6])


How to make it dynamic to replace the month value in time dimension.



Answer 3


Do you now if is possible to use "WITH MEMBER" in a MDX query using SSIS?


I ask you this, becausse when I Try to use the following MDX query :

MEMBER [Measures].[FechaInicial] as '([Periodo].[Año-Semana-Día].[Year].&[2010-01-01T00:00:00].&[2010-09-09T00:00:00].&[2010-09-15T00:00:00],[Measures].[Cant Servicios])'
MEMBER [Measures].[FechaFinal] as '([Periodo].[Año-Semana-Día].[Year].&[2010-01-01T00:00:00].&[2010-09-16T00:00:00].&[2010-09-22T00:00:00],[Measures].[Cant Servicios])'
({   [Measures].[FechaInicial], [Measures].[FechaFinal]
})ON COLUMNS , ...... etc.etc...

this raise the following error, when I press button "Parse Query":

"The Measures hierarchy already appears in the Axis0 axis."








I have an Integration Services project which creates a flat file report from Analysis Services, I'm using an OLE DB as data source and running an Openquery in the SQL statement.

the problem is that Integration services runs the query twice before getting the data into the flat file. I know this because the query runs two times in Profiler, and because the same query takes half the time when run in Management Studio.

Integration Services is running the whole query when validating. how can I disable this validation or better make it validate properly.



Hi i am using some operation from Execute SQL Task aftre that i am getting few rows with few column, aftre Execute SQL task i am calling OLE DB source in Data flow task, i want all records which i got from Execute SQL task in OLE DB Data source, so i can generate flat file.

my purpose to generate flat file using OLE DB source, i had calculation part so i am using Execute SQL task and aftre that calling ole DB source

Thanks and Regards




I am using SSIS 2008 to connect the Sybase Server version 15.0.3 64-bit. The default character set id used by this Sybase server is 4 (i.e. roman8). (http://manuals.sybase.com/onlinebooks/group-charc/chg0300e/charsets/@Generic__BookTextView/1706;pt=266)

I had installed Sybase Client for Sybase OLEDB Driver (ASEOLEDB) to integrate this in OLEDB Source Editor.

As per guidance from one of the past MSDN forum discussion, I tried setting property of AlwaysUseDefaultCodePage property to "FALSE"  (http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/f5af9328-e1fb-48d7-a85d-1d08bb7cf0e5/).

However, I am still experiencing error message as follows:-


Error at Package2 [Connection manager "xxxxxx.xxxxxxx"]: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "ASEOLEDB"  Hresult: 0x80004005  Description: "[00000]
[Native Code: 30061]
[ASEOLEDB]Could not load code page for requested charset".
An OLE DB record is available.  Source: "ASEOLEDB"  Hresult: 0x80004005  Description: "[1ZZ001]
[Native Code: 30016]
[ASEOLEDB]Internal Error".

Error at Data Flow Task 2 [OLE DB Source [1]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "xxxxxxxxxx.xxxxxxxx" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.



Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)


Please advise if anyone has encountered similar issue and found any resolution on this.

Appreciate your help....







I have a package that I developed in SSIS 2005 and recently ported to 2008. Everything runs great except an OLE DB Command component that calls a stored procedure that uses output parameters.

When I try to run this, I get this error:

Error: 0xC0202009 at Data Flow Task, OLE DB Command [100]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Syntax error, permission violation, or other nonspecific error".
Error: 0xC004701A at Data Flow Task, SSIS.Pipeline: component "OLE DB Command" (100) failed the pre-execute phase and returned error code 0xC0202009.

When I do the same thing in SSIS2005, it works fine.

So I setup a new package with 1 data flow. In that data flow, I added a source component, an OLE DB Command, and a destination.  I then tested this with a stored procedure that took no parameters. It worked fine.

I then tested it with a stored procedure with 1 parameter defined as OUTPUT. This generated the error.


Is there an issue with output parameters in an OLE DB Command in SSIS 2008?





we are using SSIS 2005 and Sql server 2005. My stored proc has 3 parameter. 2 date parameters and 1 varchar parameter. The varchar parameter is set to null as default like this:

Alter procedure uspSPName







If @pCode is null, I need to return all the records from a table. If it is not null then where @pCode = 'value'.

In SSIS package, I have 3 variables created for these 3 parameters. I am calling this stored proc in SSIS package - OLEDB Source task like this:

EXEC uspSPName ?, ?, ?

When pCode variable (string type) is left blank, package runs successfully and nothing is returned. If I put in a value, then returning data.

I read in forums that, when it is left blank it is sending charcater(0) as value. That is why not returning value.

How can I handle null value parameters.

Once this works fine, I need to call it from stored procedure using dtexec.

Right now I am doing this:



@CMD='dtexec '+


'/FILE '+ @pShareName + @pPackageName +' '+








'/SET "\Package.Variables[User::StartDate].Properties[Value]";'+


CONVERT(char(10), @pStartDate, 120)+' '+


'/SET "\Package.Variables[User::EndDate].Properties[Value]";'+


CONVERT(char(10), @pEndDate, 120)


IF(@pCode <>NULL)




SET @CMD = @CMD +' '+


'/SET "\Package.Variables[User::PCode].Properties[Value]";'+





I just showed the part of variable.



@pReturnCode = master%


I'm trying to configure SSRS 2008 SP1 for unattended execution - required for scheduled report subscriptions.  I've configured the SSRS unattended execution account and granted that account the appropriate permissions in my SQL and SSAS databases.  I've changed the data sources used by the report (all are shared data sources) to "Credentials not required" configuration.

This works for SQL based data sources, but fails for SSAS based data sources, the error indicating that the requestor does not have permission in the SSAS database.  Note that the error mentions the requestor - NOT the SSRS unattended execution account as expected.  

The only work-around I've found so far is to change the SSAS data sources to use "Credentials stored securely on the report server", setting the credentials to the same used by the SSRS unattended execution account.

What's the correct way to get SSAS-based reports to run unattended?

In case it matters, the connection strings used by the SSAS data sources look like this:


Data Source=.;Initial Catalog=MyDatabaseName;Integrated Security=SSPI;Application Name="My App Name"




I wish to use a SSIS Xml Source Data Flow Source (tried both in 2005/2008) to import an xml file and its XSD schema. When I do I get the following error when I click on the columns property:

"Error at Data Flow Task [XML Source [1]]: The XML Source Adapter was unable to process the XML data.
The content of an element can not be declared as anyType

The snippet of the XSD that contains this definition is the following:

 <xs:element name="custom" type="xs:anyType" minOccurs="0"> <xs:annotation> <xs:documentation>Extensibility point that can contain arbitary content. Expected to contain additional elements and attributes for data not captured within this schema</xs:documentation> </xs:annotation> </xs:element>

The XSD is not owned by my department and having to change it would take some bureaucracy.

Are there any suggestions to get around this problem without having to modify that schema in anyway?


Thank you very much in advanced.


I created an ssis package to populate a SharePoint list (data destination) with data from a FoxPro database (data source).

I can run it in the development environment but each time the rows are appended to the list, making duplicates.

I want to run this automatically as a SQL Agent Job and the data must updated in the SharePoint list - NOT appended.

Can I get some feedback on where I need to focus my research on this?

Hi ,
Darren has already written an article on how to insert records into MS CRM using Webservice and SSIS , that clearly explains about the one time data load.

Is there a way to build on this and do a real-time data sync between MS CRM 4.0 and other data source(Text file ,any other RDBMS) .

If we can do this how can we kick start this package when a change occurs in MS CRM 4.0 , will that be through SQL Jobs or other means , will there be any issues.

After doing much research on this online , I found that their is very little written about this except Darren's article.

If anyone has any idea about this or  implemented this and was successfull or attempted it I found some issues ........Please share.

Thank You



SSIS seems to have two types of data sources, one is ADO.NET source and other is OLEDB.

What is the difference.... why can't we have just one type of data source like JDBC (from java world)?


Good day, i need help.

I have a few variables, so in a dataflow task i have a few OELDB sources that i would like to within the dataflow update the variable

I do not want to update by using rowcount, i need to update the variable with the result of the OLEDB Sources

Is this possible, if so how please?


I have a OLEDB  Source that uses a table variable to return results. When I click 'preview', data is displayed correctly in the preview window with no errors or warnings; however, when I run the package no rows are returned. Is there a better way to do this?  (I cannot use temporary tables). The query looks something like this:

declare @result TABLE (
 Col1 int, 
 Col2 int)

select cast (null as int) as Col1, cast (null as int) as Col2;
-- lots off code to get data into table variable
-- ...
select cast (Col1 as int) as Col1, cast (Col2 as int) as Col2 from @result


I want to load a table with a OLE DB Source by a SQL command which shrinks the data to be loaded to a given time interval. Example:

    SELECT *
      FROM myTable
    WHERE loadDate = startDate

The value of startDate should be retrieved from another table containing this value for sevaral tables to be loaded by ETL processes. The metadata of this 'control table' looks like:

    CREATE TABLE control (
        tablename VARCHAR(100),
        startDate DATE)

I have implemented a stored procedure on SQL SERVER returning this startDate (as datatype DATE)  for a given tablename. This function alone works very well. Now I'm looking for a good way to pass startDate into the OEL DB Source above. My idea was to use a SQLTask which is calling the stored procedure and stores the resultset into a variable of type datetime. But this throws following exception:

"0xC002F309 at Execute SQL Task ... "The type of the value being assigned to variable "User::startDate" differs from the current variable type...."

Setting of the SQLTask:
ResultSet Type:    single row
ResultSet:    0, user::startDate
no Parameters
SQL-Statement:    SELECT dbo.f_get_startDate('Test')

My questions:
1) Why does the DATE result of the function not fit into a DATETIME variable?
2) Is it possible to assign the function result directly to a variable without a SQLTask?
3) Is there a better way to set startDate in the OEL DB source above?

Thank you in advance!

Are they joking?

As from now as a developer, whenever you need to read data from an ODBC source (and thus your only option is to use the ADO NET Source component in a data flow task - no ODBC data source anymore), you are in big trouble when you want to use ugly beasts like parameters. Parameters? Who needs them anyway they must have been thinking at Microsoft SSIS development team.

In 2005 you had the option "SQL from variable" in combination with expressions, which was a quick and dirty way to force parameter insertion, but now, since they just gave up on that option, you're in big ... as a developer.

I hope I am terribly wrong here, could somebody please help me out on how to pass parameters to an ODBC data source in SSIS 2008??

Thanks in advance,
A frustrated Tom with a deadline in mind
Tom De Cort - BI Consultant


   when do we really use this option "Impersonate the authenticated user after a connection has been made to the data source".I am using database authentication for a data source and I see the report.But when i check this above impersonate option,I am getting error.Can someone answer...




I am using a data bound generator that takes its data from a server defined by:

Data Source=MyServer\InstanceName;Initial Catalog=DBName;Integrated Security=True;Pooling=False;

There is a problem with this however, the data may not always reside on [MyServer\InstanceName] so I want to make that dynamic. More accurately, I want it to be something I can specify from my CI build. Is there any way to do that?



http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
Jamie Thomson

Hi all,

I have four Lists with identical columns :

List 1
List 2
List 3
List 4

I had to merge them, hence i created a data source ( Linked Source ) and merged these lists in SPD. I have used a data view webpart to display some columns from the merged lists data source and everything was working fine.

But now i have a new List : List 5 which also has the same columns like the other four lists and i need to update the data soure such that it is merged list of 5 lists not 4.

i updated the datasource property of the linked source to merge this new list as well. However the contents of List 5 dont appear in the data view webpart. 

Am i missing something here ? 

I dont want recreate the data view web part from scratch eachtime , because in future there might be List 6 , List 7 as well ... 

Does Report Builder 3 support SSIS data sources? If it does, how do you enable it?

I am trying to use the XML source task in SSIS to import xml data into a  table. Below is the XML.   I have 3 options  

1. XML file location -- Retrieve data from an XML file.
2. XML file from variable -- Specify the XML file name in a variable.

3. XML data from variable -- Retrieve XML data from a variable.

Option 1 is working for me but I want do achieve the same using option 3. I declared a package variable and selected a datatype as string and tried set the value to xml data below , but it is throwing an error, any suggestions would be appreciated

<FILEGROUP FileGroupName="NICE">
  <COLUMN ColumnName="LAST_NAME">
    <ColumnDelimiter />
    <ColumnType>FIXED WIDTH</ColumnType>
  <COLUMN ColumnName="FIRST_NAME">
    <ColumnDelimiter />
    <ColumnType>FIXED WIDTH</ColumnType>
  <COLUMN ColumnName="CITY">
    <ColumnDelimiter />
    <ColumnType>FIXED WIDTH</ColumnType>
  <COLUMN ColumnName="STATE">
    <ColumnDelimiter />
    <ColumnType>FIXED WIDTH</ColumnType>
  <COLUMN ColumnName="ZIP_CODE">
    <ColumnDelimiter />
    <ColumnType>FIXED WIDTH</ColumnType>


Hi All!


I'm loading a huge amount of rows from an SSIS datasource. But for further processing I need chunks of max 10000 rows to save into a destination. After having saved a chunk, I have to do anything else and then return to prcoess the next chunk of data.

Can anyone point me in a good direction? Maybe a solution with dataset variables or something...


Thanks in advance for your help.







<< Previous      Next >>

Microsoft   |   Windows   |   Visual Studio   |   Sharepoint   |   Azure