SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The SSIS Data Pump - Step 2 of the Stairway to Integration Services


The SSIS Data Pump - Step 2 of the Stairway to Integration Services

Author
Message
ben.kimball
ben.kimball
Valued Member
Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)

Group: General Forum Members
Points: 74 Visits: 49
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! Smile ) 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.
Andy Leonard
Andy Leonard
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2315 Visits: 1101
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
Data Philosopher, Enterprise Data & Analytics
joresnik
joresnik
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 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?
ben.kimball
ben.kimball
Valued Member
Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)

Group: General Forum Members
Points: 74 Visits: 49
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! Smile

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;
}

dwilliscp
dwilliscp
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2143 Visits: 784
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,
Andy Leonard
Andy Leonard
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2315 Visits: 1101
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
Data Philosopher, Enterprise Data & Analytics
dwilliscp
dwilliscp
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2143 Visits: 784
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.
david.shink
david.shink
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 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
Andy Leonard
Andy Leonard
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2315 Visits: 1101
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
Data Philosopher, Enterprise Data & Analytics
mlstephens14
mlstephens14
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 70
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search