February 5, 2010 at 4:16 pm
I've written a few DTS packages in SQL2000. Now I've been tasked to write similiar processes (importing EXCEL spreadsheets and their various worksheets ) in SQL2005. I know nothing about SSIS in SQL2005. But I have to get this completed in a couple weeks.
Does anyone know of any WEB tutorials etc that show the fundamentals of creating, modifying, scheduling etc in SSIS/DTS?
Any direction is appreciated.
...thanks
February 5, 2010 at 4:47 pm
You might also just open up BIDS and check the help in it. Once you create a SSIS package it is very familar in most parts to creating a maintenance plan.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
February 7, 2010 at 11:21 am
Thanks Shawn.
I've gone thru some tutorials and created a Package via BIDS.
If I create a Package via SSMS and save it, what UI do I use to view it's properties, change it etc.? Do I use BIDS for that (and everything), too? If so, I haven't found it yet.
thanks
February 7, 2010 at 11:35 am
We have a bunch of articles (http://www.sqlservercentral.com/tags/Integration+Services+%28SSIS%29) on SSIS and we have a new series starting soon that goes through the basics. It will go out over the next few months, so I don't know if that helps.
We also have partnered with Pragmatic Works for some online training. http://www.sqlservercentral.com/Training/
February 8, 2010 at 9:37 am
I've found the best way is to just dig in. There are tons of great articles here at SSC - I don't know what I would have done without SSC in the past. This is a great resource for learning SQL Server fast using practical applications.
February 8, 2010 at 9:42 am
Correct, SSC is a great resource. As is the Web. I've been looking at both and am overwhelmed.
I guess I have to use SSIS or BIDS to build, modify & execute these Packages. I created a simply Package in SSMS and can not find out how to modify it?
My problem is I have to get this done in a week and place it into production. And have never used it!
🙁
February 8, 2010 at 10:34 am
I'm trying to work in BIDS.
How do I SKIP HEADERS of the EXCEL FILE?
I'm also getting a conversion error (DT_R8 & DT_14) or is that because of the EXCEL COLUMN HEADERS?
February 8, 2010 at 11:28 am
The SQL Books Online have some great tutorials on SSIS, SSRS, SQL Server, SSAS and some more things.
There are also some great sites if your Google for them.
Andrew SQLDBA
February 8, 2010 at 11:36 am
Trust me, I've been Googling and playing with BIDS. I've gotten a little further.
Now I'm getting CONVERSION ERRORS that I did NOT get in SQL 2000
"CANNOT BE INSERTED BECAUSE THE CONVERSION BETWEEN TYPES DT_R8 AND DT_I4...."
I FOUND THIS IN BOL
Data types. The Excel driver recognizes only a limited set of data types. For example, all numeric columns are interpreted as doubles (DT_R8), and all string columns (other than memo columns) are interpreted as 255-character Unicode strings (DT_WSTR). Integration Services maps the Excel data types as follows:
Numeric double-precision float (DT_R8)
Currency currency (DT_CY)
Boolean Boolean (DT_BOOL)
Date/time datetime (DT_DATE)
String Unicode string, length 255 (DT_WSTR)
Memo Unicode text stream (DT_NTEXT)
I'm beginning to think that SQL2005 does NOT play nicely with EXCEL and after Googling I see a lot of messages saying to convert the EXCEL file to a CSV file?
February 8, 2010 at 11:51 am
You might find that when you convert an Excel file to .csv (whenever possible) it might work a little better. I've come across this in the past. Also, consider a Data Conversion transformation between the source and destination. I know importing Excel files can be a little tricky.
February 8, 2010 at 12:34 pm
A couple of answer I think that will help you.
1. You tell the connection manager that the first row contains header information and it will name the columns for you.
2. I would recommend against converting the data to .csv for SSIS. While it worked much better than Excel in 2000 the formatting of csv files on SSIS is a bear as it does not support standard text delimiter well. The Excel sheet will not have to worry about this an if done correctly will even provide metadata to make it easier to imports the files.
Alan
February 8, 2010 at 1:30 pm
Thanks, Alan.
But what do I do about
Now I'm getting CONVERSION ERRORS that I did NOT get in SQL 2000
"CANNOT BE INSERTED BECAUSE THE CONVERSION BETWEEN TYPES DT_R8 AND DT_I4...."
Also, should I be creating a LINKED Server to this EXCEL file? That doesn't make sense if the "connection" is for EXCEL?
February 8, 2010 at 1:34 pm
rew-370421 (2/8/2010)
Thanks, Alan.But what do I do about
Now I'm getting CONVERSION ERRORS that I did NOT get in SQL 2000
"CANNOT BE INSERTED BECAUSE THE CONVERSION BETWEEN TYPES DT_R8 AND DT_I4...."
Also, should I be creating a LINKED Server to this EXCEL file? That doesn't make sense if the "connection" is for EXCEL?
This is the meta data mapping I was telling you about that you get from excel. There are 2 easy option at this point:
1. Modify the destination column to match the input
2. Use the data conversion tool in the data flow to convert the data if possible
Is this data already in SQL server 2000 or another database?
Alan
February 8, 2010 at 1:38 pm
Modifying the DESTINATION COLUMN is NOT an Option
The data comes from an EXCEL file that gets created. It was previously being IMPORTED into SQL2000 via DTS. I just need to do the same thing in SQL2005.
I assume the "data conversion tool" is in BIDS/SSIS?
Should I create a SQL2005 LINKED Server to the EXCEL file?
February 8, 2010 at 1:42 pm
I assume the "data conversion tool" is in BIDS/SSIS?
Should I create a SQL2005 LINKED Server to the EXCEL file?
1. Yes it is in the data flow task you have created to move the data to the SQL server in your toolbox
2. I wouldn't
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply