Cursors vs. Queries: package

Showing posts with label package. Show all posts

Thursday, March 29, 2012

Cursor Problem in an DTS ActivX package.

I am using Cursor in an DTS ActivX package.

I got the problem when i update it's not updating prorerly Please find the code below and help me ..My insert is working fine but the problem with update it's only updating first record that to it's replace the last record in first record place

sSSDInsUpd = " DECLARE "& _ " @.CallID2 AS Int "& _ " Declare InsertCur Cursor For "& _ " SELECT DISTINCT CALLID "& _ " FROM [SSD] "& _ " WHERE ASSOCCLAIM is not null AND SSN IS NOT NULL AND "& _ " FILENETPROCESSFLAG=1 "& _ " OPEN InsertCur "& _ " FETCH NEXT FROM InsertCur "& _ " into @.CallID2 "& _ " WHILE @.@.FETCH_STATUS = 0 "& _ " BEGIN "& _ " If @.CallID2 not in (select CALLID FROM SSDTOARTS) "& _ " Begin "& _ " INSERT INTO SSDTOARTS ( "& _ " [ID], [CALLID],[FIRSTNAME],[MIDDLEINIT],[LASTNAME],[SUFFIX], "& _ " [DATEOFBIRTH],[SSN],[ADDR1],[ADDR2], [CITY], [STATE],[ZIP], "& _ " [DISTRICTCODE ],[COUNTYCODE ],[ACCENTCASENO],[ASSOCCLAIM], "& _ " [ASSOCCLMDTFILED ],[ASSOCCLMDECDT],[ASSOCCLMSTATUS], "& _ " [ASSOCCLMADDLACCENTACTION] , "& _ " [ASSOCCLMADDLACCENTACTIONTAKEN], "& _ " [SSAAPLFILED ],[SSAAPLDT],[SSAAPLRESULT], "& _ " [SSAAPLADDLACCENTACTION],[SSAAPLADDLACCENTACTIONTAKEN], "& _ " [SSAAplDecisionDt],[ROUNDNUMBER], [ARTSAplprocessedFlag],[ARTSprocessedDt],[DateModified] ) "& _ " SELECT DISTINCT "& _ " S.ID, S.CALLID, S.FIRSTNAME, S.MIDDLEINIT, S.LASTNAME,S.SUFFIX, "& _ " S.DATEOFBIRTH, S.SSN, S.ADDR1, S.ADDR2, S.CITY, S.STATE, S.ZIP, "& _ " S.DISTRICTCODE , S.COUNTYCODE , S.ACCENTCASENO, S.ASSOCCLAIM, "& _ " S.ASSOCCLMDTFILED ,S.ASSOCCLMDECDT, S.ASSOCCLMSTATUS, "& _ " S.ASSOCCLMADDLACCENTACTION , "& _ " S.ASSOCCLMADDLACCENTACTIONTAKEN, "& _ " S.SSAAPLFILED , S.SSAAPLDT, S.SSAAPLRESULT, "& _ " S.SSAAPLADDLACCENTACTION,S.SSAAPLADDLACCENTACTIONTAKEN, "& _ " S.SSAAplDecisionDt , S.ROUNDNUMBER, "& _ " 0 as ARTSAplprocessedFlag,Null as ARTSprocessedDt,getdate() as DateModified "& _ " FROM [SSD] AS S "& _ " WHERE S.ASSOCCLAIM = 1 AND S.SSN IS NOT NULL AND "& _ " S.CALLID = @.CallID2 AND S.FILENETPROCESSFLAG=1 "& _ " DECLARE @.err2 int "& _ " SELECT @.err2 = @.@.error "& _ " IF (@.err2 <> 0) "& _ " BEGIN "& _ " INSERT INTO SSDEXCEPTIONS (RecordData,ErrorMsg,Fileid,DateCreated) "& _ " SELECT "& _ " DISTINCT isnull(convert( varchar(10),CALLID),'') + ' |' + isnull(FIRSTNAME,'') + ' |' + "& _ " isnull(MIDDLEINIT,'') + '| ' + "& _ " isnull(LASTNAME,'')+ '|' + "& _ " isnull((SUFFIX),'') + '|' + "& _ " isnull(convert(varchar(10),DATEOFBIRTH ),'') + '|' + "& _ " isnull(convert(varchar(10),SSN),'') + '|' + "& _ " isnull(ADDR1,'') + '|' + "& _ " isnull(ADDR2,'') + '|' + "& _ " isnull(CITY,'') + '|' + "& _ " isnull([STATE],'') + '|' + "& _ " isnull(ZIP,'')+ '|' + "& _ " isnull(convert(varchar(10),DISTRICTCODE),'') + '|' + "& _ " isnull(convert(varchar (10),COUNTYCODE),'') + '|' + "& _ " isnull(ACCENTCASENO,'')+ '|' + "& _ " isnull(convert(nvarchar(2),ASSOCCLAIM),'') + '|' + "& _ " isnull(convert(varchar(10) ,ASSOCCLMDTFILED),'') + '|' + "& _ " isnull(convert(varchar(10) ,ASSOCCLMDECDT),'') + '|' + "& _ " isnull(ASSOCCLMSTATUS,'') + '|' + "& _ " isnull(convert(nvarchar(2 ),ASSOCCLMADDLACCENTACTION),'') + '|' + "& _ " isnull(ASSOCCLMADDLACCENTACTIONTAKEN,'')+ '|' + "& _ " isnull(convert(nvarchar(2),SSAAPLFILED),'') + '|' + "& _ " isnull(convert(varchar(10) ,SSAAPLDT),'') + '|' + "& _ " isnull(SSAAPLRESULT,'')+ '|' + "& _ " isnull(convert(nvarchar(2 ),SSAAPLADDLACCENTACTION),'') + '|' + "& _ " isnull(SSAAPLADDLACCENTACTIONTAKEN,'') + '|' + "& _ " isnull(convert(varchar(10 ),SSAAplDecisionDt),'')+ '|' + "& _ " isnull(convert(varchar(10 ),ROUNDNUMBER),'') ,ltrim(str(@.err2))," & sFileId & ",GETDATE() "& _ " FROM SSD WHERE CALLID=@.CALLID2 "& _ " END "& _ " END "& _ " ELSE "& _ " UPDATE [SSDTOARTS] SET "& _ " [ID]= S.ID, "& _ " [CALLID]= S.CALLID , "& _ " [FIRSTNAME]=S.FIRSTNAME , "& _ " [MIDDLEINIT]=S.MIDDLEINIT , "& _ " [LASTNAME]= S.LASTNAME, "& _ " [Suffix]= S.Suffix , "& _ " [DateOfBirth]=S.DateOfBirth , "& _ " [SSN]= S.SSN , "& _ " [ADDR1]= S.ADDR1 , "& _ " [ADDR2]= S.ADDR2 , "& _ " [CITY]= S.CITY , "& _ " [STATE]= S.STATE , "& _ " [ZIP]= S.ZIP , "& _ " [DistrictCode ]= S.DistrictCode , "& _ " [CountyCode ]= S.CountyCode , "& _ " [ACCENTCASENO]= S.ACCENTCASENO ,"& _ " [ASSOCCLAIM]= S.ASSOCCLAIM, "& _ " [ASSOCCLMDTFILED]= S.ASSOCCLMDTFILED , "& _ " [AssocClmDecDt]= s.AssocClmDecDt , "& _ " [ASSOCCLMSTATUS]= S.ASSOCCLMSTATUS, "& _ " [AssocClmAddlACCENTAction] = S.AssocClmAddlACCENTAction , "& _ " [AssocClmAddlACCENTActionTaken]= S.AssocClmAddlACCENTActionTaken, "& _ " [SSAAplFiled ]= S.SSAAplFiled , "& _ " [SSAAplDt]= S.SSAAplDt , "& _ " [SSAAplResult]= S.SSAAplResult, "& _ " [SSAAplAddlACCENTAction]= S.SSAAplAddlACCENTAction, "& _ " [SSAAplAddlACCENTActionTaken]= S.SSAAplAddlACCENTActionTaken , "& _ " [SSAAplDecisionDt] = S.SSAAplDecisionDt, "& _ " [ROUNDNUMBER]= S.ROUNDNUMBER , "& _ " [DateModified] = getdate(),"& _ " [ARTSprocessedDt]= NULL, "& _ " [ARTSAplprocessedFlag]= 0 "& _ " FROM [SSDtoARTS]AS SA, [SSD] AS S WHERE SA.CallID= @.Callid2 "& _ " AND S.ASSOCCLAIM IS NOT NULL AND S.FILENETPROCESSFLAG=1 "& _ " DECLARE @.err1 as int "& _ " SELECT @.err1 = @.@.error "& _ " IF (@.err1 <> 0) "& _ " BEGIN "& _ " INSERT INTO SSDEXCEPTIONS (RecordData,ErrorMsg,Fileid,DateCreated) "& _ " SELECT "& _ " DISTINCT isnull(convert( varchar(10),CALLID),'') + ' |' + isnull(FIRSTNAME,'') + ' |' + "& _ " isnull(MIDDLEINIT,'') + '| ' + "& _ " isnull(LASTNAME,'')+ '|' + "& _ " isnull((SUFFIX),'') + '|' + "& _ " isnull(convert(varchar(10),DATEOFBIRTH ),'') + '|' + "& _ " isnull(convert(varchar(10),SSN),'') + '|' + "& _ " isnull(ADDR1,'') + '|' + "& _ " isnull(ADDR2,'') + '|' + "& _ " isnull(CITY,'') + '|' + "& _ " isnull([STATE],'') + '|' + "& _ " isnull(ZIP,'')+ '|' + "& _ " isnull(convert(varchar(10),DISTRICTCODE),'') + '|' + "& _ " isnull(convert(varchar (10),COUNTYCODE),'') + '|' + "& _ " isnull(ACCENTCASENO,'')+ '|' + "& _ " isnull(convert(nvarchar(2),ASSOCCLAIM),'') + '|' + "& _ " isnull(convert(varchar(10) ,ASSOCCLMDTFILED),'') + '|' + "& _ " isnull(convert(varchar(10) ,ASSOCCLMDECDT),'') + '|' + "& _ " isnull(ASSOCCLMSTATUS,'') + '|' + "& _ " isnull(convert(nvarchar(2 ),ASSOCCLMADDLACCENTACTION),'') + '|' + "& _ " isnull(ASSOCCLMADDLACCENTACTIONTAKEN,'')+ '|' + "& _ " isnull(convert(nvarchar(2),SSAAPLFILED),'') + '|' + "& _ " isnull(convert(varchar(10) ,SSAAPLDT),'') + '|' + "& _ " isnull(SSAAPLRESULT,'')+ '|' + "& _ " isnull(convert(nvarchar(2 ),SSAAPLADDLACCENTACTION),'') + '|' + "& _ " isnull(SSAAPLADDLACCENTACTIONTAKEN,'') + '|' + "& _ " isnull(convert(varchar(10 ),SSAAplDecisionDt),'')+ '|' + "& _ " isnull(convert(varchar(10 ),ROUNDNUMBER),'') ,ltrim(str(@.err1))," & sFileId1 & ",GETDATE() "& _ " FROM SSD WHERE CALLID=@.CALLID2 "& _ " END "& _ " FETCH NEXT FROM InsertCur "& _ " INTO @.CALLID2 "& _ " END "& _ " CLOSE InsertCur "& _ " DEALLOCATE InsertCur "

Problem with While loop and if statement begin and end statements.check the below syantax This might help you

DECLARE @.price money
DECLARE @.get_price CURSOR

SET @.get_price = CURSOR FOR
SELECT price FROM titles

OPEN @.get_price

FETCH NEXT FROM @.get_price INTO @.price

WHILE (@.@.FETCH_STATUS = 0)
BEGIN
IF @.Price < 20
SELECT 'Under 20'
ELSE
SELECT @.Price

FETCH NEXT FROM @.get_price INTO @.price
END

CLOSE @.get_price
DEALLOCATE @.get_price

Cursor Problem in an DTS ActivX package.

I am using Cursor in an DTS ActivX package.

Problem with While loop and if statement begin and end statements.check the below syantax This might help you

DECLARE @.price money
DECLARE @.get_price CURSOR

SET @.get_price = CURSOR FOR
SELECT price FROM titles

OPEN @.get_price

FETCH NEXT FROM @.get_price INTO @.price

WHILE (@.@.FETCH_STATUS = 0)
BEGIN
IF @.Price < 20
SELECT 'Under 20'
ELSE
SELECT @.Price

FETCH NEXT FROM @.get_price INTO @.price
END

CLOSE @.get_price
DEALLOCATE @.get_price

Wednesday, March 21, 2012

Current package reference.

Iam developing a custom ssis source component, in that i need to get the reference of the current package where this component is added. I can get get the reference of the current package using this line of code

Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();

Microsoft.SqlServer.Dts.Runtime.Package pak = app.LoadPackage(@."C\DTS\Package1.dtsx",null);

Here i need to pass the path of the package. Is there any other way to get it (without the path)?

Thanks

Mani

You cannot get reference to the task or package from a component at run-time. The most you can do is to use variables. This is a deliberate design decision taken by MS early on with SSIS, to prevent self-modifying packages amongst other issues.

LoadPackage obviously requires the full path, and there is no other way. I would normally look to get a contextual reference to a parent object, but as a above this will not happen.

If you explain why you want to do this, we may be offer an alternative.

|||

I require the package reference to get the list of variables in that particular package. I tried using the

VariableDispenser method, but to get a variable it should be lock for read, for that the name of the variable is required. Is there any other method to do this?

Thanks

Mani

|||

What is wrong with using the VariableDispenser? Why would you want another method? The VariableDispenser is available from the base class you inherit when creating pipeline component, so it is available to you. Yes you have to ock variables, but again what is the problem with doing this?

IDTSVariables90 variables = null;

VariableDispenser.LockOneForRead("TableName", ref variables);

string tableName = variable.Value.ToString();

variables.Unlock()

|||

My problem is that i need to get the list of variables in the current package (without knowing their names). I need to list their names in the custom UI of the component as does the OLEDB component. Is there any method to do this?

Thanks

Mani

|||

This is where being clear in what you are trying to do and when will help. As I think I have pointed out there is no way or any need for you to enumerate variables at run-time.

At design-time I agree that this is higly desirable, and of course you can.

When building a UI, you will be using the IDtsComponentUI class, and notably the Edit method. This passes in a Variables collection which you can use, passing through to your form. You will probably also want the IDtsVariableService, which gives you a nice UI for creating variables as well. For this you use the IDtsComponentUI.Initialize method. One of the parameters is the IServiceProvider, which can be used to get the variable service. Cache the provider in a class level variable so you can access it in the Edit method, and pass through to with your UI. Convert it like this-

IDtsVariableService errorCollectionService = _serviceProvider.GetService(typeof(IDtsVariableService)) as IDtsVariableService;

|||

Why is it not possible to iterate the variables? If I want to capture variables (ie at the time of an error), I have to have a specific list of names that I want to capture for debugging purposes? Is there a list somewhere that lists all the variables that are available? What about variables that are added by other components that could help with debugging when an error is caused later on in the control flow or dataflow?

I'm just missing something completely here. Variables should be public or private and if you have them in a collection, (which they are), you should be able to iterate over them. Moreover, variables are such a basic part of any process, it should be dead simple on how they can / should be used.

|||

You can iterate variables, but not always. As above, I don't see it as an issue since you can do so at the approptiate times.

Yes, if you write some code/tasks in an On Error event, you do need to know in advance the variables you wish to read. The whole point is you would set this up at design-time. Do you expect your package to dynamically change during execution? This will not happen, and cannot happen at all in SSIS.

Components will not add any variables at runtime, so design-tme again.

Using variables is very simple. Do you actually have a real-world requirement that you could use to illustrate what you really want to do? You seem to be blustering about a problem that I don't think is there.

|||

Darren,

It baffles my mind that you cannot see why manidas wants to iterate through the variable collection at run time. Let me give some examples of why this would be nice to have and maybe you can then see the light.

1) Let's say I've got a package that pulls some startup variable values from a configuration files and the package is not working as I expect. I would want to see what the variables were at some point in time to help me troubleshoot my problem. Yes, I could write code that explicitly retrieved the values of each and every variable but that could be a lot of code when all you should have to do is write somethign to loop over the collection. Also, let's say I add or remove a variable. Now I have to go to all the places that I placed this code and update it.

2) It's pretty painful right now when you're editing a script (and you are using variables) because you can't get to the variable list while you're in the script editor. It would be nice if you could output all the variables to text and then copy and paste them into your code as you need them.

3) Almost all of my DTS packages get called from a SQL Agent Job so I send an email to myself when I'm done. Right now I use the connection collection to display the connections I'm using in the email. It would be nice to include the variables in that email as well.

Now, after just these three examples off the top of my head, if you can't see why not having access to the variables collection would be useful then either your logic skills are a bit lacking or you're trying to make excuses for something that isn't there but should be.

I'm not trying to be antagonistic but I am going to tell it like it is. If you want to debate this then I'm perfectly happy to respond to any reply you have to offer.|||

Sorry we are a newbie,

I want to add a dataflow task programmatically in the script task using the current package, Under dataflow task also create a new source and destination adapters,which uses the connection managers created. finally do copy column mapping and execute the dataflow. Is it possible? Did I mean here that it is a self modifying package. The above code gives me the current package reference using the path. Would you please give me hint of code how I can go about adding Dataflow task to the current package.

Thanks

Subhash Subramanyam

Bhavana Anand.

|||The current version of SSIS does not support adding or creating new tasks or data flows to a running package. You would have to generate a seperate package, which you could then call from the package that generates it.|||

HI,

Thank you very much for the forum. It is very helpfull to us to create new variables at runtime in UI. But we need to fill varibale details in combobox.

I used this below statement to create new varibale.

private IWin32Window parentWindow;

private DtsContainer dtsContainer;

private Variables vars;

System.IServiceProvider _serviceProvider;

private IDtsVariableService _dtsVariableService;

_dtsVariableService = _serviceProvider.GetService(typeof(IDtsVariableService)) as IDtsVariableService;

_dtsVariableService.PromptAndCreateVariable(parentWindow, dtsContainer);

Through this code i have created new varibale in varibale window at runtime in UI. But we are not able to get details of varibales which i create at runtime.

Thanks in Advance

ashok mohanty

|||

I don't get what you are asking. Can we clarify that when we refer to runtime, we mean that the package is executing. Design-time is when the user interface is manipulating the package or task. So a task UI can be running, but that is termed design-time for the package and dicussions above.

You can only create variables and manipulate objects at (package) design-time. You cannot create a variable at (package) runtime, so your statement "we are not able to get details of varibales which i create at runtime" does not make sense. You cannot get details, becaue you cannot create them so there are no details to get.

If you have a task designer or UI, and want a list of variables, look at the TaskHost parameter of the IDtsTaskUI.Initialize method, as it has the Variables collection as a property.

|||

If you just want to read the variables from the User Interface then it is easiest to just the "Variables vars" variable that should have been passed through to the UserInterface form in the constructor.

Then you can simply refrence the package paramaters either by iterating through them or using their name.

e.g. vars["Name_Of_Parameter"].Value

Current package reference.

Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();

Microsoft.SqlServer.Dts.Runtime.Package pak = app.LoadPackage(@."C\DTS\Package1.dtsx",null);

Here i need to pass the path of the package. Is there any other way to get it (without the path)?

Thanks

Mani

LoadPackage obviously requires the full path, and there is no other way. I would normally look to get a contextual reference to a parent object, but as a above this will not happen.

If you explain why you want to do this, we may be offer an alternative.

|||

I require the package reference to get the list of variables in that particular package. I tried using the

VariableDispenser method, but to get a variable it should be lock for read, for that the name of the variable is required. Is there any other method to do this?

Thanks

Mani

|||

IDTSVariables90 variables = null;

VariableDispenser.LockOneForRead("TableName", ref variables);

string tableName = variable.Value.ToString();

variables.Unlock()

|||

Thanks

Mani

|||

This is where being clear in what you are trying to do and when will help. As I think I have pointed out there is no way or any need for you to enumerate variables at run-time.

At design-time I agree that this is higly desirable, and of course you can.

IDtsVariableService errorCollectionService = _serviceProvider.GetService(typeof(IDtsVariableService)) as IDtsVariableService;

|||

You can iterate variables, but not always. As above, I don't see it as an issue since you can do so at the approptiate times.

Components will not add any variables at runtime, so design-tme again.

|||

Darren,

I'm not trying to be antagonistic but I am going to tell it like it is. If you want to debate this then I'm perfectly happy to respond to any reply you have to offer.|||

Sorry we are a newbie,

Thanks

Subhash Subramanyam

Bhavana Anand.

HI,

Thank you very much for the forum. It is very helpfull to us to create new variables at runtime in UI. But we need to fill varibale details in combobox.

I used this below statement to create new varibale.

private IWin32Window parentWindow;

private DtsContainer dtsContainer;

private Variables vars;

System.IServiceProvider _serviceProvider;

private IDtsVariableService _dtsVariableService;

_dtsVariableService = _serviceProvider.GetService(typeof(IDtsVariableService)) as IDtsVariableService;

_dtsVariableService.PromptAndCreateVariable(parentWindow, dtsContainer);

Through this code i have created new varibale in varibale window at runtime in UI. But we are not able to get details of varibales which i create at runtime.

Thanks in Advance

ashok mohanty

|||

If you have a task designer or UI, and want a list of variables, look at the TaskHost parameter of the IDtsTaskUI.Initialize method, as it has the Variables collection as a property.

Current package reference.

Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();

Microsoft.SqlServer.Dts.Runtime.Package pak = app.LoadPackage(@."C\DTS\Package1.dtsx",null);

Here i need to pass the path of the package. Is there any other way to get it (without the path)?

Thanks

Mani

LoadPackage obviously requires the full path, and there is no other way. I would normally look to get a contextual reference to a parent object, but as a above this will not happen.

If you explain why you want to do this, we may be offer an alternative.

|||

I require the package reference to get the list of variables in that particular package. I tried using the

VariableDispenser method, but to get a variable it should be lock for read, for that the name of the variable is required. Is there any other method to do this?

Thanks

Mani

|||

IDTSVariables90 variables = null;

VariableDispenser.LockOneForRead("TableName", ref variables);

string tableName = variable.Value.ToString();

variables.Unlock()

|||

Thanks

Mani

|||

This is where being clear in what you are trying to do and when will help. As I think I have pointed out there is no way or any need for you to enumerate variables at run-time.

At design-time I agree that this is higly desirable, and of course you can.

IDtsVariableService errorCollectionService = _serviceProvider.GetService(typeof(IDtsVariableService)) as IDtsVariableService;

|||

You can iterate variables, but not always. As above, I don't see it as an issue since you can do so at the approptiate times.

Components will not add any variables at runtime, so design-tme again.

|||

Darren,

I'm not trying to be antagonistic but I am going to tell it like it is. If you want to debate this then I'm perfectly happy to respond to any reply you have to offer.|||

Sorry we are a newbie,

Thanks

Subhash Subramanyam

Bhavana Anand.

HI,

Thank you very much for the forum. It is very helpfull to us to create new variables at runtime in UI. But we need to fill varibale details in combobox.

I used this below statement to create new varibale.

private IWin32Window parentWindow;

private DtsContainer dtsContainer;

private Variables vars;

System.IServiceProvider _serviceProvider;

private IDtsVariableService _dtsVariableService;

_dtsVariableService = _serviceProvider.GetService(typeof(IDtsVariableService)) as IDtsVariableService;

_dtsVariableService.PromptAndCreateVariable(parentWindow, dtsContainer);

Through this code i have created new varibale in varibale window at runtime in UI. But we are not able to get details of varibales which i create at runtime.

Thanks in Advance

ashok mohanty

|||

If you have a task designer or UI, and want a list of variables, look at the TaskHost parameter of the IDtsTaskUI.Initialize method, as it has the Variables collection as a property.

sql

Current package reference.

Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();

Microsoft.SqlServer.Dts.Runtime.Package pak = app.LoadPackage(@."C\DTS\Package1.dtsx",null);

Here i need to pass the path of the package. Is there any other way to get it (without the path)?

Thanks

Mani

LoadPackage obviously requires the full path, and there is no other way. I would normally look to get a contextual reference to a parent object, but as a above this will not happen.

If you explain why you want to do this, we may be offer an alternative.

|||

I require the package reference to get the list of variables in that particular package. I tried using the

VariableDispenser method, but to get a variable it should be lock for read, for that the name of the variable is required. Is there any other method to do this?

Thanks

Mani

|||

IDTSVariables90 variables = null;

VariableDispenser.LockOneForRead("TableName", ref variables);

string tableName = variable.Value.ToString();

variables.Unlock()

|||

Thanks

Mani

|||

This is where being clear in what you are trying to do and when will help. As I think I have pointed out there is no way or any need for you to enumerate variables at run-time.

At design-time I agree that this is higly desirable, and of course you can.

IDtsVariableService errorCollectionService = _serviceProvider.GetService(typeof(IDtsVariableService)) as IDtsVariableService;

|||

You can iterate variables, but not always. As above, I don't see it as an issue since you can do so at the approptiate times.

Components will not add any variables at runtime, so design-tme again.

|||

Darren,

I'm not trying to be antagonistic but I am going to tell it like it is. If you want to debate this then I'm perfectly happy to respond to any reply you have to offer.|||

Sorry we are a newbie,

Thanks

Subhash Subramanyam

Bhavana Anand.

HI,

Thank you very much for the forum. It is very helpfull to us to create new variables at runtime in UI. But we need to fill varibale details in combobox.

I used this below statement to create new varibale.

private IWin32Window parentWindow;

private DtsContainer dtsContainer;

private Variables vars;

System.IServiceProvider _serviceProvider;

private IDtsVariableService _dtsVariableService;

_dtsVariableService = _serviceProvider.GetService(typeof(IDtsVariableService)) as IDtsVariableService;

_dtsVariableService.PromptAndCreateVariable(parentWindow, dtsContainer);

Through this code i have created new varibale in varibale window at runtime in UI. But we are not able to get details of varibales which i create at runtime.

Thanks in Advance

ashok mohanty

|||

If you have a task designer or UI, and want a list of variables, look at the TaskHost parameter of the IDtsTaskUI.Initialize method, as it has the Variables collection as a property.

Tuesday, March 20, 2012

Current package reference.

Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();

Microsoft.SqlServer.Dts.Runtime.Package pak = app.LoadPackage(@."C\DTS\Package1.dtsx",null);

Here i need to pass the path of the package. Is there any other way to get it (without the path)?

Thanks

Mani

LoadPackage obviously requires the full path, and there is no other way. I would normally look to get a contextual reference to a parent object, but as a above this will not happen.

If you explain why you want to do this, we may be offer an alternative.

|||

I require the package reference to get the list of variables in that particular package. I tried using the

VariableDispenser method, but to get a variable it should be lock for read, for that the name of the variable is required. Is there any other method to do this?

Thanks

Mani

|||

IDTSVariables90 variables = null;

VariableDispenser.LockOneForRead("TableName", ref variables);

string tableName = variable.Value.ToString();

variables.Unlock()

|||

Thanks

Mani

|||

This is where being clear in what you are trying to do and when will help. As I think I have pointed out there is no way or any need for you to enumerate variables at run-time.

At design-time I agree that this is higly desirable, and of course you can.

IDtsVariableService errorCollectionService = _serviceProvider.GetService(typeof(IDtsVariableService)) as IDtsVariableService;

|||

You can iterate variables, but not always. As above, I don't see it as an issue since you can do so at the approptiate times.

Components will not add any variables at runtime, so design-tme again.

|||

Darren,

I'm not trying to be antagonistic but I am going to tell it like it is. If you want to debate this then I'm perfectly happy to respond to any reply you have to offer.|||

Sorry we are a newbie,

Thanks

Subhash Subramanyam

Bhavana Anand.

HI,

Thank you very much for the forum. It is very helpfull to us to create new variables at runtime in UI. But we need to fill varibale details in combobox.

I used this below statement to create new varibale.

private IWin32Window parentWindow;

private DtsContainer dtsContainer;

private Variables vars;

System.IServiceProvider _serviceProvider;

private IDtsVariableService _dtsVariableService;

_dtsVariableService = _serviceProvider.GetService(typeof(IDtsVariableService)) as IDtsVariableService;

_dtsVariableService.PromptAndCreateVariable(parentWindow, dtsContainer);

Through this code i have created new varibale in varibale window at runtime in UI. But we are not able to get details of varibales which i create at runtime.

Thanks in Advance

ashok mohanty

|||

If you have a task designer or UI, and want a list of variables, look at the TaskHost parameter of the IDtsTaskUI.Initialize method, as it has the Variables collection as a property.

|||

Then you can simply refrence the package paramaters either by iterating through them or using their name.

e.g. vars["Name_Of_Parameter"].Value

Current package reference.

Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();

Microsoft.SqlServer.Dts.Runtime.Package pak = app.LoadPackage(@."C\DTS\Package1.dtsx",null);

Here i need to pass the path of the package. Is there any other way to get it (without the path)?

Thanks

Mani

LoadPackage obviously requires the full path, and there is no other way. I would normally look to get a contextual reference to a parent object, but as a above this will not happen.

If you explain why you want to do this, we may be offer an alternative.

|||

I require the package reference to get the list of variables in that particular package. I tried using the

VariableDispenser method, but to get a variable it should be lock for read, for that the name of the variable is required. Is there any other method to do this?

Thanks

Mani

|||

IDTSVariables90 variables = null;

VariableDispenser.LockOneForRead("TableName", ref variables);

string tableName = variable.Value.ToString();

variables.Unlock()

|||

Thanks

Mani

|||

This is where being clear in what you are trying to do and when will help. As I think I have pointed out there is no way or any need for you to enumerate variables at run-time.

At design-time I agree that this is higly desirable, and of course you can.

IDtsVariableService errorCollectionService = _serviceProvider.GetService(typeof(IDtsVariableService)) as IDtsVariableService;

|||

You can iterate variables, but not always. As above, I don't see it as an issue since you can do so at the approptiate times.

Components will not add any variables at runtime, so design-tme again.

|||

Darren,

I'm not trying to be antagonistic but I am going to tell it like it is. If you want to debate this then I'm perfectly happy to respond to any reply you have to offer.|||

Sorry we are a newbie,

Thanks

Subhash Subramanyam

Bhavana Anand.

HI,

Thank you very much for the forum. It is very helpfull to us to create new variables at runtime in UI. But we need to fill varibale details in combobox.

I used this below statement to create new varibale.

private IWin32Window parentWindow;

private DtsContainer dtsContainer;

private Variables vars;

System.IServiceProvider _serviceProvider;

private IDtsVariableService _dtsVariableService;

_dtsVariableService = _serviceProvider.GetService(typeof(IDtsVariableService)) as IDtsVariableService;

_dtsVariableService.PromptAndCreateVariable(parentWindow, dtsContainer);

Through this code i have created new varibale in varibale window at runtime in UI. But we are not able to get details of varibales which i create at runtime.

Thanks in Advance

ashok mohanty

|||

If you have a task designer or UI, and want a list of variables, look at the TaskHost parameter of the IDtsTaskUI.Initialize method, as it has the Variables collection as a property.

Thursday, March 8, 2012

Cumulative package 7.. Latest update ?

http://support.microsoft.com/kb/949095/LN/
Is this the latest build out there for SQL 2005 SP2 ?
ThanksThere may be a hotfix or two that came out since this release but yes this
is the latest cumulative update for SP2.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"RF" <rf@.test.com> wrote in message
news:e1$KVv2oIHA.2064@.TK2MSFTNGP05.phx.gbl...
> http://support.microsoft.com/kb/949095/LN/
> Is this the latest build out there for SQL 2005 SP2 ?
> Thanks
>|||"RF" wrote:
> http://support.microsoft.com/kb/949095/LN/
> Is this the latest build out there for SQL 2005 SP2 ?
> Thanks
Hi
According to http://support.microsoft.com/kb/937137/ CU7 was the last
release and neither page has been updated in the last week!
John

Cumulative hotfix package (build 2153)

I was wondering where I'd find specific information on the individual fixes,
specifically bug 447: Processing performance on multiprocessor computers is
somewhat slower than expected.
I've searched for info on each of the fixes listed, but so far have not
found any details.
Thanks very much,
TomTHello,
Unfortunately Microsoft Knowledge Base articles that discuss those fixes
havent been released yet. If we have any update on this, we will let you
know. As I know this bug is regarding some issues of read locks between
processors that may bring overhead.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks Peter - I'll keep an eye out for any new kb's on these fixes.
""privatenews"" wrote:

> Hello,
> Unfortunately Microsoft Knowledge Base articles that discuss those fixes
> haven’t been released yet. If we have any update on this, we will let yo
u
> know. As I know this bug is regarding some issues of read locks between
> processors that may bring overhead.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
>
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
>

Cumulative hotfix package (build 2153)

I was wondering where I'd find specific information on the individual fixes,
specifically bug 447: Processing performance on multiprocessor computers is
somewhat slower than expected.
I've searched for info on each of the fixes listed, but so far have not
found any details.
Thanks very much,
TomTHello,
Unfortunately Microsoft Knowledge Base articles that discuss those fixes
haven?t been released yet. If we have any update on this, we will let you
know. As I know this bug is regarding some issues of read locks between
processors that may bring overhead.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks Peter - I'll keep an eye out for any new kb's on these fixes.
""privatenews"" wrote:
> Hello,
> Unfortunately Microsoft Knowledge Base articles that discuss those fixes
> havenâ't been released yet. If we have any update on this, we will let you
> know. As I know this bug is regarding some issues of read locks between
> processors that may bring overhead.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>

Cumulative Hotfix Package - Build 2153

I'm getting ready to install the Cumulative Hotfix Package - Build 2153 on a 2-node, Active/Passive cluster (MSSQL 2005 EE on Windows 2003). I've read through the docs a number of times but don't see any reference to clustering. Should I apply the hotfixes to each node or are the fixes cluster-aware?

Thanks.I also have a 2-node Active/Passive cluster (MSSQL 2005 64-bit on Windows 2003). I would like to apply the cumulative hotfix, but I too have not found any documentation specific to a clustered installation. Did you install the hotfix on your cluster? If yes, did you have any issues?

Cumulative Hotfix Package - Build 2153

Thanks.
I also have a 2-node Active/Passive cluster (MSSQL 2005 64-bit on Windows 2003). I would like to apply the cumulative hotfix, but I too have not found any documentation specific to a clustered installation. Did you install the hotfix on your cluster? If yes, did you have any issues?

Cube Processing with IS packages

Hi,

Just have a quick question regarding the cube processing.

Currently, I've created IS package for the cube processing for better controls.

Before processing the cube, if I have to update the dimension first (since dimension updates everyday and it gives an error if I process the cube itself without dimension updates) then I have to create data flow 'dimension processiong' inside the AS processiong control task?

If so, I need to link the dimension table and connect columns into the dimension in cubes and what else I need to think about before creating this?

I appreciate if anyone can give a suggestion.

Thanks.

Hello! In the starschema in the realtional source you should have foreign keys relations from the dimension tables to the fact table.

In the data source view, in the BIDS cube project, you will need to relate these tables once again, if this is not done automatically.

Your deployed cube is only dependent on the data source when you process the cube, if it is a MOLAP cube.

After you have updated your relational tables(dimensions and fact table) you will only need to process the cube dimensions first and the cube(or measure group) after that.

HTH

Thomas Ivarsson

|||

HI,

Thanks for your reply.

I've already made the relationship between Facts and dimensions in DSV and also checked Dimension usage in each cubes which are molap.

When I process the cube in SSAS, In object list, I need to check dimension update and cube process and it does not give an error but if not, it gives an error ( like attribute can not be found since the dimensions are not updated). My question is that in SSIS I have to check dimension when I run a package but is there anyway to process automatically instead of checking dimension whenever I process the cube?

I appreciate if you can give me any comments.

Thanks.

|||

Hello! I assume that you have also included the processing of dimensions in your SSIS package and before the cube?

In the process cube form in BIDS you have a buttom att the bottom(advanced?) and there you have a setting like process affected objects that you can activate.

HTH

Thomas Ivarsson

Wednesday, March 7, 2012

Cube Processing with IS packages

Hi,

Just have a quick question regarding the cube processing.

Currently, I've created IS package for the cube processing for better controls.

If so, I need to link the dimension table and connect columns into the dimension in cubes and what else I need to think about before creating this?

I appreciate if anyone can give a suggestion.

Thanks.

Hello! In the starschema in the realtional source you should have foreign keys relations from the dimension tables to the fact table.

In the data source view, in the BIDS cube project, you will need to relate these tables once again, if this is not done automatically.

Your deployed cube is only dependent on the data source when you process the cube, if it is a MOLAP cube.

After you have updated your relational tables(dimensions and fact table) you will only need to process the cube dimensions first and the cube(or measure group) after that.

HTH

Thomas Ivarsson

|||

HI,

Thanks for your reply.

I've already made the relationship between Facts and dimensions in DSV and also checked Dimension usage in each cubes which are molap.

I appreciate if you can give me any comments.

Thanks.

|||

Hello! I assume that you have also included the processing of dimensions in your SSIS package and before the cube?

In the process cube form in BIDS you have a buttom att the bottom(advanced?) and there you have a setting like process affected objects that you can activate.

HTH

Thomas Ivarsson

Cube Processing with IS packages

Hi,

Just have a quick question regarding the cube processing.

Currently, I've created IS package for the cube processing for better controls.

If so, I need to link the dimension table and connect columns into the dimension in cubes and what else I need to think about before creating this?

I appreciate if anyone can give a suggestion.

Thanks.

Hello! In the starschema in the realtional source you should have foreign keys relations from the dimension tables to the fact table.

In the data source view, in the BIDS cube project, you will need to relate these tables once again, if this is not done automatically.

Your deployed cube is only dependent on the data source when you process the cube, if it is a MOLAP cube.

After you have updated your relational tables(dimensions and fact table) you will only need to process the cube dimensions first and the cube(or measure group) after that.

HTH

Thomas Ivarsson

|||

HI,

Thanks for your reply.

I've already made the relationship between Facts and dimensions in DSV and also checked Dimension usage in each cubes which are molap.

I appreciate if you can give me any comments.

Thanks.

|||

Hello! I assume that you have also included the processing of dimensions in your SSIS package and before the cube?

In the process cube form in BIDS you have a buttom att the bottom(advanced?) and there you have a setting like process affected objects that you can activate.

HTH

Thomas Ivarsson

Cube Processing Error - Urgent - Thanks

First when I processed cube's (Process Data) using SSIS package I got "Memory error"

"Memory error: Allocation failure : Not enough storage is available to process this command."

I changed "OLAP\ Process \ BufferMemoryLimit" to 30% . I have /3GB setup. and I am using AS 2005 - 32 bit.

When I rerun the package I got olap storage engine error

Errors in the OLAP storage engine: An error occurred while processing the 'Scenario Analysis 2006' partition of the 'Scenario Analysis' measure group for the 'Scenario Analysis' cube from the Risk Reports database.

I don't know what's that, so I run cube process (Full) from Management Studio and got error below while reading data process stoped with following popup message

The trace has been stopped
Unexpected end of the file has occurred. The following elements are not closed: root, return, ExecuteResponse, soap:Body soap: Envelope. Line 13180, position 1. (System.Xml)

Thanks for any help - Ashok

Please check if you're running out of disk space...

Cube Processing Error - Urgent - Thanks

First when I processed cube's (Process Data) using SSIS package I got "Memory error"

"Memory error: Allocation failure : Not enough storage is available to process this command."

I changed "OLAP\ Process \ BufferMemoryLimit" to 30% . I have /3GB setup. and I am using AS 2005 - 32 bit.

When I rerun the package I got olap storage engine error

I don't know what's that, so I run cube process (Full) from Management Studio and got error below while reading data process stoped with following popup message

Thanks for any help - Ashok

Please check if you're running out of disk space...

Cube Processing : Diff ''Tween "Database" and "Cube" Type?

In short, I notice that when creating a SSIS package for processing the cube, you can process the "database" and/or the "cube". Is it redundant to have both being processed in a SSIS task? I assume processing the "database" will also process the "cube"?

I assume that if I want everything to be refreshed, then I should simply process the database as the only step in the task, as well as making sure it's "Process Full" for the Processing Options.

Can anyone explain the different processing types?

Thanks!

If you're doing ProcessFull, then do ProcessFull on the database. ProcessFull on the database will process all dimensions then process all cubes.

If you did ProcessFull on the cube, then it will not process the dimensions (except the first time since they would be unprocessed the first time you process the cube). So all the new facts that point to new dimension members will either blow up or be assigned to the unknown member.

Anyway... ProcessFull on the database.

|||

Hi,

Depends on your setup but have a look at http://msdn2.microsoft.com/en-us/library/ms345142.aspx may help you.

And it is worth reading the performance guide as well

http://www.microsoft.com/technet/prodtechnol/sql/2005/ssas2005perfguide.mspx

It is really up to you to decide what is best for you, we break it up into little parts. So if it does goes wrong, it is not going to roll back the entire transaction. Most of ours, unprocess, process dimensions, process a cube. Others are update dimensions, then process the cube. Never really use process database as there is often more than one cube in the database and i am only ever interested in one of the cubes.

Hope that helps

Matt

Cube Processing : Diff ''Tween "Database" and "Cube" Type?

I assume that if I want everything to be refreshed, then I should simply process the database as the only step in the task, as well as making sure it's "Process Full" for the Processing Options.

Can anyone explain the different processing types?

Thanks!

If you're doing ProcessFull, then do ProcessFull on the database. ProcessFull on the database will process all dimensions then process all cubes.

Anyway... ProcessFull on the database.

|||

Hi,

Depends on your setup but have a look at http://msdn2.microsoft.com/en-us/library/ms345142.aspx may help you.

And it is worth reading the performance guide as well

http://www.microsoft.com/technet/prodtechnol/sql/2005/ssas2005perfguide.mspx

Hope that helps

Matt

Thursday, March 29, 2012

Cursor Problem in an DTS ActivX package.

Cursor Problem in an DTS ActivX package.

Wednesday, March 21, 2012

Current package reference.

Current package reference.

Current package reference.

Current package reference.

Tuesday, March 20, 2012

Current package reference.

Current package reference.

Thursday, March 8, 2012

Cumulative package 7.. Latest update ?

Cumulative hotfix package (build 2153)

Cumulative hotfix package (build 2153)

Cumulative Hotfix Package - Build 2153

Cumulative Hotfix Package - Build 2153

Cube Processing with IS packages

Wednesday, March 7, 2012

Cube Processing with IS packages

Cube Processing with IS packages

Cube Processing Error - Urgent - Thanks

Cube Processing Error - Urgent - Thanks

Cube Processing : Diff ''Tween "Database" and "Cube" Type?

Cube Processing : Diff ''Tween "Database" and "Cube" Type?

Cursors vs. Queries

Blog Archive

About Me