Changing SSIS Production Code
Jan 14, 2008
I am curious to find out by other people on how they manage incremental code changes to SSIS packages. I am in a situation now where we deliver production SSIS packages to a client, where the client changes the connection details to the data sources and exectues the packages on a nightly basis.
We have had instances where clients are asking for enhancements and modifications to the SSIS package whereby we would develop it in our development environment then replace the existing copy in the clients production environment. There has been instances whereby the packages would work as desired on our end, but it requires further refinement on the clients side. As such, we have had instances where we had to "back-out" into the previous packages.
I am just trying to find out what methods or procedures other people are following to acknowledge situations like this or similar to this. Currently we are following an exhaustive method of having two copies of the code on the client side, the "live" code is never modified, just the secondary copy, then when all is good, we move this code into the "live" code. Does anyone have any means of performing incremental updates on SSIS packages easily?
View 8 Replies
ADVERTISEMENT
Jul 2, 2015
So I'm using the 2012 SSMS to connect to a SQL 2008 database, upon which we have code that audits all SQL logins to production and notifies us via email that someone is using logins they shouldn't. Mostly it's to notify us if people other than the DBAs are using these logins from their desktops instead of using their windows accounts.
This morning I opened up SSMS 2012, logged into my production servers in Object Explorer using Windows Auth. An hour later, I had to update an exception table, so I opened a new query with SQL Auth and used a SQL-only login it. Immediately the email pops that someone on my desktop is using a SQL Login. That's okay. That's expected.
What I didn't expect is that after I closed the query window, the email kept popping. The query window isn't even open / connected to production any more, but SQL still thinks I'm logged in using a SQL Login instead of with Windows Authentication (which is what I used on the Object Explorer connection).
Is there a bug with 2012 that causes a new connection type to affect all current connections? I.E., did it change my DB Windows Auth connection to SQL Auth on the other connection?
View 0 Replies
View Related
Jan 1, 2008
Hi
I am currently developing a DBA management console for a company who have 45 SQL Servers, 20 of which are in the production environment. As part of the development I have created a DBA dashboard with various alerts and information dials regarding the state of the SQL Infrastructure. I came accross a problem yesterday that I cannot find the answer to.
One of the alerts I have developed is a simple "Heart Beat" indicator that displays that the SQL Server is present and "alive". To do this I have a central database running on SQL 2005 which has linked server connections toall 45 SQL Servers. This central database runs a simple select statement on each server (something that I know works if the server is there). The select is run as dynamic SQL as I need to build up the SQL as part of a cursor to place the server name within the from clause. Here is the select I am using: -
select top 1 dbid from [server].master.dbo.sysdatabases
If the server is not there an error occurrs. I trap this and record that the server may not be present. The cursor then moves on to the next server to check.
This works fine when ran from Query Analyser. I can simulate a failed server by deleting the link server connection. For the server in questions an error is raised when the select is run, I store the error and on the Dashboard screen you can see an indicator saying that the server is currently non-contactable (i.e. failed Heart Beat).
My problem comes when I try and schedule this procedure. If I run it as an SQL Agent Job it behaves differently. Here, as soon as the error is detected it bombs out stating that the step has failed. I don't mind it stating that the step has failed, what I want it to do is to complete the cursor loop on all the servers. You see at the top of the procedure I set all the heartbeats flags to 0 (i.e. not contactable) so I need to to complete the run. I have search the Agent set-up and can find no flag to stop it halting processes when an error occurs.
The servers are configured not to allow RPC and I would rather not have to put a job on every server to tell me they exist as this would need managing going forward.
Any help you can offer would be greatly appreciated - Happy New Year by the way!
View 4 Replies
View Related
May 11, 2000
Hello,
Has anyone come up with an easy way to change the code page sort order in the master database. Unfortunately, I inherited two machines running different codes and neither one is ANSI standard. I would like to get them both back to ANSI. Each one contains approximately 15 custom databases.
Any suggestions except prayer would be much appreciated.
Thanks,
View 1 Replies
View Related
Apr 11, 2008
Hi, I have a formview with a label databound field in it. I want to change the labels CssClass property from code behind AFTER the record has been updated using a sqldatasource. In which event do I change the CssClass property? Or do I use Attribute.Add instead of setting the CssClass? Or is there some other property/method/way to accomplish this? Thanks, Z
View 2 Replies
View Related
Apr 28, 2006
I am having a hard time finding materials on this subject. I am guessing I am using the wrong keywords to search. Basically, I want to be able to modify database tables through a web form. They can add columns and delete columns through the form. I would just want to default the type of column and the length. I am sure it has been done, I was just wondering if anyone had some resources they could throw my way. I would appreciate it. Thanks.
View 1 Replies
View Related
Jul 26, 2000
Any experience with upgrading from 6.5 to 7.0 and changing sort order / code page in the process? Does this cause problems or matter? We're trying to go with a standard and the servers I need to upgrade aren't at that code page standard.
Thanks..
View 2 Replies
View Related
Feb 21, 2007
We have developed a project with many SSIS packages. Now we are in a stage where we are deploying this into production.
The operaitons team has asked us what are the things they need to known and do to make sure that the production system of SSIS keeps running.
Can you help me in comming up with a detailed list which I can give to the operations/admin folks so that they can ensure that this project keeps running?
I would appriciate any help in this matter.
regards,
Abhishek.
View 5 Replies
View Related
Feb 28, 2006
Hi,
I have a package that I developed on my local machine and runs well. I run
it through the command line on my production server the following way:
D: estCMS_MoveContentToLive>dtexec /f
D: estCMS_MoveContentToLiveCMSMoveIte
mToLive.dtsx -de
The -de option is because i changed the default security protection level of
the package as BOL says that for the default protection level
(EncryptAllWithUserKey): "Only the user who created or exported the package
can open the package in SSIS Designer or run the package by using the dtexec
command prompt utility."
The problem is that the package does not run successfully and vomits a lot
of error message that I do not understand, such as:
<errorMessage>
Error: 2006-02-15 18:13:43.55
Code: 0xC004706E
Source: Data Flow Task DTS.Pipeline
Description: The module containing "component "Multicast" (637)" cannot
be lo
cated, even though it is registered.
End Error
Error: 2006-02-15 18:13:43.55
Code: 0xC004706E
Source: Data Flow Task DTS.Pipeline
Description: The module containing "component "OLE DB Destination" (756)"
can
not be located, even though it is registered.
End Error
...
Error: 2006-02-15 18:13:43.62
Code: 0xC0048021
Source: Data Flow Task Multicast [637]
Description: The component is missing, not registered, not upgradeable,
or mi
ssing required interfaces. The contact information for this component is
"Multic
ast;Microsoft Corporation;Microsoft SqlServer v9; (C) 2005 Microsoft
Corporation
; All Rights Reserved; http://www.microsoft.com/sql/support;0".
End Error
Error: 2006-02-15 18:13:43.62
Code: 0xC0047017
Source: Data Flow Task DTS.Pipeline
Description: component "Multicast" (637) failed validation and returned
error
code 0xC0048021.
End Error
</errorMessage>
Notice that at the end it says that the validation failed.
I looked on the internet but I did not found any information regarding this
issue.
I also created a dummy SSIS package that does not have any data flaw but
just have a single SQL task doing a single insert in a table. When i
execute it on the production server, it runs successfully!
Then what is wrong with my "complex" package that all the data flow
components seems to: "can not be located, even though it is registered." ???
To be exact, I have an error for all the data flow components except the
script component.
Thanks for any help.
Best regards,
Francois Malgreve
PS:
I also would like to mention that on my production server, only the SSIS
service is installed, not SQL Server itself.
When you run the setup of SQL 2005, it is indeed possible to install only
the SSIS component which is what I need in my case as SSIS packages are run
from a middle tier server and connect to various DBs.
I think this is important as I just discovered that the package can run
successfully when I run it on a server which has a full version of SQL
Server 2005. By that I mean having SQL Database Server + SSIS installed. But it will run only from the command line, if i run it from an asp.net application it won't run successfully and return the same kind of error i showed in this message. in a desesparate attempt to solve my problem, I granted more rights to the ASPNET user and NETWORK_SERVICE user by adding them in the administrator group as I quite believe it might be related to security as on that server it works well from the command line. But it did not help.
View 1 Replies
View Related
Apr 10, 2006
I have migrated several (7) DTS packages to SSIS in a development environment. In this environment these packages all work as designed. I am now in the process of placing the .dtsx files on the production server and scheduling jobs around them.
I am encountering problems in 3 of these 7 packages. I get the following error:
Message
The job failed. The Job was invoked by User <domainuser>. The last step to run was step 1 (Create and Transfer Credentialling Data).
This is not what I would call very informative. I have opened the dtsx files in question in BIDS on the production server and each has the following error reported:
Error loading <name>dtsx; Failed to decrypt protected XML node "PassWord" with error 0x8009000B" Key not valid for use in specified state." You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.
I have looked at the package setting "Protection Level" and all seven packages have the same setting: EncryptSensisitveWithUserKey
Each of the connections within the packages have been defined to utilize Windows Authentictation. And the jobs are set to run under the SQL Agent Account.
What I find most puzzeling is that each of these packages are defined exactly the same and some work correctly and others do not.
Thanks for taking the time help me with this issue.
View 5 Replies
View Related
Dec 19, 2007
Hi All,
I have created an SSIS package which will import data from EXCEL file to SQL server. I had assigned the excel file path using an expression which wil be a combination of the folder path where the excel file resides + file name + date+extension. I also assigned the connection string for the OLEDB using the Expression. I have also exported the Folder path and the eonnection string variables to the package configurations and i have created the deployment project, which contains the package, packagemanifest and the package config file.
Now i need to know the steps to install the package in the production server and the steps to schedule the job in the sql server.
any of you plz give me the link to find the steps or plz tel me the things that i have to do.
Thankz to all
View 5 Replies
View Related
Feb 27, 2008
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.
View 1 Replies
View Related
Apr 2, 2015
I built a SSIS(writing out to a flat file ) in 32 bit machine and it woks fine . But however when I deploy to the produciton server(64 bit) the SSIS writes out garbage data . After some research I found out that the problem with the 32 bit OS and 64 bit OS problem.What is my next step. Am I out of luck that now I will have to redesing the SSIS in 64 bit?
View 5 Replies
View Related
Aug 14, 2006
Hi There,
In SSIS package development environment, I was able to connect to an oracle database and pull data into my sql server database. I installed the client tools for oracle and I put an entry into the tnsnames.ora and I was able to connect.
But in production environment, if I deploy the package on sql server, I was wondering if I had to do the same job of downloading the oracle client tools onto my production machine --which creates a tnsnames.ora file to it default location and then edit it with tthe tns entry-- or is there a better way to do this--avoiding the download?
Can somebody plzz help me ?
Thanks.
View 10 Replies
View Related
Jan 7, 2008
Greetings,
I need to install the SSIS components on the referenced database server. I believe the SSIS components are cluster aware, so I was planning on just doing add/remove and selecting the components. Rather than breaking the cluster apart to patch the components back to SP1, I was thinking of just running the SP2 install right over the top of the running cluster to bring everything up to SP2.
I'm looking for any suggestions/comments on whether this is the proper way to proceed or if I'm going to have a problem.
TIA
View 1 Replies
View Related
Jun 2, 2006
Hi,
I'm finding that the standard components often just don't quite meet my
needs, but would only need some fairly minor changes to save me and my team a lot of
work (and produce more elegant solutions). So I was just wondering whether the source code was available for the standard components that come with SSIS, or if there is anyway to extend their functionality?
Or do you just have to start form scratch?
Thanks,
Lawrie
View 4 Replies
View Related
Jun 16, 2006
I am having problems exporting data into a flat file using specific code page. My application has a variable "User::CodePage" that stores code page value (936, 950, 1252, etc) based on the data source. This variable is assigned to the CodePage property of desitnation file connection using Property expression.
But, when I execute the package, the CodePage property of the Destination file connection defaults to the initial value that was set for "User:CodePage" variable in design mode. I checked the value within the variable during runtime and it changes correctly for each data source. But, the property of the destinatin file connection doesn't change and results in an error.
[Flat File Destination [473]] Error: Data conversion failed. The data conversion for column "Column01" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
[DTS.Pipeline] Error: The ProcessInput method on component "Flat File Destination" (473) failed with error code 0xC02020A0. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
If I manually update the variable with correct code page and re-run the ETL, everything works fine. Just that it doesn't work during run-time mode.
Can someone please help me resolve this.
Thanks much.
View 5 Replies
View Related
Jun 6, 2006
Greetings my SQL friends,
I have a very simple package which consists of a SQL Task component and a Script Task.
The SQL Task retrieves a single value from 1 row table in one of my database and assigns that value to a variable. The Script task simply outputs the value of the variable in to a message box. I know, it's all simple stuff (I am trying to master SSIS )
The problem I have is that when I change the value of the column in my table, the value of the variable is not changing when I re-run my package. It seems to be the same value every time.
What am I doing wrong?!
Your help would be much appreciated.
View 8 Replies
View Related
Apr 15, 2008
i am transfering the table from one database to csv file format..i did it.. again i want to shift that csv files to another databse as tables. how to do this task.. pls help me.. its very urgent..out TL had given me the dead line.. send reply soon....
View 5 Replies
View Related
Sep 5, 2006
To make certain SSIS features work there are many properties that need to be set over and over for most containers in the package. For example with CheckpointRestart, you need to set (in most cases) all of the tasks' FailPackageOnFailure to True. If you miss one, the package may not restart properly and you might never know. There are other situations where as a development team we want certain properties to be usually set the same but differently from the SSIS default.
Is there a way to control the defaults that the SSIS IDE uses?
I remember back in classic VB that if you wanted to change the defaults of a bare form you could create a template form adjusted the way you like and put it in a templates folder. Then new forms added to a project would be based on the template form.
View 5 Replies
View Related
Feb 7, 2008
I'm new to SSIS so I have not found the answer yet. I have a SSIS package that needs to be run everyday,that loads a basic text file. The only problem is that the text file may not be in the same location everyday so my users will need to locate the path prior to running the package. Is there a way to browse to the text file then have my users execute the package sort of like the old DTS in SQL 2000.
Shanon
View 4 Replies
View Related
May 1, 2006
Everytime when I set this value to true; it saves but when I close the package and reopen it. It returns to false, I am using OLE DB connection... Help Please
View 1 Replies
View Related
Dec 3, 2007
I've been building a prototype using Webservices (coded in .NET) and SSIS to move data from our order/fulfillment system to our accounting system. I have struggled a bit with SSIS's WebService Tasks; but, finally have a package that runs locally that calls a web service and loads the data into SQL Server. I tried to move this to our server, I modify the HTTPConnection Parameter to the Product WS URL and the web service task fails with an "Unable to Connect to Server" error.
So, I decided to try running the production WS in my local (development) SSIS. I change the HTTP Connection URL, save, rebuild and run. The job runs successfully - against my LOCAL Web Service. It did not recognize the change or is somehow cached.
How do I get it to run against my Production Web Service???
Thanks.
Jennifer
View 4 Replies
View Related
Jan 28, 2008
Hi All,
Recently in an SSIS package I am getting the following error for a particular Data flow task.
Error: 2008-01-25 12:01:48.58
Code: 0xC0202009
Source: Import Datasynapse Data User Events Source [3017]
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x8000FFFF.
End Error
Error: 2008-01-25 12:01:48.73
Code: 0xC004701A
Source: Import Datasynapse Data DTS.Pipeline
Description: component "User Events Source" (3017) failed the pre-execute phase and returned error code 0xC0202009.
End Error
Our guess is when the data size of User Events table is more it throws this error. If we try to transfer small subset of data it succeeds. What could be reason for this error?
Since this is very urgent, immediate response would be very much appreciated.
Thanks & Regards,
Prakash Srinivasan
View 4 Replies
View Related
Feb 23, 2006
I saved a simple export/import package from the import/export wizard to a .dtsx file/package. Now I want to change the package to delete/truncate all of the tables before adding data. (since this has 80 tables it would have been a pain to edit each of them individually)
How can I easily go about doing this with the SSIS package. I can't find out where this would actually be executed.
Also, how can I actually view the code behind the SSIS package.
Thanks.
View 3 Replies
View Related
Feb 5, 2007
Hi!
I create a SSIS Package for ETL on my own machine. During development database was also on my machine. For access to this database an OLE DB connection was defined within a package in BI Development Studio. Everything worked well both in debug mode and testing package itself.
Finally I need to load data to a database on a different machine using this package.
I used several scenaries:
1) simply copied the package-file to estination machine, open it for execution, in section "Connection Managers" I edited connection string manually - changed server name and Initial Catalog. And try to execute.
2) on the destination machine I manually created an OLE DB connection (using Microsoft Data Link) to a different database (test succeded), Changed the extention of the connection file 'udl' for ' txt ' and copied its connection string to the field connection string in section "Connection Managers" (pointed in variant 1) ).
3) use Package Configurations, copied the deployment to destination machine, installed the package the way like written here - http://msdn2.microsoft.com/en-us/library/ms365338.aspx. Changed exported properties - Server name, Initial Catalog and also the whole Connection String. Also try to execute.
In all cases I recieved the same error execution message :
"Errors in the metadata manager. Either the database with ID of " OLD_DATABASE_NAME " does not exist in the server with ID of " NEW_SERVER_NAME " or the user does not have permissions to access the object."
As for access (username/pass) settings they are the same for both of them, I have the same administrative rights on both machines. And more with the same rights the ole db connection made was made manually in variant 2 - succeded!!! So I don't think the problem is here.
As for Error message - I think somewhere the OLD name of database (Initial Catalog) is saved, though I tried to change it. Though the NEW value for the server name is substituted.
Please, help me. I don't know what else can I try. And it is not a single case for my practice. So I think - something wrong in my actions.
View 9 Replies
View Related
Feb 22, 2008
I wrote a simple SSIS package that runs a batch and a stored proc. They both run fine. I can see the stored procedure run and in process under task manager I see the batch run. However I don't want the batch to run under the System account, how can I change to another user. Thank you.
View 4 Replies
View Related
May 16, 2006
First let me say, I really can't believe this chain of events myself--and they are happening to me.
I am upgrading several DTS packages to SSIS on what will be my new production server. These packages create tables, export them to a flat file, and ftp them off to other locations.
What is happening (on the SSIS side) is that the OLE DB Source is reordering some of the columns on its own (moving them to the end of the table/file. Then when my pickup/load routines run, the data is out of place and they fail.
Can anyone please explain what is happening here with the mapping. I have evaluated the table and the columns are in the order that I expect. When I preview the source table in the OLE DB Source Editor the columns are in the correct order/alignment, but when them in the OLE DB Source Editor --Columns section within BIDS the order is changed arbitrarily.
I have been somewhat successful (2 out of 3) in being able to re-map the data, but this last table just doesn't want to change.
Thanks in advance for any help and/or information you can provide
View 13 Replies
View Related
Mar 25, 2008
In my SSIS package I have a loop container that I am running the same code against 4 servers.
I have the package export the SQL data to an Excel spreadsheet that has multiple tabs.
Is there a way I can change the tab on the fly or do I need to create a Connection for the same spreadsheet 4 times
Each Connection pointing to a different tab?
I tried to set up a expression for the Excel Connection Manager to use the InitialCatalog for the tab and change it
based on the script in the loop however this causes the following error:
An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
Cannot create an OLE DB accessor. Verify that the column metadata is valid.
Thanks in advance
View 22 Replies
View Related
Feb 23, 2007
Hi,
I have a small problem. I've gone through the SSIS wizard and created a dtsx file which imports data from an access file into a SQL Server 2005 database. It has been set to delete existing rows and enable identity insert.
I then edited the .dtsx package in SQL Server Management Studio and added an environment variable configuration to allow me to change the destination database. In the script which runs the dtsx, here is what I have (it's an x64 system, so hi have to use DTExec):
"C:Program Files (x86)Microsoft SQL Server90DTSBinnDTExec.exe" /file e: estimport.dtsx /set Package.Connections[DestinationConnectionOLEDB].Properties[InitialCatalog];newdatabasename
and here is the error I get:
Description: The configuration environment variable was not found. The envir
onment variable was: "InitialCatalog". This occurs when a package specifies an e
nvironment variable for a configuration setting but it cannot be found. Check th
e configurations collection in the package and verify that the specified environ
ment variable is available and valid.
I got the package.connections.etc etc path from originally creating the environment variable as an xml config file, then I could open the config file and see what the path was...
Any help would be appreciated :)
View 11 Replies
View Related
Apr 5, 2006
We have successfully built a SSIS ETL implementation for a data mart. Most of our dimension loads are using the slowly changing dimensions task. We successfully built every package and had 1 complete successful load.
We have made a modification to one of the tables that now has 16 mil records in it. I open the dimension's package and it takes 30 sec - 1 minute to validate. When I go to the Data Flow tab and attempt to open the Slowly Changing Dimension task, SSIS will freeze up. I will get no response for hours. I my testing so far SSIS has yet to come back.
My CPU usage is below 5%. devenv.exe memory usage is at 113 Mb. My computer is using 400mb / 1Gb of memory. The status bar at the bottom says "ready". I have let it sit for up to 2 hours with no results. When I kill the process in the Windows Task Manager it ends instantly.
I have also tried deleting the task and adding a new Slowly Changing Dimension Task. When I select the table from the drop down I get the same results. I have noticed this with all my million + record tables that use the slowly changing dimension task but not the smaller tables.
I am also seeing the problem when I am in "Work Ofline" mode.
I am accessing a SQL 2005 server over a VPN connection. I am connecting to the server using a Native OLE DB OLE DB for SQL Server connection. My computer is running Windows 2000 SP4, Intel Pentium M1.86 Ghz, 1 Gb of RAM.
Can a Slowly Changing Dimension Task be used on large tables? I can understand the process taking a while to run but I do not understand why I can not edit the task.
View 3 Replies
View Related
Nov 14, 2007
What is the best way to run SSIS scripts on different servers without changing connection information. Our test server is ppntt140 and our production server is ppntd110. If I create a script on server ppntt140 what can I do so I can move it to server ppntd110 without changing any connection information? Database names are the same, it is just the server that changes. What is the best way to handle this? Thanks in advance.
View 4 Replies
View Related
Jun 5, 2007
I am testing the SCD with some sample data and am seeing duplicate rows getting inserted into my table. When configuring the wizard I am prompted to select a key type (Business or Not a key column). I assumed that the SCD wizard would NOT insert a row that already has a business key in the dimension table but that doesn't seem to be the case??? Is that by design?
View 4 Replies
View Related
