January 8, 2008 at 10:24 am
I hate to ask such a stupid question but I am lost. I can't even find the tool to create an SSIS package. With DTS it was available from Ent. Man. But in Management Studio I connect to Integration Services and I have two folders "Running Packages" and "Stored Packages". How do I create a new package? I know I am in over my head and I am going to buy a book this week. But being able to actually find the tools to create a package would be really helpful. 😛
Thanks,
Chris
January 8, 2008 at 10:48 am
Look for Microsoft Visual Studio 2005 in your Programs.Once opened.
1) Select File > New Project
2) Ensure Business Intelligence Projects is selected in Project Type.
3) Choose Integration Services Project in Templates.
4) Give a name for your Project and define the location.
5) Click OK..and you are ready to start.
You have successfully created your first SSIS Project.
I suggest reading BOL to get a clear picture, before you get your hands dirty.
[font="TimesNewRoman"] “I haven't failed, I've found 10,000 ways that don't work”........Thomas Alva Edison[/font]
January 8, 2008 at 12:25 pm
I guess you have just started using SQL Server 2005. SSIS stands for SQL Server Integration Services. It is totally different than what we had in SQL 2000 previously even though many admit that it is similar to DTS. If you know this SSIS well enough, then you will understand what I mean. Well, to answer your question well, SSIS is a part of the three business intelligence (two more are SSAS and SSRS). So to be able to open this, you have to go to the Business Intelligence and open it from there. Simply follow what Lookup_BI has posted because it is very well explained there. Then you will see 4 tabs (Control Flow, Data Flow, Event Handler, Package Explorer). This is where you will work. Once you are done, click on the debug button (it looks like a "play" button) Now you will see the fifth tab appearing called Progress. Work until there and see how much you understand. Sorry to say this, but you are way too far still. But we are all up here to help you. So don't worry and good luck. Try it and post what you encounter. I will help you out.
To be continued...........
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
January 8, 2008 at 8:19 pm
Thanks for the replies. I just started reading BOL today. My boss has unrealistic expectations. I need to extract data from a Pervasive SQL database, transform it and use it to make updates on multiple SQL server tables and preferably send out email notifications based on some of the updates. Oh yeah, and it should be done yesterday. I am so far from being able to do this. This is a complex subject. But it does seem that it is possible to do everything I want to within SSIS.
January 9, 2008 at 7:02 am
cbrinson (1/8/2008)
Thanks for the replies. I just started reading BOL today. My boss has unrealistic expectations. I need to extract data from a Pervasive SQL database, transform it and use it to make updates on multiple SQL server tables and preferably send out email notifications based on some of the updates. Oh yeah, and it should be done yesterday. I am so far from being able to do this. This is a complex subject. But it does seem that it is possible to do everything I want to within SSIS.
Since this is your situation, here are some suggestions. First, since you have a SQL database to extract the data from, you can use an OLE DB source. Here are the steps:
1. Create a new project by going to File>New>Project then give appropriate name for it.
2. Under [Control Flow], drag and drop [DataFlow] into the design pane.
3. Then go to [Data Flow] tab. (or you can double click the [DataFlow] item in the [Control Flow] that you dragged just now). It is the same, it will take you there anyways.
4. Now, drag and drop [OLE DB Source] (because your source is a SQL Database). Double click that to enter what is neccessary.
5. Now, you will see arrows popping out from the OLE DB transformation. There will be two arrows; red and green. These are called the precedent constraints. It means after the system finish doing that particular transformation, on success, what it must do next? OR on failure, what should happen? Red arrow is for failure and green one is for success.
5. Before you can even assign those arrows, you must drag and drop some transformations to the design pane. So, go ahead and do that.( I don't really know what updates you want to do) So, you can tell me what kind of updates are those and I will help you out.
6. Once (5) is done, you can actually "pull' the arrow from OLE DB to the new transformation you have done.
7. Then of course, the final process is, the destination. Since you told that it is also SQL database, you will have to drag and drop OLE DB destination. Then double click it and assign which destination. Before that, you must "pull" the arrow as well so that the transformation knows what are you assigning.
8. Steps (5) and (6) are the most important. You will have to know what you want to do. I will elaborate more after you try the other steps first. Good luck in trying.........
To be continued...................:cool:
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
January 9, 2008 at 8:53 am
You have no idea how much I appreciate your help!
I wish I were extracting from a SQL Server database. I really don't know much about the legacy database that I am extracting from except that I have been able to create a DSN to it. The driver I am using is called "Pervasive ODBC Client Interface". The database I need to extract from is the finance/accounting database and they run a really old version of MACOLA software in what I believe I was told was a Btrieve database.
In Visual Studio I open Server Explorer and connect to my DSN. I can run queries against the table I want to extract from no problem. But the package stuff makes no sense to me. I created a Data Source in the Solution Explorer pane. But the toolbox doesn't seem to have any option for connecting to the DSN I created. It seems strange that the only option to connect to a database is via OLE DB. I have no idea how to use OLE DB to connect to this btrieve database or if it is even possible.
I am still reading but just figuring out how to connect to my target database within the package has me stuck.
Thanks,
Chris
January 9, 2008 at 9:17 am
Well, I think you are really stuck. Okay. Now that you have been seeing the interface for several days, it is easier to coach you.
First: Can you confirm where your source data is sitting? Is it in a table in a database that you can see in Management Studio or not? Or is it sitting somewhere in an FTP Server? Or is it just a Flat file that is on your share hub?(if you have any).
Second: where exactly you want to take these data to? ( I believe you said to a SQL table in a SQL Database right?) Now, if this is the case, is the Database and the table created already?
Third: If you know those two situation well enough, now, tell me what are you suppose to do with the datas? What is wrong and what needs to be changed? What kind of business requirement did you receive to be done on those data.
Explain all these and I will get you started!
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
January 9, 2008 at 9:32 am
1) Under Server Explorer in Visual Studio I can open the DSN that connects to my source Btrieve database (It is not a SQL Server 2005 database). I can see all the tables listed including my source table: ARCUSFIL
(I don't know how to connect to a non-SQL Server database within Management Studio. Pretty straightforward in Visual Studio though.)
2) I want to update my existing destination SQL Server 2005 database table within my development database. Table name is: account
3) Business requirement is to update our CRM (SQL Server DB) with a flag from the Finance system (Btrieve DB) indicating customers on credit hold. We don't want to give free support to clients who haven't paid their bill. Source query would be:
SELECT CUS_NO
,CUS_CR_HOLD_FG
FROM ARCUSFIL
Using that dataset I need to loop through it and update each corresponding account with the appropriate credit hold flag in my SQL Server 2005 destination database.
January 9, 2008 at 12:11 pm
cbrinson (1/9/2008)
1) Under Server Explorer in Visual Studio I can open the DSN that connects to my source Btrieve database (It is not a SQL Server 2005 database). I can see all the tables listed including my source table: ARCUSFIL(I don't know how to connect to a non-SQL Server database within Management Studio. Pretty straightforward in Visual Studio though.)
2) I want to update my existing destination SQL Server 2005 database table within my development database. Table name is: account
3) Business requirement is to update our CRM (SQL Server DB) with a flag from the Finance system (Btrieve DB) indicating customers on credit hold. We don't want to give free support to clients who haven't paid their bill. Source query would be:
SELECT CUS_NO
,CUS_CR_HOLD_FG
FROM ARCUSFIL
Using that dataset I need to loop through it and update each corresponding account with the appropriate credit hold flag in my SQL Server 2005 destination database.
Okay I can understand that you are NOT having a SQL source. I have to tell you that you have to connect using a connection manager or something else where posibble. Your situation is a little different. Otherwise, is there any other way that you get into the source data?(forget about server explorer, tell me how else can you go there?)
Okay after you determine this one, the rest is easy.
Here are the steps again:
1. (Once we find a way to connect to the source)
2. Now drag and drop SQL command transformation
3. Drag the green arrow (precedent constraint) from the {source} to the [SQL Command] transformation. Then double click [SQL Command] transformation to plug in the properties. Simply put your query that you have stated above on. This should be done under component properties tab>custom properties>SQL command. Just click on the elipsis there and copy paste your query.
4. Now drag the OLE DB destination and duoble click it. Connect to the database and then the table which would be [account].
Try to do this and understand the process. Hopefully you will get more idea of what it is.
SSIS is hard. Really really hard. But once you get it, it is really fun and interesting. I hope you will get it.
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
January 9, 2008 at 12:58 pm
It is a standard ODBC connection to a system DSN. Surely SSIS allows you to connect to an ODBC data source?! If not, I am sunk. Even Crystal Reports can connect to an ODBC data source and query data in those tables. That is as basic as it gets I would think. Maybe I should post a new thread about how to connect to an ODBC data source in SSIS?
January 9, 2008 at 1:05 pm
Yap, Maybe someone can help you with that if you post a new one.
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
January 10, 2008 at 1:14 am
When you've got your project open in BIDS/Visual studio (whatever you call it), in solution explorer right click data sources, select new data source. The wizard comes up with a start page which you can "Next". Then select create based on new or existing connection and click "New" (NOT next).
Now at the very top is a drop down, greyed which says (probably) Native OLEDB/SQL Native client. Drop this and you'll find under .NET providers an ODBC data provider. Select this and OK and lo and behold you are asked for a user or system DSN. Fill in from there and test your connection.
Once you have a data source you can use it anywhere in that project. In the connection managers box rigt click and "add new connection manager from data source".
If the boss wants it all doing he should send you on a course. I'm off to London next week for one!
Good Luck
January 10, 2008 at 4:16 am
You're also going to have to create your destination data source before you do anything. Once you've done that, add a Data Flow Task to the Control Flow. Data Flow is where you do your import / export and any transformations.
Data Flow is the part where the tools start looking a lot like the old DTS tools. You'll need to choose a Source object and a Destination object, hook them up to your connections and then put precedence constraints from the Source to the Destination. That's plain vanilla SSIS importing.
It gets tricky, though, when you need to do other stuff, like transformations, etc. Everything else in the toolbox (that isn't under the Source or Destination headers) goes in between the two. The transformation task is a lot like the DTS transformation task, but simplier IMHO. And then there's Merge and Multicast and a host of other fun stuff you can add. Even a data viewer so you can see your data while you're debugging (though this item isn't a task so much as it is something you add to your precedence constraints).
Once you've created your Source & Destination objects, let us know if you need help with all the middle stuff.
January 10, 2008 at 7:22 am
I guess what I am not understanding is how you associate the query with the connection. Right now in Solution Explorer I have one item listed under the Data Sources folder.
- MACOLA200.ds (My source legacy database)
In the Connection Managers tab at the bottom of the screen I have selected "New Connection from Data Source", MACOLA 200 is highlighted and I click OK. An icon is added of a cylinder with arrows extending out from it and labeled MACOLA200.
Now I get confused. I can add a connection in the Connection Manager to my destination database by right clicking and selecting New OLE DB Connection, selecting my SQL Server database and clicking ok. This time an icon is added of a cylinder with a pipe underneath it. Not sure why there is a difference.
But I don't understand the purpose of the connection manager since I can't drag any connection up to the data flow. Nor can I link a DataReader Source (or I don't know how to) to one of my connections.
DTS made sense in that you add a connection to a database, then you add a query and so on. I could successfully do this the first time I opened it without reading any docs. This is not intuitive to me I guess as I can't figure out how to add a source to the data flow and tell it to use the MACOLA 200 connection. Seems like it should be easy.
Thanks for the help with a newbie!! I am reading right now. But BOL is useless so far as I can't find where it actually tells me how to do something. Just a bunch of marketing about all that SSIS can do, how great microsoft is etc. but no core how to stuff.
January 10, 2008 at 7:29 am
What you need to do next is add a data flow task to the control Flow. Data flow should be in the Toolbox. Add that, double-click the icon, and it will take you to the DataFlow tab. Alternatively, instead of double-clicking the icon, just click on the tab next to Control Flow.
Once in the DataFlow window, look back in the toolbox. There are Sources up top, tasks in the middle and Destinations on the bottom. Grab these objects (an ODBC source and an OLE DB destination). When you edit these objects, you'll see a spot that says "connection". This is where you connect the objects to the connections you just created. The connections themselves do NOT drag-n-drop like DTS.
Try that and let us know if you were successful or not.
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply