Dynamic SSIS Configurations
Aug 23, 2007
Hey there,
I know that many articles have been written describing configurations for packages but I have yet to have found one that describes if it is possible to use SQL Server configuration type for a package that is to be tested on DEV, then UAT, then PROD boxes.
I would like to know if there's a way to store values in a config table in a database on DEV, UAT and PROD but never have to change anything in the package.
I mean, I wonder if I can pass a parameter that defines the server to go get the configurations from.
The 3 servers will contain a database with a config table named the same on each server but have different configuration values to point them to the proper sources and destinations depending on which server the configuration database resides.
Thank you,
Robin
View 13 Replies
ADVERTISEMENT
Oct 9, 2007
I want to see if anyone in this community has come across this and has found a solution.
we have 3 different SQL Servers because we are still porting over SQL 2k databases 2k5. We have multiple databases of course. We would like to create some kind of template that would create the connection manager based off of the information in our XML configuration file. We are thinking we would have 2 files for this. One would house the actual server information but omit the database name and username/password. The second will be a list of all the databases with their usernames/passwords for each one.
If we put all of these in the configuration files and do not have a connection for each one, SSIS will throw the error stating it is missing connection managers for those items. We would like to see about maybe specifying the database name and have the connection manager dynamically pull the database specific information into the package. This would also need to be able to handle more then 1 database should there be the need.
I am new to SSIS. It seems pretty nice and extensible but my learning curve on it is horrible as I never programmed DTS packages before.
I hope I am making sense here. Thanks for any help offered!
- Taco
View 5 Replies
View Related
Apr 6, 2007
I have a package that will be run by many people, basically that take a flat file from one format to another format. The variables I'd need to change are @originalFilePath and @destinationFilePath.
I'm looking at package configurations now, and am wondering what I'm missing. It seems like the configuration settings are static, with an xml file or a sql server table being my most viable options.
What I need is for multiple users to be able to execute the same package with different parameters at the same time. Can anyone guide me in the right direction? I know that through ASP, I could generate XML docs on the fly, but I want to make sure I'm using the best method. What's the best way of going about dynamic configs?
View 8 Replies
View Related
Mar 28, 2007
I have a dynamic flat file I need to import to a table (in the same format as the file). The problem, I'm realizing, is that dynamic column mappings are a pain with SSIS. I have to know the format of the flat file ahead of time, which I won't.
What are my options here? Can package configurations help with this?
View 9 Replies
View Related
Dec 13, 2007
I have a problem where in I am trying to set up the package in a new environment.
I just need to change the server to which the data flow task will be populating.
I know we have xml configuration file. Is there any way i can read the files from .NET code and change the server name.
Thanks
Sai
View 4 Replies
View Related
Feb 6, 2006
http://img96.imageshack.us/my.php?image=untitled5ia1.jpg
how come my SQL 2005 surface area configuration don't have the configuration for SIS and other settings? seems to be missing alot of settings...it only say SQL 2005 Express when I m using SQL 2005 enterprise....
View 2 Replies
View Related
Apr 10, 2007
Hello, I have a SSIS package that is making one database connection (dbMAIN). I am using the XML File Configuration to specify dbMAIN's properties (server, database, password, etc).
The problem is that the File Configuration contains entries for multiple databases (dbMAIN, dbTEST, dbSTAGING, etc)
When the SSIS is run through dtexec.exe, I get this error:
Error: 2007-04-10 16:52:23.39 Code: 0xC001000E Source: TEST Description: The connection "DDMAIN" is not found. This error is thrown by Connections collection when the specific connection element is not found.End Error
I do not want it to give me an error. The XML File must house all our database connections. What can I do?
`Le
View 5 Replies
View Related
Jun 20, 2007
I have a package that executes several other packages. I have an XML packageconfiguration file to set the servername for the parent package, do I need to just add that packageconfiguration to the parent package and the servername in the XML file will be used until all the packages executed from the parent package are executed or do I need to add that configuration file to each lower level package?
Thanks for any help.
View 5 Replies
View Related
May 11, 2006
Hi there,
I've got an urgent thing I need to resolve.
I have a package that has a file connection. We have set the connection in the package for development - we also have the connection in a config file.
We are calling the package from SQL Jobs. In the step we call the package in, we try to set the value for the connection in the Data Source tab. However, the package does NOT use the value we enter.
I then removed the connection from the config file - then the package uses what the connection was set to in development. It still will not use what we enter in SQL Jobs.
Furthermore, when we run the package we lose the values that we put in the DataSource tab!!!!
My work around has been to change the SQL Job type from an INtegration services package to a straight command line - this works.
The question is - can you set a package's connection strings from SQL Jobs using the SSIS Job Type ???
Thanks!!!
View 3 Replies
View Related
Mar 19, 2007
I have a package that uses configurations to override package settings based on what environment the package runs in. The package's configuration entries begins with an initial XML config entry that overrides the package's connection manager to a SQL database that holds the remaining configs in a table. Subsequent config entries then fetch their settings from the table. This package is run from a SQL job.
This all works fine in dev. When I moved everything into prod the packages are not getting configured and are using their values stored in the DTSX files. I've triple checked the XML config file, the tables with the configs, and the packages. There are no error messages. I've added some debuging steps to the package to verify that the configs in the table are not getting into the package.
I've also tried manually changing the configs in the table where the package is set to look if the initial XML config fails to adjust the config database location. The package still fails to see any configs from the table.
What could be different between dev and prod that would produce this situation? Both dev and prod have identical copies of the package and the job and are currently pointed to the same configurations database.
By the way, the other connections in the package work for both source selects and destination inserts. Only the configurations are failing, and again there is no error message.
View 6 Replies
View Related
May 6, 2008
I am using package configurations to hold an email address. I can happily change the email adress when teh config is in an XML file, but when I choose SQL Server to store the package configurations I can change the email address but the changes are not picked up despite coming out of dev studio and back in again - it picks up the default set on the variables tab.
It simply refuses to pick up the email address from the SQL table, but happily creates the dbo.[SSIS Configurations] table with correct entries in the wizard 1st time round.
Any ideas what I am doing wrong ?
View 10 Replies
View Related
Sep 18, 2007
Hello everyone,
I am working on a SSIS project and I am facing an issue for getting the configuration settings of the package, once it is deployed and executed from SQL Server agent.
The package uses two configuration types: (listed bellow in the order they are appeared in the configuration editor)
Config1 - Xml configuration file - for storing the database connection string.
Config2 - SQL Server - for storing some user defined variables. It uses the same database as specified in Config1.
Everything works fine and the package uses the database configuration values as defined in Config2, if I execute it from Visual Studio,
However, the package doesn€™t get the configuration settings from the database when I try to execute it as a SQL Agent job.
There aren€™t any errors and the package executes all tasks successfully, using the connection object Config1 (the same we use to get the config parameters from the database) and the default values of the user defined variables.
It works ok, if I change Config2 to be of type XML configuration file.
There could be two problems:
1. SQL server agent doesn€™t read the configuration from the database and I am not quite sure how to set this. In Agent/ Job step properties screen/ Configurations tab I can only browse for a config file. I can also use the command window and /CONFIGFILE option to specify xml file, but how to use it in a case of a database configuration? Is there a /CONFIGDATABSE option or /CONFIGFILE works with database connection as well. I tried with /CONFIGFILE and database connection, but it doesn€™t seem to work.
2. SQL server agent doesn€™t get the configurations in the specified order. In my case,
it could try to read Config2 first, but at that moment it doesn€™t have the database connection from Config1 and it fails. Again, I am not sure how to set the sequence.
Thanks in advance for your comments.IT
View 12 Replies
View Related
Feb 22, 2008
I tried to follow this link last night (http://curionorg.blogspot.com/) but when i try to configure my package configuration to use sqlserver i recieved the following error message:
The Table "[dbo].[ssisencryptedconfiguration]" cannot be used as a source for SQL Server configurations. The column "ConfiguredVaue" does not have the expected data type.
consult books online for the schema requirements
I followed the blog line by line. i have closed down my project and created a new test package, new project, still the same error. Its there a property somewhere that needs to be configured to allow package configuration to use encryption with sqlserver? I'm running sql 2005 w/sp2. Its there a hotfix that i'm missing. i have not been able to google an answer.
I have been able to successfully use sqlserver as my package configuration. I wanted to see if i could use the encryption code posted on the blog above. i have seen folks reference it or point other folks to it to use for encypting sqlserver package configurations, i just wondered if they have had the same problem.
thanks
View 5 Replies
View Related
Jan 26, 2006
I have two SSIS projects each with different packages. I have setup the packages with configurations stored in a SQL Server table in MSDB. When I create the configuration on the second project it overwrites all of the first projects configurations. Is there a way to to get two different project configurations stored in the same SQL Server table? Any help would be greatly appreciated. Thanks!
View 6 Replies
View Related
May 8, 2008
I'm learning SSIS, and just started to use SSIS Package Configurations. I want to be able to switch between a dev and prod instance of our database.
I did not specify the password in the Configuration file (XML), figuring it would get it from the package itself. (That will be the same between dev and prod). But once I set up to use the Configuration, I started to get a "Login failed..." message. I added the password to the configuration, and it's the same. I quit using the configuration file at all, and then it works again.
This may be related, but when I show the properties on my connection, it shows stars for the password. When I go to the editor, the password field is blank, and if I test the connection, it fails. I type in the password, and then the connection works. I have the "Save my password" checked, but every time I go back, the password is blank. In fact, if I type in the password for the connection in the editor, and then go to the "All" page, the password is blank. It may be a red herring, but it sure looks like it's not really storing the password, and thus the Configuration file can't connect when it tries to get it. I can type in the password, test the connection (it works), close the editor dialog, open the editor dialog, test the connection again, and since the password is now blank, the connection fails.
What do I have to do to make that password actually stick!?
Without the Configuration file, when I save the package to SQLServer, it will run as a job, in spite of the password appearing to not stick. With the configuration file, it's not even running while still in Studio.
Here are at least some of the version information, which may or may not be useful.
Microsoft Visual Studio 2005
Version 8.0.50727.42
Microsoft .NET Framework
Version 2.0.50727
Installed Edition: Professional
Microsoft SQL Server Analysis Services Designer
Version 9.00.3042.00
Microsoft SQL Server Integration Services Designer
Version 9.00.3042.00
Microsoft SQL Server Reporting Services Designers
Version 9.00.3042.00
SQL Prompt 3.5
Thank you for any help you can provide.
-thursday's geek
View 12 Replies
View Related
Jan 24, 2007
Hi:
After moving my deployment folder to the Target Server, I run the Installation Wizard. As I move next, I am missing the window which is supposed to allow me to set package config values as stated in MSDN:
"If the package includes configurations, you can edit updatable configurations by updating values in the Value list on the Configure Packages page."
Source: http://msdn2.microsoft.com/en-us/library/ms141802.aspx
Does anyone know why I am not seeing it? In my deployment bundle which I have moved over has currently 3 files:
1) SSIS Deployment Manifest
2) SSIS Package
3) SSIS Config File
Again, I double click on SSIS Deployment Manifest, and it starts fine. I go thru the steps for File System Deployment, and then it prompts for installation folder path. After that, it takes me directly to validation. Why is it not showing me the Configure Packages Page as described in the MSDN Documentation. Please advise. Thanks.
View 5 Replies
View Related
Oct 20, 2015
I want to store multiple config files into single table I am unable to do it.
View 8 Replies
View Related
Mar 2, 2014
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.
View 3 Replies
View Related
May 8, 2008
Dears
I am trying to to make a dynamic column mapping using the SSIS, the mapping will be stored in a seperate table, and based on the file name, the necessary mapping will be applied.
Please advise its possibility.
View 10 Replies
View Related
Nov 22, 2007
Hi All,
I transfed one dts pckg to ssis, in this pckg i have one script that script variable i need to set into ssis. here is following script code
Add your code here
' Source Type = 0
' INI file name = E:mssqldatamember_updatemember_load.ini INI file key = source_db INI file section = db_config
' Destination = 'Global Variables';'abc_db';'Properties';'Value'
' ***************************************************
' Source Type = 0
' INI file name = E:mssqldatamember_updatemember_load.ini INI file key = source_dwdb INI file section = db_config
' Destination = 'Global Variables';'xyz_dwdb';'Properties';'Value'
' ***************************************************
' Source Type = 0
' INI file name = E:mssqldatamember_updatemember_load.ini INI file key = target_db INI file section = db_config
' Destination = 'Global Variables';'bed_db';'Properties';'Value'
' ***************************************************
' Source Type = 0
' INI file name = E:mssqldatamember_updatemember_load.ini INI file key = crdir INI file section = file_backup
' Destination = 'Tasks';'DTSTask_DTSCreateProcessTask_1';'Properties';'ProcessCommandLine'
' ***************************************************
' Source Type = 0
' INI file name = E:mssqldatamember_updatemember_load.ini INI file key = cpfile INI file section = file_backup
' Destination = 'Tasks';'DTSTask_DTSCreateProcessTask_2';'Properties';'ProcessCommandLine'
can you tell me how to set this global variables into ssis pckg and also can you expain me what is source type=0 or 1,2,3,4 up to what number.
Thanks
Aric
View 6 Replies
View Related
Oct 16, 2006
Hi ,
1.Dynamic Sql
----------------
My source table is Oracle .we want to have dynamic query the following steps we have done.
a.Created a new variable called StrSQL & ProductID variable contains values for where clause.
b.Set EvaluateAsExpression=TRUE
c.Set Expression=""select * from prod where product_id = " + @[ProductID]
d.OLE DB Source component, opening up the editor
e.Set Data Access Mode="SQL Command from variable"
f.Set VariableName = "StrSQL"
But i am getting the following error
Error at DataFlow Task[OLEDB Source[1]]:An OLEDB error has occured, Error code: 0x80040e14.
AN OLE DB records is available . Source "Microsoft OLEDB Provider for Oracle" Hresult: 0x80040e14
Description : "ORA-00936:missing expression
What could be the problme.how about the support of dynamic query (Oracle) in ssis?
2.Clarification in Lookup.
--------------------------------
How to Pass Parameters to Lookup. (Dynamic sql in Lookup)
example
select * from mastertable where reportdate = ?
here also my source is oracle table
Thanks
Jegan
View 3 Replies
View Related
Apr 2, 2008
Hello All,
I have 5 SSIS Packages out of which 1 is the parent package which calls the other 4 packages.
There are some database connections in my child packages.
Now, I want to make the packages dynamic.
I have two options.
1) Have all the connection information in the parent package and pass those values to the child packages. This way I'll be having only 1 configuration, the one for the Parent.
2) Have a separate configuration file for each package that will have all the connection related informations.
What is the better practice ?
Thanks & Regards,
Kapadia Shalin P.
View 3 Replies
View Related
Feb 13, 2007
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?
View 1 Replies
View Related
Jun 6, 2013
I have an SSIS package that has a variable called @RUN_DATE.I would like to create a sql agent job and uses the set values tab to pass a dynamic parameter to the ssis package. for example, i would like to always set the variable to the last day of the previous month. I know that If i hardcode the date as 5-31-2013 it works fine, but i have not been able to figure out how to make the value dynamic by using an expression or if this is even possible.
View 4 Replies
View Related
Oct 11, 2006
Hi ,
What is the equivalent of DTS Dynamic Properties task into SSIS ?
How do I convert this task to SSIS ?
Thanks,
Vikas
View 1 Replies
View Related
Feb 26, 2008
Hi All, i've been reading this article http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-_116683388696570741.html
in regards to creating an excel spreadsheet dynamically in SQL Server 2005 SSIS. However, i'm constantly getting an where the tab is created but not being populated. Can somebody post up a clearer example?
The problem I'm trying to solve is to automate the export of a query onto a new dynamic spreadsheet each time I run this SSIS package.
Any help would be greatly appreciated.
Thank you.
View 3 Replies
View Related
Nov 8, 2006
1) We are doing data migration from SQL Server 2000 OLTP design to SQL Server 2005 OLAP design.
2) We have used SSIS packages and data flow tasks in which we mentioned connection strings for source and target
containers.
3) We have a master execute package which contains series of execute packages in relational order.
4) When we execute this master package, we should be able to dynamically specify different source and connection
strings for all packages.
5) Can we do it with dtexec option /Conn[ection] IDOrName;ConnectionString
We know what to give for ID or Name but what do we give for ConnectionString. Will it be the source/target one? Can we specify both source and target ones with which the data flow packages get executed.
View 2 Replies
View Related
Jan 2, 2008
Dear Friends,
I currently have my excel source dynamic for the connectionstring. Using a global variable with the filename and using the expressions of this control to dynamically have the connecttionstring. My problem is that i need tto read always the first workseet and the name changes frequently and generates an erro in SSIS.
how can I set the excel source to goes only for the first sheet independently of the worksheet name?!
Regards!!
View 5 Replies
View Related
Nov 27, 2006
Hi,
I have a database with serveral tables, for example 'customer', I want to update this table with a SSIS package. However, to ensure we don't have issues if the update fails then I've put in an intermediate stage
Using an Execute SQL Task I create temporary tables, for example 'customer_tmp'. Data is then imported into these tables. When all the data is imported successfully the original tables are dropped and the temporary tables are renamed, removing the '_tmp'
This works fine and I'm happy with it. However, if someone adds a column to one of the tables in SQL server it is lost on the next upload.
Similarly I have to hard code creating the indexes into the package as well.
Does anyone know how I could copy the original table definitions and create the temporary tables dynamically. So that any new columns would be picked up?
And indeed is it possible to copy the indexes from one table to another before the drop and rename trick?
Thanks in advance.
Iain
View 4 Replies
View Related
Oct 11, 2006
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
View 14 Replies
View Related
Jul 16, 2014
I am new to SSIS and i got 1 assignment.
Requirement:
In my destination table i am having some 30 columns and the CSV files what i get may have 10 columns or 20. How do I map columns between source and destination dynamically?
View 3 Replies
View Related
Apr 11, 2008
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.
View 1 Replies
View Related
Jul 20, 2007
Do you guys have any good links or suggestions relative to this?
View 1 Replies
View Related
