Post reply

Need to dynamically generate schema in OLDEB source sqlcommand - script to big for an expression or stored proc

  • November 8, 2011 at 10:17 am

    #251512

    We have 2 identical core systems containing different information. We have an ODS database where we distinguish between the 2 using schemas. We have a separate DW for each, we want to have a generic set of packages that can be used to load the dw's, the stumbling block is that we need to dynamically set the schema value in the OLEDB Source SQL Command.

    Our current ETL enables us to set config info using a parent package and then each of the packages to load the data are child packages, we can update and deploy these child packages independently without need to redeploy the whole solution.

    To load the ODS we managed this by using expressions and SQL Command from variable, this fails for DW as we go over the 4000 character limit (SQL 2005). So we thought we would use a stored proc and dynamic SQL using varchar(max) but some of our queries are larger than a varchar(max).

    The ultimate aim is to keep the child packages generic, and jsut amend the schema name in the sql command for the oledb source.

    My current thoughts which I have no idea how to do are:

    1. Deploy the solution then somehow loop through the files and update the XML

    2. Use a script task as the source - would this help?

    3. Update the sqlcommand when the package is built?

    Any thoughts help would be appreciated we are currently thinking duplication is the best option but risky.

    Thanks

  • November 9, 2011 at 12:12 am

    #1404986

    So you are saying your queries take up more than 2147483647 bytes? In other words, more than 2 gigabytes? I find that somehow hard to believe. If it is the case, and you have a query that is larger than a small library, I would redesign your ODS 🙂

    My guess is that somewhere in the stored proc or in SSIS, there is a misconfiguration that gives you an error, not that the queries are larger than a varchar(max).

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply