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

Standard

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?

Standard

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:

Standard

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?

Standard

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?

Standard

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:

Standard

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:

Standard

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