Dynamic Database Connection In SSIS Without Using A Config File
May 1, 2008
I'm a newbie to SSIS so this question may cause people to point and laugh. I apologise if that's the case.
I have a configuration database that holds connection details to a remote database. I want to use these connection details to dynamically create a new connection to some remote database.
I usually work in with C# where this would be simple but I can't find how to do this using SSIS.
I have found people using configuration files but as I already have my connection data in a table I don't really want to strip it out. (Also, is it a security risk to have database connection strings stored in a config file?)
I have been able to implement a custom script object to build the connection string and assign it to a user variable within the package but can't find any way to then use that variable to connect to the remote database.
I am developing one SSIS package to migrate an Oracle Database to SQL Server 2005 DB through ODBC. My problem is with configuration. I have to use configuration file so that in production environment the end-user must be able to change the user credentials to connect both database servers. I am trying xml configuration file. When I try to use xml configuration, my odbc connection is giving error. But if I disable the configuration through "Package Configurations Organizer", it works fine. Using an XML Editor I checked the .dtsconfig file and entered both passwords for the both connections.
I tried logging and below are the few text from log file related to error.
Code Block User:Diagnostic, Nebu, BLRDOMNebu, DestOdbcConnection,{E3535ED4-1D21-41B9-9796-E9F5B47FFD5B}, {6820E259-18FF-4FCA-B820-73EE931D2095},12/17/2007 8:16:39 PM,12/17/2007 8:16:39 PM,0,0x,ExternalRequest_pre: The object is ready to make the following external request: 'IDbConnection.Open'.
User:Diagnostic,Nebu, BLRDOMNebu, DestOdbcConnection,{E3535ED4-1D21-41B9-9796-E9F5B47FFD5B},{6820E259-18FF-4FCA-B820-73EE931D2095},12/17/2007 8:16:41 PM,12/17/2007 8:16:41 PM,0,0x,ExternalRequest_post: 'IDbConnection.Open failed'. The external request has completed.
I am using Protection level = EncryptSensitiveWithPassword for the package.
I have packages that must generate log errors dynamically including time of execution and the name of the task.
I make it changing the properties of the connection inserting two expressions.
1.-I alter the File Usage Type to 1 to generate this files. 2.- I alter the connection string as: @[User::myvariable] +"constant_description"+ time description+ +".txt"
The time description is : (DT_STR,40,1252) DAY (GETDATE())+"-"+(DT_STR,40,1252) MONTH( GETDATE())+"-"+(DT_STR,40,1252) YEAR( GETDATE())+" + REPLACE( (DT_STR,10,1252) (DT_DBTIME) GETDATE(),":","_") But it is not the problem
In those packages I have one connector for all the tasks and in execution time it creates one file for each of the tasks.
The problem is the way I insert in the filename the task name.
I have a pre-execute event handler in each task that modifies a string variable( myvariable) appending the task name. When I execute de package it works great but when I only execute a task, the program do not enter in the event and do not put the task’s name.
How can I put that name without using that handler? There is another handler can I use to do it that happens before the system generates the new file name and after pre-execute? Anyone knows another way to do this kind of things?
I'm currently trying to convert over packages from SQL 2000 to SQL 2005. The biggest obstacle at the start has to do with converting my "Dynamic Properties" control. I use it to read an .ini file and load the user name and password to my connections. With 2000 it's nice and easy 1 file 3 lines. While trying to convert it I€™ve had nothing but problems. I've tried the Registry Entry but it forces everything into the Current User and I can't use that I need local machine. There is no documentation and I can't have a xml file with 1000 different username and password settings because 1 person has to update that file with the new passwords. Any help would be GREATLY appreciated.
Currently have a single hard coded file path to the SSRS config file which parses the file and provides the reporting services web service url. Â My question is how would i run this same query against 100s of servers that may or may not share the same file path as the one hard coded ?
Is there a way to query the registry to find the location of the config file of any server ? which could be on D, E, F, H, etc.Â
I know I can string together the address followed by "reports" and named instance if needed, but some instances may not have used the default virtual directory name (Reports).
Am I going about this the hard way ? Is there a location where the web service url exists in a table ? I could not locate anything in the Reporting service database. Basically need to inventory all of my reporting services url's.
I am trying to dynamically change my initial catalog in a SSIS project. Each day I get a snapshot of data from the production server. I am performing ETL on that database and loading it into my warehouse. I am trying to put something together so that each day I get the latest snapshot and if its newer then my most recent one I pass that new catalog into the connection manager. I can already run the sql script to compare the two catalogs but I can't populate the connection manager.
I am trying to create an ssis package with dynamic csv file as output. and out format contains query output.
sample file name:
Unique identifier + query output + systemdate();
The expression is looking like this.
@[User::FilePath] + @[User::FileName] + ".CSV"
-- user filepath is a variable from ssis package. File name is the output from SQL query. using script task i have assigned the values to @[User::FileName] .
When I debugged the script task the value getting properly but same variable am using for Flafile destination. but its not working.
Currently we have a 1.1 app that sets the connection string as a Application variable, we are wanting to change that and place it in the web config file, when I do this in 2.0 it's a simple process but in 1.1 it's different. It doesnt seem to like the <connection string> tag, does it go in the appsettings tag and if so what should it look like and how is it referenced.
SO coming to my question. 1)What is it doing. I mean how is the package talking to this config file 2) Irrespective of what I do in the Connection String the package executes fine, which It should not do as far as my understanding goes. Why is it doesn't matter whats in String. where as it matter in later.
I'm trying to connect to a Sybase DB using an OLE DB connection. Now, it works fine when I run the project without the package config enabled, but when I add the Sybase connection to the xml config file, & enable it the connection fails.
This is the error I see in the progress Tab
[Connection manager "AcidPrd.basketUser"] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "ASE OLE DB Provider" Hresult: 0x80004005 Description: "Login Failed. Check for valid user ID, server name and password.". An OLE DB record is available. Source: "ASE OLE DB Provider" Hresult: 0x80004005 Description: "[Native Error code: 4002] [DataDirect ADO Sybase Provider] Login failed. ".
I've recently uploaded my website, http://www.bigredsongbus.com, to my host. Unfortunately, they don't support the use of SQL Server Express. So, I've purchased an addon through my host - discountasp.net. I've attached my database file to their server, now all that I need to do is to change the connection string in my .config file so that it points to the SQL Server. I don't know how to do this. Any help, please?Jeffrey Way
I have several SSIS packages, some of which retrieve data from excel files - they work fine from th VS designer until I try to store configurations in a SQL database - then I get the following error:
SSIS package "ITAMdiv2.dtsx" starting.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Error: 0xC0202009 at ITAMdiv2, Connection manager "ExcelDIV2": SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
Error: 0xC020801C at Data Flow Task, Excel Source [84]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "ExcelDIV2" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Error: 0xC0047017 at Data Flow Task, DTS.Pipeline: component "Excel Source" (84) failed validation and returned error code 0xC020801C.
I am looking for help trying to configure an XML dtsconfig file, I am using different connection managers for my Source and Destination Databases, I am also using an XML dtsconfig file to set these parameters dynamically, however I dont want to use userid/password to connect to my source/destination databases and instead use trusted connection or windows authentication, I remember we had an option in earlier DTS where we could set it up as 0 or 1and use windows authentication, I cannot find this in the new SSIS package properties.
This has been happening to me since I started using Configuration files. I use one XML config file that stores Machine Specific settings and one connection string to my Metadata database. The problem is the database connection.
When I set the conection in the Configuation file, save, close and reopen the package, a dialog box opens with the title "Synchronize Connection Strings".
To reproduce the problem:
1. Ceate New SSIS Project 2. New Data Source Localhost.Adventureworks 3. In Package.dtsx create an New Connection from Data Source... 4. Choose AdventureWorks 5. Save Package 6. Close Package 7. Open Package
There is no problem at this point.
8. Enable Package Configurations 9. Create a new XML Configuration File 10. Give it a name and a location 11. Click Next 12. Under Connection Managers Click Adventure Work 13. Click Next 14. Give it a Name 15. Click Finish
16. Save 17. Close 18. Open
Uh-Oh!
"Synchronize Connection Strings" Dialog Box Hare is what it tells me.
Connection Adventure Works
Data Source Adventure Works
Old Connection String Data Source=.;User ID=;Initial Catalog=AdventureWorks;Integration Security=True;
Old Connection String
Data Source=.;Initial Catalog=AdventureWorks;Integated Security=True
19. Press OK
20. Save Package 21. Close 22. Open
Uh-Oh, still there.
Has anyone else run into this? If so, how can I get around it?
After developing SSIS Package (.dtsx file) if I need to deploy to all environments dynamically, then how can I create .dts config file and mention properties in it ?Â
I have deployed to production a number of nested packages (parent packages that call child packages) to the SQL msdb via the Save As option rather than building a deployment utility. These packages reference configuration files in a static location off of the c: drive on the production server. In the development environment, when connection changes are made and I run the Reload with Upgrade option the connection manager takes on the new server and user id settings. However, out on the production side I get the following error from the SQL job log:
Cannot load the XML configuration file. The XML configuration file may be malformed or not valid.
As a result the SQL job uses the default connection information which references the development database rather than the production database. I did research the error but found no good solutions. Is there a way to ensure the configuration files are formed correctly and that the packages are correctly referencing the configuration files? We are trying to run the ETL updates via a SQL job.
While trying to build SSIS Package Project, the following error is thrown. I have added configuration file recently to fetch the dynamic values such as connection string, output path etc.
I tried manually cleaned up the debug folder and tried build but still the error occurs.
Can any one help in suppressing this error?
Error 1 System.ApplicationException: Could not copy file "D:ExportExport.dtsConfig" to the deployment utility output directory "D:ExportinDebug". ---> System.IO.IOException: The file 'D:ExportinDebugExport.dtsConfig' already exists. at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath) at System.IO.File.InternalCopy(String sourceFileName, String destFileName, Boolean overwrite) at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.CopyFiles(ICollection fileNames, String outputPath) --- End of inner exception stack trace --- at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.CopyFiles(ICollection fileNames, String outputPath) at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.CreateDeploymentUtility(IOutputWindow outputWindow) 0 0
Possible or not? --> I maybe lazy - but I want to achieve just specifiying 1 variable in SSIS package ("environment") - and all the connectionStrings should "poof" magically be adjusted to correct locations
In DTS I created a SetDTSenvironmentVariables function for all my packages - so how wouldIi achieve this in SSIS?
Function SetDTSenvironmentVariables( environment ) Folder = "MyDtsPackageFolder" Select Case environment case "DEV" DTSGlobalVariables("WorkingDirectory").value = "C:Packages" & Folder case "STAGING" DTSGlobalVariables("WorkingDirectory").value = "D:Sql_working_directoryMy_productionSTAGING" & Folder case "LIVE" DTSGlobalVariables("WorkingDirectory").value = "D:Sql_working_directoryMy_production" & Folder End Select
' ' Set Connection Properties ' dim oPackage, oConn set oPackage = DTSGlobalVariables.parent oPackage.LogFileName = DTSGlobalVariables("WorkingDirectory").value & "LogsErrors.txt" For Each oConn In oPackage.connections Select Case oConn.Name case "My_DB" Select Case environment case "DEV" oConn.datasource = "SERVER01" oConn.Catalog = "My_Production" case "STAGING" oConn.datasource = "SERVER06" oConn.Catalog = "My_Staging" case "LIVE" oConn.datasource = "SERVER06" oConn.Catalog = "My_Production" End Select case "Schools.xls" oConn.datasource = DTSGlobalVariables("WorkingDirectory").value & "" & "School_Codes.xls" case else oConn.datasource = DTSGlobalVariables("WorkingDirectory").value & "" & oConn.Name End Select Next
set oPackage = nothing set oConn = nothing End Function
I need to dynamically change the file name of my destination flat file connection everytime the package runs, obviously i do not want to edit the config file everytime.
I think the best way to do this is by a script taks that sets the connection manager filename property with a variable that i dynamically populate.
Is this the right way?
I just want to be sure that there is not an easier or better way to do this?
For MS Press Book, ADO.NET 2.0 Step by Step, 2005 Edition by Rebecco Riordan, installing the code samples created a ConfigDB.exe file under SampleDBs folder. Running this exe file created a AdoStepByStep into the Database folder in SQL Server 2005 Express.
However, for ASP.NET 2.0 Step by Step, 2005 Edition, by George Shepherd, there is no Config.exe to install the ASPNETDB SQL Server 2005 Express database. I right clicked the Database folder to 'Attach' the database. But, the database just showed a 'c: ASPNETDB.MDF filfe path as a name. Further, running the aspx files in Chapters 7 and 12 showed a 'Timeout Expired' error msg, indicating something wrong with accessing the SQL Server database.
How can I run the aspx files in Chapters 7 and 12? How can I install the ASPNETDB?
Requirement: When we create SSIS Pacakge using Businessinteligence studio.Each Source and Destination or whatever we using the Control required DB Connection.
we connect theDB server and Database Table through manaully .Instead of Manual i need dynamic Global varible for Connection String .How to achieve this connection string.
because suppose we create SSIS Package in Developement Server Latter We change the Server from Developement to Another Testing Server . at that time we dont requierd for changing manulay.any one pls reply me.
Same as in Dotnet we give configiration XML file .we gave the Connection strng. how to in SSIS we do?
I have a SSIS Package that exports data from Sql Server to an Excel file. I need help figuring out how to have the file name be "Report_02132007.xls". Basically I want to append the date to the file name. Any ideas?
I have CSV file as source for SSIS package every time the filename will be changing like trd_1990M1_1990M12.csv,trd_1991M1_1991M12.csv , trd_1992M1_1992M12.csv etc.,
so it will vary as per user selection . i need to run the same SSIS package to execute the different file name with the same structure.
Please let me know the solution for that how to pass the file name dynamically to SSIS package.
Hello Experts, I am createing one task (user control) in SSIS. I have property grid in my GUI and 2 buttons (OK & Cancle). PropertyGrid has Properties like SourceConnection, OutputConnection etc....right now I am able to populate Connections in list box next to Source and Output Property.
Now my question to you guys is depending on Source Connection it should read that text file associated with connection manager. After validation it should pick header (first line of text file bases on record type) and write it into new file when task is executed. I have following code for your reference. Please let me know I am going in right direction or not.. What should go here ? ->Under Class A
Hi *, is there a way to connect Excel to SQL Server so that Excel serves as a frontend to SQL Server? I heard rumors that this is possible with the 2007 release.
I'm looking for a product that helps me storing massive data outside an Excel file. Right now, I'm using Palo (open source multidimensional database).
My task is to read the filename from a database table and transfer the flat file data in to a table. In SSIS,I am able to fetch the file name using a Data Reader Source; but how to pass this fileName parameter to Flat File Source ?
In DTS I have used ActiveX script to pass filename variable as flatfilecon.Source. Any help ?
. . . . I am specifying the row delimiter as : </row>{CR}{LF}<row> When I create Flat File Connection and when I see the preview of columns...in the first row "<row>" remains and in the last row "</row>" remains...
I am trying hard to sort out and remove these extra string but unable to do so...
Please let me know how to approach this? How should I specify the Row Delimiter?
I experienced a weird error while deploying my SSIS package. After running the manifest file, i noticed that one of the configuration file's path was not updated in the dtsx file. My solution has 8 packages and almost every package has 2 configuration files. Except 1 file every other config file's path is being updated. Has anybody experieced such a problem?
i've been asked to move our ssis project to another machine and i've a lot of file system connection whice i must rename them to the new path in the other machine. there is away to rename the file system connection dynamic or i must go and rename one by one ?? thanks.
In toder to make my SSIS packages portable, I need to be able to set connection string to sql package configuration. I thought I can do that via XML package, problem is, at execution time SQL server package configuration is queried first, then variables from XML. This way, I have no chance to set connection via XML. Any ideas on how to aproach this ? (I want to have as much configuration on SQL server so I can modify it easily from future GUI application.
