SSIS Error on opening a package: The connection “{GUID}” is not found. The error is thrown by connections collection when the specific connection element is not found

In my case, This error came up in SSIS after some copy-pasting happened in our TFS. I tried opening a package & it gave the “The connection {GUID} is not found…” error. So here’s how I was able to solve it:

1. After I got the latest version of files, I navigated to the Integration Services package file on my local machine.

2. Opened the  file in notepad to look at XML.

3. After I was able to see the XML code, I searched for the connection GUID “xyz…” that was showing up in the error

4. Now, once you locate the GUID, figure out the package component that the connection is being used. In my case it was a “Execute SQL Task”

5. I then opened my package and fixed the connection in the Task.

That’s about it for this post. I hope this helps someone out there.

How to strip double quotes while importing data from CSV or TSV using SSMS Import Data wizard OR SSIS?

Long Title! Let me explain. This post will help you solve following problem if you run into it:

1) You are using SSMS Import data wizard to load data from a comma (or tab) separated value (CSV/TSV) file into SQL Server Table & you find that your source data values has double quotes and so you want to strip them before loading to destination table.

2) You are using SSIS to load data from a CSV/TSV file into SQL Server Table & you want to strip the double quotes in source table fields before you load the data to destination table.

Double Quotes CSV file SSMS SSIS LoadSolution:

1. After you’ve configured the Flat File connection. you’ll reach to a point where you’ll see “Flat File Connection Manager” in SSIS. Or in the SQL Server Import & Export data wizard, you’ll see a dialog box to configure flat file connections.

2. In the Text Qualifier, enter

Strip Double Quotes SSMS SSIS Import Wizard3. Make sure to Preview the data to verify that the double quotes around data fields have been trimmed.

4. That’s it! You’ve successfully configured the flat file connection manager to strip double quotes.

Recapping my social media activities during Jan 1 – Feb 24 2013:

Recapping my social media activities during Jan 1 – Feb 20 2013:

That’s about it for this post.

If we want to read related past posts, here they are:

OCT 3 – OCT 10 2012

OCT 11 – OCT 18 2012

OCT 19 – NOV 11 2012

NOV 12 – DEC 31 2012

Let’s connect and converse on any of these people networks!

paras doshi blog on facebookparas doshi twitter paras doshi google plus paras doshi linkedin

How to view error(s) that occur during Master data Service’s Staging Process?

To view error(s) that occur during MDS’s staging process, we have two views: stg.viw_name_MemberErrorDetails & stg.viw_name_RelationshipErrorDetails. For the purpose of this blog post, let me show you how I (as an administrator) view information from stg.viw_name_MemberErrorDetails.

1) Name of the entity: supplier & Name of model is Suppliers

2) I imported data into staging table stg.supplier_leaf via SSIS

3) Now here’s how you can see the errors that occur during MDS’s staging process:

we can go to MDS web application to initiate the staging process > start the batch > after completion you can see the status as well as if it has any errors or not:

Master Data Services Errors4) Now if you see that there’s an error, then you can go to stg.viw_name_MemberErrorDetails to see what are the errors. In my case, I am going to run the query:

select * from stg.viw_supplier_MemberErrorDetails where Batch_ID=2

You can get the above query via MDS web application too:

Master Data Services SQL Server Staging Process

5) And as you can imagine, you can get access to this error data via SSIS (SQL Server Integration services) too. So if you have a workflow that a. Loads data to MDS and b. initiates the Batch process via Stored Procedure; then you can program it to get access to errors from the stg.viw_name_MemberErrorDetails & stg.viw_name_RelationshipErrorDetails tables.

SQL Server Integration Services and Master Data Services

That’s about it for this post! I hope this helps. Your comments are very welcome!

How to standardize data using Data Quality Services?

I was playing with Data Quality Services when I though that it would be nice if I could implement what I had learned and create a quick demo that shows how to standardize data using the technology. So here it goes:

Note: This is not a step by step Guide – I am assuming that you are aware of different pieces of DQS. I am going to post a brief outline of how did I go about implementing a quick demo.

Technology used:

1) SQL Server 2012 Data Quality Services

2) SQL Server Integration services to show the DQS cleansing  component in action.

Problem:

We want to standardize to Gender to have only two values M & F – but now data coming from two files creates data inconsistency issues:

Two files that look like this:

File 1: (Excel)

1 sql server data quality services data source 1 excel file

File 2: (CSV)

2 sql server data quality services data source 2 csv file

Solution:

Let’s see how SQL server 2012′s data quality services would help us solve this problem:

1) I created a Knowledge base with a domain Gender and created domain values that looks like this:

3 data quality services domain management values sql server

2) Tested the Knowledge base quickly by creating a Data Quality Project. It worked!

3) Switched to SSIS and created a Data Flow which uses following components:

4 data quality services sql server integration services

What would be interesting to you to see mapping tab in the DQS cleansing transformation component, so here it is:

5 data quality SSIS data cleaning transformation

5) And let me show you the Target File – note that you can choose to NOT have Gender_Source, Gender_Status & Record Status. The column of interest is Gender_Output

6 standardized output SSIS gender data quality services

That’s about it for this post. I hope this helps!

How to import data from Excel file to SQL Server:

In this post, we would see how to import data from Excel file to SQL Server using Import and Export Wizard, we would import data from four excel worksheets to SQL server. Here are the steps:

1) Right click the Target Database > Tasks > Import Data to open the SQL Server Import and export wizard

import data ssms sql server import export

2) select the Data Source. In this case, Excel file. You would select the worksheets to fetch data from later, for now – select the excel file as shown below:

import data ssms sql server import export

3) choose the Target Database:

choose the target database import and export systems

4) Select the tables (in our case- worksheets) or specify a query. For the purpose of this demo, we would go with “copy data from one or more table or views” option:

 specify table copy or query

5) Select the worksheets from the Excel file and also specify the tables in the target systems. If the tables in the target database are not already created, then would be created by this wizard:

sql server map source and targetsql server map source and target

6) You have the option of running this wizard immediately or you have the option of creating a SSIS package and then run it later. For the purpose of this post, we would select Run Immediately

7) Review the information and click Finish

sql server review the complete wizard

8) After successful execution:

8 sql server import export execution was successful

Conclusion:

In this blog post, we saw how to import data from Excel file to SQL server.

 

Visualizing what Merge Transformation in SQL server Integration Services (SSIS) does:

Visualizing stuff is powerful – if you have visualized something then you can remember it for a longer period of time. So in this post, we will visualize what the Merge Transformation in SQL Server Integration services (SSIS) Does:

merge tranformation SQL Server Integration Services

Note:

1) Do not Confuse it with Merge Join Tranformation. “Merge Join” is NOT same as “Merge”

2) Merge Transformation can accept only TWO (sorted) data sources. Whereas if you want to “combine” more than two data-sets, then go for Union ALL tranformation

Summary of Just Learned Tips that i have submitted for year 2011 at BeyondRelational.com site

Just learned is a section in the BeyondRelational site that is meant for members to post a nuggets (short) of knowledge. I like this section – and so time to time i open the section and browse through the posts and once in a while, contribute. Here is the list of tips that i submitted this year:

1. [SSIS] Execution of an SSIS package that has Excel Desti/Source in 64bit SQL server will throw an error and Workaround for this

2. In windows server 2008 R2, IE asks you to add each website you visit to allowed site list. I learned how to override it for my demo machine.

3. Just learned how to allow file downloads in IE in Windows server 08 R2

4. SQL Server PowerPivot for SharePoint not available with standalone sharepoint

5. SQL Azure’s next service release will take the first step towards having common feature set between cloud and SQL server Denali!

6. Deploying azure web roles just got faster with “Windows Azure Accelerator for Web Roles”

7. Windows Azure toolkit for social games – makes developing social games on windows azure platform just got easier and quicker

8. General – Lorem ipsum has no meaning and just used this phrase to redirect attention

9.[Azure] a MSDN subscriber gets an alert email in case the Azure usage crosses the allowed usage

10. Press F11 on IE and Firefox to view web pages in full screen mode

11. never let your domain name expire

12. [Azure] One can map (unfriendly) azure storage domain to a custom domain name (Easily)

13. The new recognition system on MSDN, Technet and Expression profile is powered by windows Azure!

14. Posting code in wordpress.com

15. [Azure] Charges dropped for ingress (inbound traffic)

16. [Azure] One can combine a web role and a worker role

17. SQL Azure – currently, Maximum 6 servers are supported per subscription

18. Azure will charge you until you delete your hosted service

19. Atleast 2 instances of a role are required to meet the Azure SLA requirements

20. a DMV [sys.dm_db_partition_stats] in SQL Azure to calculate size of DB

Upload CSV and excel files to SQL Azure via “Data Transfer” tool

[UPDATE 9/12/2012: The URL's that point to the service does not seem to work. The service may be out of "lab phase" and hopefully we would see this integrated in Portal or as an Independent service.]

Microsoft recently announced a tool (which is in CTP) called “Data Transfer” on SQL Azure Labs. It let’s you transfer CSV and excel data to SQL Azure or Windows Azure blob. I think this service would make our task easier than before. Earlier, We had the option to use SQL server integration services (SSIS) or BCP to transfer excel files or CSV files to SQL Azure. It was straightforward too but what if we had the tool in which you just point it to your excel/csv file and it would do try to do the rest. wouldn’t that be easier? Yes, so Data Transfer aims to achieve exactly that. To test drive Data Transfer tool go to https://web.datatransfer.azure.com/

In this blog post, I will show you how to upload an excel file to SQL Azure via this tool:

1. After you have registered yourselves (via a Live ID) on https://web.datatransfer.azure.com/ – you will see

image

2. In my case, since I wish to transfer an excel file to SQL Azure, I am going to go with first option i.e. Microsoft SQL Azure

3. In the next step, provide the credentials of a SQL Azure DB (that exists already)

image 2

You have the option of saving a connection. And the drop down that you see in the above image will let you see all your saved connection.

4. In the next step, point to your file and select appropriate options via the Advanced options which has the File Encoding, Column Delimiter, Row delimiter and Text Qualifier options. So this are useful if you had a flat file with {tab} instead of {,} to separate column values. In my case, I am going to leave the advanced options to their default values. Point to the excel file and since my first row has column names – I am going to check the box that says “column names in the first data row” and then click on analyze.

image

5. if you had not checked the “Edit table defaults” box, then Done! you can view the status of the process from the My Data tab. It’s this easy.

If you checked the Edit table defaults, the next page would allow you to edit the data-type. The power of this tool resides in the fact that it guesses the data-type and assigns it to the data that we had specified. And if you want to over-ride the data-type chosen by the tool, you can do so here at this page:

BTW: I find this amazing!

Edit table defaults

6. Click on save and the data is ready to be uploaded. it’s that simple. And after the upload, you can view the details here on the “my data” page:

image

7. You can log into the SQL Azure manage portal and browse the tables. And after upload, the link to “manage” these tables will also be available in the “my data” page.

image

Conclusion:

As you imagine, the process is simple and “Data Transfer” tool makes it easier to upload CSV/Excel file to SQL Azure. And it also let’s you upload a file to Azure blob storage account. Give it a spin! URL:  https://web.datatransfer.azure.com/

And learn more about it here: http://www.microsoft.com/en-us/sqlazurelabs/labs/datatransfer.aspx

[SSIS] unzip a file in SQL server Integration services (SSIS) package

An assignment I was working on recently required me to unzip a file in an SSIS package. I didn’t knew it – so I asked my mentor Rushabh Mehta sir about it and he was kind enough to email me a demo. Now based on the deemo, my next task was to input couple of configuration’s like location of zipped file and then i was good to go! And so i learned something new, and I just thought of documenting it.

Aim: To unzip a file in an SSIS package

SSIS Task used: I’ll be using the Execute process task to carry out the task.

Now, here is the configuration of the Execute process task.

Executable: C:\Program Files\WinRAR\WinRAR.exe

[complete path of the executable. I have used WinRar to unzip a file – you could any valid tool you wish]

Arguments: e voila.zip  -o+

[General format: e <filename> –o+]

WorkingDirectory: D:\blogpsot\

[Path of the working directory. The file will be unzipped at the same location. If you wish to unzip at different location, you could use something like:

e “D:\blogpost\voila.zip” “D:\blogpost2\” –o+

and leave the WorkingDirectory blank, Then in this case the file will be unzipped at D:\blogpost2\ ]

Now,  here is the screenshot of the configuration. Just in case:

image

So yeah, happy unzipping!

Related links:

Execute process task