Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««34567»»

The SSIS Data Pump - Step 2 of the Stairway to Integration Services Expand / Collapse
Author
Message
Posted Friday, October 19, 2012 10:27 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 16, 2013 9:12 AM
Points: 26, Visits: 48
You are very correct on my trying to do it using SSMS instead of SSIS. I had just learned that you can view an existing SSIS package through SSMS by using "Connect to... Analysis Services", but it doesn't seem you can create one that way, so I made things worse by running down the wrong path at first. :/

I am currently going through your tutorial and making progress (Up to Step 4 now! :) ) and taking notes that I will post here when I'm through the whole thing.

One tiny gotcha I ran into using the database backup you supplied: The backup seems to be in the state of "after going through the tutorial" - found that dbo.Contact was already there which threw me for a couple minutes when the tutorial tried to create that table. No big deal once I figured that out, deleted dbo.Contact and was able to keep going.

Thank you in advance for a tutorial focusing purely on SSIS, all the rest out there seem to introduce SSIS at the same time as cubing/mining (Also new to me) which is too much to process at one time.
Post #1374917
Posted Friday, October 19, 2012 8:24 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 1:39 PM
Points: 388, Visits: 1,034
Hi Everyone,

I corrected the typo and added a link to the first step (Level 1) of this series. I also updated Level 1 to that it walks you through creating the initial SSIS project.

I accept full responsibility for any confusion and I apologize. I hope many will enjoy this series and learn more about SSIS by reading it and building the solution.

Thanks to everyone who provided feedback - please continue.

:{>


Andy Leonard
CSO, Linchpin People
Follow me on Twitter: @AndyLeonard
Post #1375075
Posted Monday, October 22, 2012 12:15 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 20, 2012 9:24 AM
Points: 3, Visits: 27
Andy Leonard (10/18/2012)
joresnik (10/16/2012)
Figure 13

The server name can be typed into or selected from the Server Name dropdown. Once the server name is configured, SSIS actually connects to the server and retrieves a list of databases. You can enter or select the database in the “Select or enter a database name” dropdown. In this case, I connected to the (local) instance of SQL Server and selected the AdventureWorks database.


I think you skipped something here. What if a person new to this does not have a local instance of SQL server created? Will they know how to do this?


Hi Joresnik,

While I understand your point, I disagree. This series is focused on learning to develop data integration solutions with SSIS.

Hope this helps,
Andy


I do see your point, but you do also cover the basics of BIDS, so describing how to create a local instance doesn't seem like it would be beyond the scope of the tutorial. The fact is, I am one of the people who did not know how to create a local instance, and I had to take a break from your tutorial and find another tutorial that described this.

Perhaps a solution would be to provide a link to a tutorial that covers it?
Post #1375664
Posted Monday, October 22, 2012 2:22 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 16, 2013 9:12 AM
Points: 26, Visits: 48
Alright! I have successfully made it through your tutorial and have learned much! Thank you for your initial handholding, I think I can actually build something in SSIS now! :)

As promised, here are my notes: (As well as C# equivalents to your VB.NET examples)

Here's the list of corrections/suggestions I said I'd provide from my notes of running through your tutorial. In order to refer to specific pieces of your tutorial, I went with the step number followed by the figure the suggestion/correction appears nearest (Usually right below that figure).

Step 2, Fig 36: Using the copy of the database you supplied, the dbo.Contact table already exists. Need to delete that table in SSMS in order to complete this step.
Step 4, Fig 33: The double-quotes around Humperdinck have been converted to Word-style double quotes (The kind that point in towards the text they are surrounding). Convert these in the editor to use HTML escape sequence &dquo; to avoid this.
Ex: &dquo;Humperdinck&dquo;

Step 5, Fig 2: Wording is a bit rough for describing the addition of new Data Flow "Delete Rows". Initially thought I was supposed to add a new "Execute SQL Task".
Step 8, Fig 13: Succeed Script Task 1 dialog doesn't appear. Perhaps I missed something?
Step 8, Fig 29: Says to disable "Sequence Container 1 - the original one", but "Sequence Container" is the original.

Also converted your example script code from VB.NET to C#, will come in handy for those that work primarily in C# like me.

Step 6, Figure 13: C# Version:

public void Main()
{
string sTaskName = Dts.Variables["TaskName"].Value.ToString();
MessageBox.Show(sTaskName + " completed.");
Dts.TaskResult = (int)ScriptResults.Success;
}


Step 7, (On Completion (Failure) [No Listing # on code example]):

public void Main()
{
var sTaskName = Dts.Variables["TaskName"].Value.ToString();
var iResponse = MessageBox.Show("Succeed " + sTaskName + "?", sTaskName + " Success Question", MessageBoxButtons.YesNo, MessageBoxIcon.Question);

if (iResponse == DialogResult.Yes)
{
Dts.TaskResult = (int)ScriptResults.Success;
}
else
{
Dts.TaskResult = (int)ScriptResults.Failure;
}
}


Step 8, Listing 1: C# Version:

public void Main()
{
var taskName = Dts.Variables["TaskName"].Value.ToString();
var response = MessageBox.Show("Set MyBool to True?", taskName, MessageBoxButtons.YesNo);

if (response == DialogResult.Yes)
{
Dts.Variables["User::MyBool"].Value = true;
}
else
{
Dts.Variables["User::MyBool"].Value = false;
}

Dts.TaskResult = (int)ScriptResults.Success;
}


Step 8, Listing 2: C# Version:

public void Main()
{
var sTaskName = Dts.Variables["TaskName"].Value.ToString();
var iResponse = MessageBox.Show("Succeed " + sTaskName + "?", sTaskName + " Success Question", MessageBoxButtons.YesNo, MessageBoxIcon.Question);

if (iResponse == DialogResult.Yes)
{
Dts.TaskResult = (int)ScriptResults.Success;
}
else
{
Dts.TaskResult = (int)ScriptResults.Failure;
}
}


Step 9, Listing 1: C# version:

public void Main()
{
int iErrorCode = Convert.ToInt32(Dts.Variables["ErrorCode"].Value);
string sErrorDescription = Dts.Variables["ErrorDescription"].Value.ToString();
string sSourceName = Dts.Variables["SourceName"].Value.ToString();
string sSubComponent = "Script Task 4 OnError Event Handler";
string sMsg = String.Format("Source: {0}ErrorCode: {1}Error Description: {2}", sSourceName, iErrorCode, sErrorDescription);

MessageBox.Show(sMsg, sSubComponent);

Dts.TaskResult = (int)ScriptResults.Success;
}
Post #1375723
Posted Friday, February 08, 2013 12:15 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 11:21 AM
Points: 293, Visits: 499
Thanks for the lovely article... this is my first effort at SSIS.

I am trying to do the same steps, in this second article, but on our databases. I am getting a data error.

Error 1 Validation error. Data Flow Task: Data Flow Task: Column "MRP_Area_CD" cannot convert between unicode and non-unicode string data types. Package.dtsx 0 0

Both source and destination tables have the same data type, so I guess this is something that SSIS is doing, how do I fix it?

From the alter table code...

[MRP_Area_CD] [varchar](10) NOT NULL,
Post #1417868
Posted Monday, February 11, 2013 9:49 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 1:39 PM
Points: 388, Visits: 1,034
Hi dwilliscp,

When data is read from some flat file sources, SSIS treats it as unicode data. The data flow will not implicitly convert unicode to non-unicode data types, so you need to perform this conversion yourself. One way is to use the Data Conversion tranformation. Another way is to use a Derived Column transformation. In both cases, a new column is added to the data flow task's pipeline with the desired data type (you cannot change the data type of a column using either of these tasks). If you use the Derived Column transformation, you need to perform a type cast operation on the unicode column to create a new non-unicode column. If your input column is named MyCol, the type cast will read something like (DT_STR, 255, 1252)[MyCol]. DT_STR is the type cast that yields an SSIS data type compatible with the SQL Server varchar data type. The 255 is the length of the string, and 1252 represents the US-EN code page.

Hope this helps,
Andy


Andy Leonard
CSO, Linchpin People
Follow me on Twitter: @AndyLeonard
Post #1418531
Posted Monday, February 11, 2013 2:33 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 11:21 AM
Points: 293, Visits: 499
Andy,

Both tables are in SQL Server 2005.. different servers. However I did find a way to fix the problem...

In SSIS I went to Tools > Database Tools > Table and Database Designers > Column Options

In the drop down "Default column type" change to "varchar".

That fixed SSIS's conversion of the data to nvarchar.

However if I ever have to go from a nvarchar to varchar.. I guess one of the ways you listed is the way to go.

Oh, I have made it down to Level 5... Very good article and easy to follow.
Post #1418635
Posted Thursday, July 18, 2013 5:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 24, 2014 12:05 PM
Points: 9, Visits: 241
Andy:

I am an Oracle DBA who has worked with SQL Server off and on since SQL Server 4.2. Ever since SSIS replaced DTS, I have found SSIS to be the hardest part of SQL Server to learn. This article helps take away some of the mystery. Thank you for takeing the time to write this post.

David Shink
Post #1475238
Posted Thursday, July 18, 2013 8:22 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 1:39 PM
Points: 388, Visits: 1,034
david.shink (7/18/2013)
Andy:

I am an Oracle DBA who has worked with SQL Server off and on since SQL Server 4.2. Ever since SSIS replaced DTS, I have found SSIS to be the hardest part of SQL Server to learn. This article helps take away some of the mystery. Thank you for takeing the time to write this post.

David Shink


Thanks, David. It's not you; it's SSIS.

:{>


Andy Leonard
CSO, Linchpin People
Follow me on Twitter: @AndyLeonard
Post #1475253
Posted Tuesday, September 10, 2013 1:49 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 14, 2013 10:13 AM
Points: 13, Visits: 65
Andy,
I've done quite a bit of SSIS, but being a contractor I as on a 5 months Access assignment. Next position is back to SSIS, your articles are the best for reviewing. Thank you
Post #1493349
« Prev Topic | Next Topic »

Add to briefcase «««34567»»

Permissions Expand / Collapse