How to set parameters' values to SSIS packages

Hi,

This post to walkthrough step-by-step how to pass parameters to a SSIS package.
Usually you have set of parameters and you want to set the values at run time before the package runs.

The methodology that i will implement in this post, is to export the parameters that i want to set from an XML file by defining the SSIS configuration file using Visual Studio BIDS 2008/2010 or SQL Data tools.

Let's say, you have a SSIS package with a parameter called @myParameter, Here is how to setup the configuration file for the SSIS so we can pass it when the package runs.

Steps:
1) Open yout SSIS package.
2) Right click after you open the package and select on "Package Configurations".

 

3) Set the configuration file name and click on Next button.



4) Under Variables node, select the parameter name "@myParameter" you want to export to the configuration file, then click on Next button.
5) Write a name for the configuration and click on next.
6) The VS BIDS will create a XML file with the exported parameter properties.
7) open "TestPackage.dtsConfig" using the notepad and set the value property for the exported parameter.
8) Change the value of the parameter value in the dtsConfig and then save the file.
9) Run your package and the SSIS package will get the updated value from the xml file.

Hope this helps.


 

Import SSIS package error 0xC0010014 when importing SSIS package

Hi Folks,

I'm writing this blog post to share an interesting find while i was developing a SSIS package using VS 2012 with BI tools. Microsoft has release on March this year 2013 BI tools to VS 2012 so you don't need to use VS2010 shell while developing SSIS packages.

The download link for BI tools in VS 2012 is here:
http://msdn.microsoft.com/en-us/library/jj856966.aspx

After i developed my SSIS package and try to deploy it on the SQL Server 2008 R2. I was getting the following error message when importing the package, here is the screen shots:

 

If you are using the Management studio you will get this above error without details!, but to get the detailed error message use SSIS Execute Package Utility, you can launch it from SQL SERVER 2008 R2 --> Integration Services --> SSIS execute Package utility

.

If you read the actual error message it says:
Package Migration from Version 6 to Version 3 failed with error 0xC001700A

Basically the Version that i used to develop the SSIS is higher than the recognized version by the SQL Server 2008 R2. To fix this problem you need to change the package version from 6 to 3 and it works like a charm!

Follow the following steps:
1) Right clich on our dtsx file and select edit, Open the file in notepad.
2) Look for:

      DTS:Name="PackageFormatVersion">6

3) Change the number 6 for the version to 3 and save the file.
4) Select the File System File from the SSIS execute utility and you will not get any errors!
6) From the management studio you can import the SSIS with no errors.

Hope this helps.

[Update 05/09/2013] You will run through other issues if you used VS 2012 SQL Data tools against SQL Server 2008 R2, one of them is the sql job agent will fail to run the imported job due to the xml format mismatch while executing the package. So, it is recommended to develop any SSIS SQL2008 R2 components using the old BIDS VS 2008.


-ME
 

How to parse comma delimited string into IN Clause in SQL Server

Hi Folks,

Today i was trying to pass multi-value parameter into my select query in SQL Server. To accomplish this i stored the multi-value parameter in a comma delimited string. Then, i want to pass it in my IN clause of the select statement. here is an example to what i want to accomplish:

Delcare @myVar varchar(500)
Set @myVar='1,2,3,4,5,7'

Select * from Employee
where EmployeeId IN (@myVar)

You will get this error:
Msg 245, Level 16, State 1, Line 7
Conversion failed when converting the varchar value '1,2,3,4,5,7' to data type int.

It makes sense, because i have a varchar that holds all my values and i want to pass those in the IN clause that only accepts integer values!.

The solution for this problem is to parse this string into set of integers and pass it back to your query in your IN clause. For this reason, i have created a function that return a table of 1 column of type int. This will be passed back to the select statement i have mentioned above.

The function code to parse comma delimited string into set of integers:

-- =============================================
-- Author: Mostafa Elzoghbi
-- Create date: 12/10/2012
-- Description: Parse a string into set of numbers
-- =============================================
CREATE FUNCTION [dbo].[ParseCommaDelimitedString]

( @CommaSeparatedStr
nvarchar(1000) =NULL)
RETURNS @myTable TABLE ([Id] [int] NOT NULL)
AS
BEGIN
declare @pos int
declare @piece varchar(500)
-- Need to tack a delimiter onto the end of the input string if one doesn't exist
if right(rtrim(@CommaSeparatedStr ),1) <> ','
set @CommaSeparatedStr = @CommaSeparatedStr + ','
set @pos = patindex('%,%' , @CommaSeparatedStr )
while @pos <> 0
begin
set @piece = left(@CommaSeparatedStr , @pos - 1)
-- You have a piece of data, so insert it, print it, do whatever you want to with it.
insert @myTable
select @piece

set @CommaSeparatedStr = stuff(@CommaSeparatedStr , 1, @pos, '')
set @pos = patindex('%,%' , @CommaSeparatedStr )
end
RETURN
END
-- =============================================

What you need after that to re-write your query as follows:

Select *
from Employee
where EmployeeId IN
( select * from ParseCommaDelimitedString(@myVar))

Now, you will be able to pass any comma delimited string, varchar,nvarchar to this function and it returns table of integer that you can set in the IN clause of you select statement or any other T-SQL statements you works with your logic.

Hope this helps.

--ME

Error: unable to retrieve column information from the data source in SSIS

Hi Folks,

I was trying to call a stored procedure from OLE DB Source in SSIS. and i getting this error:

"Error: unable to retrieve column information from the data source"

I wasn't even able to get the columns returned from this stored procedure.

My stored procedure is having a dynamic query i'm building based on passed parameters from SSIS package.

My Stored procedure code:

Declare @sqlStatement as nvarchar(1000)

SET @sqlStatement = ' SELECT * FROM dbo.myTable
WHERE ID IN ('+ @Ids + ' )'
-- For tracing purposes
print @sqlStatement
execute sp_executesql @sqlStatement

To fix this problem, the SSIS when it executes the stored procedure in the design time, it doesn't pass any parameters, with that being said, you have to make sure that your stored procedure is working when you pass NULL values by default. and this was the trick to fix the problem. In my case, when i pass NULL values for my parameter that contains multiple values my SQL query statement is not valid!

I fixed my stored procedure through the following:

Declare @sqlStatement as nvarchar(1000)
IF @Ids is not NULL
SET @sqlStatement = ' SELECT * FROM myTable WHERE ID IN (' + @Ids + ' )'
ELSE
SET @sqlStatement = ' SELECT *FROM myTable'

-- For tracing purposes

print @sqlStatement
-- EXEC @sqlStatement
execute sp_executesql @sqlStatement

After fixing my stored procedure to work with null passed values and my dynamic sql statement is correct i was able to view my columns returned from the stored procedure and it works like a charm!

Hope this tip helps you when you create any stored procedure that is being called from SSIS objects such as OLE DB source or SQL Task.

Thanks,
--ME

Unable to use SQL Server because ASP.NET version 2.0 Session State is not installed on the SQL server. Please install ASP.NET Session State SQL Server version 2.0 or above.

Hi Folks,

I was deploying an asp.net website which configured to store session data in a custom databases in SQL Server 2008. After i scripted the whole DB and moved it to a new server and after deploying the website and try to run the application, I was getting this error:

Unable to use SQL Server because ASP.NET version 2.0 Session State is not installed on the SQL server. Please install ASP.NET Session State SQL Server version 2.0 or above.

My web.config configurations:

 <system.web>
 
      
      <sessionState
       mode="SQLServer"
       cookieless="false"
       timeout="20"
       sqlConnectionString="data source=SERVERNAME; initial catalog=MYDB; user id=mostafa; password=mostafaelzoghbi" 
       allowCustomSqlDatabase="true"
      />
      
       system.web> 

Since this error message is misleading, you don't need to run aspnet_regsql command since i was using a custom database and all stored procedures, tables, custom user data types and DB objects have been scripted and deployed too. the cause of this error if you didn't give enough permission to the user that accesses the database. User should have execute permissions or grant dbo.owner to it.

Hope this helps.

Best Practices for SQL 2008 in SharePoint 2010 Farm

Hi All,


In this post i'd like to share with you best practices for SQL 2008 R2 in SharePoint 2010 farm and the operations and configuration that need to be taken in place when installing, configuring and administrating SharePoint 2010.


1) Designing SQL 2008 servers in your SharePoint 2010 farm: When you decide which topology you will have in your SharePoint 2010 farm, the SQL Server 2008 should be in a dedicated server and shouldn't be having any other role in your SP 2010 farm.


2) After installing SQL 2008 R2, you shouldn't go and start SP 2010 installation with SQL 2008 R2 Default configuration, here is what you need to revise:
    a) Don't enable auto-create statistics on SQL Server that's supporting SharePoint farm. You can enable/disable this feature on the DB level, here is the command you need to execute in your DB:

ALTER DATABASE dbname SET AUTO_CREATE_STATISTICS OFF

     b) Set Maximum degree of parallelism to 1 for all SQL server instances that host SP 2010 DBs. The value behind this is that each request is served by a single SQL Server Process.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'max degree of parallelism', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO

3) Secure and harden your SQL 2008 server DB by blocking the default ports and block default SQL server default ports. Check Out ref #3.

4) Use SQL Server 2008 backup compression feature to speed up the SharePoint backup. This feature is available in SQL 2008 Ent. edition and SQL 2008 R2 standard edition too. This feature significantly reduces the backup file size and shipped logs. The only consideration you should know that this feature consumes more processing time that might affect the performance of your DB server if the server doesn't properly planned to have enough processing power. This feature you enable it on the server level under Database settings, you will find a check box to use DB compression.

Hope this helps and let me know if you have any additional notes or concerns.

Refereces:

1) Auto-Create Feature in SQL 2008:

http://blogs.technet.com/b/rob/archive/2008/05/16/sql-server-statistics.aspx

2) Set the maximum degree of parallelism in SQL 2008:

http://msdn.microsoft.com/en-us/library/ms181007.aspx

3) Harden & Secure SQL 2008 server DBs:

http://technet.microsoft.com/en-us/library/ff607733.aspx

http://technet.microsoft.com/en-us/library/cc262849.aspx

5) SQL 2008 Best practices in SharePoint 2010 Farm:

http://technet.microsoft.com/en-us/library/hh292622.aspx

Regards,
Mostafa Arafa
http://twitter.com/mostafaelzoghbi

Building PPS solutions in SharePoint 2010

Hi All,

During giving my session in SharePoint "The Conference" in DC, I provided some few links for my attendees that helps them to start working with PerformancePoint Services in SharePoint 2010.

To start working with SharePoint 2010 that has SP 2010, Office 2010, SQL 2008 & Project Server 2010.

There is an Information Worker VM to start working with PPS in SharePoint 2010:

http://www.microsoft.com/download/en/details.aspx?id=21099

To get a started with SQL2008 experience and BI, here is a training kit to start working with BI:

http://www.microsoft.com/download/en/details.aspx?id=16281

Regards,

Mostafa Arafa

twitter.com/mostafaelzoghbi

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding

Hi All,

If you are experiencing a problem connecting to your SQL Server 2008 R2 DB server from any remote machine in your network, Please make sure of the following:

1) You are able to ping the machine from the remote server.

2) Try to connect make sure that the SQL Server Engine service is up and running, You can open "SQL Server Configuration Manager" and make sure that the service is up and running.

3) Make sure that you have at least one protocol TCP/IP,Named Piper or Shared Memory is allowed on both your client and your DB Server and this you can check it from SQL Configuration Manager.

4) THIS FIXED MY PROBLEM: Disable the Domain firewall in the DB machine that blocks all inbound connections to your DB Server. To do this:

a) From Server Manager.

b) Under Configuration - > Select Windows Firewall.

c) Click on Windows Firewall Properties and disable your domain profile firewall.

Try to refresh your page and you will be able to connect to your DB.

Hope this helps.

Regards,

Mostafa Arafa

twitter.com/mostafaelzoghbi

Create Reports by Business users and deploy it to SharePoint 2010

Hi All,

Usually the business users are trying to create reports for their backend DBs. You can build your own Report and deploy it to SharePoint 2010.

Without using VS.NET you can develop and create your reports using FREE Reporting tool called "Report Builder". In this tool, You can connect to your DB and build your report.

To Download SQL 2008 R2 Report Builder tool, Here is the link:
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=d3173a87-7c0d-40cc-a408-3d1a43ae4e33&displaylang=en

Once you have "SQL 2008 R2 Report Builder 3.0" tool, You can connect to your DB and you will be able to create Charts and Reports.

Once you are done, You can deploy the report by saving it locally "*.rdl" then from sharepoint Document library with Report Builder content type, You just need to upload the rdl file to the document libarary and once you click on the report you will be able to see it.

TO create the document libaray with Report builder content type, check this blog post:
http://dinesql.blogspot.com/2010/06/configuring-reporting-services-2008-r2_07.html

Once you create the document, Just upload the rdl to your Document library.

If your farm is not configured to allow SSRS reports in SharePoint 2010, read this blog:
http://moustafa-arafa.blogspot.com/2010/06/how-to-configure-reporting-services.html


Enjoy...

Regards,Mostafa Arafa
twitter.com/mostafaelzoghbi

Troubleshooting Cloud Service Deployment in Azure

Hi All,

I was working on last few days to deploy a sitefinity CMS website to the cloud on Windows Azure. I faced different problems and stop points while trying to deploy my Cloud service to Azure.

In this post i will share the main check points that you have to go through before start deploying you application to the cloud, since if you tried to deploy your application and you were getting either runtime errors or you can't start your service on the cloud or any other problems you might face because of missing files or verification points you have to do on your development fabric environment before deploy to the cloud.

1) Make sure that web worker role is compiled with no errors on your development machine.

2) Make sure that all custom DLLs that you have reference for are copied to the output file, To do this, right click on all your custom or third party dlls and open properties window and select Copy to output directory to COPY ALWAYS.

3) Run your service and make sure that you are not getting any runtime errors on the development fabric -Local before start deploying.

4) If you published your service, and when you are trying to start it, it keep stopping, this means there's a problem on your service package file.

5) If you are having any problem starting your cloud service, Install this tool (IntelliTrace) on your Visual studio 2010 Ultimate edition.
http://blogs.msdn.com/b/jnak/archive/2010/05/27/using-intellitrace-to-debug-windows-azure-cloud-services.aspx

6) If you face any problem, from Azure web portal, you can submit support ticket for your problem, the tip is include your subscription Id and Deployment Id on your ticket to get fast and reasonable resolution with analysis.

7) If you are using VS2010, Use Server explorer to navigate through your azure account components.

8) Using SQL 2008 R2, Script the DB ( Schema + Data ) and then connect to the SQL AZURE using SQL Server Management Studio.

9) IMPORTANT: Before deploying your website, make sure to update the web.config to point to the SQL Azure DB. If you miss this step, You might encounter a problem while deploying your web application when you try to start the application and the solution keep moving to stopped state because of trying to connect to the DB.

10) Advice: If you had the problem of your application keeps moving to the stop state, what you need to do is to submit a ticket to the support team and they will be able to check your VM event log and guide you to fix it since they are a lot of parameters to look at when you deploy.


Hope this helps.


Regards,
Mostafa Arafa
twitter.com/mostafaelzoghbi

WebMatrix and RAZOR for web development with no cost

Hi All,

Microsoft released the WebMatrix platform (FREE) that has all you need to build any websites, with WebMatrix you can do:
1) Design your websties.
2) Develop/Deploy your websites using RAZOR: is a new declarative simple language to build your websites.
3) Platform support ASP.NET, WebForms, MVC, AJAX, CSS and all the power of .NET Framework.
4) It’s just 15 M to download.
5) It includes IIS Express, SQL Compact,.NET Framework runtime and all you need to develop websites.
6) Scale up your sites to VS 2010 and SQL Server 2008 if required.
7) Download and build websites on open source CMS such as Word Press, Drupal 7, PHP and MySQL.
8) More….

Get a quick tour:
http://www.microsoft.com/web/default.aspx

Code without boundaries using WebMatrix and start developing websites with no cost for the tools :)

Regards,
Mostafa Arafa
twitter.com/mostafaelzoghbi

How to configure Reporting Services 2008 in SharePoint 2010

Hi All,

In This post i will show how to configure SQL Reporting services in SharePoint 2010.

If you have a SharePoint 2010 site and you navigate to site settings and try to click on "Managed Services Schedule" you will get this error:
Unsupported Reporting Services Functionality

This is because your SharePoint configuration is not configured with Reporting services server.

- To fix that, follow below steps:
1) Login to the Reporting Services server and make sure that the service is up and running, and double check that the reporting server is working:
a) Open SQL Reporting configuration manager
b) Select Report Manager Url.
c) click on the url to make sure it is working.
For example: http://SERVERNAME/Reports/

2) Open SharePoint Central administration:
a) Select General Application Settings.
b) Select Reporting Services Integration.
c) Set the report server url.
d) Select authentication mode, Windows if the reports show personalized data.
OR select the trusted account that will be used for impersonation.
e) Click on Ok, You will get a successful page.
f) You might get the following error:
"Failed to establish connection with report server. Verify the server URL is correct or review ULS logs for more information. Product area: SQL Server Reporting Services, Category: Configuration Pages"

This means that the SSRS is not configured to run with SharePoint Integrated Mode, to change it,read below article "Overview of Configuration Steps on a Report Server":
http://technet.microsoft.com/en-us/library/bb326356.aspx


3) Go to your sharepoint site settings and enable Report Integration feature on site collection features.

4) Go to your site and add reporting services webpart in your page and configure it to your report.

Note: It is recommended to have a library that has all your reports in one place and all SQL Reporting services webparts are connecting to this library.

Happy SharePointing.... :)

Update [7/1/2010]: My friend @awsaxton On twitter- added this tip:
@MostafaElzoghbi On your SharePoint/RS blog, you may want to mention the need for SP1 CU7 to get RS 2008 to work with SP 2010.
Thanks Adam Saxton.

Regards,
Mostafa arafa

SQL 2008 Troubleshooting Tip : "Mailbox unavailable. The server response was: 5.7.1 Client does not have permissions to send as this sender".

Hi Folks,

I was trying to send Emails using SSIS package deployed in SQL 2008. I was getting this error when i am trying to run the SQL Agent Job which sends Email address on the success of this job.

Error message in event log:
"Mailbox unavailable. The server response was: 5.7.1 Client does not have permissions to send as this sender".

To fix this problem check the following:

1) Check the service account for SQL Agent Service in your server.
2) Make sure the service account is a domain account, because if the system administrator doesn't allow to send emails for anonymous users, then this is one of the main reasons to get this error.
3) Don't forget to restart the SQL service agent if you changed the service account.
4) Inform your system administrator to add anonymous users with a new receive connector in Exchange, check this article:

http://computermutt.wordpress.com/2009/09/02/smtpexception-mailbox-unavailable-when-sending-from-web-page/

5) Make sure in your SSIS Package you check use windows authentication checkbox in the SMTP Connection manager.

Hope this helps.

Regards,
Mostafa arafa

Cloud computing in action presentation

Hi All,

Last week i presented "Cloud Computing in Action" presentation in Reston,VA. In this presentaion i talked about Azure cloud computing platform, and i covered the following topics:

- Windows Azure
- Windows Azure Storage.
- SQL Azure / SSMS / Storage Explorer
- AppFabric / Development Fabric
- Develop & Deploy Application on Azure using VS 2010 RC.
- Showcase for a full LOB ASP.NET application hosted on Azure.
- Q & A.

I like also to share my presnetation with all of you to make use of it.

http://cid-4bc94054914a6469.skydrive.live.com/self.aspx/Blog%20Code/Cloud%20In%20Action.pptx?ccr=5803

Thanks to Dan Kasun to provide me good presentation and materials for Azure.

Hope it helps.

Regards,
Mostafa arafa

Cloud Computing Thoughts with Azure

Hi All,

Today i attended the Government Cloud Computing event at Washignton DC sponsored By Microsoft which held on Ronald Regan Conference center. I had cool conversation with the DPE team and Azure Technical guys in the event with some few business contacts in the Federal government Reps.

The event started with the Cloud computing vision on the furture and the challenges the face Microsoft and ISVs to engage the government to be open and approve the cloud computing solutions.

Then, Microsoft showed some show cases / Demos for implementing Windows Azure,Dallas and AppFabric with Blobs on the cloud.

Then we had a great panel that includes some of technology experts and innovators from the government and other private sector.

I really like the innovation in NASA and the CIO who was so open to the new technologies and how it helps NASA's mission and the scientific research that it has and how it helps them to reduce the operational cost for their IT services.

The event had 3 Tracks : Business Track, IT Track and Developer Track.

I will give my insights in the Developer track as bullet points:

- You can start developing Azure cloud computing with ZERO Dollar investment.
- Use VS 2008 with Azure training Kit to start developing cloud apps.
- ALL MSDN subscribers have a promotion from MS to have 750 hrs for 8 months FREE.
- Cloud Storage Studio is a free tool to manage your Azure Storage data.
- blogs.msdn.com/publicsector to get all public sector news.
- Accessing Cloud data using RESTful services using Odata protocol, www.odata.org
- myazurestorage.com is another tool to manage azure storage.
- SQL Azure migration wizard is an open source project in codeplex to migrate your DB to Azure compliant.
- MS Synch. Framework power pack for SQL Azure - Nov CTP to synch between different SQL Azure Databases and on premise DBs.
- Microsoft has a program called : Open government data initiative OGDI to expose data to the citizens using Azure technologies.
- OGDI provides toolkits with different programming languages to help developers to consume these data using Azure Platform.

These are my notes, hope it helps :)

Update: My page on the cloud : http://metrostarsystems.cloudapp.net/

Regards,
Mostafa arafa

How to shrink SharePoint_Config DB

Hi all,

I had one of my SharePoint Configuration database for one of my sharepoint sites reached 122 GB since i had a problem on having any operation from the FE server to the DB server.

My installation for the DB server is SQL 2008, the problem is the transaction log is too large and to minimize or shirnk the size, here is the steps:

1) Take a full backup from your database - Important.
2) Change the DB recovery model to Simple ( Right click on the SharePoint_Config and click on properties -> Options - > Recovery model : change it to simple.

3) Open a new query in the management studio and write this shrink command:

DBCC SHRINKFILE('Sharepoint_Config_Log',1)

4) Check the HD free space, your free space on the HD has been updated.

5) Don't forget to return back the recovery model to full for any future backups.

Check the transaction log file (ldf file of the sharepoint_config) it has been shrinked to the minimum size.

In my case the transaction log has been shrinked from 122GB to 2MB.

Hope this helps.

Regards,
Mostafa arafa

How to build your DB on MS SQL Azure

Hi Folks,

I would like to share with you some tips for how to move your on-premise DB to MS SQL Azure on the cloud.

First, To be able to connect to SQL Azure Prepare your environment with the following:

1) Install MS SQL 2008 R2 on your machine - Nov CTP, having any other version of SQL 2008 will not give you the full functionality like working with object browser in SQL Server 2008 R2.

Tips :

a) Before installing SQL 2008 R2, please check your exisiting environment by using SQL Server upgrade advisor (it is a part of SQL 2008 R2).

b) Using your credientials in SQL Azure, you will be able to connect using SQL 2008 R2 with no issues and you will be able to navigate through your objects in the DB.

1) How to move your on premise Database to SQL Azure on the cloud:

1) Script your DB from your server.
2) There is some changes you have to do it on the script before executing it on the SQL Azure DB.

Tip: 1) Remove any reference to the file group such as : ON [PRIMARY] on your script.

Then when you remove the reference, you will be able to execute the script using SQL Management Studio and when you refresh the server you will be able to see your new created objects.

Hope this helps.

Regards,
Mostafa arafa @ Azure Cloud......

WOW... Get Started with SQL 2008

Hi Folks,


New Site has been published for New MS Product SQL 2008, if you are developer,IT Professional or want to know about the product for fun,Get started with this site :

http://www.microsoft.com/sql/experience/html/Default.aspx?loc=en



Regards,
Mostafa arafa

Download SQL Server 2008 NOW

Hi All,

Now you can download MS SQL 2008 for 180 Days Evaluations period ,try it :

http://www.microsoft.com/sqlserver/2008/en/us/trial-software.aspx

Site contains other very useful demos and virtual labs :

http://www.microsoft.com/sqlserver/2008/en/us/demos.aspx

http://www.microsoft.com/sqlserver/2008/en/us/virtual-labs.aspx

enjoy :)



Regards,
Mostafa arafa