Losing rows from file to destination table - need troubleshooting help

I am losing data from time to time and can not figure out where the rows are vanishing to. In essence, I have 5 files that I process. The processing occurs on a daily basis by one SQLAgent job that calls 5 individual packages, each doing a different piece of work.

I stage each file to a temp table, then perform some minor transformations, then union the rowsets from each file before landing to another temporary table. A subsequent package performs additional transformations that apply to the entire dataset, before inserting them to their final destination. Along the way, I reject some records based on the business rules applied. Each package in the entire Job is logging(OnError, TaskFailed, Pre and Post execute). Their are no errors being generated. No rows are being rejected to my reject tables either.

Without getting into the specific transforms, etc. being used in this complex process, has anyone seen similar unexplained behaviour? I've not been able to identify any pattern, except that it is usually only 1 or 2 of the record types (specific to a source file), that will ever not be loaded. No patterns around volumes for specific source files. There are some lookups and dedupes, however I've seen the records 'drop off' before reaching these transforms. I have noticed I had my final desination load not using fast load. However sometimes the records disappear before even getting to my final staging table which is inserting using fast load. I am going to turn on logging the pipelinerowssent event. Any other suggestions for troubleshooting/tracking down these disappearing records?

Thanks

Couple more clarifications:

I have run the same files thru manually in debug mode to find that I can watch all the rows thru the entire process.

We have seen strange behaviour in running packages as scheduled jobs thru SQLAgent

Utilizes unions which seem a bit klunky.

|||

Joe,

We have seen similar problems on my current project so yesterday we turned on OnPipelineRowsSent logging.

Another thing we have done is output the data from each transform component to a file for later examination. The MULTICAST transform is invaluable in order to do this.

as yet we haven't found out what is going on. Its strange.

-Jamie

|||

I'm not sure I would use the adjective strange, but..

In trying to troubleshoot this process, I first changed the union tranform that was taking 6 input streams, and busted it out to 5 individual waterfalling unions each with 2 input streams. No change in behaviour.

I then changed the package that moves this data, by adding in multicasts to output to a file after every transform along the way up to the final destination, after the 5 unions. Just by adding the multicasts into the flow has resulted in no rows vanishing for the daily loads for the past week. Unfortunately, I don't have time to really troubleshoot further, but I think that this demonstrates that there is indeed a serious bug here. I still suspect it has to do with the union transform. I am quite scared for anyone else's shop that has decided to standardize ETL to this tool, as we have. As developers, we have only time to test our code, not testing that the native tool functionality is behaving as expected. In addition ,to have to monitor on a regular basis that it is performing properly, is not acceptable.

Hoping this problem magically went away with SP1....

JH

Losing it again - how to pass value to sp?

I cannot remember how to pass a value to a stored procedure. I would work this through but I am really running out of time, any help greatly appreciated. This is my stored procedure and I need to pass CompanyID from the code behind page in for the stored procedure @.C_ID value.

PROCEDURE dbo.EditCompanyInfo
@.C_ID int,
@.CS_CompanyName nchar(100),
@.CS_City nchar(50)

AS
UPDATE tblCompanyInfo_Submit
SET CS_CompanyName = @.CS_CompanyName, CS_City = @.CS_City
WHERE C_ID = @.C_ID
RETURN

This is my aspx. page:

<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" Runat="Server" >
<asp:TextBox ID="TextBox1" runat="server"> </asp:TextBox> <br />
<asp:TextBox ID="TextBox2" runat="server"> </asp:TextBox> <br />
<asp:TextBox ID="TextBox3" runat="server"></asp:TextBox><br />
<asp:DetailsView ID="DetailsView1" runat="server" DataSourceID="SqlDataSource2" Height="50px"
Width="125px">
<Fields>
<asp:CommandField ShowEditButton="True" ShowInsertButton="True" />
</Fields>
</asp:DetailsView>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString2 %>"
InsertCommand="CompanyInfoSubmit" InsertCommandType="StoredProcedure" OnInserted="SqlDataSource2_Inserted"
SelectCommand="SELECT CS_CompanyName, CS_City FROM tblCompanyInfo_Submit WHERE C_ID = @.CompanyID "
UpdateCommand="EditCompanyInfo" UpdateCommandType="StoredProcedure">
<UpdateParameters>
<asp:Parameter Name="CS_CompanyName" Type="String" />
<asp:Parameter Name="CS_City" Type="String" />
</UpdateParameters>
<InsertParameters>
<asp:Parameter Direction="ReturnValue" Name="ReturnValue" Type="Int32" />
<asp:Parameter Name="CS_CompanyName" Type="String" />
<asp:Parameter Name="CS_City" Type="String" />
</InsertParameters>
</asp:SqlDataSource>
<br />
</asp:Content>

CODE BEHIND:

public partial class aaatest : System.Web.UI.Page
{
int CompanyID;
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
DetailsView1.ChangeMode(DetailsViewMode.Insert);
TextBox1.Text = "insert";
TextBox3.Text = Convert.ToString(CompanyID);
}
}
protected void SqlDataSource2_Inserted(object sender, SqlDataSourceStatusEventArgs e)
{
{
foreach (System.Data.SqlClient.SqlParameter param in e.Command.Parameters)
{
string RValue = Server.HtmlEncode(param.Direction.ToString());
if ( RValue == "ReturnValue" && Page.IsPostBack)
{
TextBox1.Text = Server.HtmlEncode(param.Value.ToString());
TextBox2.Text = "Return";
CompanyID = Convert.ToInt16(TextBox1.Text);
TextBox3.Text = Convert.ToString(CompanyID);
}
}
}
}
}

This might help... although you'll need to adjust it to be UpdateParameters on an update etc.

SqlDataSource1.SelectParameters["ParamName"] =

newParameter("ParamName",TypeCode.String,"Value");sql

Loosing primari keys when system crash

Hello,
When we our MS-SQL server has system crash a lot of all our primair keys are lost.
Some time even fields are lost.
We are running MS-SQL server on a cluster and on a stand alone server
Dos some one no the problem and nows a solution.What kind of the backup strategy do you use?

Looping thru time..

I have a DTS PACKAGE IN SQL 2000 where I need to use vbscript to loop thru the files. I have the package detecting the directory - and the file, BUT the file itself has an interval number from 1- 48 set within it. So in one day I am getting 48 different files.

I can not delete previous files or move them - so I need to - each day - loop thru the day for each interval.

Any thoughts on how I can do this?

Interval 1 = 12:00 AM

Interval 2 = 12:30 AM

Interval 3 = 1:00 AM

etc. etc.

Thanks!

M

I am not clear exactly what you're after. If you want to translate intervals into respective times for the 48 intervals, you can do like so:

Code Snippet

--create dummy #digits table
select top 48 i=identity(int,1,1)
into #digits
from syscolumns

--the query
select i, right(convert(varchar,dateadd(minute,(i-1)*30,0),100),7) [time]
from #digits

--drop #digits
drop table #digits

|||

Well not exactly taking the intervals and transposing them into a time. Each interval means it is a certain time frame (a 30 min time frame within the 24 hours - giving me a total of 48 intervals in a day).

The file that I get - I get 48 times a day. So I have to loop thru the 48 files in activex for the dts. And I am a bit stuck on that part.

|||

Moved to the SSIS forum, since you are looking for a solution with no T-SQL code. This is more likely to have someone to answer it here. Thanks

|||Are you trying to do this in DTS or SSIS? If it's DTS, try this newsgroup - microsoft.public.sqlserver.dts.|||Forums site is screwy - finally was able to get in and edit/reply... this is for DTS in SQL 2000...

Looping thru time..

I have a DTS PACKAGE IN SQL 2000 where I need to use vbscript to loop thru the files. I have the package detecting the directory - and the file, BUT the file itself has an interval number from 1- 48 set within it. So in one day I am getting 48 different files.

I can not delete previous files or move them - so I need to - each day - loop thru the day for each interval.

Any thoughts on how I can do this?

Interval 1 = 12:00 AM

Interval 2 = 12:30 AM

Interval 3 = 1:00 AM

etc. etc.

Thanks!

M

I am not clear exactly what you're after. If you want to translate intervals into respective times for the 48 intervals, you can do like so:

Code Snippet

--create dummy #digits table
select top 48 i=identity(int,1,1)
into #digits
from syscolumns

--the query
select i, right(convert(varchar,dateadd(minute,(i-1)*30,0),100),7) [time]
from #digits

--drop #digits
drop table #digits

|||

Well not exactly taking the intervals and transposing them into a time. Each interval means it is a certain time frame (a 30 min time frame within the 24 hours - giving me a total of 48 intervals in a day).

The file that I get - I get 48 times a day. So I have to loop thru the 48 files in activex for the dts. And I am a bit stuck on that part.

|||

Moved to the SSIS forum, since you are looking for a solution with no T-SQL code. This is more likely to have someone to answer it here. Thanks

|||Are you trying to do this in DTS or SSIS? If it's DTS, try this newsgroup - microsoft.public.sqlserver.dts.|||Forums site is screwy - finally was able to get in and edit/reply... this is for DTS in SQL 2000...

Looping through one row at a time

Hoping for a little help... I'm attemting to call a stored proc, pass parameters, and display the data 1 record at a time. I need to be able to show the data in a series of lables or text boxes. So the user will see one record, pushed into the lables, click a button and go to the next record...so on and so forth.

I think I have the code to get the data correct, it's the displaying data in lables and looping through the recordset the has me clueless.

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
If Not Page.IsPostBack Then ' IF This is the first page load
Dim UserID As String = Request.QueryString("UserID")
' parameter for stored procedure
Dim RoleID As String = Request.QueryString("RoleID")
Dim DS As DataSet
Dim MyConnection As SqlConnection
Dim MyCommand As SqlDataAdapter
MyConnection = New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("connectionString"))
MyCommand = New SqlDataAdapter("getdirective", MyConnection)
MyCommand.SelectCommand.CommandType = CommandType.StoredProcedure
MyCommand.SelectCommand.Parameters.Add(New SqlParameter("@.roleID", SqlDbType.NVarChar)).Value = RoleID
Try
DS = New DataSet
MyCommand.Fill(DS)
'Display data in a series of lables or highly formated datagrid
Catch ex As Exception
Response.Write("<font color=red>Error: " & ex.Message & "</font>")
End Try
Else 'IF the page is being reloaded
End If
End Sub
Private Sub cmdAck_Click(...) Handles cmdAck.Click
'This need to loop through the records
End Sub

Thanks for any help!!!For that you need to maintain the total record number in a viewstate. And also for each click (previous, next, ..) you need to add/remove the nos respectively.

Hope it solves your problem.

Loooonnngggg delay in loading due to "pre-validation". Can it be turned OFF?

During dev. I _know_ certain things are invalid. I don't need the validation, it just wastes a lot of time...

You can set SSIS to be offline (SSIS Menu), or set the delay validation on the component.

Both these mean you can't validate the component at design time.

|||

Perfect. Thanks!

And thanks for not saying "RTFM"! (I have been reading but all it takes is one moment of lack of concentration and you miss some detail like this).

Ken

loooong columnnames

Hi,

A long time ago I set up a database with *very* descriptive and therefore
long table names. :(
I did the same with the column names. :((
The column names even repeat the table name. :(((
This was done in a period in which I was still using a lot of dynamic sql
:(((( and sql in the code of the app :(((((.
The tables all have the prefix 'tbl' (yes, I know) :((((((
There are even ghastly underscores in it too :(((((((
As a result, I'm starting to develop a nasty case of carpal tunnel syndrom
(aka RSI) :((((((((

For example there are names like:
dbo.tbl_SalesOrderLine.SalesOrderLine_SalesOrder
I have to type them in QA a gazillion times a day
The number of objects in the database has grown well beyond even beginning
to think about renaming them all.

Now my question: Is there an MS SQL client utility out there that has
autocomplete?

Cheers,

Bas"Bas" <nomailplease> wrote in message news:<412e45ab$0$144$e4fe514c@.dreader9.news.xs4all.nl>...
> Hi,
> A long time ago I set up a database with *very* descriptive and therefore
> long table names. :(
> I did the same with the column names. :((
> The column names even repeat the table name. :(((
> This was done in a period in which I was still using a lot of dynamic sql
> :(((( and sql in the code of the app :(((((.
> The tables all have the prefix 'tbl' (yes, I know) :((((((
> There are even ghastly underscores in it too :(((((((
> As a result, I'm starting to develop a nasty case of carpal tunnel syndrom
> (aka RSI) :((((((((
> For example there are names like:
> dbo.tbl_SalesOrderLine.SalesOrderLine_SalesOrder
> I have to type them in QA a gazillion times a day
> The number of objects in the database has grown well beyond even beginning
> to think about renaming them all.
> Now my question: Is there an MS SQL client utility out there that has
> autocomplete?
> Cheers,
> Bas

There's a list of alternative client tools here:

http://www.aspfaq.com/show.asp?id=2442

And of course:

http://www.google.com/search?source...de+autocomplete

Simon

Looking for direction on how to add 4000+ users into ASPNETDB manually

Hello and thanks for reading,

I would like to know if it's possible to add around 4000 users into the ASPNETDB without adding them one at a time. I have to avoid using a "create user wizard" because this login is for a private online election only. The list of users is in an access database and looks like this:

Username Password

DHunziker Hu8645

SEnderton En0854

BAckler Ac9576

...

Unfortunately, I don't have valid emails for all 4000 people, nor do I have the time and resources to collect security questions and answers so I don't know if this is even possible. Can you help me?

Thanks!

Peter Kellner (http://peterkellner.net/zdm_1_2/index.php) provides a nice membership management tool for this database and you can download from his site.

You can use his objectdatasource to do the batch insert. Here is a sample from a button click:

Create a SpreadSheet with these columns: UserName,password, and email.

Import this excel file to this database as table: yourUsers$

Here is the code for the button click:

protected void batchInsertButton_Click(object sender, EventArgs e) {string strConn = ConfigurationManager.ConnectionStrings["1ConnectionString"].ConnectionString;string strSQL ="SELECT * FROM yourUsers$"; SqlConnection myConnection =new SqlConnection(strConn); myConnection.Open(); SqlCommand myCommand =new SqlCommand(strSQL,myConnection); SqlDataReader myReader; myReader = myCommand.ExecuteReader();while (myReader.Read()) { ObjectDataSourceMembershipUser.InsertParameters["UserName"].DefaultValue = myReader["UserName"].ToString();//TextBoxUserName.Text; ; ObjectDataSourceMembershipUser.InsertParameters["password"].DefaultValue = myReader["password"].ToString();//TextBoxPassword.Text; ObjectDataSourceMembershipUser.InsertParameters["passwordQuestion"].DefaultValue ="your qestion";//TextBoxPasswordQuestion.Text; ObjectDataSourceMembershipUser.InsertParameters["passwordAnswer"].DefaultValue ="your answer";//TextBoxPasswordAnswer.Text; ObjectDataSourceMembershipUser.InsertParameters["email"].DefaultValue = myReader["email"].ToString();//TextBoxEmail.Text; ObjectDataSourceMembershipUser.InsertParameters["isApproved"].DefaultValue ="true";//CheckboxApproval.Checked == true ? "true" : "false"; ObjectDataSourceMembershipUser.Insert();//hard code this user role Roles.AddUserToRole(myReader["UserName"].ToString(),"NormalUser"); } myConnection.Close(); GridViewMemberUser.DataBind(); GridViewRole.DataBind(); }

Hope this helps.

|||

Wow! Thanks!

I have already got peter's managment tool running and the information that you provided is very helpful. Thanks a million!