Post reply

Run dynamic SQL through SSIS

  • August 24, 2011 at 2:35 pm

    #146075

    I have a setup table which has Client, State and FileName Column for eacch of the clients.

    My SSIS package runs through the setup table (with Foreach loop) and generates the file for different clients.

    Files gets generated for each state which are specified in the setup table. This runs fine since in the query I just need to

    specify table.State = ? and I have a parameter mapping going on.

    But I need to make this package more robust. I need to able to pass some sql statements to the where clause in my query in SSIS package

    since query seems to be keep changing for each of the client. So, I added a column called SqlFilter in my setup table and I tried to

    specify my query there like (and table1.state = 'NY' and table2.Customergroup in '101', '102').

    Now, the question is how do I pass this SQLFilter to my SSIS package?

    To my understanding you need to have some feild = ? in the query you cannot just say put ? (for SqlFilter col)...Is there other way to accomplish this?

    I hope I made my question clear.

    I think I can create a stored proc and call the package through the stored proc for each record, but I really like to do this in SSIS

    package itself if I could..

    ANy help would be appreciated.

  • August 24, 2011 at 9:46 pm

    #1372929

    You could use either a script component or an EXEC sql task to build the statement. I'd probably use the script component. I'm curious what is being used to pull the data? Data-flow? If so then the source can accept a command from a variable. However this is important. The variable needs to comtain a query that returns all the fields with the right types at design time or it will fail. It won't be used at run-time but it is needed to generate the correct meta-data.

    CEWII

  • August 25, 2011 at 7:52 am

    #1373157

    Thanks for the explanation. I am still having some confusion and I am hoping that I would be explain it lil better.

    I have a column called SQlFilter in my setup table. Which I read and and pass it to SSIS varaiable and map it to SqlFilter variable there in SSIS (Yes source is Data Flow and there is parameter mapping, which is done correctly)

    SqlFilter field contains statement like 'and t1.state = '''NY'' and t2.Customergroup in (100,200)'

    My query in Data Flow is like this....

    SELECT T1.Name, T2.State

    FROM Table T1

    JOIN Table T2 ON T1.ID = T2.ID

    (My question is how do I sepcify SQlFilter here.....????)

    IF I just put ? it gives a syntax error.....

    Seems like to use ? u need to have something on the left side of the expression....like t1.col = ? or sth.

    Please advise.

    Thanks for your help again.

  • August 25, 2011 at 9:24 am

    #1373250

    Instead of using "SQL command" try using "SQL command from variable". The variable would need to contain the entire SQL command rather than just the filter.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • August 25, 2011 at 10:20 am

    #1373316

    Thanks for your reply. I am getting there.

    Now, what i did was, since my source sql was very big, I created a view for it. So I declare two variables.

    One to store the the view (SqlSourceQuery) and another one for the filter (SqlFilter).

    For my variable SQLSourceQuery, this is how I am setting up my expression.

    "SELECT col1, col2 FROM vw_my_view

    WHERE" + @[User::SqlFilter]

    It didn't show any syntax error there.

    So, on my OLE DB source, I am chosing SQL Command from Variable, and on the variable name I am selecting user::SqlSourceQuery

    Now I am getting the following error message when I try to click OK..

    An expression of non-boolean type specified in a context where a condition is expected near ".".

    Any suggestion?

  • August 25, 2011 at 2:09 pm

    #1373483

    You're probably missing the space between the "WHERE" and your filter.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • August 25, 2011 at 2:18 pm

    #1373491

    I changed it to like this..Still no luck

    "SELECT col1, col2 FROM vw_my_view

    WHERE [" + @[User::SqlFilter] + "]"

  • August 25, 2011 at 3:08 pm

    #1373517

    I never said anything about adding square brackets. Those tell SQL to treat your filter as if it were the name of a single item.

    You may want to put a "watch" on your command variable to make sure that the SQL it's producing is correct.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • August 25, 2011 at 3:49 pm

    #1373526

    The problem is its erroring out when I try to select this variable in Command from Variable in OLEDB source and I cannot bypass this mesage to watch the value of the variable to see what is coming through.

  • August 26, 2011 at 11:30 am

    #1374004

    An easy way to solve that would be to just put a Script Task in, and pass the variable into the script task, then put a MessageBox command in your script to output the variable.

  • August 26, 2011 at 12:14 pm

    #1374025

    Thanks for all of your help. My existing query actually worked. All I had to do was put the default value for the variable to get it working.

  • August 26, 2011 at 11:41 pm

    #1374188

    Hi,

    You can make this change through the properties of OLEDB source component.

    Thanks

    Sam

  • March 24, 2019 at 2:50 pm

    #2025307

    check this video it will help you
    SSIS Dynamic SQL Command

Viewing 13 posts - 1 through 12 (of 12 total)

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