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....
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
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
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
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
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.
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!!!
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.
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 ?
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
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
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 RelatedI'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
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.
I want to store multiple config files into single table I am unable to do it.
View 8 Replies View RelatedHi Everyone,
When I first installed BI Studio everything was working fine, but I went into it today and I noticed that the FTP task seems to be missing, I can't see it in the toolbox, everything else appears to be their but not that.
Any idea how I can get this back, I really don't wont to uninstall and reinstall at the moment, and I'm not even sure if that would work.
Thanks
I am attempting to import data into SQL 2005 (using the Tasks->Import Data wizard) and get as far as selecting a datasource. My source is ODBC and I am selecting ".NET Framework Data Provider for Odbc" but then get stuck on the connection string... I am attempting to import from 'Relativity Client' which as far as I can tell is an interface to a Cobol database, I have also had this problem importing from FoxPro - how do I come up with a connection string?
I have also tried to create a DTSX package, created the connection to the odbc source, tested ok, then when selecting the connection as a source (datareader source) I get the message 'Cannot acquire a managed connection from the run-time connection manager'...
I have also created a DTS package in SQL2000, upgraded to a 2005 package, this creates a file which after opening cannot be saved 'System.OutOfMemoryException', on my laptop with 2gig physical ram (1.3gig in use), 8gig virtual memory...
This is getting just a little frustrating as the SQL 2000 wizard fetches the data straight in from any data source I have ever tried with no complication whatsoever...
Unfortunately the first training course I can get on is in the middle of January. But I would prefer not to have to install SQL2000 and SQL2005 (SQL2000 for DTS, SQL2005 for reporting) just to get a few reports up and running.
What do I need to do to make SSIS import ODBC data? Or if someone can direct me to a good source (preferably with examples) of how to get SSIS working it would be greatly appreciated.
I have installed in my machine SQL 2005 with SSIS components, and the Visual studio 2005. The installation process was successfully and all components seem to be on the machine, but I did not find the shortcut to allow me running SSIS environment.
When I re-run the SQL setup the SSIS components is there but is unable to be used.
Any idea how to solve this issue?
Thank you,
Marian
I want to start the SSIS wizard to import data from a flat file, but I right click the DB > select Task and the Import and Export options are not there. Is there something I have to enable before the options show up
Thanks
We have an SSIS package which runs regularly throughout the day, on 15 minute intervals. This package is moving data between two SQL Server instances, performing some simple identity mapping along the way. The primary source table is large, and we want to move only those records which have changed since the last time the package ran, so we use VersionDatestamps in the table, and pick up the dataset to be transferred by using the pacakge execution time, and the (previously recorded by the package) last-run time.
The problem we are having, is that the initial dataset picked up by SSIS is often missing records. The missing records are clearly within the time window that the package queried for, though they are near the boundary (within a minute, of the boundary, but as much as 30 seconds away) and typically all have an identical version datestamp to each other, within a single execution of the package.
At first, we thought this was an issue with date precision, but we've both truncated the dates, and even expanded the time window, and we still experience the same phenomenon.
The stored procedure which is responsible for updating the records in question, runs as a previous step to the SSIS package, within a single SQL Server Agent Job.
Has anyone experienced anything similar, or have some recommendation on how to track the source of this down?
Windows 2003(64bit) server, SQL 2005 Server(64bit), Oracle client 10G rel2 (64bit) is installed. But when I am going to create a datasource to the Oracle database the Oracle OLEDB Provider is missing, the only Providers avaliable is from Microsoft.
I have tried to install ODAC (64bit) with no result.
Anyone who have come across this problem and how do I do to resolve it?
BTW! Is there a way to try communication with the OLEDB provider from the commandprompt?
I want to skip running the SSIS data flow task when the source file is missing. We have a scheduler that copies the source file to the staging area. This SSIS package runs as SQL server job. So when a SSIS package fails due to missing file the remaining steps in the SQL scheduler won't execute. I want to handle the missing source file condition grace fully. Please advise.
Thanks in advance.
Hello all.
I am trying to import a flat file using the Wizard. That option does not show up. It shows up fine on my other machine. I installed 2005 Standard edition and they are still missing. I then uninstalled everything, rebooted and reinstalled Standard edition.
The flat file option is still missing. Any thoughts?
--Thanks
Joe
Hello All,
I have a package in which I have enabled "Package Configuration".
When I run the package i am sure that it reads the configuration file and executes the package correctly.
However if I remove the configuration file, the package still executes correctly with the settings which were used at the time of development.
I have event handlers for OnError and OnWarning and both these are NOT invoked.
IMO, this is incorrect behavior because if a package has been configured for "package configuration, then we should atleast have a warning generated that SSIS did not find the configuration and it would execute the package with hard coded values (from the time of development).
Is there any work around for this? how can I make SSIS warn me if the config file is missing for a package which was configured for package configuration?
I can not seem to locate this file any where on my computer let alone in the specified directory. With out this file, it would be very difficult if not impossible to go through rest of the SSIS tutorials. Could I ftp this file from some locations so I could get started on the tutorials.
I would very much appreciate any help.
Thanks
IS this procedure exactly correct?
http://support.microsoft.com/kb/913967
Line 10 does not say go to advanced but that is the only place I see Integration Services to install.
Can't make a Maint. Plan for backups because the server is missing SSIS..
Thanks.
Hi there,
Something a little wride mysterious is happening with my package when I deploy it to run at the SSIS server instance. Everytime that I try to deploy it (from my local development environment) to SSIS server, my package is not keeping its database user and password.
As the database user and password are the same one we dont need to use the XML setting to keep these data there.
So, does anyone know what could be happening with my package and/or my deployment?
Thank you,
Luis Antonio - Brazil
Hi All,
I have a requirement of logging the failure and the error of a job executing a SSIS package to text file.
However, when I go to job step -> Advanced, in the section "SQL Server
Integration Services Package" where that info to setup logging is, it
is blank and just says:
"There are no options for this command type"
Any ideas?
I thought that this might not be supported in Standard Edition as I am running standard edition. Then I checked on two other servers, where I have Enterprise edition with SP2. On one of the installations, I am able to give the log file, the other one still shows,
"There are no options for this command type"
What is the problem? Both of them I am using the sysadmin role account. I have proxy account created on both the SQL instances. I don't think this is the problem, but still I checked this.
Can anybody help me?
Regards,
Virendra
in order to use an SSIS package as a data source in a report, I need to enable the SSIS extension in the RSReprotDesigner.config and RSReportServer.config files. That extension is in neither of these files. I have SSIS running on my machine with Reporting Services.
The path to RSReportServer.config: C:Program FilesMicrosoft SQL ServerMSRS10_50.MSSQLSERVERReporting ServicesReportServer
The path to RSReportDesigner.config: C:Program Files (x86)Microsoft Visual Studio 9.0Common7IDEPrivateAssemblies
Why is this extension not in either of these files?
The following is an abbreviated list of what's present in the RSReportServer.config file:
<Data>
 <Extension Name="SQL"
 <Extension Name="SQLAZURE"
 <Extension Name="SQLPDW"
 <Extension Name="OLEDB"
[Code] ....
The following is an abbreviated list of what present in the RSReporDesigner.config file:
<Data>
 <Extension Name="SQL"
 <Extension Name="SQLAZURE"
 <Extension Name="SQLPDW"
 <Extension Name="OLEDB"
[Code] ....
I'm running SQL Server 2008 R2
ProductVersion  ProductLevel  Edition 10.50.1600.1 RTM   Enterprise Edition (64-bit)
The operating system is Windows 7 Professional 64 bit.
I have an SSIS package that inserts website URLs from a SQLServer table into a variable used by an HTTP Connection Manager, then downloads the data files from those URLs using a ForEach Loop and a Script Task. Works beautifully when a data file is found at the URL, but hangs if no data file is found. I've set the Timeout property on the HTTP Connection Manager to 30 seconds, but doesn't work. how to first check if a data file exists, of if the request returns nothing, or how to trap this situation in a try-catch?
Here is the VB code I'm using in the Script Task:
Public Sub Main()
Try
' Connect to website using HTTP connection manager
Dim nativeObject As Object = Dts.Connections("HTTP Connection Manager").AcquireConnection(Nothing)
' Create a new HTTP client connection
Dim connection As New HttpClientConnection(nativeObject)
[code].....
I am using SSIS 2014 and installed adapter for sharepoint list source and destination and when I refresh the toolbox I don't see them. Is there a way to manually add them?
View 4 Replies View RelatedBasically i'm trying to create an SSIS workflow to download Sharepoint List data to SQL Server on a schedule of some kind.do we actually have to use the GAC install approach in order to get the Sharepoint List Destination and Sharepoint List Source entries to appear on the SSIS Project workflow entities?
View 4 Replies View RelatedI'm using Script Component to load data into Oracle DB due to the poor performance issue. Now, I found it will missing some data during the transmission. Please see the screenshot below:Â
SQL Server：
Oracle:
DDL:
create table Person
(
BusinessEntityID Integer,
FirstName nvarchar2(50),
MiddleName nvarchar2(50),
LastName nvarchar2(50)
);
Result:
I follow up this article:Â [URL] ....
VB Script:Â
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
[Code] ..........