custome code - global?

Is the custom code you write in the Properties section of one report
accessible to other reports within the same project? if so, how is it
accessed?
Thanks,
PMNo it is not. Consider code behind reports. If you want to do this you have
to use custom assemblies. I.e. create the code outside of RS and then
reference it from RS.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"PacMan" <PacMan@.discussions.microsoft.com> wrote in message
news:32547C22-21CA-4EB6-9482-6DDB4A21A630@.microsoft.com...
> Is the custom code you write in the Properties section of one report
> accessible to other reports within the same project? if so, how is it
> accessed?
> Thanks,
> PM

Custom Task: How to access/modify the variables of a package

Hi,

I'm trying to simplify the deployment process of my project. I already had some troubles with the config files but lets say I solved that issue. I'm going to read a flat file and set the variables of my packages from this file. I was thinking to use a Script Task to do that but I will need to copy this task in every package (I have at least 30). So if I want to make some change this will be painful.

Then, I came up with the idea of creating a Custom Task called Config File Task. I'm working on this but I got stuck trying to get the variables from the package that is running my Config File Task.

This is the code I had in the Script Task:

Dim streamReader As New StreamReader(Dts.Variables("ConfigFilePath").Value.ToString)
Dim line As String
Dim lineArray As String()
Dim variableName As String
Dim variableValue As String
Dim readConfigurations As Boolean = False

While (streamReader.Peek() <> -1)
line = streamReader.ReadLine()
If line = "[CONFIGURATIONS]" Then
readConfigurations = True
ElseIf line = "[/CONFIGURATIONS]" Then
readConfigurations = False
Else
If readConfigurations And line <> "" Then
lineArray = line.Split("|".ToCharArray())
variableName = lineArray(0).Trim()
variableValue = lineArray(1).Trim()

If Dts.Variables.Contains(variableName) Then
Dts.Variables(variableName).Value = variableValue
End If
End If
End If
End While
Dts.TaskResult = Dts.Results.Success

All I want to do is set the variables that exists in each package from the config file. In my UI Class (ConfigFileTaskUI.cs) I can have access to the variables via the TaskHost which is passed as an argument of Initialize() method.

Any thoughts? I'd really appreciate some help!

P.S. I've been working on this for 2 days!

I don't have an answer to your question, but have you looked at using package configurations (SSIS > Package Configurations from the BIDS menu)? these can be stored as simple XML files that can be used for the same purpose as what I think you are trying to do here.

Note that BIDS will not save connection string passwords for you - you would have to open the XML file and edit it manually to add these if you are using it for connection strings.

You can use these XML files to store variable values as well as lots of other properties of your package objects. You could write your own code to manipulate the contents of the XML configuration files however you like.

Another option would be to store your values in a database and fetch the values into the variables using SSIS Execute SQL tasks or a similar method.

Maybe you've thought of all this already and ruled it out for some other reason - but based on your post this would seem a much simpler method?

|||Hi Slicktop,

Yes, I tried the XML config file but you cannot have configurations that don't exists in the package. About the SSIS_Configuration table, I thought of this but it's not a good idea for my deployment process. Somehow, I need to get the information from this table and put it in a flat file so I can store it in CVS. Plus, I need to fill this table every time I deploy my packages. I know this is not so difficult but my client don't want to do this.

There is a property in the Package object which disable the warning messages from the configuration file. If I set to True, I don't get an error message when I have just one XML config file with multiple configurations which they may or may not exists in the package to be executed. I don't like doing this because it doesn't seem right.

Thanks for your ideas.|||

What about just reading the flat file using SSIS? You could either load it into a work table and then query it to set variables, or maybe even use a transform to extract the values you need.

When you say "you cannot have configurations that don't exist in the package", what do you mean exactly? Your variable names aren't defined in the package?

|||That's right!. I don't have the same variables in every package. I have a log file per package so in the flat file I have:

log_package_1 = something
log_package_2 = something
log_package_3 = something
conn_db = connection string to db
file_vpn = path to the vpn file

and so on, so in package 1 I only have declared variable log_package_1 but not the rest of the variables of my flat file. Reading the flat file with SSIS could be a possible option but the thing is I have a custom flat file, I mean it's not comma separated and I have some "tags" so I don't think SSIS can read it.

Thanks for the ideas!|||

well obviously I don't know all the details of your situation but it sure seems like you are trying to do this the hard way.

Is there some reason why your custom flat file has to have a "custom" format?

If your goal is to dynamically populate package variables, and the variable names are defined in the package definition, then you can use package configurations to do this. If your goal is to have only a single flat file that contains all variable values across multiple packages, then why not just write these into a standard, machinable format (XML, delimited file, whatever) and then use SSIS to load the file into a DB table. Then from each package, query the table for the variable values needed for that particular package and use the resultset to populate the package variables.

Your table could be generic, with two varchar columns, var_name and var_value.

so, in package 1, you could write an execute SQL task that runs "select var_value from control_table where var_name = 'log_package_1', etc.

If you put a bit of thought into this you could probably make parts of this solution quite generic so that you could write it once and copy/paste the tasks into each package or nest your packages to re-use it. for example, maybe add a "package_name" column to the control table that you could use to query all variables for a package using the same SQL.

sorry, I know I haven't answered your original question (don't know the answer) but thought maybe there is a better (easier) way to do this.

|||Why are you punishing yourself with such a masochistic task?

I mean, why not just use XML Configuration files?|||

Has the original question been answered?

The Task.Execute method is where you will place the code that reads from the file and sets variables. The execute gives you access to the VariableDispenser object as on of the parameters, in much the same way as the TaskUI.Initialize method does, is this not enough?

The Dts.Variables syntax you have used in the Script Task is not applicable, you need to manually control the locking in the variable dispenser. Look at the Lock* methods as documented for the variable dispenser.

|||Hi DarrenSQLIS,

No, the original question haven't been answered yet. I know that Dts.Variables is not applicable in my case when creating a Custom Task. I checked the VariableDispenser object and there are a few methods. For example, the GetVariables(ref Variables variables) but I don't have the collections of variables to pass it as the parameter for this method.

I think I need somehow to tell SSIS which variables are for read-only access and which ones are for read/write. Even so, I would like to know first the variable name and their respective values first. The VariableDispenser object seems useless because I don't know how to get the variables within the package that is executing my Custom Task.

My goal is to know the name of all the User Variables in a package and then set these variables to their respective values that I'm reading from a flat file.

Using XML configuration files is not a solution, I'm not going to set the SuppressConfigurationWarnings property to True in each package. Probably you think I'm trying to do it the hard way but I have to follow some constraints and standards from my client.

Thank you all for your replies.|||

The methods, GetVariables, LockOnForRead or LockOneForWrite all expect a reference parameter of type Variables. This is just the variables collection that is filled with the 'locked' variables that you have asked for, e.g.

Variables variables = null;
variableDispenser.LockOneForRead(this.SnapshotName, ref variables);
snapshotName = variables[0].Value as string;
variables.Unlock();

From what you're saying though you don't know in advance what variables you want to lock, and that is a problem. There is no way to just ask for a list of all variables. I assume your pseudo config item and the variable name match, so at best you can loop through the config items and ask if a variable of that name exists, using the VariableDispenser.Contains method. If found then lock it and do what you want, but you cannot just eneumerate a collection of all variables in the package to find out what is there.

|||Hi DarrenSQLIS,

Yeah, you're right! My pseudo config item and the variable name match so I can loop through the config items and that's what I did. I wasn't understanding the purpose of the GetVariables() method of VariablesDispenser. After I played with it some time I realized it copies the variables from the package that you lock (read-only or r/w) before to the Variables collection that you pass by reference. Then you can access the variables from this collection and set them. What I don't understand completely is how the variables are return to the package or VariablesDispenser. I'm guessing is after you unlock them.

Well, thank you so much for your tips/ideas/suggestions. I finally finished building my Custom Task as I wanted. It's a shame you cannot access all the variables without knowing the name of the variables ahead. In this case I'm assuming that whatever is in the package is also in the config file but my process will fail if the package has a variable that the config file doesn't.sql

Custom Parameter entry with Reporting Services 2005

Hi,
Does anyone know how to do this ? I need to create a multi-tier parameter
entry screen for a reporting service project.
For example, I have one dropdown box - the user selects an item and that
Item will deterimine what is in a different list box, then the user can
select multiple items from the listbox to run the report.
I have no problem creating a custom ASPX page but I dont know how to
inegrate it into the report server, I would like the main report menu to
still be able to access this report ( and send it to the custom param page)
Any ideas on how to solve this problem ? I would like to avoid creating my
own custom report menu page if possible.
Thanks,
--
Dave GI don't know what you want exactly but from what I understand here is my
solution.
From your first parameter, make a new DataSet for the values in your second
parameter. Make a DataSet parameter that contains the report parameter
value. Then, in your second parameter, take values from With request and
select your DataSet.
If your values for the parameters aren't in your database then I don't see
how to do it unless you make an Expression to set your values in the second
parameter. You then have to verify the first parameter if it is the good
one. If yes then show the value. If not return Nothing.
I hope this will help you!
"Dave G" wrote:
> Hi,
> Does anyone know how to do this ? I need to create a multi-tier parameter
> entry screen for a reporting service project.
> For example, I have one dropdown box - the user selects an item and that
> Item will deterimine what is in a different list box, then the user can
> select multiple items from the listbox to run the report.
> I have no problem creating a custom ASPX page but I dont know how to
> inegrate it into the report server, I would like the main report menu to
> still be able to access this report ( and send it to the custom param page)
> Any ideas on how to solve this problem ? I would like to avoid creating my
> own custom report menu page if possible.
> Thanks,
> --
> Dave G|||What you are describing is called cascading parameters. RS supports this.
Search books on line for the phrase cascading parameters.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Dave G" <DaveG@.discussions.microsoft.com> wrote in message
news:70C8C6F5-E011-4098-9A90-22E72BB0C562@.microsoft.com...
> Hi,
> Does anyone know how to do this ? I need to create a multi-tier parameter
> entry screen for a reporting service project.
> For example, I have one dropdown box - the user selects an item and that
> Item will deterimine what is in a different list box, then the user can
> select multiple items from the listbox to run the report.
> I have no problem creating a custom ASPX page but I dont know how to
> inegrate it into the report server, I would like the main report menu to
> still be able to access this report ( and send it to the custom param
> page)
> Any ideas on how to solve this problem ? I would like to avoid creating my
> own custom report menu page if possible.
> Thanks,
> --
> Dave G

Custom Data Flow Components, DllImport, and third party dlls

I've been having an issue with the integration of a third-party DLL into a custom data flow component.

The company sent me a C# project that generates a simple console application. The project includes a class that calls their DLL with DllImport. The console application runs fine.

I created a stand-alone class using the C# class they sent to expose the methods of their DLL. In my custom component, I'm referencing this class to pass data to and from their DLL.

The first method from that stand-alone class that my component encounters simply gets their installation path from the registry and does not use DllImport. That path retrieval works fine. The next method calls a function that is declared with DllImport. Each time the call fails with "System.DllNotFoundException = {"Unable to load DLL AMZip.dll': Exception from HRESULT: 0xE06D7363"}".

I've copied this DLL to countless locations (e.g., the PipelineComponents directory, the project/solution bin directory) and included these paths in all manner of path variables.

What am I missing here? Their DLL is not strong named (does this matter since I'm using DllImport?), my stand-alone class is, and of course, the custom component itself is. I appreciate the help.

No bites, eh? OK, is there a standard directory for C++ dlls used in custom components?|||I think the question on everyone's mind is, what does this have to do with SSIS?|||Strictly speaking, your question might be better served in a C# forum or something. While we do custom development here, this isn't a programming forum.|||

Phil Brammer wrote:

I think the question on everyone's mind is, what does this have to do with SSIS?

Thanks for giving me the opportunity to explain why I posted this question in the SSIS forum!

Like I said, the class in my sample console application hums like a sewing machine. When I try to integrate it into an SSIS component it fails by an error for which I can find no explanation and the finest .NET programmer I know can find no explanation, having not dealt much with SSIS internals. There aren't a whole lot of people on Earth who program SSIS components, but one place they can be sure to be found is on this forum.

I guess the upshot is, I'm hoping to get a suggested nugget from someone who may have been down this road before, as opposed to a cocksure question.

|||If your application is built on the .net 2.0 framework, then this shouldn't be that hard. The 1.1 framework won't work in SSIS, I believe.

Getting your DLLs registered in the GAC might also help.|||

The third-party component in question is a C DLL. It has dependencies to msvcm80.dll, msvcp80.dll, msvcr80.dll, and Microsoft.VC80.CRT.manifest. I have a wrapper class for the DLL with DllIImport statements that makes its functions available for .NET.

The question is... does SSIS look for non-.NET dependencies in the system path? They are obviously not in the GAC since they're not .NET.

I am getting an error that leads me to believe the dependencies are not found; however, they are in one of the directories in the system path. What else can I do to get SSIS to find them?

|||

I would expect SSIS to behave much like any other program. Really the SSIS package will be hosted, but I would expect system32 to be a good location. Similarly your dependencies for the C++ DLL would need to be there. You could look at the SSIS install bin folder, not the project, but is a bit unreliable as may not always use a MS host that is in that bin folder. Getting system32 to work I think would be better in the same way that we say always put .Net dependencies in the GAC to ensure the runtime can find what it needs.

So if you try or have tried system32 and it still fails, what is missing? Use Filemon to look for file not found or access denied events and see where it is really looking. http://www.microsoft.com/technet/sysinternals/default.mspx

|||Are you using Vista? Ever since I brought my applications into Vista, I get that error when I try to link any DLL other than windows API. The same applications worked fine on XP. I copied the file all over the place and used the full path when linking and it still says that. I get this problem with all languages, not just C#.|||

Solution -1

1.Copy DLLS to your application\ bin folder(Virtual Dir)

2. Then do the following in code.........before you do a pInvoke

StringBuilder LocalFolder = new StringBuilder(AppDomain.CurrentDomain.BaseDirectory);

LocalFolder.Append("bin");

Directory.SetCurrentDirectory(LocalFolder.ToString());

3. Then give security permission "modify" for the "bin" folder for the following

a. ASP.Net Machine accounts..

b. Network services

c. youself!!!!!!!!!!(ur login)..

4. Try ur luck..

This happens when u are in a comapany with strict access

DISCLAIMER...

I tested it out..works for me..may not work for u..I am not responsible for the damage caused due to this post

Soultion -2

The Administrator need to grant you access to the ASP.NET temporary directory (somewhere in the WINNT ).

DISCLAIMER...

I didn't test it out...got a clue from net..which lead to my solution..I am posting it bcos it may help other poor souls who has this problem..I am not responsible for the damage caused due to this post.

Pls exuce my weak knowledge of .net tehnologies.. I am a C++ /Windows programmer forced to do .Net deployment

Custom Data Flow Components, DllImport, and third party dlls

I've been having an issue with the integration of a third-party DLL into a custom data flow component.

The company sent me a C# project that generates a simple console application. The project includes a class that calls their DLL with DllImport. The console application runs fine.

I created a stand-alone class using the C# class they sent to expose the methods of their DLL. In my custom component, I'm referencing this class to pass data to and from their DLL.

The first method from that stand-alone class that my component encounters simply gets their installation path from the registry and does not use DllImport. That path retrieval works fine. The next method calls a function that is declared with DllImport. Each time the call fails with "System.DllNotFoundException = {"Unable to load DLL AMZip.dll': Exception from HRESULT: 0xE06D7363"}".

I've copied this DLL to countless locations (e.g., the PipelineComponents directory, the project/solution bin directory) and included these paths in all manner of path variables.

What am I missing here? Their DLL is not strong named (does this matter since I'm using DllImport?), my stand-alone class is, and of course, the custom component itself is. I appreciate the help.

No bites, eh? OK, is there a standard directory for C++ dlls used in custom components?|||I think the question on everyone's mind is, what does this have to do with SSIS?|||Strictly speaking, your question might be better served in a C# forum or something. While we do custom development here, this isn't a programming forum.|||

Phil Brammer wrote:

I think the question on everyone's mind is, what does this have to do with SSIS?

Thanks for giving me the opportunity to explain why I posted this question in the SSIS forum!

Like I said, the class in my sample console application hums like a sewing machine. When I try to integrate it into an SSIS component it fails by an error for which I can find no explanation and the finest .NET programmer I know can find no explanation, having not dealt much with SSIS internals. There aren't a whole lot of people on Earth who program SSIS components, but one place they can be sure to be found is on this forum.

I guess the upshot is, I'm hoping to get a suggested nugget from someone who may have been down this road before, as opposed to a cocksure question.

|||If your application is built on the .net 2.0 framework, then this shouldn't be that hard. The 1.1 framework won't work in SSIS, I believe.

Getting your DLLs registered in the GAC might also help.|||

The third-party component in question is a C DLL. It has dependencies to msvcm80.dll, msvcp80.dll, msvcr80.dll, and Microsoft.VC80.CRT.manifest. I have a wrapper class for the DLL with DllIImport statements that makes its functions available for .NET.

The question is... does SSIS look for non-.NET dependencies in the system path? They are obviously not in the GAC since they're not .NET.

I am getting an error that leads me to believe the dependencies are not found; however, they are in one of the directories in the system path. What else can I do to get SSIS to find them?

|||

I would expect SSIS to behave much like any other program. Really the SSIS package will be hosted, but I would expect system32 to be a good location. Similarly your dependencies for the C++ DLL would need to be there. You could look at the SSIS install bin folder, not the project, but is a bit unreliable as may not always use a MS host that is in that bin folder. Getting system32 to work I think would be better in the same way that we say always put .Net dependencies in the GAC to ensure the runtime can find what it needs.

So if you try or have tried system32 and it still fails, what is missing? Use Filemon to look for file not found or access denied events and see where it is really looking. http://www.microsoft.com/technet/sysinternals/default.mspx

|||Are you using Vista? Ever since I brought my applications into Vista, I get that error when I try to link any DLL other than windows API. The same applications worked fine on XP. I copied the file all over the place and used the full path when linking and it still says that. I get this problem with all languages, not just C#.|||

Solution -1

1.Copy DLLS to your application\ bin folder(Virtual Dir)

2. Then do the following in code.........before you do a pInvoke

StringBuilder LocalFolder = new StringBuilder(AppDomain.CurrentDomain.BaseDirectory);

LocalFolder.Append("bin");

Directory.SetCurrentDirectory(LocalFolder.ToString());

3. Then give security permission "modify" for the "bin" folder for the following

a. ASP.Net Machine accounts..

b. Network services

c. youself!!!!!!!!!!(ur login)..

4. Try ur luck..

This happens when u are in a comapany with strict access

DISCLAIMER...

I tested it out..works for me..may not work for u..I am not responsible for the damage caused due to this post

Soultion -2

The Administrator need to grant you access to the ASP.NET temporary directory (somewhere in the WINNT ).

DISCLAIMER...

I didn't test it out...got a clue from net..which lead to my solution..I am posting it bcos it may help other poor souls who has this problem..I am not responsible for the damage caused due to this post.

Pls exuce my weak knowledge of .net tehnologies.. I am a C++ /Windows programmer forced to do .Net deployment

Custom Data Flow Components, DllImport, and third party dlls

I've been having an issue with the integration of a third-party DLL into a custom data flow component.

The company sent me a C# project that generates a simple console application. The project includes a class that calls their DLL with DllImport. The console application runs fine.

I created a stand-alone class using the C# class they sent to expose the methods of their DLL. In my custom component, I'm referencing this class to pass data to and from their DLL.

The first method from that stand-alone class that my component encounters simply gets their installation path from the registry and does not use DllImport. That path retrieval works fine. The next method calls a function that is declared with DllImport. Each time the call fails with "System.DllNotFoundException = {"Unable to load DLL AMZip.dll': Exception from HRESULT: 0xE06D7363"}".

I've copied this DLL to countless locations (e.g., the PipelineComponents directory, the project/solution bin directory) and included these paths in all manner of path variables.

What am I missing here? Their DLL is not strong named (does this matter since I'm using DllImport?), my stand-alone class is, and of course, the custom component itself is. I appreciate the help.

No bites, eh? OK, is there a standard directory for C++ dlls used in custom components?|||I think the question on everyone's mind is, what does this have to do with SSIS?|||Strictly speaking, your question might be better served in a C# forum or something. While we do custom development here, this isn't a programming forum.|||

Phil Brammer wrote:

I think the question on everyone's mind is, what does this have to do with SSIS?

Thanks for giving me the opportunity to explain why I posted this question in the SSIS forum!

Like I said, the class in my sample console application hums like a sewing machine. When I try to integrate it into an SSIS component it fails by an error for which I can find no explanation and the finest .NET programmer I know can find no explanation, having not dealt much with SSIS internals. There aren't a whole lot of people on Earth who program SSIS components, but one place they can be sure to be found is on this forum.

I guess the upshot is, I'm hoping to get a suggested nugget from someone who may have been down this road before, as opposed to a cocksure question.

|||If your application is built on the .net 2.0 framework, then this shouldn't be that hard. The 1.1 framework won't work in SSIS, I believe.

Getting your DLLs registered in the GAC might also help.|||

The third-party component in question is a C DLL. It has dependencies to msvcm80.dll, msvcp80.dll, msvcr80.dll, and Microsoft.VC80.CRT.manifest. I have a wrapper class for the DLL with DllIImport statements that makes its functions available for .NET.

The question is... does SSIS look for non-.NET dependencies in the system path? They are obviously not in the GAC since they're not .NET.

I am getting an error that leads me to believe the dependencies are not found; however, they are in one of the directories in the system path. What else can I do to get SSIS to find them?

|||

I would expect SSIS to behave much like any other program. Really the SSIS package will be hosted, but I would expect system32 to be a good location. Similarly your dependencies for the C++ DLL would need to be there. You could look at the SSIS install bin folder, not the project, but is a bit unreliable as may not always use a MS host that is in that bin folder. Getting system32 to work I think would be better in the same way that we say always put .Net dependencies in the GAC to ensure the runtime can find what it needs.

So if you try or have tried system32 and it still fails, what is missing? Use Filemon to look for file not found or access denied events and see where it is really looking. http://www.microsoft.com/technet/sysinternals/default.mspx

|||Are you using Vista? Ever since I brought my applications into Vista, I get that error when I try to link any DLL other than windows API. The same applications worked fine on XP. I copied the file all over the place and used the full path when linking and it still says that. I get this problem with all languages, not just C#.|||

Solution -1

1.Copy DLLS to your application\ bin folder(Virtual Dir)

2. Then do the following in code.........before you do a pInvoke

StringBuilder LocalFolder = new StringBuilder(AppDomain.CurrentDomain.BaseDirectory);

LocalFolder.Append("bin");

Directory.SetCurrentDirectory(LocalFolder.ToString());

3. Then give security permission "modify" for the "bin" folder for the following

a. ASP.Net Machine accounts..

b. Network services

c. youself!!!!!!!!!!(ur login)..

4. Try ur luck..

This happens when u are in a comapany with strict access

DISCLAIMER...

I tested it out..works for me..may not work for u..I am not responsible for the damage caused due to this post

Soultion -2

The Administrator need to grant you access to the ASP.NET temporary directory (somewhere in the WINNT ).

DISCLAIMER...

I didn't test it out...got a clue from net..which lead to my solution..I am posting it bcos it may help other poor souls who has this problem..I am not responsible for the damage caused due to this post.

Pls exuce my weak knowledge of .net tehnologies.. I am a C++ /Windows programmer forced to do .Net deployment

Custom Connection Manager or SSIS Provided one?

Hi,

I have been working with a client on a legacy application migration to SSIS and I found SSIS to be very helpful, as this is my first project using integration services I found myself in a situation where multiple solutions can work but I cannot assess the risk of using one or the other and the effort required for the implementation.

The situation is as following, we cannot touch the input files that come from the other legacy systems so I need to get information that is packaged following the next multipart schema:

&Header with context information from the source
&BatchHeader with context information from the batch (including format N)
- Data in format N
&BatchHeader with context information from the batch (including format K)
+Data in format K
&BatchHeader with context information from the batch (including format P)
=Data in format P

Etc... Needless to say that they can come in whatever order and they need different processing depending on the format. I have been tempted to use the techniq shown in "Handling Different Row Types In The Same File" from http://www.sqlis.com/default.aspx?54.

However, there are multiple issues that I have to resolve to do that: First is how to feed the pipeline with the Data in format N and then reparse them in an easy way cause the data that comes from the other system is very wide (aprox 120 columns), so using a parsing script is by no means a viable solution (I have multiple formats to handle). So I do not see a way to treat the data without using a Custom Connection Manager. Here comes my first question: Is there anyway someone can use the already provided managers to handle an scenario like this one?

Next comes the separation, which one is the wisest thing to do: Write again that to disk in as easier format or pass along that information to the next task in memory? The volume of data is in the order of hundreds of megabytes (100 to 200), maybe half a Gigabyte in the extreme cases. How about uploading that to SQL Server and work from there?

BTW, the production servers will have 6 to 8 Gb of memory and 4 to 8 processors (more or less).

Thanks in advance.

Federico

Hi, I am not sure I understand your questions.

>>>> First is how to feed the pipeline with the Data in format N and then reparse them

The concept show in the article loads the whole row as 1 column to get the data into the pipeline and then you make decissions and parse from there. The article shows using script components to parse but you could also just use a derived column transform and the expression syntax, it all depends on the complexity of splitting your file. My guess is the trick in your case is identification of the rows. Is there something that can identify each row?

File header (multiple rows?)
K_BatchHeader (multiple rows?)
K_rows
P_BatchHeader (Multiple Rows?)
P_rows
...
...

Or is the only way you know K rows for sure is they follow the KBatchHEader and precede some other header?
Could be a multi step approach is best. One to "Split" parse to the source files to individual file formats (k,p,N) and another to read those files and do the actual data processing. This way, the 2nd process can likely use the built in parsing features.

|||

What I can suggest is an approach.

Creating a custom connection manager won't do any good here because connection managers are agnostic to the Dataflow task. What you need is a custom source adapter...

Or, in certain cases you can use the flat file adapter to read in the file row by row and use a script transform to do the parsing.

The best thing to do is to create a custom source adapter. You'll have much more control and the solution will ultimately be more robust.

K

|||although a custom source component will do the trick, a script component may suffice.|||

Thanks Craig, you understood correctly :)

In fact the solution you propose is what I am going to implement because I could be able to get a sample of the format (what I had until this point was the functional extraction of the legacy Cobol programs) and the rows format is not even normalized. So the problem now reduces to Normalize the file format (where I can split it with a custom task) and then do the Staging on the database and further processing.

So in fact I am moving toward your solution ;)

Again, thanks for the help.

Greetings
Federico Andrs Lois

|||

Yeah I used the wrong name, I meant a source adapter :)

As I wrote to Craig, I am moving toward a prenormalization and splitting in a task just because I found the data wasnt as clean as I though (regarding the format) so it will be required a normalization step before that.

Thanks too for the response.

Greetings
Federico Andrs Lois

Custom Assembly generates error in Aggregation Wizard

I have a fairly simple custom assembly for adding some functions to a cube. Having a reference to the assembly in the cube project causes the following error when I try and run the aggregation wizard for the cube:

Message: Errors in the metadata manager. The system assembly cannot be modified by a user request. To restart the process, resolve the problem and then click start.

I get the message after the "Specify Object Counts" step, when it starts the process of determining the aggregations.

Is this a bug or is there something I need to do in the custom assembly differently? I started with the code framework from the AdventureWorksAMO sample project, and added/removed functions.

After applying SP1, I now get the following error when running the aggregation wizard:

"clr assembly must have main file specified"

The workaround is to remove the assembly reference and then run the aggregation wizard, and then re-add the assembly reference.

Although this works, it bring into question why the error in the first place? Any CLR assembly I've tested causes the same problem.

|||

This error will be fixed in the next SP, meanwhile the work-around is indeed to remove the assemblies and add them back after designing aggregations.

About why the error happens in the first place:

- when ran from BI development studio, the aggregation design wizard can have unsaved changes in the background (in the cube and/or dimension editors). or the entire BI project database might not have been deployed

- the wizard requires the database to be saved to the server in order to use the aggregations designing algorithm, so it does temporarily deploy the database (in a transaction that is being rolled back at the end); this is where the error about the assemblies happens

Adrian Dumitrascu

Custom Assembly generates error in Aggregation Wizard

I have a fairly simple custom assembly for adding some functions to a cube. Having a reference to the assembly in the cube project causes the following error when I try and run the aggregation wizard for the cube:

Message: Errors in the metadata manager. The system assembly cannot be modified by a user request. To restart the process, resolve the problem and then click start.

I get the message after the "Specify Object Counts" step, when it starts the process of determining the aggregations.

Is this a bug or is there something I need to do in the custom assembly differently? I started with the code framework from the AdventureWorksAMO sample project, and added/removed functions.

After applying SP1, I now get the following error when running the aggregation wizard:

"clr assembly must have main file specified"

The workaround is to remove the assembly reference and then run the aggregation wizard, and then re-add the assembly reference.

Although this works, it bring into question why the error in the first place? Any CLR assembly I've tested causes the same problem.

|||

This error will be fixed in the next SP, meanwhile the work-around is indeed to remove the assemblies and add them back after designing aggregations.

About why the error happens in the first place:

- when ran from BI development studio, the aggregation design wizard can have unsaved changes in the background (in the cube and/or dimension editors). or the entire BI project database might not have been deployed

- the wizard requires the database to be saved to the server in order to use the aggregations designing algorithm, so it does temporarily deploy the database (in a transaction that is being rolled back at the end); this is where the error about the assemblies happens

Adrian Dumitrascu