none
Prompt for a date and pass the date to the packages in my ssis project.

    Question

  • Please pardon my newbieness. This will be my first SSIS package and I am learning as I go. So far this is what I have. 

    I created a Control Flow. Then I created two ADO.Net connections, one for source and one for destination. Then I created 11 data flow tasks, each one copies data from a table in one database into a corresponding table in another database on the same server. Each data flow task has an ADO NET Source and ADO NET Destination. In each source I have a query that tells the source which columns I want from what table. The destination simply maps the fields together. 

    Okay, so far so good. Here's the rub. Some of the source queries have date criteria. An example would be:

    SELECT LastName, FirstName, Address, EmploymentDate FROM TableName WHERE EmploymentDate >= '6/1/2013' AND EmploymentDate <= '7/1/2013'

    I want to replace these hard-coded dates with variables. Something like:

    WHERE EmploymentDate >= StartDate AND EmploymentDate <= EndDate

    I've read several posts and tried to do what is being described, but it's not sinking in. So...please use my example to tell me how to do this. It would be nice if I could have the package prompt me for the EndDate when I run it, but I'd be very happy just to learn how to pass a variable into the query.

    BTW, the reason I am not using OLE DB Source and OLE DB Destination is because I was getting the error:

    SSIS cannot convert between unicode and non-unicode string data type

    I tried solving the problem with a data conversion transformer with no luck. Switching to ADO.Net solved that problem.

    tod

    Friday, July 12, 2013 9:36 PM

All replies

  • Please refer the below sample,

    I have added a Script Task and a Data Flow Task for simplicity. As we are going to prompt START Date and END Date we need to place some code.

    We need 2 variables to do the query i.e STARTDATE and ENDDATE. The scope of this 2 variable is at package level i.e you can access these variables within the package. Select the datatype as DateTime.

    Then Double click Script Task and paste the following VB.NET codes inside Main module.

    Public Sub Main()
            Dim startdate As DateTime
            Dim enddate As DateTime
            startdate = CDate(InputBox("Please supply startdate. i.e mm/dd/yyyy", "Start Date", CStr(Today())))
            enddate = CDate(InputBox("Please supply enddate. i.e mm/dd/yyyy", "End Date", CStr(Today())))
            If IsDate(startdate) = True And IsDate(enddate) = True Then
                Dts.Variables("STARTDATE").Value = startdate
                Dts.Variables("ENDDATE").Value = enddate
            End If
            Dts.TaskResult = Dts.Results.Success
    End Sub

    Also please make sure to set the ReadWriteVariable properties as variable lists. Refer the below screen shots.

    For step 4, the query is,

    SELECT        NAME, STARTDATE, ENDDATE
    FROM            SSISTEST
    WHERE        (STARTDATE > ? ) AND (ENDDATE > ?)

    Finally set or assign variables to the input parameters.


    Regards, RSingh




    Saturday, July 13, 2013 6:07 AM
  • The screen shots in your example do not match what I have. For example in my Script Task Editor, for ScriptLanguage I only have the choices of:

    Microsoft Visual Basic 2010

    Microsoft Visual C# 2010

    I also had to change DTS.Results.Success to SriptResults.Success to get the compiler to like it. 

    There are some other differences, like button names and such. Also, for the query in step 4, should I put these in each of my Data Flow Tasks? For example, if the query in one of my Data Flows is:

    SELECT LastName, FirstName, CreatedDate FROM TableName WHERE CreatedDate >= '6/1/2013' AND CreatedDate <= '7/1/2013'

    And I want to replace the dates with StartDate and EndDate, what do I do.

    I guess I know less than you think I do. :0)

    Please try again.

    tod

    Monday, July 15, 2013 2:29 PM
  • Hi,

    sorry i got busy couple of days ago and could not help you. remember I posted a post for you with example if you get chance to see how you can pass the parameters to your query

    http://sqlage.blogspot.com/2013/07/ssis-how-to-pass-parameter-value-to-ole.html

    Check this and here I am using only one parameter but you can convert to two.

    Your query will be something like this

    Select col1,col2 from dbo.table where StateDate>? and EndDate<? and you will map the variables.

    The conversion issue you were getting, you need to convert your data to proper data type before insert into your destination. You can use Data Conversion or Derived Column to achieve that.

    Thank you


    http://sqlage.blogspot.com/

    Monday, July 15, 2013 2:52 PM
  • I changed everything to ADO.NET based on your video on how to avoid the unicode data type error. I had tried to use the data conversion transformer to match up the data types but that did not work. So now I am using ADO.NET and do not get the error, but I can not use your example below because it is for OLE DB. 

    I'm sorry I am so new. I wont be new forever. :0)

    Can you help me more? I either need to go back to using OLE DB and have you help me avoid the error OR keep using ADO.NET and have you help me with passing the parameter. 

    tod

    Monday, July 15, 2013 3:34 PM
  • Yes, you can  change to OLE DB Source , Lets us know which transformation you are using to change data type.

    Also provide the data type of input columns and what is data type of your destination table... 

    thank you


    http://sqlage.blogspot.com/

    Monday, July 15, 2013 4:12 PM
  • Okay. I am going to start over and use OLE DB this time. SO...

    I created a new SSIS package. Then I create an OLE DB Connection to my source database and another one for the destination, and tested them both successfully. Then I created a Control Flow and pulled in an OLE DB Source and OLE DB Destination. I connected my source to the OLE DB connection for the source database and entered this query:

    SELECT  Id, IsDeleted, ContactId, CreatedById, CreatedDate, Field, OldValue, NewValue
    FROM     ContactHistory
    WHERE  (CreatedDate >= '6/1/2013') AND (CreatedDate <= '7/1/2013') AND (Field IN ('Status_Rating__c', 'Status_Type__c'))

    For the OLE DB Destination I selected the connection for the destination and made sure that my columns map. I pointed the source data flow to the destination data flow. 

    NOW...before I run debug I have the X inside the red circle on the OLE DB destination that tells me:

    Column "Id" cannot convert between unicode and non-unicode string data types

    I use Advanced Editor to see that the data types are indeed different for Id between the source and target. The same is true for all of the fields. So I pull in a Data Conversion and put it between the OLE DB source and destination and I put in the correct data type for each field. Then I click the Start Debugging button. I get package validation error that lists an error for each column, stating the same as above...

    Column "Column Name" cannot convert between unicode and non-unicode string data types.

    Soooo...that's where I am now. I can't get to the subject of passing variables until we solve this. So what am I missing? There was mention earlier of using a Derived Column. Do I need that to?

    Thanx for hanging in there with me. I promise if I can just get through my first stinking package I'll be better at doing this.

    tod


    Monday, July 15, 2013 9:47 PM
  • WOW!! I finally figured out how to do this correctly! I needed to spend more time with the Data Conversion transformation. It sucks that I have to throw one of these into each of my 11 data flows, but...whatever. I mean, shouldn't SSIS be able to see the data types in my destination connection and do the conversion or at least prompt for conversions or something without me explicitly stating them.

    Anyway..so now I have to learn the next step - having the package prompt me for an End Date, have it calculate my start date based on that entry, and use the input in the criteria of my data flow queries. 

    I'm getting there. Slower and more painfully than I'd hoped, but getting there just the same.

    thanx.

    tod

     
    Tuesday, July 16, 2013 8:02 PM
  • Good job. And no we like explicit conversions. It forces coders to think about data types as much as they do code logic.

    Hope this is Helpfull. J Alverson Microsoft BI Solutions Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, July 16, 2013 10:33 PM
  • Thanx. 

    I have a new problem. I build the whole thing and then ran debug. Yesterday, out of my 11 data flows, only two of them run successfully. Out of frustration I just saved everything and shut it down for the day. Today I opened it up and ran it, without making any changes, and it all but one ran. Some of them completed with errors. Is there a reason why I have varying levels of success each time I run the package. I own both databases, so I know it's not locks or traffic. Here are some of the errors that came from the last run:

    (It's wordy, but hopefully some SSIS genius can read something from this that can help me)

    [EventRelation [1047]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
    An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "The statement has been terminated.".
    An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Violation of PRIMARY KEY constraint 'PK_EventRelationID'. Cannot insert duplicate key in object 'dbo.EventRelation'. The duplicate key value is (0REa0000003HaIBGA0).".

    [EventRelation [1047]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "EventRelation.Inputs[OLE DB Destination Input]" failed because error code 0xC020907B occurred, and the error row disposition on "EventRelation.Inputs[OLE DB Destination Input]" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.

    [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "EventRelation" (1047) failed with error code 0xC0209029 while processing input "OLE DB Destination Input" (1060). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.

    [STC Task [1562]] Error: Setting the end of rowset for the buffer failed with error code 0xC0047020.

    [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on STC Task returned error code 0xC0209017.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.

    [STC ContactHistory [1298]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

    [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on STC ContactHistory returned error code 0xC02020C4.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.

    [STC CampaignMember [1217]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

    [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on STC CampaignMember returned error code 0xC02020C4.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.

    [STC contact [1262]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

    [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on STC contact returned error code 0xC02020C4.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.

    Wednesday, July 17, 2013 3:31 PM
  • Hi , 

    You are trying to insert duplicate rows in your table. See the error..

    x80004005  Description: "Violation of PRIMARY KEY constraint 'PK_EventRelationID'. Cannot insert duplicate key in object 'dbo.EventRelation'. The duplicate key value is (0REa0000003HaIBGA0).".

    Do you truncate your table before load it again or you only load new records? 

    If you truncate then you might have missed this table. Truncate your table and reload.

    If you are only loading new records then you can use Lookup to make sure you ware only loading what is not present in your destination.

    Thank you


    http://sqlage.blogspot.com/

    Wednesday, July 17, 2013 3:46 PM
  • You are correct. I forgot to truncate my tables before running the package. Now here is what is interesting. I mentioned in my previous post that only two out of the 11 data flows were successful, then today only one did not run successfully. Today's run was before I truncated. NOW that I have truncated and run the package again, I'm back to only two data flows running successfully, and one of those was only partially successful. 

    Here's my extra long list of errors for this run. (The successful run was for Xtma_Email_Result__c

    [STC contact [1262]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
    An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Protocol error in TDS stream".
    An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Protocol error in TDS stream".
    An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Protocol error in TDS stream".

    [Data Conversion 6 [610]] Error: Data conversion failed while converting column "AccountId" (1484) to column "Copy of AccountId" (647).  The conversion returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

    [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on STC contact returned error code 0xC0202009.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.

    [Data Conversion 6 [610]] Error: The "Data Conversion 6.Outputs[Data Conversion Output].Columns[Copy of AccountId]" failed because truncation occurred, and the truncation row disposition on "Data Conversion 6.Outputs[Data Conversion Output].Columns[Copy of AccountId]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

    [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Data Conversion 6" (610) failed with error code 0xC020902A while processing input "Data Conversion Input" (611). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.

    [STC User [1766]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
    An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Protocol error in TDS stream".
    An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Protocol error in TDS stream".
    An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Protocol error in TDS stream".

    [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on STC User returned error code 0xC0202009.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.

    [STC LoginHistory [1529]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
    An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Protocol error in TDS stream".
    An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Protocol error in TDS stream".
    An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Protocol error in TDS stream".

    [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on STC LoginHistory returned error code 0xC0202009.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.

    [STC Event [1337]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
    An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Protocol error in TDS stream".
    An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Protocol error in TDS stream".
    An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Protocol error in TDS stream".

    [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on STC Event returned error code 0xC0202009.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.

    [STC Campaign [1166]] Error: Setting the end of rowset for the buffer failed with error code 0xC0047020.

    [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on STC Campaign returned error code 0xC0209017.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.

    [STC CampaignMember [1217]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

    [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on STC CampaignMember returned error code 0xC02020C4.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.

    [STC Task [1562]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

    [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on STC Task returned error code 0xC02020C4.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.

    [STC TaskRelation [1718]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

    [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on STC TaskRelation returned error code 0xC02020C4.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.

    [STC ContactHistory [1298]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

    [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on STC ContactHistory returned error code 0xC02020C4.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.

    Wednesday, July 17, 2013 3:56 PM
  • Also, I'm going to start over (AGAIN - for about the fourth time!) And this time I'll create only one of the data flows, test it, fix it, then create the next one, test it, fix, etc. Instead building them all and then trying to fix thiem all. So if you can read my error messages about data flow named 'STC CampaignMember', that's the one I'm starting with. Here it is again:

    [STC CampaignMember [1217]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

    Sorry to be so green. :0|

    tod

    Wednesday, July 17, 2013 4:06 PM
  • Here is the issue

    Data Conversion 6 [610]] Error: Data conversion failed while converting column "AccountId" (1484) to column "Copy of AccountId" (647).  The conversion returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

    You are trying to convert AccountId to some data type that can not hold the value.  Means AccountID is bigger in which you are trying to convert this column. 

    Lets say you have 23456 number and if you try to convert this to Tinyint that can hold max 255 value then you will get these kind of error.  

    Check what is your conversion data type. 

    Thank you


    http://sqlage.blogspot.com/

    Wednesday, July 17, 2013 4:08 PM
  • STC CampaignMember ran just fine when I removed all the other data flows. Hmmm..interesting. Could it be that having 11 data flows running at once is overwhelming the network or the database something like that? Is there a way to throw in spaces between the tasks or make them run one at a time in my package. 

    I'm going to keep building and see what happens. (I hope somebody in the future reads this extra long exercise in futility and learns from it!! 

    tod

    Wednesday, July 17, 2013 4:10 PM
  • Here's something else I'm going to do based on someone else's suggestion. I have all 11 dataflows within one control flow. I'm going to put each data flow into its own control flow. Stop me if you think this is a bad idea. Or for that matter, let me know if this is what I should've done already.

    tod

    Wednesday, July 17, 2013 4:17 PM
  • 11 Data Flows are just fine, if you want to run one at a time, Connect them one after one. So if first will complete then next will start. 

    Even you have all of them in parallel they should be fine. SSIS know how many it can run in parallel depending upon the number or processors. 

    How many records each data flow loads? 

    Thank you


    http://sqlage.blogspot.com/

    Wednesday, July 17, 2013 6:04 PM
  • Oh. Well I already started putting one data flow per control flow. the way I had it before it was one control flow and then withing that, on the data flow page it would be like:

    ole db source a ->  data conversion a -> ole db destination a

    ole db source b ->  data conversion b -> ole db destination b

    ole db source c ->  data conversion c -> ole db destination c

    etc....

    I noticed when I ran debug that they did not run in any particular order. 

    One advantage I see in putting one data flow per control flow is that I can run just one table update without running the entire package. So this may be better for me anyway. I'm sure that when I'm done there will be EVEN MORE shhhhhtuff I have to deal with!! So I'll come back to this thread to get some help on the next steps. 

    I'm getting there!

    tod

    Wednesday, July 17, 2013 6:12 PM
  • Well here is a question that is going to come up eventually anyway, so I might as well get some advice now. Out of my 11 table updates, with 4 of them all I am doing is copying the entire contents of the source table and copying to the destination table. Easy. But with the remaining 7 there are criteria that limit the results being brought over to the new table.

    What is the best way to handle that? Right now I am putting a query in the OLE DB Source that has a WHERE clause with the criteria in it. BUT..is there some transformation or other object I can put in the chain where I could put the criteria? Is there another way to do this? Any sage wisdom would be helpful.

    tod

    Wednesday, July 17, 2013 6:21 PM
  • When you say one data flow per control flow. 

    Means you have 11 Data Flow task in Control Flow Pane and then inside each of Data flow you are loading data oledb source--Conversion--Destination, That's what you mean or something else? Sorry for not understanding

    4 are straight forward, as you truncate and load them... 

    In other 7 tables you can have different conditions. You are not deleting but actually loading new records and might be updating few of the records. 

    Please explain one scenario and we will be able to help you out with transformations you can use. If you need to do insert new records and update old if any value changed , you can use lookup to find those records and use OLE DB Command transformation to update.

    Here is link for upsert by using SSIS

    http://jahaines.blogspot.com/2009/09/sss-performing-upsert.html

    thank you


    http://sqlage.blogspot.com/

    Wednesday, July 17, 2013 7:19 PM
  • Hi Tod,

    For the remainign 7 tables, if you have to perform UPSERT (update+insert), either you can use lookup component as Aamir suggests, or you can try T-SQL MERGE statement to perform the action. MERGE certainly has far better performance as compared to look up in SSIS.


    Thanks, hsbal

    Wednesday, July 17, 2013 9:32 PM
  • Okay guys. It worked!

    So here is what I have and an explanation of my next step. 

    I have a package with 11 Control Flows, all pointed to each other in a line so they run one at at time. Each Control Flow represents a table in my destination database. Inside each Control Flow I have an OLE DB Source connected to an OLE DB Destination, with a Data Conversion in the middle. 

    When I click debug each Control Flow runs one at a time and they all run successfully! SO. NOW..

    In some of these Data Flows I have hard coded some dates. Something like this:

    CreatedDate >= '6/1/2013' AND CreatedDate < '7/1/2013'

    Obviously I want these dates to change each month. So now I need a way to replace the dates with a variable be able to assign a value to the variable when I run the package. That's where I need your help next. 

    I look forward to your answers tomorrow. This is been a fun learning experience for me and I will pass on my new knowledge to the next guy!

    tod

    Wednesday, July 17, 2013 10:01 PM
  • This worked perfectly. Thanx. I created two parameters - StartDate and EndDate and set default values for each. NOW..how do I modify the values of these parameters at run time? Also, I have date criteria for seven of my 11 flows. Do I do this in each one? And then when I pass the new value does it work for all seven?

    Tod

    Thursday, July 18, 2013 4:26 PM
  • Tod,

    You can have a query in exec sql  task , for example

    SELECT GETDATE()

    and save the result of this query into StartDate variable. Similarly, for Enddate, you can save the result of a query into that.

    Then in your ole db source, you can pass these variables, as parameters...


    Thanks, hsbal

    Friday, July 19, 2013 5:17 PM
  • The part I don't know how to do is save the results into the StartDate Variable. 

    I can do an execute task to get the dates I want, but I don't know how to use the results to modify the StartDate variable. 

    Friday, July 19, 2013 6:06 PM
  • This will help you to understand, how to save result from exe sql task into SSIS variable.

    https://www.simple-talk.com/sql/ssis/ssis-basics-using-the-execute-sql-task-to-generate-result-sets/

    it also explains about passing the values of the variables to other queries in the package.


    Thanks, hsbal

    Monday, July 22, 2013 3:30 PM
  • Is your Problem solved of running 11 DFT' at a time?

    Please Help People When they need..!!

    Monday, July 22, 2013 4:15 PM
  • Yes. I created a single Control Flow for each Data Flow set, then strung the control flows to together, and that solved my problem.

    tod

    Monday, July 22, 2013 4:23 PM