Integration Manager scripts - using a variable to in an advance sql connection

(0) Share

Report

Posted on by Chuck Evans

1,175

I have an accounts payable integration with a before integration script that prompts the user for the company id and user id, sets the user id as a variable and executes a sql stored procedure which imports the source information from a location determined by the user id and create a table with the user id in its name. I'm running the source data through sql because it needs extensive manipulation before importing to Dynamics. All this works fine. What I don't know how to do is tell the source query in IM which table to look at. The source query is a standard select statement. Is there a way to subsitute the table name with a variable name and have it execute the select against the desired table?

Thanks
Chuck

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (0)

Richard Wheeler 75,848 Moderator on at

Like (0)

Report

Sounds like this is a job for DYNAMIC SQL.

www.sommarskog.se/dynamic_sql.html

The SQL statement is built using a variable and then executed. In your case the table name will be a variable so you build the SQL statement using the table name variable and then execute the SQL statement.

Was this reply helpful? Yes No
Chuck Evans 1,175 on at

Like (0)

Report

Hi Richard. Thanks for your response. The variable table name works fine in SQL. The problem I'm having is to get it to work in the Integration Manager Query. If I try to build it in the query itself I loose the columns. If I try the Before Query script command Query.OverrideFileLocation and building the select statement, it basically ignores the script. Is there a way to bring the getVariable("??") command into the SQL statement area of the query properties?

Was this reply helpful? Yes No
Richard Wheeler 75,848 Moderator on at

Like (0)

Report

I can't say I have ever tried doing this in IM. I would think if you build your integration with a fixed table and then change the query using Dynamics SQL and just choose to not refresh the field names I would think it would work. Is it possible to build a before Integration Query that populates a temp work table and then build your integration off that temp table?

Was this reply helpful? Yes No
Chuck Evans 1,175 on at

Like (0)

Report

I tried that but it forced a refresh on the fields which results in no fields. I thought I could use some sort of substitute command like "Query.OverrideFileLocation " but that doesn't seem to work and I can't find anything else. If I could I could point it to a default location and then have the before query script change it to criteria defined in the before integration script. Do you think this concept should work or can you think of any other way to do it?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hello Chuck,

Did you ever resolve your issue? I have an exact similar need and haven't figured it out yet.

Thanks for your input.

Thanks,

Was this reply helpful? Yes No
Suggested answer

Redbeard 12,931 on at

Like (0)

Report

You might want to add something like the following to your before integration script. I have a much more complex before integration script, in use, these are just the relevant components.

Dim sourceFile

Dim sourcePath

Dim sql_dsn

Dim sql_user

Dim sql_password

Dim sql_db

sourceFile = "filename.txt"

sourcePath = "\\Server\Import Data\"

sql_dsn = "DynamicsGP"

sql_user = "user"

sql_password = "password"

sql_db = "COMPANY DB"

bcp "SELECT FirstName, LastName FROM AdventureWorks2008R2.Person.Person ORDER BY LastName, Firstname" queryout filename.txt -c -T

Of course the select statement above is just a placeholder for your SQL query.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I will try that. Thanks for your response.

Was this reply helpful? Yes No