April 17, 2008 at 8:55 am
I'm struggling trying to insert a group of records into my test database with the DTS wizard because many of the columns in the database, which aren't in my XLS, do not allow nulls. Instead of inserting their default information, it's just failing.
I have only 3 columns in my XLS and > 20 in my SQL table. How do I get my data in? I don't have the expertise to make my own DTS package (I've been trying for the last 2 hrs with no progress). I've gone so far as adding all the other columns to my XLS and padding them with zeroes or spaces which seemed to get me some progress but I still couldn't get my package working, my manual one gives no errors but doesn't insert anything either. 🙁
April 18, 2008 at 10:09 am
Mindy,
Hi, let's see if I can talk you through this....
Start a new DTS package.
Drag an Excel Connection in to the design space.
Configure the connection to point at your Excel file.
Drag a SQL Server Connection in to the design space.
Configure this connection to point at your target database.
Click on your Excel connection.
Hold down the ctrl key and click on your sql connection and then let go of ctrl.
From the toolbox, select the Transform Data task.
Check that the source sheet is correct on the Source Tab.
Check that the table is correct on the Destination Tab. (if not, use the drop down to select)
Click on the Transformations tab.
The designer should draw in the 3 transformations from col1 to col1, 2 to 2, and 3 to 3. If this is what you want click ok. If not Click Delete All.
To redefine the transformations:
Click on the destination column.
Hold down the mouse button and drag the column across to the source column that you want.
For a straight copy, choose Copy Column from the dialog that appears
Click ok.
Do that for each column and voila. Job done. Any Q's give me a shout.
HTH, :hehe:
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply