August 27, 2009 at 12:35 pm
Hello all,
I'm trying to create a script task in SSIS 2005 that uses a custom business object (dll) which I wrote and use in my web applications. I have added the dll to the GAC and I have also copied it to the SQL Server assemblies folder. I have been able to add a reference to the dll in the script tag and create an instance of my class. This is all great and I love it.
Now my problem is that I look for the connection string that the dll uses using the following line of code.
string connectionString = ConfigurationManager.ConnectionStrings["MembershipConnectionString"].ConnectionString;
This is looking for the connection string in the web.config file of the web application. Now how can I get this to work or look for the connection string in a different place. I have figured out how to create a .dtsConfig file but I don't know how to get my custom dll to look for the ConnectionString value in this file.
Can anyone help me with this?
Thanks
August 27, 2009 at 3:00 pm
I have not used a .dtsConfig file. However, another alternative is to store the SSIS package config data in a SQL table. By default BIDS uses a table named [SSIS Configurations] and creates it in whatever database you specify. If your custom object needs the package connection string, it could issue a SQL Query to the [SSIS Configurations] table to retrieve it.
August 27, 2009 at 3:23 pm
What have you written? A custom control flow task or a DLL that is referenced by a Script task?
You could create a variable at the package level and use a dtsConfig file (or any other PAckage Configuration method) to set it and then simply ready the variable, this method is easily accomplished in either a custom control flow task or a script task..
CEWII
August 27, 2009 at 3:52 pm
This is a DLL that is referenced by a Script task.
The problem is that I want code from inside the dll to look for/at the configuration file and read the connection string like I do with the web app. I don't want to have to pass the connection string into the custom class to make this work. I realize though that this may be the only way.
August 27, 2009 at 4:03 pm
I have a further question, is this connection string likely to be the same one that the rest of the package is using?
CEWII
August 27, 2009 at 5:31 pm
mmm, probably not. Part of my issue here is that I have a business logic layer which is specific to a database and I want it to always use the same connections string which could often be different from the package. I think it should be separate from the package as the package could be use my business logic with a completely different database.
August 28, 2009 at 6:27 am
Ok, I can see that.
Then choose a location and write a file to that location. Your DLL can just read it, don't require it to be passed in. Take SSIS entirely out of the equation. Since it sounds like the only reason you need to know the connection string is to tell the DLL, I'm not sure why the Script task needs to know it at all.. If anything, you might want to make the location variable and let the package tell the DLL where to find it, but have a default location if one isn't passed.
CEWII
August 31, 2009 at 1:57 pm
Thank you for the help. I've got this working now. I also found this article http://www.codeproject.com/KB/aspnet/EmailFramework.aspx which helped a bit.
Now I'm off to the next problem.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply