How To Test SSis Package And What Are The Things I Need To Test It ?
Nov 27, 2007
hi every one,
i need to test SSIS pacakge which will import data from different database where record count is around 5 millions.
iam planning to test it through c# code as well as manually also.
SSIS source : consist of 7 tables
SSIS destination :consist of 7 tables
Using c# code iam trying to run ssis package through batch file.
i am putting expected rowcount, column count in an excel file and comparing same with destination tables by writing query implementing ADO.Net concept.
am i going right way ,can any one suggest best and productive way to test the ssis package .
what are the other things i need to test it.
do any one can add test cases to it.
S.No
Test Case
1
Verify all the tables have been imported.
2
Verify all the rows in each table have been imported.
3
Verify all the columns specified in source query for each table have been imported
4
Verify all the data has been received without any truncation for each column.
5
Verify the schema at source and destination
6
Verify the time taken /speed for data transfer
7
Fields truncated due to difference in length of the field at destination.
Regards
Arif shareef
Now that we have a good programming model in SSIS - the question is whether to write automated unit tests for your packages, and would it generally be a good idea for packages?
Also - if yes to write tests - then where to find more informations regarding How to accomplish that?
I want to do something likeif "Product_code" is Nullinsert dbo.t_Shopping_cart (Product_code,Name,Price,Product_group,Quantity,Total)select Product_code,Name,Price,Product_group,Quantity=1,Yhteensa=1FROM dbo.t_Shopping_cartWHERE Product_code='AHTU140213' elseupdate dbo.t_Shopping_cartset Quantity=Quantity+1where Product_code='AHTU140213' In short: I cant test existence of a record.RegardsLeif
We are setting up a test lab environment with 100 machines. We want one master testing db that gets replicated to each to run scripted application tests nightly.
My goal is to minimize the amount of work to move this thing to each of the 100 test machines. I am wondering if we need to even have the sql local and invest in a monster db server with 100 copies of the db we restore and each test machine point to their own db on that server, or if I should use db mirroring or something to get the master test db to each of those machines instead.
I need to restore test DB from production backup but once it is restored I would need all the permissions of sql logins and windows AD account intact in test Db as it was before.
Are global variable parameters in a DTS package specific to "only" that one package and do not effect other packages?
I need to add a variable to a test package but am worried that if I add this parameter it might affect the live production Table. There are two different servers on different sides of the firewall, there are two different databases and there are two tables but with different names, and there are DTS packages but they reference different tables in the query code.
So is it ok to add the global variable which would allow this DTS to delete recent records from the "test" version and it wont do anything to my live version?
I am able to run the package successfuly in test database. but not in production database. It throughs up error saying
Description: Unable to load the package as XML because of package does not have a valid XML format. A specific XML parser error will be posted. Description: Failed to open package file "D:\TAHOE\APPS\SSISPackages\Integration Services Packages\ArchiveMain.dtsx" due to error 0x80070015 "The device is not ready.". This happens when loading a package and the file cannot be opened or loaded c orrectly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format. End Error Could not load package "D:\TAHOE\APPS\SSISPackages\Integration Services Packages\ArchiveMain.dtsx" because of error 0xC0011002. Description: Failed to open package file "D:\TAHOE\APPS\SSISPackages\Integration Services Packages\ArchiveMain.dtsx" due to error 0x80070015 "The device is not ready.". This happens when loading a package and the file cannot be opened or loaded corr ectly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format.
I am trying to test to see if my code is returning rows. If it's not I want to display an error saying "Nothing Found" Please review and give me your thoughts on the best way to accomplish this. 1 Protected Sub btnLogin_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnLogin.Click 2 Dim SubEmail As String 3 Dim SubPassword As String 4 SubEmail = txtNewsEmails.Text 5 SubPassword = txtNewsPassword.Text 6 Session("NewsEmail") = SubEmail 7 Session("NewsPassword") = SubPassword 8 Dim sID As Integer 9 10 Dim cs As String = ConfigurationManager.ConnectionStrings("csTiPs3").ConnectionString 11 Dim cn As SqlClient.SqlConnection = New SqlClient.SqlConnection(cs) 12 cn.Open() 13 Dim selectString As String = "Select SubscriberID from NewsletterSubscribers WHERE SubscriberEmail = '" + SubEmail + "' AND SubscriberPassword = '" + SubPassword + "'" 14 15 Dim cmd As SqlClient.SqlCommand = New SqlCommand(selectString, cn) 16 17 Dim reader As SqlDataReader 18 reader = cmd.ExecuteReader 19 While reader.Read() 20 sID = reader("SubscriberID") 21 End While 22 Session("SubscriberID") = sID 23 24 reader.Close() 25 26 rtsNewsletters.SelectedIndex = 1 27 rtsNewsletters.FindTabByText("Subscribe").Enabled = True 28 rmpNewsletters.SelectedIndex = 1 29 30 End Sub
I recently lost my job and wanted to do some test development to keep my skills up to date. The problem is I don't have access to any data sources. Is there such a thing around? I tried installing microsoft's trial of SQL 2005 but can't get it to run on my laptop and SQL 2000 trial no longer exists. I simply need to create a SQL db/tables etc. Thanks in advance.
Hi! I am currently utilizing the checksum function to generate a hash that I later compare to detect changes in a row. CHECKSUM(field1, field2, field3, field4) Now I'd like to use the HashBytes function instead over the same fields. But the HashBytes function accepts only one data value. What is the most effective and reliable way of getting an MD5 over several fields? Thanks
Hi. I have an application where I allow users to type in their SQL queries. Before I store those queries I have to make sure that they are correct, both syntax-wise and data type-wise. For that I execute the query against the database and trap any errors that may be returned and that's how I judge if the query was OK. In order to keep this test as quick as possible, I tried to add a WHERE clause to it like: WHERE 1=2, so no results are returned. But then I discovered that the addition keeps errors from happening if they are of ata type nature.For instance "select orderid + 'test' from orders where 1=2", run against the Northwind database, returns no errors, while OrderID is numeric and U'm adding a string to it! Next, I tried to return only one row: "select top 1 orderid + 'test' from orders". This time the error is thrown, however the query still takes a looong time when run on a huge table. I don't kknow why that is, but it seems that the engine runs the query for the entire table and then gets the first row! Does anyone have an idea ehat's happening or have a better suggestion on how I can perform my test without killing the database? Thanks.
I have a stored procedure which runs a query, then turns it on its side (so that the rows are column headers).What I need to do now is test if any of the values in a row from this stored procedure are null.Any suggestions on how to get started? Is there some function that will tell me if any value in the row is null?
I'm having a problem with a VB6 program I'm writing that posts data to a SQL server. The problem occurs when the SQL server is unavailable. When this happens it causes the program to hang for at least 30 seconds before releasing. The hang happens at the code that connects to the SQL server, because the server isn't there. I've added code found on this site to test the connectivity prior to making my actual data connection, but this causes the same hang. Is there any way to verify connectivity to an SQL server without actually trying to make the connection? I'm really trying to eliminate the hang that occurs when the server is unavailable.
I have gotten some criticism from coworkers regarding this test and just wanted to see what you guys think. I realize the wording could use improvement and any criticism towards making it easier to understand is much appreciated.FWIW - I had to solve this problem on the job so I feel it is a real-world test that helps me understand how people think and if they try to find alternate solutions.Thanks!~~~~~~~~~~~~~~~~~~~~Given a table that has over 100,000 records…SUBSIDIARY=========PARENT_IDINTCHILD_IDINTULTIMATE_PARENT_IDINTCLEANUP_INDBIT…where each PARENT_ID can have multiple CHILD_ID values, but the PARENT_ID should not equal the CHILD_ID. After an initial data load, the ULTIMATE_PARENT_ID and CLEANUP_IND columns contain NULL values (see page 2 for sample data).ULTIMATE_PARENT_ID is defined as the topmost parent in the chain for the particular CHILD_ID record, so if the chain was only 2-level’s deep the ULTIMATE_PARENT_ID is the CHILD_ID’s PARENT_ID’s PARENT_ID.Please write an answer for all three questions below:A)Which of the following queries should you run first?B)Write an optimized query to identify the ULTIMATE_PARENT_ID for each CHILD_ID and set its value into the ULTIMATE_PARENT_ID column.C)Write a query to identify ALL of the circular references and mark each record that is a circular reference by updating the CLEANUP_IND column to 1.~~~~~~~~~ Page 2 ~~~~~~~~~ Sample Data, remember though this table has over 100,000 records and the parent-child chain can go n-levels deep – where n is not known.PARENT_IDCHILD_IDULTIMATE_PARENT_IDCLEANUP_IND1024512NULLNULL362300NULLNULL887541NULLNULL10221024NULLNULL546887NULLNULL5122305NULLNULL112967NULLNULL697123NULLNULL901452NULLNULL2300666NULLNULL334445NULLNULL512903NULLNULL884554NULLNULL313313NULLNULL554884NULLNULL112119NULLNULL967555NULLNULL2305333NULLNULL33336NULLNULL541546NULLNULL10301020NULLNULL112999NULLNULL
Whats the best way to test scalability on a database? I'm working on a new app, and have handed off developing the database to someone with more experience. Some tables will grow to many million records, and I don't want it to bottleneck. I need to have it fully tested before it goes live.
Is it reasonable to ask the person helping me to fill it with 10's of millions of rows to test performance? Is this a decent solution? If so, what would the best way to fill it be? If not, what steps should I take?
HI... i need test papers on SQL DB. multiple choice with 3 levelsof difficulty: beginner, intermediate and advanced. can anyone tell mewhere i can get them made? or anyone who can help me with this? anysites... any info will be helpful. thanks in advance.
Can anyone tell me how to find out if a .sdf file is encrypted? Im using C# and would like to know if the db that I am trying to open is encrypted. I would then like to attempt to open the db.
How can I test sql codes and don't want to connect to company database? My company is using sql server 2000. Below is the test codes which create a simple table, calculate and just want to check before writing more complicated codes.
create table EquipmentUptime ( equipmentId int not null ,transactionId int not null ,dateOccured datetime not null ) go -- insert into EquipmentUptime values (1,200,'01/01/2007') insert into EquipmentUptime values (2,200,'01/01/2007') insert into EquipmentUptime values (3,200,'01/01/2007') insert into EquipmentUptime values (2,100,'02/12/2007') insert into EquipmentUptime values (1,100,'02/25/2007') insert into EquipmentUptime values (3,100,'03/10/2007') insert into EquipmentUptime values (1,200,'03/14/2007') go -- select equipmentId ,100*(cast((cast(sum(UpDays)as decimal(6,3))/cast(365 as decimal(6,3))) as decimal(4,3))) 'PctUpTime' from ( select c.equipmentId ,datediff(dd,c.[UpDate],c.DownDate) 'UpDays' from ( select a.equipmentId ,a.dateOccured as 'UpDate' ,coalesce( (select top 1 b.dateOccured from EquipmentUptime b where transactionId=100 and b.equipmentId=a.equipmentId and b.dateOccured >= a.dateOccured order by b.dateOccured asc ),'01/01/2008') as 'DownDate' from EquipmentUptime a where a.transactionId=200 ) c ) d group by d.equipmentId
We have Asynchronous DB Mirroring established for our Production database
which runs on SQL Server 2005.
For the DR Test we plan to do the following during the period of low activity :
1. Pause the mirroring on the Principal Server.
2. Break the mirror on the Mirror Server
3. Take on offline backup on the Mirror.
4. Bring the Mirror Database up.
5. Run the DR Tests on the Mirrored Database
6. Restore the Offline Backup taken in step 3.
7. Reestablish the mirror from the Principal to Mirror.
8. Resume Mirroring on the Principal Server.
9. Verify Mirroring is happening on the Mirror Server
Can u please let me know if this plan is feasible and if there should be any modifications to the plan that are required. Any other suggestions/input is appreciated.
Is there a way to manually run a subscription? I have a report with an email subscription. The only way I know of to test it is to set the schedule for a minute or two in the future and wait for it to run. Is there any way of telling the subscription to "run now"?
For that matter, how can I run a subscription manually from a Stored Procedure?
We are setting up a strictly test lab with virtual servers. One of the servers will have SQL loaded on it that other servers will hit. The question is can we use our MSDN license for SQL? If not what kind of license do we need. We also need to know about the licensing of the OS on the virtual machines so if anyone knows the information about that it would be great to get.
How do you guys get a baseline test of your database? I mean how do you test your SQL database to make sure it is fast enough?
Also, how do you know that the numbers you are getting back are in fact fast enough? I mean is there a gauge that one goes by to verity that an INSERT should take no longer then 'n' miliseconds...
