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'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?
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 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.
I have been asked to write a script to hit about 50 SQLServer databases to check the individual status of those dbs. and i need help to write this script.
what i need to do is.
1. hit sqlserver#1 and hit all the databases from that box and see whether the sqlserver dbs are ok or are suspect. if they are anything else other then OK then I send an email via sendfile. 2. hit sqlserver#2 and continue doing this.
is this possible? do anyone of you know how i can do this programatically via an ssis job or have a script that I can use?
A small part of our project needs to be customizable for each of our clients. Each of our clients have the concept of a "JOB"; The details of which are held in their own database. When our product is installed it must gather Job information from their database and match the appropriate fields to our generic Jobs table. Once this has been done, we can generate a stored procedure that will periodically poll the clients database for new/updated jobs and transfer the data to our database.
What we really need is to come up with something that has a generic UI that will allow us to connect to any data source, enumerate the databases and their tables so it will allow someone to perform the operations above.
My question is can SSIS assist us in achieving this goal??
I have created a package which imports a unit of measure table. I now want to populate more rows in the same table from itself. In Access VBA the below code extract does the first part of the job I need to do. Can anyone point me in the right direction to convert this into an efficient SSIS solution. SQLstr =€?SELECT [UOM conv].[Short Item No], [UOM conv].UOM, Count([UOM conv].[UOM 2]) AS [CountOfUOM 2] €œ & _ €œFROM [UOM conv] €œ & _ €œGROUP BY [UOM conv].[Short Item No], [UOM conv].UOM €œ & _ €œHAVING (((Count([UOM conv].[UOM 2]))>1)); €œ
Set RsMoreThanOne = db.OpenRecordset(SQLstr) With RsMoreThanOne While Not .EOF SQLstr = "SELECT [UOM conv].* FROM [UOM conv] WHERE ((([UOM conv].[Short Item No])=" & ![Short item No] & ") AND (([UOM conv].UOM)='" & !UOM & "'));"
Set RsSql = db.OpenRecordset(SQLstr, dbOpenSnapshot) RsSql.MoveLast x = RsSql.RecordCount Set rs = db.OpenRecordset("UOM Conv") rs.Index = "PrimaryKey" RsSql.MoveFirst Item = RsSql![Short item No] For y = 1 To x Um1(y) = RsSql![UOM] Um2(y) = RsSql![uom 2] Fct(y) = RsSql!factor RsSql.MoveNext Next y For y = 1 To x - 1 For k = 1+y To x rs.Seek "=", Item, Um2(y), Um2(k) If rs.NoMatch Then rs.AddNew rs![Short item No] = Item rs!UOM = Um2(y) rs![uom 2] = Um2(k) rs!factor = Fct(k) / Fct(y) rs!calculated = True rs.Update Else If rs!calculated = True Then rs.Edit rs!factor = Fct(k) / Fct(y) rs.Update End If End If rs.Seek "=", Item, Um2(k), Um2(y) If rs.NoMatch Then rs.AddNew rs![Short item No] = Item rs!UOM = Um2(k) rs![uom 2] = Um2(y) rs!factor = Fct(y) / Fct(k) rs!calculated = True rs.Update Else If rs!calculated = True Then rs.Edit rs!factor = Fct(y) / Fct(k) rs.Update End If End If Next k Next y
RsSql.Close .MoveNext rs.Close Wend .Close End With db.Close
Say I do File -> New -> Project and make a new project called RedApple under C:. For simplicy, I chose root, but I would designate location for all my SSIS projects. I also check the box "Create directory for solution".
I see that this creates C:RedApple
Under this, I have another RedApple directory with a .sln and .suo files. In this 2dn RedApple directory, I have .database, .dtproj, and .dtproj.user files.
I deleted the default package1, so I don't have any .dtsx files yet.
As far as the above files are concerned (.sln, .suo, .database, .dtproj, .dtproj.user), I understand these are some kind of system or meta data files and I shouldn't be messing around with them. And that's as far as I need to concern myself on these files.
As I add packages to my project, I see that it is creating .dtsx files under the 2nd RedApple directory. So, I'm thinking these are the package files, one for each package I see in my solution explorer, correct?
MY QUESTION: Now, I am looking at another SSIS directory tree for a project that I created before I started to pay attention to all this. In this other directory tree, I see that there is also a bin directory, which I do not yet have in C:RedAppleRedApple. In this bin directory I see more .dtsx files, the same ones as I see in the directory above it, but also other .dtsx files that have the names of other packages that I onoce created and deleted along the development of this particular project. What are these .dtsx files?
We need to migrate data from Sqlserver2000 database to Sqlserver2005. The SQLserver2000 DB was poorly Normalized and the DB was redesigned inSqlserver 2005. Both the databases are operational.Currently we have around 90 tables that need to migrate the data from the OLD DB to NEW DB, all the table mappings between the old schema and new schema have been successfully established.
Each Table is treated on its own merit and applying its own business rules which allows the data to be transformed to the new tables.The client wants us to build a SSIS solution that needs to performs the following
a. Import all Look Tables.
b. Import all master Tables ( One time import of some tables data and other tables may need to import every 4 hrs)
c. Import all Transaction tables( One time import and other tables may need to import every 4 hrs of data)
My Question is how to apporoach this scenario of pulling one time lookup, master and transaction tables and scheduling 4 hrs data importing process, how to build a SSIS solution for this type of scenario.
SELECT a.TestID, a.TestCode FROM TableA a WHERE UPPER(RTRIM(a.TestCode)) IN SELECT (SELECT UPPER(RTRIM(b.TestCode)) FROM TableB b)
Of course the above query is missing a few things but with ETL the where clause UPPER(RTRIM does not appear to be something that has an object or property that I can use in the Lookup.
I would like to have my SSIS tasks to be transacted, but due to infrastructure issues in our network, cannot utilize DTC, because our SQL Boxes are situated where the DTC cannot communicate. I know we can use execute SQL task with Bein Trans, but I dont think dataflow and all will not be in scope of this and cannot be rollback in case of any error in the underlying tasks.
I have created a solution which contains only 2 packages say Package1.dtsx and Pakage2.dtsx. I want to create a deployment utility to deploy onto other developers machince. I changed the project properties "CreateDeploymentUtility" to TRUE. When I do the build it is not creating the files in "Deployment" folder. It is saying Rebuild All Failed but the error is not showing.
For more information the 2 packages have 4 indirect configurations from environment variables which are storing the actual config file path.
I have a solution with a couple of ssis projects in it. Everytime I open the solution, Visual Studio creates an extra .database file for the project's existing xxx.database file. The solution is under VSS control and VS2005 checks out the project and shows the file as a newly added file.
What causes this and how can I prevent this from occurring?
There are 2 of us working on the solution and the other fellow does not see this behavior.
The files do not show up in the VS2005 solution explorer. If they are user specific as I suspect (impersonation info?), then they should not be added to the .dtproj project file.
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?
VB.NET 2005 Express and SQL Server 2005 Express - NOT saving updates to DB - SOLUTION!
-----------------------------------
The following article is bogus and confusing:
How to: Manage Local Data Files - Setting 'Copy to Output Directory' to 'Do not copy' http://msdn2.microsoft.com/en-us/library/ms246989.aspx
You must manually copy the database file to the output directory AFTER setting 'Copy to Output Directory' to 'Do not copy'.
Do not copy
The file is never copied or overwritten by the project system. Because your application creates a dynamic connection string that points to the database file in the output directory, this setting only works for local database files when you manually copy the file yourself.
You must manually copy the database file to the output directory AFTER setting 'Copy to Output Directory' to 'Do not copy'.
-----------------------------------
The above article is bogus and confusing.
This is rediculous!
This is the most vague and convoluted bunch of nonsince I've ever come accross!
Getting caught out on this issue for the 10th time! And not being able to find an exact step-by-step solution.
--------------------------
I've tried it and it doesn't work for me.
Please don't try what the article eludes to as I'm still sorting out exactly what is supposed to be happening.
If you have a step-by-step procedure that can be reproduced this properly please PM me.
I would like to test its validity then update this exact post as a solution rather than just another dicussion thread.
Many thanks.
This is the exact procedure I have come up with:
NOTE 1: DO NOT allow VB.net to copy the database into its folders/directorys.
NOTE 2: DO NOT hand copy the database to a folder/directory in your project.
Yes, I know its hard not to do it because you want your project nice and tidy. I just simply could NOT get it to work. You should NOT have myData.mdf listed in the Solution Explorer. Ever.
Create a folder for your data following NOTE 2.
Copy your data to that folder. * mine was C:mydatamyData.mdf
Create a NEW project.
Remove any Data Connections. ( no matter what)
Save it.
Data | View Data Sources
Add New Data Source
select NEW CONNECTION ( No Matter what, do it!
Select the database. * again mine was C:mydatamyData.mdf
Answer NO to the question: Would you like to copy the file to your project and modify the connection? - NO ( no matter what - ANSWER NO ! - Absolutely NO ) Then select the tables you want in the DataSet. and Finish.
To Test ----------
From the Solution Explorer | click the table name drop down arrow | select details Now Drag the table name onto the form.
The form is then populated with a Navigation control and matching Labels with corresponding Textboxes for each field in the table.
Save it.
1) Run the app.
Add one database record to the database by pressing the Add(+) icon
Just add some quick junk data that you don't mind getting lost if it doesn't save.
YOU MUST CLICK THE SAVE ICON to save the data you just entered.
Now exit the application.
2) Run the app again.
And verify there is one record already there.
Now add a second database record to the database by pressing the Add (+) icon.
NOW add some quick junk data that you WILL intentionally loose.
*** DO NOT *** press the save icon.
Just Exit the app.
3) Again, Run the app.
Verify that the first record is still there.
Verify that the Second record is NOT there. Its NOT there because you didn't save the data before exiting the app.
Proving that YOU MUST CLICK THE SAVE ICON to save the data you just entered.
Also proving you must add your own code to catch the changes and ask the user to save the data before exitiing or moving to another record.
As a side note, since vb.net uses detached datasets, (a copy/snapshot of the dataset in memory and NOT directly linked to the database) the dataset will reflect all changes made when moving around the detached datasets. YOU MUT REMEMBER TO SUBMIT YOUR CHANGES TO THE DATABASE TO SAVE THEM. Otherwise, they will simply be discarded without notice.
Whewh!
I hope this saves me some time the next time I want to start a new database project.
Oh, and uh, for anyone else reading this post.
Thanks, Barry G. Sumpter
Currently working with: Visual Basic 2005 Express SQL Server 2005 Express
Developing Windows Forms with 101 Samples for Visual Basic 2005 using the DataGridView thru code and every development wizard I can find within vb.net unless otherwise individually stated within a thread.
I have an SSIS package built by another developer, and now that I'm running it under my login the passwords won't save. The solution and packages are setup with ProtectionLevel EncryptSensitiveWithUserKey, but how do I get the User Key to reset so I can now save passwords? I can re-enter them, but whenever I enter hte password and test it then click OK it still has the red arrow next to the connection as if there's an error. I can create new connections and those passwords save fine, but with 40-50 items in this package I hate the thought of having to go into each and change the connection.
I tried changing the package and solution to DontSaveSensitive then rebuilding and closing then reopening, I hoped there was some option to reset the User Key just as if I created the solution. If this option doesn't exist why?
we have automated build on every night. In our solution is SSIS project, where each package is encrypted per password. We call build process per command line like this..
C:ProgrammeMicrosoft Visual Studio 8Common7IDEdevenv.exe (c:DevelopmentX3\X3.sln /build Release)' in 'c:DevelopmentProjectsDailyBuild
Through build process we get a error:
External Program Failed: C:ProgrammeMicrosoft Visual Studio 8Common7IDEdevenv.exe (return code was 1):
We think a reason is, that on build of SSIS project must be entered a password. You can wonder for what we need that SSIS packages are part of our build. We hope that on build process is also created Deployment Utility, if so set in dtproject.user. Is it so? Is there any way to create Deployment utility on automated build process? Can be a password provided pre command line?
The following is my code. What I am trying to do is find all the students a teacher as assessed during a give time. Then find out which assessment was done the most recently. After that I will then be aggregating those results. I have never written any pl/sql or T-SQL... heck I don't even know what to call it!The first sql command is doing what I want it to. I can only assume the cursor is working correctly. Any help would be greatly appreciated.BryanALTER procedure Domain@UserID numeric,@StartDate datetime,@EndDate datetime AS-- Variable DeclerationDECLARE @SessionID varchar(1000)DECLARE @EachSessionID numeric --Cursor to find all Children that the teacher has assessed for the given timeDECLARE ChildID_cursor CURSORFORSELECT DISTINCT childID FROM capsessionWHERE userid = @UserIDAND sessiondate BETWEEN @StartDate AND @EndDate-- looping through all Children to find there most recent assessment.OPEN ChildID_cursorDECLARE @ChildID numeric FETCH NEXT FROM ChildID_cursor INTO @ChildIDWHILE (@@FETCH_STATUS <> -1)BEGIN IF (@@FETCH_STATUS <> -2) BEGIN DECLARE SessionID_cursor CURSOR FOR SELECT TOP 1 CAPSessionID FROM CapSession WHERE (ChildID = @ChildID) AND (SessionDate BETWEEN @StartDate AND @EndDate) ORDER BY SessionDate DESC END FETCH NEXT FROM ChildID_cursor INTO @ChildIDENDCLOSE ChildID_cursor OPEN SessionID_cursorFETCH NEXT FROM SessionID_cursor into @EachSessionID SET @SessionID = ''WHILE (@@FETCH_STATUS <> -1)BEGIN IF (@@FETCH_STATUS <> -2) BEGIN SET @SessionID = @SessionID + @EachSessionID+ ',' END FETCH NEXT FROM ChildID_cursor INTO @ChildIDENDCLOSE SessionID_cursorRETURN @EachSessionID
Hi, I wanna know if one has to undergo the 'sql video tutorials' on this website before trying to understand data access tutorials, again on this site. Are the two related by any chance. Also I am not able to find the customer database files he has created in tutorial # 5, dumb to ask, but what to do?,...so anyone willing to shed light!!!
as you might have seen, I'm a total newbie to this Site and M$ SQL. I browsed the forum to see if there are already matching answers to my question, with no success.
My question: As a MySQL & PHP user I ask myself if there is a possibility to use M$ SQL the way I use MySQL and PHP!? Is there a way to generate dynamic websites with M$ SQL 2000 and a webserver running PHP sources? A brief yes or no would really help me, if you have any links or resources on this topic, I'd be glad to hear about it.
I get the error "General SQL Error" when reading or writing to a memo field in Sql Server through a frontend build in Delphi 4. I'm Using the latest BDE. I have recently installed this onto a NT enviroment in the same manner as my own enviroment but get the error message above.
On my own enviroment and a friends enviroment I do not get any errors.
Can someone please give me some help on this or a direction to run in ?
Hi, folks. I've a production SQL machine with more than 20 users making transactions 24 hrs in 6 days a week. I've only Sunday for maintenance. The server has fixed 2 GB RAM allocation for SQL. Is it good to Restart SQL ( or machine) to clear the Buffer-Cache( or is it good to keep the cache) .... :rolleyes:
Maximum Number of rows allowed in sql server 2005 tables ? Maximum Number of columns allowed in sql server 2005 tables ? What are all the built in string functions can be allowed for 'TEXT' DataType in sql server 2005?
Before I go into specifics, this is my problem. I've have a tablethat gets updated with large amounts of data on a monthly basis.Sometimes (rarely) identical rows of data are on one months import thatalready exist from the previous month. I can identify these rows from acombination of two fields (sampleID and testname).My question is this. Would it be an appropriate 'fix' if I created anew 'formula' field on the table comprising of a concatentation ofthese two fields and then made that an index field (no duplicates)? Myguess would be that if we then tried to import a record with a sampleIDand testname that already existed, then the import for that recordwould simply fail.Would this work? Is there a better way? My background is more withAccess so apologies if I'm not using the right terminology.Simon Harris
hi...i would like to know best practice when assigning a value to a variableexample belowsetorselectdeclare @var1 varchar(25)set @var1='abc'select @var1='abc'to meset is implicitselect implies conditionsi see both used all over the place here at my new company....so witch is better practicetksMJ
Is comp.databases.ms-sqlserver more active thanmicrosoft.public.sqlserver.server, or is it the other way around??Which NG is better for general and 'learning SQL 2005' types ofquestions??Thank you, Tom
