February 8, 2007 at 7:06 am
I have a DTS package where i have a Data Pump task with data getting loaded into a sql table from a text file.This sql table has 8 column , 6 of which gets loaded from the text file. The other 2 columns needs to be set to the value of global variable thats in DTS. Can anyone please let me know how to set the value sof these 2 columns to the global variable value. Any help on this will be greatly appreciated.
TIA
February 8, 2007 at 9:17 am
February 8, 2007 at 10:40 am
This works but if i have multiple files and the transformation runs for each file then if i use global variable th last records is selected. I am trying to have an activex in the transformation by openinga records set but somewhere it seems be failing Has anyone sone anything similar?
TIA
February 8, 2007 at 10:46 am
I guess I don't quite follow what you are saying. Can you describe in more detail your multiple file scenario?
February 8, 2007 at 11:32 am
I have a DTSPump taskw here transformatiosn are set up from a text file to a sql table. Thsi runs for multiple files on a single folder. ALso in the transformations i need 2 columns which should be populated from another sql table and thats the reason why i am using record set . Thsi is what i am trying
DIM objADOConn, objRecordset, conn,strconnection
Set objADOConn = CreateObject ("ADODB.Connection")
conn = "Provider=SQLOLEDB;SERVER=UID=;PWD=;database="
Set objRecordset = CreateObject("ADODB.recordset")
Set objRecordset.ActiveConnection = strConnection (It fails when setting the connection only.
objRecordset.open "Select col1, col2 from filea where ID =" &DTSGlobalVariables("gv_IDCpu").Value
DTSDestination("idle") = DTSSource("Col005")
DTSDestination("processdate") =objRecordset.col1
DTSDestination("servername") = objRecordset.col2
Let me knwo if my activexscript is going wrong somewhere?
February 8, 2007 at 12:28 pm
If you need to dynamically popluate a destination column from another table, you need to use a lookup. Have you tried this yet?
February 8, 2007 at 12:39 pm
No i havn't tried this before. Any suggestions on this please. TIA
February 8, 2007 at 3:48 pm
I'm a bit confused by your ActiveX script. Did you say that you have a data pump task? From your initial post, I was under the impression that you had a TextFile Source Connection object with a data pump (transformation) between that and a OLE DB SQL Server connection object. Your ActiveX script appears to be opening up ADO connections to the databse.
Now that I understand you want to loop through all files in a folder, I see what you are trying to do. You want to use a single ActiveX task so that your file names can be dynamically read in as opposed to the static file name restriction on the TextFile Source connection object. Where is your code that handles looping through the directory?
I don't typically write ADO connection objects into a DTS ActiveX script. My preferene here, and this may be off the mark, would be to write one ActiveX script to read the folder contents and select, select the next file, and create a working file from it. I would then set up the TextFile Source connection object to work off of the static 'working' file name. You could then take advantage of the ability to set up lookups and use those lookups in the ActiveX data pump task. All you would need then is to add some logic to loop through these steps for each file in the folder.
I'm not 100% on how to code the lookup using ADO, but you could probably create a recordset to read your dynamic values from and use those in your script.
Hope this helps.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply