I have SSIS packages that use some configuration details (connection strings, email address, stored procedure names) stored in a sql table and this is working. Currently, some variables (xmla scripts for creating and processing dimensions and partitions) are not stored in the table, they are embeded in the package. And any time there is a need to modify the scripts, it normally involve the whole package. In am attempt to make it easier for maintenance, I wanted to store all the xmla scripts in the configuration table. This is where I am having problems. Some scripts have up to 20,000 characters but the columns in the configuration table have datatype nvarchar(n) where I can increase the n to 4000. But this is not up to the 20,000 characters I intend to store. I have tried using nvarchar(max) and varchar(max) but its not working. Please, is it that ssis doesn't use nvarchar(max) and varchar(max)? is there any other method I can use to correct the problem. I am aware of storing the scripts in a file and using environment variables. But the server team does not support this. Please any help will be appreciated.

Why would you need Environment Variables? Can you not just store your scripts in text files like you suggested, then in the Configuration table, put in a pointer to the file location. That way you can work on developing and desinging a new script alongside an existing one, then when it's done, all you need to do is change the pointer in the Config table. I'm not 100% up on XMLA for Dimension Processing. How exactly are they used? What Task would use them? If you loaded the pointer into an SSIS variable via standard Configurations, could you not set the ConnectionString property of the text file based on the Variable?Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

Storing the scripts in a file and then using a pointer in the table to indictate the location of the file was my previous design. But because of operational difficulty and security issue, it was abandoned. The reason is the server infrastructure team is different from the DBA team. It will involve going through the process of seeking approval from the server team to create a location/folder, storing the file and maintenance but the DBA team is not ready to do this, especially when access to the file is not in their control. That is why I decided to have everything stored in the sql table. I'm not 100% up on XMLA for Dimension Processing: I have a script for dimension processing and the task that runs this script is "Analysis Services Execute DDL Task".

Well you can create you own table to hold this XMLA script . On runtime write a SP to get the data and bind it to variables you dont have to use the config table always. Another other is store the XMLA in a folder and configer the folder path and file names in config table.Ashwani Roy Blog Twitter Please click the Mark as Answer button if a post solves your problem!

Roy, What I am doing is to store the xmla in a table. But the datatype I am using is nvarchar. I guess this has a limit of 4000 but the characters in the script is far more than 4000.

Use NVARHCAR(MAX)Ashwani Roy Blog Twitter Please click the Mark as Answer button if a post solves your problem!

I have tried it before. Whenever I change the column of the 'ConfiguredValue' to nvarchar(max) datatype, the package fails to load. It comes up with error: Microsoft Visual studio is unable to load this document: The server threw an exception (Exception from HRESULT: 0X80010105(RPC_E_SERVERFAULT))

I think you're going to have to 'roll you own' configuration, but only for this instance. (Stick with built-in Configurations for everything else). Try creating your own table in the Config db, and include an ID column and a VARCHAR(MAX) column. Put your XMLA data in the latter. Use an Execute SQL statement to retrieve it: SELECT [XMLA data] FROM [MyXMLA Config table] WHERE [ID] = <something> use a Single Relust set and get it into the varaible in the package.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

I tried your suggestion but at the point assigning the resultset fronmm the sql statement to ssis variable, I got the following error: Execute SQL Task: An error occurred while assigning a value to variable "SSIS_VAR": "The type of the value being assigned to variable "User::SSIS_VAR" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. Please, any reason am getting this error? SSIS_VAR variable is declared as String.

I get a feeling that you are trying to assign more than one row to the a variable to string type. Check if your result set property is set to single row and that you assign one column of the single row result set to this variable. Here is a good blog post By Darren Green to explain how to assign value to a variable using execute SQL task http://www.sqlis.com/post/The-Execute-SQL-Task.aspxAshwani Roy Blog Twitter Please click the Mark as Answer button if a post solves your problem!

This sounds more like the max characters available for an expression than anything else. You can have, store and assign strings of any length (varchar or varchar). However, you can only create an expression based on a string of 4000 (uni-code / WSTR) or 8000 (STR). One way that you can potentially look at performing this operation is to write this information out to a temp file and use the temp file as your source for the execute sql statement. hope this helps.Please mark answered posts. Thanks for your time.