Need Help with importing EXCEL

[slesniak123]

Hello,

I am very new to sql server 2005, and i wanted to know if anyone can help me in creating a SSIS packge that can do the following:

1. Import an excel sheet
2. Delete uneeded columns
3. Merge two fields together, only if first column is not null, and if it is null delete that record.
4. Dedupe records
5. export into dbf format
6. If possible, records that get exported get marked as exported in database.

Any help would be very much appreciated.

 

[Catadmin]

I'm not going to go into exact details, because what you're wanting to do sounds very much like a basic SSIS package and it's better if you learn the basics yourself. However, I will give you some ideas.

First, you want to look into Temporary Tables (Books Online -BOL- is a great reference) and the Data Flow Tasks. BOL will tell you how to create connections to an Excel spreadsheet, then you just import the data into a temporary table inside of SSIS (if you don't actually want it in your SQL Server database). You shouldn't actually need to delete columns since you can select specifically only the columns you want.

Concatenating the two columns you want added together is fairly simple. You can do it in a variety of ways, but the simplest is a SELECT statement. Check Concatenate in BOL as well.

Then, just write a query to find your duplicate records with an Execute SQL Task or do something with the FOR or FOREACH containers.

Exporting is just as easy as importing. Once you have everything formated the way you want it, simply have it connect to the Output connection manager (dbf) and send the file to where you want it to go. Have a column on your database table marked with another Execute SQL Task upon success of the final file move and you're done.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[slesniak123]

Thank you very much for your response, it was very helpful. I have just created a SSIS package and everything is working except for one part, i am trying to insert some data into a table but i keep getting this error: "No disconnected record set is available for the specified SQL statement." I can't figure out why i keep getting this. Any help would be very much appreciated.

 

[Catadmin]

What sort of task are you using? If it's a SQL Task, could you post the code?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"