About Me

I am Munish Bansal, working as a Software Professional with one of the leading Software Companies in India.

I am MCTS (SQL Server 2005). I have started this blog keeping in mind that people will share their knowledge, professional experiences, ideas & queries etc with others and every body will be in pace with the updated technologies.

Like this:

Be the first to like this page.

44 Comments »

44 Responses

on February 7, 2009 at 10:14 am Harman Manchanda

You are doing real good work buddy… keep it up
on February 9, 2009 at 3:17 am Munish Bansal

Hey Harman,
Thanks a lot for your encouragement…!
I’ll try to make it more & more useful down the time.
Keep in touch.

thanks,
Munish Bansal
on May 25, 2009 at 8:09 pm Ali

Hi,
im ali
i live in Iran

Please Help Me,
For Create Website Tab ,Alike Your Website’s.

thanks.
on May 26, 2009 at 3:35 am Munish Bansal

Hi Ali,

I really didn’t get your requirement. Please make it more clear so that I may help you out something.
Thanks.
on May 27, 2009 at 4:07 pm Kaladhar

Need help in SQL server reporting Services.
I have input parameters like

BachNumFrom, BatchNumTo, StartDate and Enddate

I have Dropdownloist which has values PaymentDate and BatchNumber

When I select PaymentDate in dropdown list I need to Enable Start Date and End date parameters and disable the BatchNumFrom and BatchNumTo parameters.

When I select BatchNumber in dropdown list I need to Enable BatchNumFrom and BatchNumTo parameters and disable the Start Date and End date parameters .
Can anybody please let me know how to do in SSRS report

Thanks in Advance.
Kaladhar
on May 28, 2009 at 3:09 am Munish Bansal

Hi Kaladhar,

You can somehow achieve it using dependent parameters. I mean you can make data set for ‘Start Date and End date’ dependent upon the value from PaymentDate. And thus until & unless some value is selected from PaymentDate, Start Date & End Date parameters would be disabled.
Similiarly you can do for other set of parameters.

This all is required if you are using Report Manager to display your reports, but if you have liberty to use Report Viewer or so, you can easily implement these kind of functionality using custom drop down lists etc.

Munish Bansal
on July 20, 2009 at 12:56 pm Hanna

Hi,

I want to send all the items of listbox to a stored procedure where stored procedure can select records from database on basis of all those parameters.

Please help me out.

I’ll b thankful…

Regards,

Hanna.
on July 21, 2009 at 3:31 am Munish Bansal

Hey Henna,

Then you can pass/use the same logic/query in SP which you are using to get the data set for listbox/drop down list.

Munish Bansal
on July 24, 2009 at 12:11 pm Jerry

Hi Munish,

Your article and response to my query is a big help on me.

I have another problem with regards to the implementation in SSIS.

I have two tables in database
- ParentTable
- ChildTable

and have foreign key constraint

The goal is to archive the data from Database1 and move the records to Database2.

My query to the table is
SELECT *
FROM ParentTable p
INNER JOIN ChildTable c
ON p.ID = c.ID
WHERE

At the end of the execution of package and after the records are copied to Database2, I need to delete the transferred records in Database1.

The problem is, in SELECT condition i used the columns value of both parenttable and childtable to meet the requirements, and when i delete the records in childtable the conditions are still met. But when i delete the records in ParentTable the conditions are not satisfied since the reference to ChildTable is gone (deleted).

My plan is to get all the IDs of Parent records from the ParentTable of Database2. using that IDs i can delete the records in ParentTable of Database1 without using the conditions that need to refernce to ChildTable of Database1.

My plan is, get the ID’s using Execute SQL Task and store the IDs in a variable with type of Object (this is expected that all IDs are saved in the variable). Then, used the variable in the Query of Delete Statement.

DELETE ParentTable
WHERE ID IN ()

How can i implement this in SSIS?
on July 24, 2009 at 6:40 pm Munish Bansal

Hi Jerry,
You should first copy all those records from database1 to database2 by using a DFT. In this DFT use an OLE DB source and an OLE DB destination.
Now use an Execute SQL Task to delete those records from database1.
For deleting such (parent-child) records from database1, you can use the following query:

DECLARE @TempTable Table
( ID INT)

INSERT INTO @TempTable
SELECT PT.ID FROM ParentTable PT INNER JOIN ChildTable CT
ON PT.Id = CT.Id

DELETE FROM ChildTable WHERE ID IN (SELECT ID FROM @TempTable)
DELETE FROM ParentTable WHERE ID IN (SELECT ID FOM @TempTable)

-Munish Bansal
on July 25, 2009 at 1:01 am Jerry

Hi Munish,

Thank you for your response.

Aside from the implementation that you mention.

The Data Architecture team requires us not to use a storedprocedure and not to use physical Temporary Tables. But this idea is great!

Is the @TempTable is created physically using the script you created?

Is there are other ways to implement this using SSIS components only, without using storeProcedure.

Like:
DFT —> gets the IDs in either Database1 or Database2
and use the IDs in another SSIS component
like the Executre SQL Task. Is this possible?
on July 25, 2009 at 3:24 am Jerry

Hi Munish,

Thank you for your response, this is a great help.

I have a few questions.

Is the script is a stored procedure? and is the @TempTable created physically?

The Data Architecture team requires us not to use SP and physical Temporary tables. But this is great.

Do you have any idea on how to implement this using simple query and ssis components.

Thanks in advance
on July 27, 2009 at 4:16 am Munish Bansal

Hey Jerry,

You can use the above mentioned query directly in the ExecuteSQL Task, no need to create a separate stored procedure for that.
Also @Table is just an in-memory table, it’s not created physically in the database.

-Munish Bansal
on July 27, 2009 at 9:40 am Jerry

Thank you so much Munish. You’re a big help to me
on July 28, 2009 at 10:34 am Jerry

Hi Munish,

With regard to the implementation of the above query. It works well if the package is connected in localhost, but when i change the connection to other pc (build machine that act as server)

I got this error message:
[Execute SQL Task] Error: Failed to acquire connection “IP\ESA_FY09_02_Dev.2334_EnablServiAppli_Prod_BK”. Connection may not be configured correctly or you may not have the right permissions on this connection.

When i open the Execute SQL Task and click Build Query. the error message below is displayed.

“The Declare cursor SQL construct or statement is not supported.”

I click ok button of the message box and Query Builder window is displayed. But when i run the script a login window is displayed to connect to the server.

How to implement this without any connection problem..

Please help me.
on September 1, 2009 at 5:48 am Aashi Mittal

Hello Munish,

good work…i appreciate..
aneways i m searching code for disabling Ctrl C option in my aspx page..
on October 6, 2009 at 4:39 pm Sudarshan

Hey Munish,
Great work i really Appreciate.

in your blog there is no Attachment option.if at all i can send my Report so that u can understand well my Problem is.

Here
i need to Design a Report
10 Colums Totally.
first 3 columns will use “State” as Parameter and
the other 3 columns use “IV-E” as paramter and
rest use the “IV-A”.
These 3 are @Payment Type Pmts.
should i go with Su-Rpts or Any other Solution for this Situation.
Plese Reply me ASAP.
Thanks for the Everthing.
on October 7, 2009 at 5:58 am Munish Bansal

Hey thanks a lot for your appreciation, Sudarshan.
on November 23, 2009 at 9:29 am Qaiser

Hey Munish.

I need to ask that, i need to automate a process which is to log in to a website by entering the credentials and then we choose the year and reporting period , choose the type of report and view the report and sales figures on a website. this website is in JSP basically. and i dont know how to call the items of the website. There are Javascripts being used like the calendar, from where we select the year and month or day.

So my question is , is it possible to make it happen by using SSIS package. where the package would automatically connect to the website, select the reporint period and then once the results are shown , download it in CSV file format to the local drive. As there is an option of downloading the result in CSV file on the website , but it is only shown once all the selections are made ?

So i be glad if you have any idea how to do such task , so is it even posssible to do so ?

Thank you

Qaiser
on February 2, 2010 at 7:31 pm Vel

HI
I need help in SSIS
I have lenthy Query in that we have lots of case statements for that query we need to create SSIS pkg
plz let me know which transformation is suitable for case statements

Thanks!
Vel
on February 4, 2010 at 2:24 am Jerry

Hi Vel,

Much better if you placed a case condition that you’ve mention in order to simulate or discuss it clearly.
on February 5, 2010 at 3:43 pm Vel

HI Jerry,
Thanks for reply
here is my part of the query including the case statement

CASE
WHEN SUBSTR(TableName.Column1,-6,5) IN (’11353′,’10080′)
THEN ‘Region1′
WHEN SUBSTR(TableName.Column2,-6,5) = ’10920′
THEN ‘Region2′
WHEN SUBSTR(TableName.Column2,-6,5) = ’10048′
THEN ‘Region3′
WHEN SUBSTR(TableName.Column2,-6,5) = ’10053′
THEN ‘Region’4
WHEN SUBSTR(TableName.Column2,-6,5) = ’10073′
THEN ‘Region4′
WHEN SUBSTR(TableNameColumn2,-6,5) = ’10068′
THEN ‘Region5′
WHEN SUBSTR(TableName.Column2,-6,5) = ’10057′
THEN ‘Region6′
WHEN SUBSTR(TableName.Column2,-6,5) = ’10052′
THEN ‘Region7′
WHEN SUBSTR(TableName.Column3,-6,5) IN (’10163′ ,’10169′ ,’10190′ ,’10164′ ,’10775′ ,’10194′ ,’10166′ ,’10087′ , ’10178′ ,’10184′ ,’99999′ ,’10199′ ,’10195′ ,’10182′ ,’10185′ ,’10183′ ,’11267′) THEN ‘Region8′
WHEN SUBSTR(TableName.Column3,-6,5) IN (’10130′ ,’10934′ ,’10128′ ,’10047′ ,’10135′ ,’10119′ ,’10139′ ,’10127′ ,’10143′ ,’10125′ ,’10132′ ,’10120′ ,’10141′)
THEN ‘Region9′
ELSE ‘Unknown’
END “Filedname”

Like this I have more case statements in my Query and we are getting the data (Using diff tables from Oracle) from Oracle to Sql server

Plz help me

Thanks!
Vel
on April 14, 2010 at 7:41 am Easvaran

Hi Munish,
You are doing a great service. Keep it going.

I have a doubt. How to import data from a CSV file containing comma’s for their field values.
The default field delimitter for CSV file is comma(,). But if we also have comma for field values, how can we import such data correctly into a table.

Need Help
on April 14, 2010 at 1:51 pm Munish Bansal

Hi Easvaran,

If you have got comma as the field delimiter as well as in the values itself, you can configure to automatically detect & split the values untill & unless you know the max. width of the column data i.e. you can go for fixed width option while configuring the Flat File Connection Manager.

Thanks,
Munish Bansal
on May 12, 2010 at 2:57 pm Rick Garrison

Munish,

Here’s a question for you:

Do you know how to increase the line thickness in the legend of a Dundas chart?

We use high-resolution charts and it is difficult for our users to decipher the colors in the legend. To solve this problem, we wish to make the lines thicker, so they would be easier to see.

I’ve had this question posted on the Dundas support site for over a year but they haven’t responded. I thought you might know how.

Thanks in advance for your response.

Rick Garrison
on June 11, 2010 at 10:01 am Manu

hi
I need a solution of extracting multiple excel sheets from multiple excel files which are residing in a folder called EXCEL FILES without using the script task .. ( ssis 2008 )
Eg: EXCEL FILES ( DIR )
ExcelFile1 — > Sheet1, Sheet2, Sheet3
ExcelFile2 — > Sheet1, Sheet2, Sheet3
.
.
.
ExcelFilen — > Sheet1, Sheet2, Sheet3

The Schema is same for everysheet

Thnks
SM
on July 8, 2010 at 5:03 pm Kiran Nalluri

Hi Munish,
Can you send me the sample code for Dynamic columns mapping – Script Component as Destination (SSIS). I highly appreciate your help

Thanks!
Kiran.
on July 28, 2010 at 11:05 pm Anand Vanam

Hi Munish,

Can you send me the code samples or package file for Dynamic columns mapping – Script Component as Destination (SSIS).

Thanks in Advance

Anand
on August 3, 2010 at 12:24 pm sam

Hi munish,

I am currently learning ssrs with custom data extension.

I have downloaded a sample appln for that and worked out. it was working fine with ssrs2005.

But when i tried with ssrs2008, it is giving error as “Could not connect to datasource. Attempted to perform an operation forbidden by CLR host”

Why this error comes…

Pls help me..

Thanks & Regards
Sam
on October 29, 2010 at 8:11 am Nikhil Pereira

HI,

I would like to subscribe to your blog. request if you could sign me up for the same

Regards
Nikhil
on January 8, 2011 at 7:01 am Pallavi

Dear Munish Bansal,

Thnx for sharing the knowledge about MultiValued Parameters.
Pls also provide updates for SSIS.

Rgds
Pallavi
on January 8, 2011 at 7:04 am Pallavi

Dear Munish Bansal,

Pls provide me steps for creating UDF for Multivalued Parameters in SSRS 2005

Thnx & Rgds
Pallavi
on January 23, 2011 at 4:12 pm Raquel

Hi Munish,

First of all thank you for this blog for sharing and helping everyone.
I have a question if you can help me or anyone. Here is my question on SSRS:

sample SSRS preview of Detail data (source: stored proc)

StudentId TestScoreMath Course EnrollStatus
100 200 Nursing Withdraw
100 200 Physical Therapy Enrolled
200 100 Database Enrolled
—–
Total 300

Total Student TestScoreMath should be 300 not 400

How can I SUM the value of MathTestScore of all distinct student in SSRS design, I cannot change the stored proc to do the calculation or grouping?

Thank you and please help.

Raquel
on March 19, 2011 at 7:28 am Neetu Singla

Hi Munish,

I am impleminting service broker in sql server 2005 and facing the problem while creating a dialog variable.

Given below 8 steps are working correctly but in 9th execution, i m getting the error

“Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable “@dialog_handle”.

Tell me where this dialog variable is being stored and how can i begin dialog conversation for adventureworks databse.

1. CREATE MESSAGE TYPE sendMessage
VALIDATION = WELL_FORMED_XML

2. CREATE QUEUE sendQueue WITH STATUS = ON,
ACTIVATION (
PROCEDURE_NAME = sendProc,
MAX_QUEUE_READERS = 5,
EXECUTE AS SELF)

3. CREATE MESSAGE TYPE sendMessage
VALIDATION = WELL_FORMED_XML

4. CREATE QUEUE sendQueue WITH STATUS = ON,
ACTIVATION (
PROCEDURE_NAME = sendProc,
MAX_QUEUE_READERS = 5,
EXECUTE AS SELF)

5. CREATE CONTRACT sendContract
(
sendMessage SENT BY INITIATOR
);

6. CREATE SERVICE sendService
ON QUEUE
[dbo].[sendQueue] (sendContract)

7. CREATE SERVICE initiaterService
ON QUEUE [dbo].[sendQueue]

8. DECLARE @dialog_handle UNIQUEIDENTIFIER;

9. BEGIN DIALOG CONVERSATION @dialog_handle FROM
SERVICE [sendService] TO SERVICE ‘recieveService’ ON CONTRACT [sendContract];

10.SEND ON CONVERSATION
@dialog_handle MESSAGE TYPE [sendMessage]
(‘John’)

11. END CONVERSATION @dialog_handle

Thanks a lot in advance……………..

Regards:
Neetu Singla
on April 13, 2011 at 8:52 am Chitvendra

Hi

I am chitvendra kumar from Delhi (India)
on April 19, 2011 at 9:29 pm Wence

Munish, my name is Wence and I am having a problem with a script editor in Visual Studio 2005. Short note, I am using a Foreach Loop Container to read multiple delimited files then have a date flow which consists of a flat file source, script editor, and OLE DB Destination.

I am running into a problem with the script editor. I am using VB to attempt to read the delimiter and pass along the column values to a table. I get a runtime error saying “string must be exactly one character long”.

I tried to use a code you have posted but you were using it to convert to integer and I need to convert to string. Let me know if you can assist with this. Thank you.

Wence
on April 22, 2011 at 6:31 am Fazal

Hi,munish

I like your blog.Keep it up. good work

Thanks
on April 25, 2011 at 9:24 am Raghu

Hello,

I am using Matrix in SSRS 2008 R2. In the Matrix, in the “Row” cell, i did put “Category”field, in the “Column” cell, i did put “Description” filed and in the “Data”cell, i did put “Details” field.

Under the “Category” field there are three categories, which are “Avg Consumption”, “Total Consumption per month”, and “Total expenditures” respectively.

For the “Avg Consumption”, i need “Row Average” at the last cell in the row and for the “Total Consumption per month”, and “Total expenditures” i need total of the row in the last cell of the row.

If i want to do “Row Total”, i would do by selecting “Add Total” and then “Row”. But, here my problem is bit different, that, “Avg” of the row, which must display at the end of the “Cell” for the “Avg Consumption” category , and “Total Consumption per month”, and “Total expenditures”, “total” of the row must display at the end of the “Cell”.

Kindly please help me by providing suitable solution, you would be much appreciated.

Thanks in advance.
on July 15, 2011 at 10:06 am Nayana

Hi
I m using SSIS for the first time.
My requirement is:
I have to migrated data from one DB table to another.
I have a column which will have values separated by ;(semicolon).
I have to insert that many rows in a secondary of this main table.

Can you please help me on this
on August 5, 2011 at 9:39 am Puneet

Hey Munish!
How r u doing?
Great Work dude…..Keep it up.

What is ur contact number?

Cheers,
Puneet Ajmera
on December 5, 2011 at 6:50 am pasaks

Hi,,
I think this blog will be helpful to me in the work I have to got through…but I think I need to make my problem more clear to you to get into it..
on December 5, 2011 at 6:56 am pasaks

Hey bro,
Actually nowadays I am working in a database. As you might be familiar that after developing database I have to design a form so that user can enter the datas which is to be inserted in the database..

What I am thinking is after developing database,when working in form side…how could I get the table fields dynamically. If some column name is added later then it should automatically shown in form side,which decreases burden to adding field name in form side……..

I am quite unfamiliar with this..so hope of getting reply asap..

Regards
pasaks
Nepal
on December 6, 2011 at 4:03 am pasaks

hey bro,
I really need a help from you as soon as possible….I think you can really help me out to solve my query….you can contact me on writ2ks@gmail.com….
…please reply me asap..

Regards
pasaks
on December 9, 2011 at 8:03 am pasaksaks

Hey bro,
Actually nowadays I am working in a database. As you might be familiar that after developing database I have to design a form so that user can enter the datas which is to be inserted in the database..

What I am thinking is after developing database,when working in form side…how could I get the table fields dynamically. If some column name is added later then it should automatically shown in form side,which decreases burden to adding field name in form side……..

I am quite unfamiliar with this..so hope of getting reply asap..

Regards
pasaks
Nepal

Comments RSS

	shimon on Extending SQL Server Reporting…
	How to get 5000 face… on rrdsc00295
	Emil on Dynamic columns mapping – Scri…
	anki on Passing multi-value parameter …
	Siraz on Passing multi-value parameter …
	Siraz on Passing multi-value parameter …
	Pranav Kapadia on How to remove duplicate record…
	diablo 3 beta on Disclaimer
	Shirley Kobie on Disclaimer
	Venice Zapata on Disclaimer
	外遇抓姦 on Disclaimer
	keerti on Dynamic columns mapping – Scri…

Tech Updates

…keep in pace with technology !

About Me

Like this:

44 Responses

Leave a Reply Cancel reply

Top Posts

Recent Comments

Recent Posts

Categories

View Stats

Category Cloud

Archives

Pages

My Links

Tutorials

Follow “Tech Updates”