March 20, 2006 at 2:14 pm
I'm having a hard time uploading a 4 column, comma delimited text file into my table. The error is that it cannot convert the string data in the text file to int4 data in the table.
So I looked up some vb that would help convert the data, and I put in this activex script:
' Copy each source column to the destination column
Function Main()
DTSDestination("PIID") = convert.toint4(DTSSource("Col001"))
Main = DTSTransformStat_OK
End Function
This returned an error that I had to supply an object with the convert function.
I thought an upload from a text file would be simple? What am I missing here?
March 20, 2006 at 5:02 pm
Ok this is getting rediculous. I have a simple upload, and it's becoming too complicated. There has to be an easy way.
I've got a text file of rows of comma delimited data. One row looks like this:
tagID,t,meas,FACalc
3556,23967,456.54667,0
3556,23968,456.53435,0
etc etc
And I want to append it to a static table, which has the exact same rows as the text file, minus the primary key:
PIID int Primary
tagID int
t bigint
meas float
FACalc bit
So how is this not an easy upload? I'm having problems converting the string values from the text file to the types I have set up in my table, and using vBScript (visual basic) I had thought it would be easily remedied by convert.toint16, etc. That's not working. On top of that, the Primary key doesn't seem to be autofilling, which I had assumed would be the case (got an error when I was using the temp table way (located below) that read "PIID cannot be null"). How do you get it to autofill the primary key? You'd figure it would automatically autofill if the user does not give it a key value.
I was going to make a temporary table to put the data in all varchar(255) fields, and then make a procedure to then convert the data when copying to the static table. This is causing all kinds of complexity, like how do I select just one row when I don't care which one it pulls from? I need to then delete the row I just copied over, so it doesn't go over a second time. etc
Can't I just convert on the way in from the text file instead of making two or three other complicated procedures and temp tables, etc? This is really getting out of hand, uploading some simple data like this.
March 20, 2006 at 5:14 pm
Looks like VBScript doesn't have a lot of datatypes to choose from, therefore you can't convert data types on the fly when uploading. Not sure why they didn't just use TSQL then instead of VBScript.
Looks like I'm going to have to upload to a temp table with varchars, convert and copy to my static table, then DROP the temp table.
March 21, 2006 at 7:16 am
This should be simple. But something is choking. Are there any values in the first column that can't be converted to a 4 byte integer?
Does your input file have the column headers as the first row? If so, you need to tell DTS to skip that row.
jg
March 21, 2006 at 8:10 am
March 21, 2006 at 9:09 am
I told dts that my first row are column headers. In my sample data, the first column is the same 4 digits repeated throughout the column. Basically, this is a set of measurements from one sensor on a piece of machinery over a particular day. The first column (tagID) is the sensor ID, second is the timestamp (t), third is the measurement (meas), and fourth is whether or not this is a calculated measurement (FACalc).
No for that data I'm using float.
Here's the set up on my static table where I'm storing the measurements:
Primary key PIID is int - Unique measurement ID
tagID is int - Which sensor the measurement is for
t (microsoft's integer timestamp) is bigint - Time of the measurement
meas (measure) is float - The measurement
FACalc (boolean yes or no) is bit - Calculated or actual
March 21, 2006 at 9:26 am
If you don't specify an IDENTITY column in an insert statement, or if you don't specify the column in a DTS transform, the identity column will "auto fill". By "Identity column", I do mean a column that was created with the identity property.
Setting the Primary Key on an Int column does not make it an identity column. In your case, if you do not have the identity property set, you will have to supply a unique value for that column.
Also, from the ActiveX transform you showed earlier, it seems that you are mapping the Col001 from the text file to the PIID column. Is that not the column that you want to auto-fill? Your destination table has 5 columns and your source data has only 4, so you'll need to specify the correct mapping in the transform properties of the data pump task. A simple "copy column" type of transform should work just fine, as long as you have the identity property set on your PIID column.
March 21, 2006 at 1:23 pm
Yes I originally did not set the pump to touch the identity column (and it is set as identity in the receiving table). The problem was that the incoming data is a string, and the destination field is some other data type. ActiveX transformation seems not to have any way of converting the data type "on the fly". So here's what I'm working on right now...
DTS loads all 4 columns of data from the text file into a 'created' table (destination tab, click 'create'), with 4 fields set up as varchar(255). This gets the data into the database, so I can exec a sproc on it.
Here's my sproc so far:
CREATE PROCEDURE dbo.PIConvertAndVerify
@PIID int,
@t bigint,
@tempTagID int,
@tempT bigint,
@tempMeas float,
@tempFACalc bit
AS
/*JA: set up the check*/
SELECT @t=MIN(t) FROM tempCaV
WHILE (@t <> NULL) BEGIN
/*JA: get tempTable values to variables*/
SELECT @tempTagID=CAST(tagID AS int), @tempT=CAST(t AS bigint), @tempMeas=CAST(meas AS float), @tempFACalc=CAST(FACalc AS bit) FROM tempCaV WHERE t = @t
/*JA: put row into static table*/
INSERT INTO PIData
VALUES (@tempTagID, @tempT, @tempMeas, @tempFACalc)
/*JA: set up the next check*/
SELECT @t=MIN(t) FROM tempCaV
END
Although I have yet to add in a 'delete row' step, or data validation, etc.
I was having a problem with the sproc syntax check, because the temp table didn't exist yet in my dts package, but now everything seems to check out.
Next I'll have to figure out how to fire the sproc after I upload to this temp table. And I guess I would have to end with a DROP for this temp table? (because it's not a temp table if I don't 'DROP' it afterwards)
March 21, 2006 at 2:00 pm
You can put a call to the sproc in an "execute SQL" task and make that task depend on the success of the data pump task.
at the end of the sproc, you can add the "drop table XXXXX".
What I would do is create the table, and leave it there. Create an execute SQL task that truncates the staging table, then run the Data Pump task, and then the execute SQL task that calls the sproc.
By leaving the table, you can inspect it to make sure all the rows made it in the final destination.
Also, if you can validate the data using rowset functions, you will get better performance. An abbreviated example with fake column names:
Insert FinalTable ( Col001,Col002,Col003,Col004)
Select Col001,Col002,Col003,Col004
from StagingTable
Where fnValidate1(Col001) = 1 and fnValidate2(Col002) = 1 and fnValidate3(Col002) and fnValidate4(Col004) = 1
Then you can avoid doing it row by row.
March 21, 2006 at 5:54 pm
Well the validation is going to be really tough, because I think I have to ask the user to supply corrections if errors are found.
March 22, 2006 at 2:20 pm
You should probably gather the business requirements before you go too much farther on this package. Once you know what those requirements are, you'll have a better idea of how to proceed.
jg
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply