August 2, 2006 at 12:52 am
Hi all,
Somehwat emabarrassing. I'm a SQL 2000 Certified DBA but am just dipping my toes into 2005.
As a first thing to try I just want to create a DTS package (that's what I call it - I guess its now a SSIS somethingorother) which gets data from Excel and appends it to a table in my database.
Sounds simple enough. So I go through the Integration Services design environment and I go to create a new data source, thinking I'd just find it in a drop down list somewhere. In the Data Source Wizard, I went to create a new data source.
There's a provider drop down, and I have choices to connect to a whole range of databases, but nothing about Excel. I went searching through the help, and documentation talks about the Excel Connection Manager.
How the ^&*%$# do I install it or get the option to design a package with Excel as the data source?
August 2, 2006 at 4:19 am
Hi Danster,
One the CONTROL FLOW pane of your new SSIS Package (that's what they're called now), drag a DATA FLOW from the TOOLBOX TAB to your pane. Renamed it as appropriate (for example, one of mine is called Import BuyList Excel Spreadsheet).
Now, right click the DATA FLOW object (or you can double-click it, iirc). This will take you to the DATA FLOW pane, and on your TOOLOBOX you will now see 3 groupsing of controls: DATA FLOW SOURCES (yes - that's where you'll find your EXCEL source), DATA FLOW TRANSFORMATIONS (where you can do all kinds of fun), and finally DATA FLOW DESTINATIONS - which will likely be the OLE DB DESTINATION or SQL Server Destination, depending on what you are doing. From the research I've done, there been a fair bit of mention regarding issues using the SQL Server DEstination, so I'm actually using the OLE DB DESTINATION to load my data.
A quick tip from my still limited experience with SSIS.
Right-click you CONTROL FLOW pane, and have a look at the ProtectionLevel property. The default is EncryptSensitiveWithUserKey - which is great during development, but causes all kinds of pain once you are trying to deploy, unless you happen to develop with the user that will be executing the job in production. There are several alternatives - I've started using EncryptSenstivieWithPassword, which means that anyone trying to open, or execute, the package needs to supply the password - but you are no longer tied to specific users! The password can be specified in the SQL Server Agent job step, for example, or the command line execution via DTEXEC, etc. etc.
... well, I hope that helps
CiaO 4 NoW
August 2, 2006 at 5:49 pm
Thanks Wanderer.
I was able to create a simple package and just need to massage it somewhat to make it work. Super answer!
Doncha love how SSIS is soooo intuitive? NOT
cheers
Danster
August 3, 2006 at 4:42 am
*grin*...
although, perhaps it should really be *groan*
It has been a "challenging opportunity, providing excessive personal growth and an impressive learning curve" in "management-speak" (or a "bleedin' nightmare" in normal speak :0 )
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply