October 18, 2012 at 6:48 am
ben.kimball (10/16/2012)
What version of AdventureWorks are you using? This example fails with AdventureWorks for SQL Server R2.Person.Contact doesn't exist anymore, it's been broken into separate tables for EmailAddress, etc.
Hi Ben,
A backup of the AdventureWorks database I use may be found here.
Hope this helps,
Andy
Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics
October 18, 2012 at 9:55 am
Thank you so much! 🙂
October 18, 2012 at 7:37 pm
ben.kimball (10/16/2012)
Oops, just noticed a previous reply about the same thing.However, in the link in your reply, it just takes me to Step One of the SSIS Stairway. I still can't make the connection between what you say in Step Two and its reference to Step One.
What exactly was supposed to have been done in Step One to be ready for Step Two. Nothing looks like an "Action Item" in Step One.
Hi Ben,
When we started this series, I started writing the first three articles together. I wrote a few different versions of Step 1 which were renamed and which had different content. I updated the content of Steps 2 and 3 to adjust, but I obviously missed some of the changes n content and the name change for Step 1 in at least one place in Step 2.
I apologize for any confusion this has caused.
Andy
Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics
October 18, 2012 at 7:50 pm
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.
Also, unless I'm misunderstanding something, Persons.Contact seems to be a pre-made table, and I don't see you referencing it anywhere until it's being used. When I follow the steps of the tutorial, that table is not associated with my project.
A backup of the AdventureWorks database I use may be found here.
Hope this helps,
Andy
Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics
October 18, 2012 at 8:14 pm
ben.kimball (10/16/2012)
OK, I am fully confused. I created a "First_SSIS_Project" project, and now have an empty solution that looks like:First_SSIS_Project
- Connections
- Queries
- Miscellaneous
Now what do I do?
Hi Ben,
I believe you created a new project in SQL Server Management Studio and not in SQL Server Business Intelligence Development Studio. My first article in the series did not do a good job of explaining how to create a new SSIS solution. I have edited the first article and hope the updates will be published soon.
Sorry, remember, I am completely inexperienced with SSIS, so even absolute beginner steps are needed with such things.
Ben, you are my target audience. You have no reason to apologize. I apologize for not writing an article clear-enough for you to know exactly how to create the desired project type.
Andy
Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics
October 19, 2012 at 10:27 am
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.
October 19, 2012 at 8:24 pm
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, Chief Data Engineer, Enterprise Data & Analytics
October 22, 2012 at 12:15 pm
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?
October 22, 2012 at 2:22 pm
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;
}
February 8, 2013 at 12:15 pm
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.
Error1Validation error. Data Flow Task: Data Flow Task: Column "MRP_Area_CD" cannot convert between unicode and non-unicode string data types. Package.dtsx00
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,
February 11, 2013 at 9:49 am
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, Chief Data Engineer, Enterprise Data & Analytics
February 11, 2013 at 2:33 pm
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.
July 18, 2013 at 5:34 pm
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
July 18, 2013 at 8:22 pm
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, Chief Data Engineer, Enterprise Data & Analytics
September 10, 2013 at 1:49 pm
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
Viewing 15 posts - 46 through 60 (of 66 total)
You must be logged in to reply to this topic. Login to reply