DTS file to table automation

[Jay]

Hi friends,
I have a immediate requirement for our company to load text files into different tables in sql 2000.
Requirement is this:: people submit txt files to our webserver via asp pages. Acording to the schedule event(immediate,end of day) DTS should pick up the file and the table they selectd and load the file into the coresponding table.
(main feature we are looking for is how we do a generic dts pakage, which will satisfy different tables loads acording to the corresponding text files.)
I need to have only one dts package doing the upload, regardless the filename and the tablename they selected. I think it should be done by active x and global variable. I am new to dts programming and I am kinda stuck here.
If anyone out there could help me on how to do it, I really really appreciated.
Thanks in ad...
Jay

[Hugh]

For starters, you probably have to standardize file names for submission of text files to ASP. You may not have to use global vars. or ActiveX. You can capture text file names right in a DTS package - Create a Text file connection source - Set the individual text file path & name - Create a 'Transform Data Task' with whatever SQL you need - Then set your SQL database destination.

Create a source connection & 'Transform Task' for however many text files you have - assuming its a reasonable number. You may have to define workflow properties - but this should be faster to set up than creating some Active X script.



------------
Jay at 4/8/2002 1:52:23 PM

Hi friends,

I have a immediate requirement for our company to load text files into different tables in sql 2000.
Requirement is this:: people submit txt files to our webserver via asp pages. Acording to the schedule event(immediate,end of day) DTS should pick up the file and the table they selectd and load the file into the coresponding table.
(main feature we are looking for is how we do a generic dts pakage, which will satisfy different tables loads acording to the corresponding text files.)
I need to have only one dts package doing the upload, regardless the filename and the tablename they selected. I think it should be done by active x and global variable. I am new to dts programming and I am kinda stuck here.
If anyone out there could help me on how to do it, I really really appreciated.
Thanks in ad...
Jay

[WizKid]

Unless the table structures are the same, you might need to create seperate transformations for each table.
Have these transformations run after SUCCESS of a ActiveX Script task. There are several ways to handle this situation.

YOU MUST KNOW : The crieteria to decide the table for the input file.

Easiest, would be to use different file name for each transformation and rename the name of input file to the filename used in corresponding table's transformation. This rename is done in the first ActiveX Script Task. This way even if all the transformations execute after this step, all, but one, of them will not have a file to process.

HTH..,
------------
Jay at 4/8/2002 1:52:23 PM


Hi friends,
I have a immediate requirement for our company to load text files into different tables in sql 2000.
Requirement is this:: people submit txt files to our webserver via asp pages. Acording to the schedule event(immediate,end of day) DTS should pick up the file and the table they selectd and load the file into the coresponding table.
(main feature we are looking for is how we do a generic dts pakage, which will satisfy different tables loads acording to the corresponding text files.)
I need to have only one dts package doing the upload, regardless the filename and the tablename they selected. I think it should be done by active x and global variable. I am new to dts programming and I am kinda stuck here.
If anyone out there could help me on how to do it, I really really appreciated.
Thanks in ad...
Jay

[Jay]

Hi Hugh,
Thanks for the reply. The problem I have is that I have tables already created in the database and I have to append the rows from flatfiles to the corresponding table. This should be done dynamically through an asp interface page, where users will select what file they wanto submit and the table which they want to upload the data to.
Is there a way that I could create a generic DTSpackage, where depending on the selection from asp page the tables will be loaded from the corresponding flatfile?
Thanks and I look forward to hear from you soon.
Thanks,
Jay


------------
Hugh at 4/8/2002 5:44:47 PM

For starters, you probably have to standardize file names for submission of text files to ASP. You may not have to use global vars. or ActiveX. You can capture text file names right in a DTS package - Create a Text file connection source - Set the individual text file path & name - Create a 'Transform Data Task' with whatever SQL you need - Then set your SQL database destination.

Create a source connection & 'Transform Task' for however many text files you have - assuming its a reasonable number. You may have to define workflow properties - but this should be faster to set up than creating some Active X script.



------------
Jay at 4/8/2002 1:52:23 PM

Hi friends,

I have a immediate requirement for our company to load text files into different tables in sql 2000.
Requirement is this:: people submit txt files to our webserver via asp pages. Acording to the schedule event(immediate,end of day) DTS should pick up the file and the table they selectd and load the file into the coresponding table.
(main feature we are looking for is how we do a generic dts pakage, which will satisfy different tables loads acording to the corresponding text files.)
I need to have only one dts package doing the upload, regardless the filename and the tablename they selected. I think it should be done by active x and global variable. I am new to dts programming and I am kinda stuck here.
If anyone out there could help me on how to do it, I really really appreciated.
Thanks in ad...
Jay

[Hugh]

I agree with WizKid - you might have to limit which table are appended - and you may have to create seperate transformations. What you are describing sounds pretty *****ious - you may want to work your way up to a fully dynamic, generic DTS.


------------
WizKid at 4/8/2002 6:22:27 PM

Unless the table structures are the same, you might need to create seperate transformations for each table.
Have these transformations run after SUCCESS of a ActiveX Script task. There are several ways to handle this situation.

YOU MUST KNOW : The crieteria to decide the table for the input file.

Easiest, would be to use different file name for each transformation and rename the name of input file to the filename used in corresponding table's transformation. This rename is done in the first ActiveX Script Task. This way even if all the transformations execute after this step, all, but one, of them will not have a file to process.

HTH..,
------------
Jay at 4/8/2002 1:52:23 PM


Hi friends,
I have a immediate requirement for our company to load text files into different tables in sql 2000.
Requirement is this:: people submit txt files to our webserver via asp pages. Acording to the schedule event(immediate,end of day) DTS should pick up the file and the table they selectd and load the file into the coresponding table.
(main feature we are looking for is how we do a generic dts pakage, which will satisfy different tables loads acording to the corresponding text files.)
I need to have only one dts package doing the upload, regardless the filename and the tablename they selected. I think it should be done by active x and global variable. I am new to dts programming and I am kinda stuck here.
If anyone out there could help me on how to do it, I really really appreciated.
Thanks in ad...
Jay

[Jay]

Hi guys,
Thanks for the reply. I have about 140 table in the database, among those the user could select any table they want to upload. In this case I beleive that I have to create 140 pkgs. But can you please tell me, if this functionality is not posible even by global variables and activex scripting?
If you have any workaround, can you please post it? Thanks,
Jay


------------
Hugh at 4/8/2002 8:12:02 PM

I agree with WizKid - you might have to limit which table are appended - and you may have to create seperate transformations. What you are describing sounds pretty *****ious - you may want to work your way up to a fully dynamic, generic DTS.


------------
WizKid at 4/8/2002 6:22:27 PM

Unless the table structures are the same, you might need to create seperate transformations for each table.
Have these transformations run after SUCCESS of a ActiveX Script task. There are several ways to handle this situation.

YOU MUST KNOW : The crieteria to decide the table for the input file.

Easiest, would be to use different file name for each transformation and rename the name of input file to the filename used in corresponding table's transformation. This rename is done in the first ActiveX Script Task. This way even if all the transformations execute after this step, all, but one, of them will not have a file to process.

HTH..,
------------
Jay at 4/8/2002 1:52:23 PM


Hi friends,
I have a immediate requirement for our company to load text files into different tables in sql 2000.
Requirement is this:: people submit txt files to our webserver via asp pages. Acording to the schedule event(immediate,end of day) DTS should pick up the file and the table they selectd and load the file into the coresponding table.
(main feature we are looking for is how we do a generic dts pakage, which will satisfy different tables loads acording to the corresponding text files.)
I need to have only one dts package doing the upload, regardless the filename and the tablename they selected. I think it should be done by active x and global variable. I am new to dts programming and I am kinda stuck here.
If anyone out there could help me on how to do it, I really really appreciated.
Thanks in ad...
Jay

[Tom]

You are putting a lot of faith in your user community. You are also talking major security risk. Is this a mission critical application? Please keep posted on how you accomplish.


------------
Jay at 4/8/2002 8:21:29 PM

Hi guys,
Thanks for the reply. I have about 140 table in the database, among those the user could select any table they want to upload. In this case I beleive that I have to create 140 pkgs. But can you please tell me, if this functionality is not posible even by global variables and activex scripting?
If you have any workaround, can you please post it? Thanks,
Jay


------------
Hugh at 4/8/2002 8:12:02 PM

I agree with WizKid - you might have to limit which table are appended - and you may have to create seperate transformations. What you are describing sounds pretty *****ious - you may want to work your way up to a fully dynamic, generic DTS.


------------
WizKid at 4/8/2002 6:22:27 PM

Unless the table structures are the same, you might need to create seperate transformations for each table.
Have these transformations run after SUCCESS of a ActiveX Script task. There are several ways to handle this situation.

YOU MUST KNOW : The crieteria to decide the table for the input file.

Easiest, would be to use different file name for each transformation and rename the name of input file to the filename used in corresponding table's transformation. This rename is done in the first ActiveX Script Task. This way even if all the transformations execute after this step, all, but one, of them will not have a file to process.

HTH..,
------------
Jay at 4/8/2002 1:52:23 PM


Hi friends,
I have a immediate requirement for our company to load text files into different tables in sql 2000.
Requirement is this:: people submit txt files to our webserver via asp pages. Acording to the schedule event(immediate,end of day) DTS should pick up the file and the table they selectd and load the file into the coresponding table.
(main feature we are looking for is how we do a generic dts pakage, which will satisfy different tables loads acording to the corresponding text files.)
I need to have only one dts package doing the upload, regardless the filename and the tablename they selected. I think it should be done by active x and global variable. I am new to dts programming and I am kinda stuck here.
If anyone out there could help me on how to do it, I really really appreciated.
Thanks in ad...
Jay

[Jim]

You can use a dtsrun statement to start you dts and pass the table name and text file as global variables:

DTSRUN /S "yourservername" /N "yourdtspackagename" /A "FILE_NAME":"8"="usersfilename " /A "TABLE_NAME":"8"="userstablena me" /E

Use the "Dynamic Properties Task" to assign these global variables within the package to the appropriate item.

Ex. Add a "Text File Source". In the Dynamic Properties Task set the "File Name" to the FILE_NAME global variable from above. Initially you will have to set the file name to an existing file. At runtime the global will overwrite it.

The hard part will be if your table structures are not all the same. Setting up the table tranformation could be pretty hard.

The might have to use the first dts to call the appropriate 2nd dts and pass it the filename.


------------
Tom at 4/9/2002 3:00:13 PM

You are putting a lot of faith in your user community. You are also talking major security risk. Is this a mission critical application? Please keep posted on how you accomplish.


------------
Jay at 4/8/2002 8:21:29 PM

Hi guys,
Thanks for the reply. I have about 140 table in the database, among those the user could select any table they want to upload. In this case I beleive that I have to create 140 pkgs. But can you please tell me, if this functionality is not posible even by global variables and activex scripting?
If you have any workaround, can you please post it? Thanks,
Jay


------------
Hugh at 4/8/2002 8:12:02 PM

I agree with WizKid - you might have to limit which table are appended - and you may have to create seperate transformations. What you are describing sounds pretty *****ious - you may want to work your way up to a fully dynamic, generic DTS.


------------
WizKid at 4/8/2002 6:22:27 PM

Unless the table structures are the same, you might need to create seperate transformations for each table.
Have these transformations run after SUCCESS of a ActiveX Script task. There are several ways to handle this situation.

YOU MUST KNOW : The crieteria to decide the table for the input file.

Easiest, would be to use different file name for each transformation and rename the name of input file to the filename used in corresponding table's transformation. This rename is done in the first ActiveX Script Task. This way even if all the transformations execute after this step, all, but one, of them will not have a file to process.

HTH..,
------------
Jay at 4/8/2002 1:52:23 PM


Hi friends,
I have a immediate requirement for our company to load text files into different tables in sql 2000.
Requirement is this:: people submit txt files to our webserver via asp pages. Acording to the schedule event(immediate,end of day) DTS should pick up the file and the table they selectd and load the file into the coresponding table.
(main feature we are looking for is how we do a generic dts pakage, which will satisfy different tables loads acording to the corresponding text files.)
I need to have only one dts package doing the upload, regardless the filename and the tablename they selected. I think it should be done by active x and global variable. I am new to dts programming and I am kinda stuck here.
If anyone out there could help me on how to do it, I really really appreciated.
Thanks in ad...
Jay

[Jay]

Hi Jim,
Thanks for the reply. You are right, we have about 154 tables out of it, we need 40 tables should be loaded by some of our analysts. These tables are different in structure unfortunatly. My understanding was, if we have different files loaded in to the same table, we can use the same DTS Package using global variable and Active X scripting. Here the scenario is different.
Even if we call the package from another package, the column mapping will be different. My thinking is creating 40 different packages in total for all the tables and using global variable for the files they submit. Here we dont have to worry about the security as we show them the upload page according to their privileges. Can you please advise me if you have any other work around.
Thanks,
Jay


------------
Jim at 4/9/2002 5:10:40 PM

You can use a dtsrun statement to start you dts and pass the table name and text file as global variables:

DTSRUN /S "yourservername" /N "yourdtspackagename" /A "FILE_NAME":"8"="usersfilename " /A "TABLE_NAME":"8"="userstablena me" /E

Use the "Dynamic Properties Task" to assign these global variables within the package to the appropriate item.

Ex. Add a "Text File Source". In the Dynamic Properties Task set the "File Name" to the FILE_NAME global variable from above. Initially you will have to set the file name to an existing file. At runtime the global will overwrite it.

The hard part will be if your table structures are not all the same. Setting up the table tranformation could be pretty hard.

The might have to use the first dts to call the appropriate 2nd dts and pass it the filename.


------------
Tom at 4/9/2002 3:00:13 PM

You are putting a lot of faith in your user community. You are also talking major security risk. Is this a mission critical application? Please keep posted on how you accomplish.


------------
Jay at 4/8/2002 8:21:29 PM

Hi guys,
Thanks for the reply. I have about 140 table in the database, among those the user could select any table they want to upload. In this case I beleive that I have to create 140 pkgs. But can you please tell me, if this functionality is not posible even by global variables and activex scripting?
If you have any workaround, can you please post it? Thanks,
Jay


------------
Hugh at 4/8/2002 8:12:02 PM

I agree with WizKid - you might have to limit which table are appended - and you may have to create seperate transformations. What you are describing sounds pretty *****ious - you may want to work your way up to a fully dynamic, generic DTS.


------------
WizKid at 4/8/2002 6:22:27 PM

Unless the table structures are the same, you might need to create seperate transformations for each table.
Have these transformations run after SUCCESS of a ActiveX Script task. There are several ways to handle this situation.

YOU MUST KNOW : The crieteria to decide the table for the input file.

Easiest, would be to use different file name for each transformation and rename the name of input file to the filename used in corresponding table's transformation. This rename is done in the first ActiveX Script Task. This way even if all the transformations execute after this step, all, but one, of them will not have a file to process.

HTH..,
------------
Jay at 4/8/2002 1:52:23 PM


Hi friends,
I have a immediate requirement for our company to load text files into different tables in sql 2000.
Requirement is this:: people submit txt files to our webserver via asp pages. Acording to the schedule event(immediate,end of day) DTS should pick up the file and the table they selectd and load the file into the coresponding table.
(main feature we are looking for is how we do a generic dts pakage, which will satisfy different tables loads acording to the corresponding text files.)
I need to have only one dts package doing the upload, regardless the filename and the tablename they selected. I think it should be done by active x and global variable. I am new to dts programming and I am kinda stuck here.
If anyone out there could help me on how to do it, I really really appreciated.
Thanks in ad...
Jay

[Jay]

Hi Tom,
Thanks for your reply. Why u say I am talking major security risk. I beleive that I didn't tell you about how people are accessing the packages. These packages will be accessed in a secure way. Do you know any workaround for this functionality? If you do, plese post me updated. Thanks
--Jay


------------
Tom at 4/9/2002 3:00:13 PM

You are putting a lot of faith in your user community. You are also talking major security risk. Is this a mission critical application? Please keep posted on how you accomplish.


------------
Jay at 4/8/2002 8:21:29 PM

Hi guys,
Thanks for the reply. I have about 140 table in the database, among those the user could select any table they want to upload. In this case I beleive that I have to create 140 pkgs. But can you please tell me, if this functionality is not posible even by global variables and activex scripting?
If you have any workaround, can you please post it? Thanks,
Jay


------------
Hugh at 4/8/2002 8:12:02 PM

I agree with WizKid - you might have to limit which table are appended - and you may have to create seperate transformations. What you are describing sounds pretty *****ious - you may want to work your way up to a fully dynamic, generic DTS.


------------
WizKid at 4/8/2002 6:22:27 PM

Unless the table structures are the same, you might need to create seperate transformations for each table.
Have these transformations run after SUCCESS of a ActiveX Script task. There are several ways to handle this situation.

YOU MUST KNOW : The crieteria to decide the table for the input file.

Easiest, would be to use different file name for each transformation and rename the name of input file to the filename used in corresponding table's transformation. This rename is done in the first ActiveX Script Task. This way even if all the transformations execute after this step, all, but one, of them will not have a file to process.

HTH..,
------------
Jay at 4/8/2002 1:52:23 PM


Hi friends,
I have a immediate requirement for our company to load text files into different tables in sql 2000.
Requirement is this:: people submit txt files to our webserver via asp pages. Acording to the schedule event(immediate,end of day) DTS should pick up the file and the table they selectd and load the file into the coresponding table.
(main feature we are looking for is how we do a generic dts pakage, which will satisfy different tables loads acording to the corresponding text files.)
I need to have only one dts package doing the upload, regardless the filename and the tablename they selected. I think it should be done by active x and global variable. I am new to dts programming and I am kinda stuck here.
If anyone out there could help me on how to do it, I really really appreciated.
Thanks in ad...
Jay

[WizKid]

Jay,
You don't have to create 140 packages. You need to create 140 Transformations in the same package.
An alternative is to use ActiveX to dynamicaly generate the transformations. Anything (almost) you do with in a DTS designer (like defining transformation, workflow etc..) can be controlled with in a ActiveX script. So, if you have identified the file and the corresponding talble you can create a transformation between them on-the-fly with in the ActiveX script of the DTS package. Refer to BOL under "DTS Import wizard -> scripting data transformation" to see if it applies to you.
If even possible, I would suggest you get the files in XML format. This makes the file self defined and can be moved into corresponding table easily. I believe XML parser(/SDK) is downloadable from Microsoft.

HTH.,.



------------
Jay at 4/8/2002 8:21:29 PM

Hi guys,
Thanks for the reply. I have about 140 table in the database, among those the user could select any table they want to upload. In this case I beleive that I have to create 140 pkgs. But can you please tell me, if this functionality is not posible even by global variables and activex scripting?
If you have any workaround, can you please post it? Thanks,
Jay


------------
Hugh at 4/8/2002 8:12:02 PM

I agree with WizKid - you might have to limit which table are appended - and you may have to create seperate transformations. What you are describing sounds pretty *****ious - you may want to work your way up to a fully dynamic, generic DTS.


------------
WizKid at 4/8/2002 6:22:27 PM

Unless the table structures are the same, you might need to create seperate transformations for each table.
Have these transformations run after SUCCESS of a ActiveX Script task. There are several ways to handle this situation.

YOU MUST KNOW : The crieteria to decide the table for the input file.

Easiest, would be to use different file name for each transformation and rename the name of input file to the filename used in corresponding table's transformation. This rename is done in the first ActiveX Script Task. This way even if all the transformations execute after this step, all, but one, of them will not have a file to process.

HTH..,
------------
Jay at 4/8/2002 1:52:23 PM


Hi friends,
I have a immediate requirement for our company to load text files into different tables in sql 2000.
Requirement is this:: people submit txt files to our webserver via asp pages. Acording to the schedule event(immediate,end of day) DTS should pick up the file and the table they selectd and load the file into the coresponding table.
(main feature we are looking for is how we do a generic dts pakage, which will satisfy different tables loads acording to the corresponding text files.)
I need to have only one dts package doing the upload, regardless the filename and the tablename they selected. I think it should be done by active x and global variable. I am new to dts programming and I am kinda stuck here.
If anyone out there could help me on how to do it, I really really appreciated.
Thanks in ad...
Jay

[Jay]

Hi WizKid,
Thanks for your reply. Yes you are right. I am working on it as you said and I will let you know how it went. Thanks for your advice.
With Rgds,
Jay


------------
WizKid at 4/10/2002 11:25:21 AM

Jay,
You don't have to create 140 packages. You need to create 140 Transformations in the same package.
An alternative is to use ActiveX to dynamicaly generate the transformations. Anything (almost) you do with in a DTS designer (like defining transformation, workflow etc..) can be controlled with in a ActiveX script. So, if you have identified the file and the corresponding talble you can create a transformation between them on-the-fly with in the ActiveX script of the DTS package. Refer to BOL under "DTS Import wizard -> scripting data transformation" to see if it applies to you.
If even possible, I would suggest you get the files in XML format. This makes the file self defined and can be moved into corresponding table easily. I believe XML parser(/SDK) is downloadable from Microsoft.

HTH.,.



------------
Jay at 4/8/2002 8:21:29 PM

Hi guys,
Thanks for the reply. I have about 140 table in the database, among those the user could select any table they want to upload. In this case I beleive that I have to create 140 pkgs. But can you please tell me, if this functionality is not posible even by global variables and activex scripting?
If you have any workaround, can you please post it? Thanks,
Jay


------------
Hugh at 4/8/2002 8:12:02 PM

I agree with WizKid - you might have to limit which table are appended - and you may have to create seperate transformations. What you are describing sounds pretty *****ious - you may want to work your way up to a fully dynamic, generic DTS.


------------
WizKid at 4/8/2002 6:22:27 PM

Unless the table structures are the same, you might need to create seperate transformations for each table.
Have these transformations run after SUCCESS of a ActiveX Script task. There are several ways to handle this situation.

YOU MUST KNOW : The crieteria to decide the table for the input file.

Easiest, would be to use different file name for each transformation and rename the name of input file to the filename used in corresponding table's transformation. This rename is done in the first ActiveX Script Task. This way even if all the transformations execute after this step, all, but one, of them will not have a file to process.

HTH..,
------------
Jay at 4/8/2002 1:52:23 PM


Hi friends,
I have a immediate requirement for our company to load text files into different tables in sql 2000.
Requirement is this:: people submit txt files to our webserver via asp pages. Acording to the schedule event(immediate,end of day) DTS should pick up the file and the table they selectd and load the file into the coresponding table.
(main feature we are looking for is how we do a generic dts pakage, which will satisfy different tables loads acording to the corresponding text files.)
I need to have only one dts package doing the upload, regardless the filename and the tablename they selected. I think it should be done by active x and global variable. I am new to dts programming and I am kinda stuck here.
If anyone out there could help me on how to do it, I really really appreciated.
Thanks in ad...
Jay