DTS newbie help...
-
Ok, I'm new to this DTS thing, and to make matters worse, I don't know ActiveX, VBscript, or JScript (but I can write some SQL -yay!)
Here's what I need to do: Take an Excel2k file and import/update a SQL 7 table. The columns should (almost) all be the same. Essentially, I need to see if a record exists, and if it does update the record with new data, otherwise append to the table. I also want to update a matching table for each entry.
I know I can't just use a Data Pump action, but I don't know how to modify the process.
I appreciate any help.
Update:
Ok, here's some updated info - I've created a data driven query task, and I've created the two step - an insert and an update with parameters. So, now I have some more specific questions:
In the ActiveX control, how do I create conditions to determine if the current row is an insert or update (DTSTransformstat_InsertQuery or DTSTransformstat_UpdateQuery)?
I only seem to have Excel 8 as an option - will this suffice for Excel 2k?
-
You really have two paths to achieve this.
1) use a Data driven query. This way you supply T-SQL statments for each of the actions (INSERT/UPDATE/Etc...) and the data transformation is an ActiveScript that determines (via lookups) which t-SQL statement to use. This works fine but bear in mind it changes the datapump to process a row at a time. This can really chew up resources and take a long time if you have 1000's of rows to import.
2) Probably the better option. Import your data into a second table in the database. Then run insert/update/etc... procedures to merge the data. This gives you the speed of a fast data import and set based updating of your existing data.
--------------------
Colt 45 - the original point and click interface
-
1:> I've created the ins/upd sets, but I need help creating appropriate lookups & conditions for launching them.
2:> Sounds reasonable. Can I create a #temp table for the upload, then move data across with Exec SQL tasks?
-
No, don't use a temp table...use a real life "Staging" table. Use the "DTT" in DTS to import the file into a staging table that exactly matches it (this makes setting up the DTT a snap).
Next, update all existing records by joining the staging table to the destination table on whatever key defines their distinctness.
Next, Insert all new records by doing the same join, but this time use "Left" and looking for the destination key being "null" in your Where clause (IE: insert all records where the distinct key does not exist in the destination table).
Absolutely do not use the DDQ; it's performance is horrible due to the fact that it deals with each records one at a time instead of processing it in sets.
Now, if you're wondering what this "distinct" key is...that's the clincher. It's whatever you need it to be.
A trivial example:if you had a table that you wanted to have distinct first name and last name in, your distinct key would be a combination of first name and last name. So an update of that sort would look like:
Update d
set Count = d.Count + 1
from Staging s
JOIN Destination d on s.FirstName = d.FirstName and s.LastName = d.LastName
Insert Destination (FirstName, LastName, Count)
select s.FirstName, s.LastName, 1
from Staging s
LEFT JOIN Destination d on s.FirstName = d.FirstName and s.LastName = d.LastName
where d.FirstName is null and s.FirstName is null
cl
Of course, FirstName and LastName are bad choices for distinctness unless thats the only thing the table does. Usually the distinct (also called unique or "primary") key is an integer.
Signature is NULL
-
I'm not clear with this step:
"No, don't use a temp table...use a real life "Staging" table. Use the "DTT" in DTS to import the file into a staging table that exactly matches it (this makes setting up the DTT a snap)."
Should I create a SQL Task to create a table? I don't really want to create an empty table to leave laying around between updates; I'd rather use a temp table just to hold the data while I move it. I could just truncate it when I'm done, I suppose.
I think I have my ins/update SQL code set, though. If I'm going to do it this way, I'd like to know how to setup that interim table for the upload.
Also, do you know if there's a way to tell a package to execute via query (like a storedproc (exec sp_example))?
Thanks
-
You can definitely use a SQL task to create a table as the 1st step of the import, then another SQL task to drop the table at the end. The reason I say don't use a temp table is due to complications with using a DTT and a temp table. But if you're set on that, knock youself out (and use "##").
If you need more details on using DTTs check out http://www.sqldts.com/default.aspx
If the column names in your staging table are exactly the same as the header names in your file you're going to be able to set this up in only a couple of minutes.
cl
Signature is NULL
-
Since you'll be importing data from MS Excel, be cautious about checking to see IF you need to TRIM the Excel data for spaces. Also, I had where one of my departments were trying to keep a set of business names and addresses (they all had a unique 5 digit code) in Excel. They were cautioned about NO FIELD LONGER THAN 30 CHARACTERS. I guess you can imagine how messy that got. I finally moved their data to MS Access where field lengths could be enforced. Problems gone!
Butch -
Ok, here's a new issue. I created a SQL task using the incoming Excel as the datasource, and created a temp table using 'select into'. The problem is, it created a new page in the Excel file instead of a global temp table on the server. Whe I try to do a similar thing with a DDQ, it tells me the object doesn't exist.
The whole problem is that I don't know how to bridge the two datasources and write my own SQL.
-
And, the other question is how do I execute this thing? I'd like to do it on command, but I'm not sure the best way. I've looked at the "xp_cmdshell 'dtsrun'" option, but I get a "Could not find stored procedure 'xp_cmdshell'." error. Any suggestions?
-
A DTT has two parts...a source and a destination. The source and destination of your DTT can be Text, Excel, or any ODBC data source (and probably some others I'm not thinking of). The source of your DTT is the Excel file, the destination is a table in a database (trust me, a permanent table).
It's pretty obvious that your DTT has the source and destination being excel. Set up a database connection, highlight your excel connection, then highlight your database connection, then click the "Data Transformation Tasks" icon. Open up the black line that you get, click the 2nd tabl and choose "Create table" as the destination. Click the 3rd tab and your Excel column to database column mapping should already to set up for you. Piece of cake.
For executing a DTS package, see Books Online with the keywords "DTS Designer, executing DTS package"
Signature is NULL
-
Well, I've looked in the Books, and I can't find anything really explicit or helpful. I've also read through the FAQ at sqldts.com, and tried their suggestions.
I, apparently, don't have the xp_cmdshell procedure. And I tried implementing the OLE method, but it just fails (without a really helpful error message).
Any suggestions?
-
Why are you trying to use xp_cmdshell to execute the package? You can schedule the DTS package as a job... and then just start the job (can do this with a stored proc also). Or just call DTSrun from the command prompt.
You probably can't run xp_cmdshell, because you aren't connected to the master db.
-
Well, I'm trying to integrate this as part of a larger process. I need to programically send a command to the SQL server to tell it to 'run' the package I created; so I can't just run it from a command line.
Can you tell me how to set it up to start the job by sending it a SQL statement? You suggested a stored proc - can you tell me how to write it?
-
This is assuming that you have your package saved as a "Local Package" on the server.
When looking at the list of Local Packages, right click on the package and hit Schedule Package. Then, go ahead and set an arbitrary date in the future (you'll delete the schedule shortly.)
When the package is scheduled, it will create a SQL Server Agent job. In EM, goto Management | SQL Server Agent | Jobs. Double- click on your new job. Click the schedule tab, and delete the schedule entry, and save the job.
Now you have a job configured without a schedule, so it will only execute when you tell it to.
You can right click on the job and hit 'start job', or use a stored proc, like I suggested.
Use sp_startjob. Here is the example from BOL:
USE msdb
EXEC sp_start_job @job_name = 'Nightly Backup'
Make sure you use the name of the job and not the DTS package name.
-
Even doing this doesn't work?
EXEC master.dbo.xp_cmdshell 'dir c:\'
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply