Problem with Dynamic Properties and Work Flow task
-
Hi,
I am using Dynamic task to set up the Data source for excel files at run time. In my package I have five excel files. So what I have done is first execute the Dynamic Properties task at the start so that it will change the data source for all the five excel files. But it is not doing that. It is changing the data source for the first excel file. It is not changing data source for the remaining files. My package execution sequence is shown below.
Dynamic Properties Task-->SqlConn1--->ExcelConn1-->Execute SqlTask1--->SqlConn2--->ExcelConn2--->ExecuteSqlTAsk--->ExecuteSqlTask-->SqlConn3--->ExcelConn3(2 data transformation tasks in parallel)...etc
As you can see I have several excel connections. And I want to set the data source for all excel connections at the start. And when I execute the package, it is not executing in sequence. like one of the data transformation task of (sqlConn3-->ExcelConn3) is executed first. SO my question is, IS there a way to overcome this so that I can define the sequence? I am using WorkFlow properties to do this but somehow it is not working correctly.
Please let me know the solution.
Thanks,
Sridhar!!
-
Have you set the on success/completion precedence for both steps in the sqlconn3-->Excelconn3? If you've only sset it to one of them then it would execute when the package started.
-
Your first step should be an ActiveX before the DPT. When you use Excel as a destination, the file must already exist when the transformation begins. So you will need to create these. You can use a template for each of your destinations. Use the FileSystemObject in the ActiveX to copy the template(s) and save them as destination files. Your file names will probably involve some sort of dynamic logic such as including the report date. At this point the destination files will be empty workbooks whose path and file names you now know, because you just created them. In the ActiveX assign each of these UNC path/file names to global string variables. On success of the ActiveX, go to the DPT. In the DPT dialog box, click on ADD for every one of your Excel workbooks and assign the DataSource to the appropriate global string. On success of the DPT, go to your first transformation and so on. Open each one of your "candy canes", i.e., workflows and inspect them for correctness of logic. Right click on each one of your transformations, select Workflow Property and inspect them for correctness of logic as well.
Have Fun! DTS Rocks!
[font="Courier New"]ZenDada[/font]
-
Hi,
I am creating an excel template manually and placed it in the directory. It is the same excel file for all the excel connections. So I created a global variable and assigned to it the excel filename before running the package. In the DPT I am assigning this global variable to all the excel connections. But this is not working. it is setting the filename to the first excel connection but not for all of them. And I have checked the workflow properties. they are all correct.
Can you please let me know how to solve this?
Thanks,
Sridhar!!
-
You should change your design to the one I described. While called a "DataSource" the Excel File that is referred to in the DPT is the destination. Each transformation must have a unique destination. Therefore you must have a separate Workbook and unique global for EACH AND EVERY transformation destination.
[font="Courier New"]ZenDada[/font]
-
Hi,
I have the same excel file for all the excel connections. The only thing that changes is the sheet name with each connection.
Thanks,
Sridhar!!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply