Hi,
I am using custom dll in script component in SSIS package. This dll is looking for some configuration settings and dsplays the message as "Configuration section could not be found in the configuration source" . Please tell me the configuration source it looks for.
I've run into a problem with SSIS packages wherein tasks that write or copy files, or create or delete directories, quit execution without any hint of an error nor a failure message, when called from an ASP.NET 2.0 application running on any other machine than the one where the package was created from. By all indications it appeared to be an identity/permissions problem.
Our application involves a separate web server and database server. Both have SQL Server 2005 installed, but the application server originally only had Integration services. The packages are file system-deployed on the application server, and are called using Microsoft.SqlServer.Dts.Runtime methods. For all packages that involve file system tasks, the above problem occurs.
When the above packages are run using the command prompt (either DTEXEC or DTEXECUI) the packages execute just fine. This is expected since we are using an administrative account. However when a ShellExecute of the same command is called from ASP.NET, the same problem occurs.
I've tried giving administrative permissions to the ASPNET worker process user to no avail.
I have likewise attempted to use the SQL Server Agent job approach but that approach might not be acceptable for our clients since it means installing SQL Server 2005 Database services on the application server.
I have read the relevant threads in this forum, namely http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1044739&SiteID=1 and http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=927084&SiteID=1 but failed to find any solution appropriate for our set up.
Anybody got any idea on how to go about this?
We manage some SSIS servers, which has only SSIS and SSIS tools installed on them and not the sql server DB.
SSIS packages and configuration files are deployed on a NAS. We run the SSIS packages through DTEXEC by logging in to the server.
We want to allow developers to run their packages on their own on the server, but at the same time we dont want to give them physical access on the server i.e we do not want to add them into RDP users list on server properties. We want them to allow running their packages remotely on the server.
One way We could think of is by using powershell remoting and we are working on that. But is there any other way or any tool already present for the same.
I have around 500 packages (SQL 2005)Â deployed in file system & all this packages are running on daily basis via SQL agent. Now I need to migrated all 500 packages into SQL server 2008 R2.
There is no inventory to track which package belogs to which team and any other information.
Now, I need a method to query the pakages connection string details with database respective. Is there any method?
I am looking for some advice regarding saving custom component data when saving packages.
For custom "properties", this is not an issue, as saving the package will save these properties. Howver, I also have information for each column (besides the properties that columns provides, like Name and DataType) that I need to save if a package were to be saved, and right now it does not save because I am using my own objects to store the data.
I am wondering as to how I can save this information. I have looked up on serialization, but I would like to know if there is another way besides serialization to save this inforamtion as I'd rather not save this to a seperate file.
Thanks
Hi all
I'm into a project which uses a lot of views for joining 2 or more tables. Using the MERGE component in SSIS will be a huge effort coz it only has 2 inputs and I gotta SORT the input too.
Isnt it possible to have a VIEW like component that joins more than 2 tables and DOESNT need sorting??
(I've thought about creating views in database engine but it breaks my data floe in SSIS and is'nt a practical solution)
Hi all,
I have finished the development of an SSIS package but I would like to make it a bit more flexible by adding a configuration file. The script migrates a database from a source to a destination and syncs with another source.
So there is the source db and the destination db. However, I have noticed that standard SSIS configurations can get a little tricky. For instance, I have two connection managers for the destination db (one that points to the schema and one that doesnt). So my question is: Is there any way to create a config file thta you can input a few attributes like sourceDB, destinationDB, schemaName and get the package itself to parse and process it?
Thanks,
Yannis
Is there a way of disabling a custom property in a component so that during design-time the property is grayed out? I looked around the properties of IDTSCustomProperty90 and nothing sticks out.
Thanks
Mike
Hi,
Near the end of 2005 Microsoft made available some sample C# apps that implemented custom SSIS tasks and components. I think Doug Ladenshlager may have had a heavy hand in building them.
Does anyone know where they are? I can't darned well find them!
-Jamie
Does anyone here have a favorite site or set of sites with resources on SSIS custom component (tasks, transforms, log providers, etc.) development? I've been searching around to try to find this on my own, but so far I've had little luck.
Darren - I've noticed that you always seem to have the most insightful responses to questions related to SSIS .NET development (such as your recent response to evaluating expressions in a custom component) so I am particularly interested in anything that you have to share.
Thanks in advance, everyone!
How do I remove a custom component from SSIS Designer pernamently? I can easily remove it from the tool box, but I want to get rid of it completely.
I tried searching in MSDN for an answer to this question but I could not find it.
Thanks
I have two packages share same configuration file. There are 4 connections in the first package and 2 connections in the second. Configuration file contains properties for 4 connections. First package opens in VS designer with no error. The problem with second package.
I get error that connection exist in the configuration but not in the package
and
"Could not copy file " Master_Configuration.dtsConfig" to the deployment utility output directory. Master_Configuration.dtsConfig' already exists."
Any help?
Hi,
At just the point at which I was going to write some verbose schpeel, I found this, which really does it all for me:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1163727&SiteID=1&mode=1
Basically it seems that whilst you can indeed share a config file, it doesn't aggregate sets of say different connection managers, into a superset - you choose to reuse, but it actually overwrites. Any ideas, anyone??
Hello,
I'm using an XML file to configure my dataBase connection string. I try to deploy my package on a new server and it works perfectly.
Then I made a second package which also need a configuration for the dataBase connection string. (I made the connection with the connection manager inside packages). The configuration is the same that for my first package, so I thought to use the same configuration file.
I can use the same configuration file but the problem is when I try to generate a deployment for my solution. I got an error which tell me that the xxx.dtsConfig file already exist and can not be copy again.
When I made the configuration in the second package I said that I want to reuse the file ... and I thought that for the deployment SSIS would know that it has to include that file only once ...
Did somebody already have this problem ?
Thanks !
dear experts,
i'm trying to build a package programmatically from client c# application. I'm working to create three dataflow components:
- OleDB Source
- Derived Column Transformations
- OleDb Destination.
My package works good, but i have several problems when insert an expression as Value of an IDTSCustomPropriety90 object, like this one:
[LEN](#firsname.lineageID) > 5 ? <<if true>> : <<if false>>
Simple expressions like concatenate strings, for example "#firstname.lineageID" + "#lastname.lineageID" seem to work good.
Thanks a lot in advanced.
Paganelli Francesco
Hi Guys,
I created a SSIS custom component, transformation (Asynchronous) with one Input collection and 2 output collections.
The SSIS Package which includes the Component I created works well in the Business Intelligence Studio, but when the same Package is run in the 'Execute Package utility' It fails to run. ( when you Double click on the dtsx file)
The cause of the failiure is
public override void PrimeOutput(int outputs, int[] outputIDs, PipelineBuffer[] buffers)
method receives only one output buffer when executed using the 'Execute Package Utility' { outputs = 1 , buffer.Length = 1 } ( when executed in the BI studio, the method receives parameters of both the output buffers that I expect { outputs = 2 , buffer.Length = 2 } )
The property ComponentMetaData.OutputCollection.Count = 2 as well. Yet the PrimeOutput method provides only 1 buffer.
The Validation Succeeds on both instances, which I assume means that Meta Data is Provided Properly.
What would be the reason for the same pakage to run in 2 different ways like this,
What might I have missed out to do, to make the package run in different ways on 'Business Intelligence Studio' and 'Execute Package Utility'
Thanks a lot
Below are some of the lines from the ProvideComonentProperties Method which deals with the output Collection, Isn't this sufficient for the PrimeOutput to provide 2 output buffers?
ProvideComponentProperties()
public override void ProvideComponentProperties()
{
RemoveAllInputsOutputsAndCustomProperties();
base.RemoveAllInputsOutputsAndCustomProperties();
base.ProvideComponentProperties();
//other function calls
IDTSOutput90 output1 = ComponentMetaData.OutputCollection[0];
output1.Name = "Output1";
output1.Description = ".......................";
extracted.SynchronousInputID =0;
IDTSOutput90 output2 = ComponentMetaData.OutputCollection.New();
output2.Name = "Output2";
output2.Description = "..........................";
output2.SynchronousInputID = 0;
//other function calls
}
Hello,
I need some clarification. I am trying to utilize the XML Direct Configuration in my SSIS packages to utilize database connections in the package. I am wanting to utilize this the same way you could use UDLs in the SQL Server 2000 DTS packages.
Currently, I am creating the dtsConfig file and saving it to my desktop. I then modify it with notepad and add the following configuration where it looks like this:
<DTSConfiguration>
<Configuration ConfiguredType="Property" Path="Package.Connections[ConnectionName].Properties[ConnectionString]" ValueType="String">
<ConfiguredValue>Data Source=[ServerName];Initial Catalog=[DBName];Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;</ConfiguredValue>
</Configuration>
</DTSConfiguration>
Once this is created, I am trying to "re-use" this in SSIS packages created moving forward, where they all point to this configuration for the same database connection.
What I don't understand is when "Enabling Package Configuration" and then pointing to this dtsConfig file doesn't create a connection in "Connection Manager" NOR does it provide a way to create a blank connection and point to this configuration.
I feel like I am missing something thats so "great" about XML configuration files. Any help would be appreciated.
Thanks,
Daniel Lackey
MCSD
The standard flat file connection component does not handle flat files with different column counts on different rows. Since that's the type of file I have to read, I like to create a modified version of the flat file connection manager that can do this. I have found some info in how to create a connection manager. But I can't find anything that tels me how to build one that would be recognized by the flat file source dataflow component.
So how do you build an connection manager that can used by the standard flat file source component? Anyone ever did this or knows were to get the docs?
I know my problem is also solvable by reading the row into a single column and then using a script component to split them but that is not the solution I want.
Hi all,
my problem is that: I do read informations (e.g. connectionString) from configuration file by one custom assembly.
My environment is RS2005, VS.NET 2005 Prof.Edition, WIN2003 Server SP1, .NET Framework 2.0.50727
The scenario is that:
A text box into my report require a function contained into MyCustom.dll. This code do read information from MyCustom.dll.config.
This file contains:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<connectionStrings>
<add name="IS2009cfg" connectionString="data source=servernameinstancename;initial catalog=dbname;user id=xx;password=yy;" />
</connectionStrings>
<appSettings>
<add key="DefaultHeaderLogo" value="This is a test" />
</appSettings>
</configuration>
Using
ConnectionStringSettings conn = ConfigurationManager.ConnectionStrings["IS2009cfg"];
the result is null.
Also using
string defaultHeaderLogo = ConfigurationManager.AppSettings["DefaultHeaderLogo"];
the result is null.
MyCustom.dll and MyCustom.dll.config are located into locations of Report Server and Report Designer, with this Build Events on succesfully build:
rem Copy custom assembly to ReportDesigner location...
copy "...mydeveloplocation...inDebugMyCustom.dll" "C:Program FilesMicrosoft Visual Studio 8Common7IDEPrivateAssembliesMyCustom.dll"
copy "...mydeveloplocation...inDebugMyCustom.dll.config" "C:Program FilesMicrosoft Visual Studio 8Common7IDEPrivateAssembliesMyCustom.dll.config"
rem Copy custom assembly to ReportServer location...
copy "...mydeveloplocation...inDebugMyCustom.dll" "C:Program FilesMicrosoft SQL ServerMSSQL.4Reporting ServicesReportServerinMyCustom.dll"
copy "...mydeveloplocation...inDebugMyCustom.dll.config" "C:Program FilesMicrosoft SQL ServerMSSQL.4Reporting ServicesReportServerinMyCustom.dll.config"
The question is simple: where I do locate configuration file for obtain value during report execution?
Many thank's in advance
Claudio Barca
Turin - Italy
I have 5 packages in a solution.
View 19 Replies View RelatedHi,
I am currently migrating from Oracle to MS SQL Server 2005 using SSIS. Since the new schema being used on the SQL Server is very different, we have created separate packages for each target table.
We have several different sets of data stored as separate Oracle schemas, and I would now like to change to a different schema for the OLE DB Source objects in my packages to use (Table/column names to remain the same). While I can go through all packages and search through the drop-down list to select the table of the same name from other Schema, doing so is an extremely slow process.
Is it possible to use configuration files to set a schema to be used for a number of OLE DB Sources over a number of packages?
At the moment I am doing a find and replace over the .dtsx files, but can't believe I have to resort to this for something that I imagine many people would want to do with SSIS.
Thank you,
The Captain
Suppose 2 environments on a single machine.
Each environment has different configuration settings....different
databases etc.
All the packages in the first environment have a hardcoded config files
referencing the local drive.
In order to create the second environment do I have to
go into each package and manually change the location of the
hardcoded config files. If I don't it will it not use the config files
from the first environment.
Thanks
Hi,
I have implemented a custom source component that can be used as the data source in the Data Flow task.
I have also created a custom UI for this component by using the IDtsComponentUI .
But my component does not have the capability of setting the custom properties via the DTS Variables using the Expression Builder.
I have looked around for samples on how to do this, but I can only find samples of how to do this for custom Control Tasks, i.e. IDtsTaskUI.
My question is, How can implement the Expression Builder in my custom Source component + custom Source UI. Or do you know of any samples which I can look at.
Thank you,
Jameel.
When I try to debug the break points will always say the source code is different from the current version, but the custom component in the GAC has the new version number. The other strange thing is the toolbox will not reset to the original version meaning it will not remove the custom components. The funny thing is after I compile the custom components and restart VS the custom component runs with the new code changes. I can see the new features I added, but the debugger and toolbox still seem to be broken.
I have tried the following
1) Reset the tool box.
2) uninstall all my custom dll from the GAC €œC:WINDOWSassembly€?
3) remove all my custom dll from €œC:Program FilesMicrosoft SQL Server90DTSPipelineComponents€?
4) restart VS 2005
5) reselect the custom components.
6) reboot my computer.
It seem like VS has another cache. For the tool box or something.
Does anybody have any suggestion?
Hi all,
I am creating a customer data flow component for SSIS for use in a package. I've got some custom properties that I am exposing using the supplied advanced editor (no custom property editor here).
Some of my properties are enumerated types, and I have deciphered how to get those properties to show as dropdown lists of their respective enumerations. (For those of you who may be looking as hard as I did as to how to accomplish this, see the end of this post.)
I also have a few properties which request SSIS package variable names - such as an file name variable. However, I can't figure out how to tell the advanced editor that the property is looking for an SSIS variable, so that it can show a dropdown list of package variables, much like virtually any other Microsoft supplied Data Flow component can.
Is there a Type Converter I could specify for those custom properties? Is there another way to instruct SSIS that my custom property is expecting a variable? Or do I need to code a custom UI for editing my Data Flow Task?
To create a dropdown list of values for a custom property that represents an enum, do the following:
1. Create your enum definition, such as "public enum ThisIsMyEnum { one, two }"
2. Create a new class that inherits from TypeConverter, such as "public class MyEnumConverter : TypeConverter"
3. Override "CanConvertFrom", and return true if "sourceType == typeof(string)"
4. Override "CanConvertTo", and return true if "destinationType == typeof(string)"
5. Override "ConvertFrom", and return the enum value (such as "one" or "two" in my example) that corresponds to the string passed in the parameter "value"
6. Override "ConvertTo", and return a string that corresponds to the enum value passed in the parameter "value"
7. Override "GetStandardValuesSupported" and return true
8. Override "GetStandarValuesExclusive" and return true to indicate that ONLY the enum values should be accepted
9. Override "GetStandardValues", and return a new StandardValuesCollection constructed with Enum.GetValues() of your enum, such as "return new StandardValuesCollection(Enum.GetValues(typeof(ThisIsMyEnum)));"
10. Just above your "public enum" declaration, add a "TypeConverter" attribute to link your type converter to your enum, such as "[TypeConverter(typeof(MyEnumConverter))]"
11. In "ProvideComponentProperties", after you've created your custom property like this: "IDTSCustomProperty90 propEnum = ComponentMetaData.CustomPropertyCollection.New()", add another line to specify the TypeConverter property of the property to the full assembly name of your type converter, like so: "propEnum.TypeConverter = typeof(MyEnumConverter).AssemblyQualifiedName;"
Hi,
I'm fairly new to the SSIS world, and I've recently ported a bunch of dts packages over to SSIS. I'm an ASP.NET developer so I'm very familiar with the capabilities that configuration files give you, and I attempted to set up my solution as follows:
All of my "Data Sources" are at the project level, and added (with the same name) to each package. I wanted to have a single config file that had all of the project-level settings (i.e. connection strings, data file paths, etc). I then have a config for each package with the package level settings - i.e. variables, etc.
The problem becomes that all packages do not use all data sources. This results in an error when I try to open up a package for editing, it complains that it doesn't have a reference to data source XYZ that it is seeing in the configuration file.
Is there any way that I can get around this? If I have a password to a database change, I don't want to have to look through every config file and change it in multiple places.
My current environment has multiple packages stored in SQL server (MSDB).
When working on a set of packages I want to bring them into my local development area
Add existing package only allows you to pull one package at a time - anyone have the secret to selecting multiples
Thanks
HI, we are beginning a new project at my company and I was wondering where is the best place to save SSIS packages: file system or SqlServer. I have used other ETL products and they always create a repository on an RDBMS. Since SSIS offers us the choice of DB storage or file system, is there pros and cons of both approach? Will the deployment of our application be simpler by using Sql Server since we would onky move metadata instead of files?
Thank you for your help,
Ccote
We deployed the ssis package to SQL server and now trying to configure but it only allow use to change environmental variables there is no option to browse and select XML configuration file. Does this mean when you  are using SQL server deployment mode u can only use environmental variable ?
View 3 Replies View Related
Hi
I have to import a list of users and then add them to my Sql database via .NET framework APIs
Now the following code works from within my web application but I cannot get it to run in an ssis transormation script.
Has anyone got experience with this requirement in an SSIS package script?
Code Block
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Static users As New System.Collections.Hashtable
Dim password As String = String.Empty
If (Not users.ContainsKey(GetUserCode(Row.UserID))) Then
users.Add(GetUserCode(Row.UserID), GetUserName(Row.FullName))
'create a random password
password = System.Web.Security.Membership.GeneratePassword(7, 1)
Try
'insert new user
System.Web.Security.Membership.DeleteUser(GetUserCode(Row.UserID))
System.Web.Security.Membership.CreateUser(GetUserCode(Row.UserID), password)
'link roles to new user
System.Web.Security.Roles.AddUserToRole(GetUserCode(Row.UserID), "MANAGE_SCORECARD")
Catch ex As Exception
System.Windows.Forms.MessageBox.Show(ex.Message)
End Try
End If
End Sub
Posts 7
Dear all
I have developed some packages (around 40) on my local system.
now i m trying to move the integration services project on the production server.
when i double click on the intergration services from the local drive of the server all the packages are up(works fine till here)
now i open any of the package-->this is what happens
Prompt1)-->TITLE: Microsoft Visual Studio
------------------------------
There were errors while the package was being loaded.
The package might be corrupted.
See the Error List for details.
------------------------------
BUTTONS:
OK
------------------------------
i press ok and then the prompt comes
Prompt2-->There were build errors.Do you want to continue with the last build and continue..yes..no..
i click yes ..and then error comes
prompt3-->
Error loading RTS-IMRB-DISTRIBUTION.dtsx: The connection "Excel Connection Manager" is not found. This error is thrown by Connections collection when the specific connection element is not found.
and finally the prompt for the OLE DB error
prompt 4-->[OLE DB Destination [14]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "NDELNTX46.IMRB RTS.siddharth" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Possible work around suggested to me was that in the base system(read local) before exporting go to package properties and the security and set the security as "Encrypt all with password" and also put some Password which i did and it worked for one.
other thing i thought off was creating the configuration file but first i thought of fully running one complete package on the production and then think of the configuration file.
so i went inside the invidual excel connection and pointed it to the correct new file location and also edited the OLE DB database connection and put my user name and password and checked save password and trying to run the entire thing.
but still the same problem..
one more thing i observed was that my OLE DB was going off everytime i am running the package.and i have to enter my password again...so othe save password i guess is not working
I know its a little long post..but i wanted to explain the entire process and problem to all with description.
all suggestions are welcome
Thanks in advance
cheers & Happy new year
sid
I am writing a custom dataflow transformation component and I need to get the name of the preceeding component.
I have been trying to find a way to get a reference to the Package object, MainPipe object or IDTSPath90 object (connecting to the IDTSInput90 of my component) from my component because I think from there I can get to the information I want.
Does anyone have any suggestions?
TIA . . . Ed
Hi,
I've created a Custom Data Flow Component and added some Custom Properties.
I want the user to set the contents using an expression. I did some research and come up with the folowing:
Code Snippet
IDTSCustomProperty90 SourceTableProperty = ComponentMetaData.CustomPropertyCollection.New();
SourceTableProperty.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
SourceTableProperty.Name = "SourceTable";
But it doesn't work, if I enter @[System:ackageName] in the field. It comes out "@[System:ackageName]" instead of the actual package name.
I'm also unable to find how I can tell the designer to show the Expression editor. I would like to see the elipses (...) next to my field.
Any help would be greatly appreciated!
Thank you