|
|
|
SSC Rookie
      
Group: General Forum Members
Last Login: Monday, February 04, 2013 10:11 AM
Points: 26,
Visits: 47
|
|
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.
|
|
|
|
|
Old Hand
      
Group: General Forum Members
Last Login: Monday, May 13, 2013 2:53 PM
Points: 375,
Visits: 947
|
|
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
|
|
|
|
|
Forum 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?
|
|
|
|
|
SSC Rookie
      
Group: General Forum Members
Last Login: Monday, February 04, 2013 10:11 AM
Points: 26,
Visits: 47
|
|
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; }
|
|
|
|
|
SSC-Enthusiastic
      
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 9:52 AM
Points: 187,
Visits: 332
|
|
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,
|
|
|
|
|
Old Hand
      
Group: General Forum Members
Last Login: Monday, May 13, 2013 2:53 PM
Points: 375,
Visits: 947
|
|
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
|
|
|
|
|
SSC-Enthusiastic
      
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 9:52 AM
Points: 187,
Visits: 332
|
|
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.
|
|
|
|