Looking For Some General Feedback On Working With SQL, SSIS And SAP
Jun 14, 2007
This is less of a specific question and more of a request for for some advice as to possibilities and directions. Here's the current situation. My company is using SAP for its purchasing, inventory, etc. This system is pretty much opaque to me - it's managed by another group within the company, and changes to it go through a complicated approval process. At the same time, the majority of our users, internal and external, are looking at this same data through a more accesible and more user friendly collection of web applications - done in classic ASP, up through ASP.NET 1.1 and 2 - and stored in an assortment of MS-SQL 2000 databases. Data is exchanged between SQL and SAP via DTS packages, some nightly, some run more frequently.
There's some issues here - data is never quite synchronized between the two sides, sometimes the same data must be updated twice, leading to possible data integrity issues, etc. Given that, we're going to be moving to SQL 2005 within the next year or so. From everything I've understood, within that context, there are vastly better ways of dealing with out situation than the way we're currently doing it.
So what I'm looking for is just a general impression of what can be done, with SSIS and SAP. Any approaches that might prove more fruitful, an y pitfalls to watch out for, that sort of thing.
I have an idea to generate an animation file in SSIS based on the data it imports.
Has anyone done that? Is Flash the best format for this or something else? I have programmed in several languages, and written programs before that wrote other programs, but I have never used any of the animation tools such as Flash. Can anyone point me to some resources I can use to quickly get up to speed on how to write such animations, in whatever tool you'd recommend? The tool needs to be low footprint as the application I have in mind would go on Internet for customers to see.
Any feedback on this idea whatsoever is most welcome indeed.
I have a package which consists of 3 Execute SQL Tasks -
1) Drop old database & Restore a new one 2) Run DDL 3) Run DML
My task was to put all of these in a transaction. I started using the "TransactionOption" property for this package to be "Required" and each child tasks also were set to the same transaction option property of "Required". However, after running the package, it errored out saying Task 1 cannot be in a transaction (which is logical) so, I removed the transaction required property from the first task and kept it on for the remaining 2 tasks. In addition, I have also set up a failover strategy, where in if these tasks were to fail, the package should restore the previous working copy of the backup. To test this scenario, I deliberately created an error in the DML task, so logically only this task should rollback, instead it rolls back the entire transaction and to my horror the failover step is not executed as well.
Is there something which I am not doing correctly?
I have built a package that firstly shrinks a database and then backs it up which is scheduled to run each day, but I would like to add a check (Check Database Integrity) in as the first task and if the Database checks out ok then continue on, if not send an email.
Now, I am unsure if the Check Database Integrity Task in SSIS actually returns the success or failure message back to the package that I am after. The SQL behind the task includes the NO_INFOMSGS option.
Can anyone advise if it is possible to have the status/integrity of the database returned to the SSIS task and to proceed based on the result?
I'm fully supportive of the product Feedback Center initiative and the subsequent withdrawal of sqlwish.
Problem is, if nobody ever gives us feedback on the things that we submit then it simply is a glorified version of sqlwish with no added value.
I apologise if there are plans afoot to address to offer feedback to the things we put up there but I (and others) have been freely submitting bugs and suggestions for a few months now without hearing anything back and I'm beginnign to wonder why we bother.
Even a simple "This is a good idea and will be considered for Katmai" or "This is a terrible idea now go and stick your head back in the sand" would be better than a cut and pasted response which is just about all I've seen so far.
I'm pretty new with this whole SSIS, so I'm still trying to figure out what this whole thing is about. My question is, will SSIS allow me to copy an Oracle Database into SQL Server 2005 and also be able to schedule a time to run and update the new data for that day?
I need to load Excel files, which have 200+ columns. There is a check for every column, some are simple type and range checking, some checking involve a little bit complex logic that depends on other columns. The legacy problem use a VB code check each column one by one, row by row, and color the problematic cells.
- What's the best practice to do it? - How to generate a human readable error log in SSIS? the output error log is so hard to read.
I need to create an SSIS package that takes a table name as a parameter and exports out its data in a CSV file.The challenge is that if I use a data flow task and a flat file connection manager (for csv export), I have to specify the file/table structure. I will not know the table structure until the run time because it is input specific.I was thinking along the lines of using C# in a script task, but could not come up with a full blown solution.
This is first time I am trying to ste timeout on SSIS package ,Can some one let me know which is the best way to set timeout for connection? And what is the difference between General TimeOut and Command TimeOut?
I am using the script below to update a DB2 table and it successfully executes. However, the database doesn't update. Can anyone help me?
Public Class ScriptMain Inherits UserComponent
Dim connMgr As IDTSConnectionManager90 Dim conn As OdbcConnection Dim cmdSelect As OdbcCommand Dim cmdUpdate As OdbcCommand Dim cmdInsert As OdbcCommand Dim paramInsert As OdbcParameter Dim paramSelect As OdbcParameter Dim paramUpdate As OdbcParameter Dim ds As DataSet
Public Overrides Sub AcquireConnections(ByVal Transaction As Object) connMgr = Me.Connections.Connection conn = CType(connMgr.AcquireConnection(Nothing), OdbcConnection) End Sub
Public Overrides Sub PreExecute() cmdUpdate = New OdbcCommand("UPDATE WEBLIB.A2JBMSKHF SET A2DESC = @FullName WHERE Rtrim(A2JOBN) = @FileNameNum", conn) paramUpdate = New OdbcParameter("@FullName", OdbcType.VarChar) cmdUpdate.Parameters.Add(paramUpdate) paramUpdate = New OdbcParameter("@FileNameNum", OdbcType.VarChar) cmdUpdate.Parameters.Add(paramUpdate) End Sub
Public Overrides Sub BorrowerRecords_ProcessInputRow(ByVal Row As BorrowerRecordsBuffer) Try With cmdUpdate .Parameters("@FileNameNum").Value = Row.FileNameNum.Trim.ToString() .Parameters("@FullName").Value = Row.FullName.Trim.ToString() .ExecuteNonQuery() End With Catch ex As Exception ex.ToString() End Try End Sub
Public Overrides Sub ReleaseConnections() connMgr.ReleaseConnection(conn) End Sub End Class
Is the intent of Microsoft to enforce a vertical presentation of the SSIS Control Flow, Data Flow etc? I like to work down and to the right and I am having problems with the connectors (precedent) or in DTS 2000 world Success or On Success implied. Is there away to connect from the lower center anchor point to the side of the next object in the flow? For some reason, I can't get a similiar look to DTS 2000 environment (going from the bottom middle of the object to the side anchor pint (small box) symbol to the left. It wants to anchor on the bottom right, left, or middle, if going horizontally. I must be missing something, here??
This may be a question for multiple forums but here it goes.
I have a large SSIS package that works great in Visula Studio (BIDS) and in SQL Server under MSDB folder. The properties are all set to save with a sql server authentication package password and that works great too. The problem occurs when I try to use this package in a schedule. I get an error about an OLB DB not working. I've tried many things and figured out that all the OLB DBs do not work in this package but only error as a STEP in a schedule.
They all reference a Client Access ODBC connection - which is a connection to an AS400 IBM machine. The actual error is pasted below, where I guess the root of the problem is a incorrect password. The password is fine and I have proven that by running the package successfully under the MSDB folder of Stored Packages under Integration Service.
Message Executed as user: CNBSQL2SYSTEM. ...ion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 3:29:35 PM Error: 2007-11-22 15:29:38.13 Code: 0xC0202009 Source: HTEIMPORT2 Connection manager "Client Access ODBC Driver (32-bit) 7" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft OLE DB Provider for ODBC Drivers" Hresult: 0x80040E4D Description: "[IBM][Client Access Express ODBC Driver (32-bit)]Communication link failure. comm rc=8002 - CWBSY0002 - Password for user COGNOS on system 172.16.1.2 is not correct". End Error Error: 2007-11-22 15:29:38.13 Code: 0xC020801C Source: Airport PR010ap v2 OLE DB Source 25 [60] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Client Access ODBC Driver (32-... The package execution fa... The step failed.
Please provide any insight if you can - I am still new at SQL2005.
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 a simple Data Flow within a Squence Controller and have added a Event Handler for OnPostExecute which contains a simple insert to a table, but this is not working. My package gets successfully execute but data is not getting inserted in the table used in Event Handler. I have also tried OnError, OnPackageFailer etc... but no results. Please guide.
I'm still fairly new to some of SSIS's enhanced funtionalities, one of them being Event handlers. I have tried creating a simple package, that simply contains a SQL Execute task that basically creates a simple table. Now i have then added a OnPostExecute even handler as a test which basically runs a script task that simply shows a msgbox. I have gone to execute the package, however after the completion of the SQL Execute task, nothing happens, the even handler doesnt get fired for some reason, i switch to the even handler page and the script task has not been executed. It is like this for every event handler i have tried, even the OnError event. Could this be a problem with my installation of SSIS or have i done something wrong?
Any help would be greatly appreciated as i have done SSIS training and to create an event handler was never this hard!
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???
Might be purchasing the SQL Spotlight product, any experience/input from current users would be appreciated. Looks like good product, little worried about the overhead
I haven't touched MSSS in a looong time When a SELECT returns say 3 rows what does the feedback say ? (eg Oracle/sqlplus says "3 rows selected") What if "no data found", what is the feedback ?
hi. ive just written my first complex SP for SQLServer 2005. id be REALLY grateful for any feedback please. if there's anything i'm doing wrong then it's better that i know sooner rather than later! im wondering if its over complex or just plain wrong. please bear in mind that i have at least made an attempt at this, this is my first proper SP.
the SP that I have written is to insert an Account and a User. The SP should do the following.
1. Start a transaction. 2. Insert a user by calling a nested SP. if the return value is greater than zero then the user has been successfully inserted. 3. only attempt to insert an account afterwards if the user has been inserted successfully. 4. if the user or account insert is not successful then rollback. otherwise commit the transaction 5. return an error code from the SP as such: 0 = success. 1 = failure, a user already exists with the given username. 2 = an unknown error occurred whilst inserting the user. 3 = an unknown error occurred when inserting the Account.
I have some long running scripts which I fire at my database using osql.(These are big files and mostly doing inserts but some also do a few otherthings.) It would be nice to have some activity indication (other than thedisk activity light) that these are running. When I used to use Oracle,their equivalent to osql had an option to print a dot (without a carriagereturn) for every "n" statements. This gave a nice "I'm alive" indicator. Ican simulate this by adding a few "print" statements in my sql, but printalways adds a carriage return. Does anyone know a way of doing a print butwithout the addition of a CR (or CR/LF)? So that a second "print" sends itsoutput to the same line as the first?I know this is a nicety and I can live without it, but it would be nice.thanks in advance,Brianwww.cryer.co.uk/brian
I haven't touched MSSS in a looong timeWhen a SELECT returns say 3 rows what does the feedback say ?(eg Oracle/sqlplus says "3 rows selected")What if "no data found", what is the feedback ?thanks
I€™d like everyone€™s help is do some research into an often heard, but rarely explained, complaint related the SQL Express. Your answers will help me plan future versions of SQL Express. Feel free to respond directly in the forum or by sending e-mail to me. (Note: Remove the word online from the e-mail address in my profile or it will bounce.) The complaint: €œSQL Express is too big.€? I€™m trying to understand what this really means and what specific technical issues are caused by this €œbigness€?. Here are some questions to help frame your answer. Size means size 1. Is the size of the SQL Express installer package an issue for you? (SQL Express 32 €“ 52 MB / SQL Express Advanced ~250 MB) 2. Why is this size an issue? 3. Would you be willing to sacrifice functionality to reduce the size of the installer package? Size means disk space 1. Do the SQL Express binaries take up too much room on the hard drive? 2. Would you be willing to sacrifice functionality to reduce the amount of HD space needed? 3. Do your databases take up too much disk space? 4. Would you be willing to pay money to reduce the size of the database file? 5. How much money? Size means memory 1. Does SQL Express take up too much memory when it€™s running? 2. What impact does this have on you? 3. SQL Express currently reduces its memory set when it is idle which results in a delay when it becomes active again? Is this a reasonable trade-off to reduce memory usage when you€™re not using the database engine? 4. Do you normally use SQL Express for single user applications (local data store) or for multi-user applications (server data)? 5. If you run SQL Express as a server, do you run it on a dedicated computer or on a computer running other programs as well? 6. What kinds of programs does SQL Express have to share with? 7. Should SQL Express give up memory resources to other programs running on the same machine? 8. Are you willing to accept a reduction in performance in order to have memory resources shared? Size means something else 1. Is there something I didn€™t cover? I€™ll be tracking this thread, but will try not to comment to much since this is about your feedback, not my answers. Mike Wachal SQL Express Program Management
I wrote a post in the connect forums almost two weeks ago, but haven't got any response yet. So I'll post here as well, hoping that someone from the SQL team will stumble upon this.
My post is about a possible bug in SQL. Here's the url: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=342390
how to evaluate working day through script component in SSIS
I have a data source. I need to verify if the date belongs to working day, if it does belongs to holiday or weekend ,then I need to send that row to the out put table. I think the only way I can verify this working day issue through script component. Can any one give me some idea/thoughts?
hi all iwant to make feedback after insert data or delet date Like( the data is sucssfuly way) or(data is delete sucssuse way) but not alert iwant feedback thank you
I am interested in hearing from those who are using SQL7 in production. Please include size of database, number of users, implementation date and experiences to date - good bad or indifferent. Thank-you, Leo
Hi folks, I have implemented this technique to simplify SCD loads and also to maintain consistent units of work during update/insert of a single row. Wanted to get your feedback on this technique: performance, transaction issues, etc.
I send all rows to an OLE DB Command that performs both update and insert for a single row in a single command:
Code Snippet UPDATE PROPERTY SET ORD_TERM_DT = ? WHERE ACCOUNT_NBR = ? AND ORD_TERM_DT = '9999-12-31 23:59:59';
This way I can guarantee that if the termination (update) of an old row (say, row 10) succeeds, but insert of the new row 10 fails, that it will roll back. Otherwise, row 10 will get terminated without being replaced with a current record...
Performance: load of 7,734 changed records into a table of 6.8M existing records was roughly 8 seconds. The data flow task container TransactionOption = Required.
I submitted a posting to connect here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=287213
Here is what I wrote:
A number of tasks output data to variables or text files. e.g. WMI Data Reader Task, Execute SQL Task, Web Service Task.
Consuming that output in a data-flow isn't particularly easy. You either need a Flat File source adapter (which requires an othrewise superfluous connection manager) or write come code in a script component to parse the recordset. There is simply no easy way to push data from these sources into the dataflow.
Thw built-in mechanism for passing data between different tasks is raw files. Currently they can only be used by the dataflow but I see no reason why they couldn't be used by other tasks as well. It makes complete sense to me for a WMI Datareader Task to push some data into a raw file and then we chew that data up in a dataflow.
The following response came back
Our current architecture actually doesn't have the buffer system as in Data Flow, when you are in the Control Flow. What you are asking would require us to build a similar buffer system in the Control Flow, which is a fundemantal architectural change. We'll not be able to take this, sorry.
I'm afraid I don't understand that response. Obviously I know that buffers are only in the data-flow - but I don't see why that's relevant. Raw files are just files on the file system, same as any other. OK, their format is very proprietary but its you guys that built the format. Essentially all I'm asking you to do is output the data in raw file format as opposed to flat file format. There's no notion of buffers in a raw file because its just a lump of data. Or is there? If not, I'm afraid I don't understand your answer.
