Data Manager - Import SQL

[Former Member]

Hi

I'm looking to load BPC via an SQL Server2005 table using Data Manager's "Import SQL" package.

Does anyone have any information on how to use the Import SQL package?

FYI - we are running BPC 7.5 sp4.

Thanks

[Former Member]

Hi Humptyc

What kind of information do you require?

We have a modified ImportSQL.dtsx package running in my current project and it seems to be working quite nicely, the only limitation we are fining is with the number of records we import.

One limitation we found, was that we were only able to import from a table in the applicationset database, we were not able to import from another database on the same instance, even if we use the fully qualified name (i.e. DBName.dbo.tblName)

This was on version 7.0, I have not tested it on version 7.5

Kind Regards

Daniel

[Former Member]

Hi Daniel

Apologies, I should have been more specific.

I'm looking for any information on how to configure the Import SQL task. I can't find any documentation on this but ran the Import SQL package via the "Run Package" option in BPC, passed in the database & table names and provided a list of columns but received an error. Is this the normal way of doing this or do I need to amend the default package in SSIS before loading it via BPC?

Thanks

Wayne

[Former Member]

The ImportSQL package works exactly the same way the standard default import package, the only difference is that the package is using a SQL table as a datasource.

It really depends on your requirements, you need to ensure that your transformation and conversion files are correct and that there are no errors.

I would suggest to take and extract of the data in your SQL table to a text file, and use that file to "Validate and process" your transformation file to ensure that you will not have any issues with the data import. If you have done this, and are still getting errors, post the error message and we can troubleshoot it further.

Once you are happy that there is no errors, then you would run ImportSQL package to import the data in your BPC application.

Please note, I have found that the table must be in the appset database, I have not been able to make it work using a table in another database.

Typical scenario's for using the ImportSQL package are when you have multiple data sources and want an automated solution, in which you are not reliant on users uploading their data. It is really dependant on your situation and what you want to achieve.

Kind Regards

Daniel

[former_member225916]

Hi Daniel,

I tried to import the data from another DB( not appset DB)  to Appset DB with IMPORT SQL package. i got following error.after that i tried to import the data with in appset db (from one application DB to another) but still i was getting same error.

As per your suggestions, Validate and process the flat file and it successfully validated.

then what may be the cause???

can u pls help me on this issue??

TOTAL STEPS  3

1. SQLToTxt:          completed  in 0 sec.

2. Convert Data:      Failed  in 0 sec.

3. Import SQL:        completed  in 0 sec.

[Selection]

------------------------------------------------------------

DB=FEDERAL_BANK

TABLE=tblFactCOPY

COLUMNS=FB_ACCOUNT,FB_CATEGORY,FB_ENTITY,FB_TIME,SIGNEDDATA,FB_DATE,FB_DEP,FB_DATASRC

TRANSFORMATION=\FEDERAL_BANK\FB_PLANNING\DataManager\TransformationFiles\\JUN2.XLS

CLEARDATA= No

RUNLOGIC= Yes

CHECKLCK= Yes

[Messages]

------------------------------------------------------------

Could not open the input file.[C:\PC_MS\Data\WebFolders\FEDERAL_BANK\FB_PLANNING\PrivatePublications\Naidu G\TempFiles\Tempypmw_2512_.tmp]

[EvModifyScript Detail]

------------------------------------------------------------

06-02-2013  22:36:44 - Debug turned ON

INFO(%FILE%, C:\PC_MS\Data\WebFolders\FEDERAL_BANK\FB_PLANNING\PrivatePublications\Naidu G\TempFiles\Tempypmw_2512_.tmp)

CONNECTION(SQL, ConnectionString, Data Source=SAPDEMO;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;)

CONNECTION(SQL, InitialCatalog, FEDERAL_BANK)

CONNECTION(SQL, ServerName, SAPDEMO)

CONNECTION(TextFile, ConnectionString, C:\PC_MS\Data\WebFolders\FEDERAL_BANK\FB_PLANNING\PrivatePublications\Naidu G\TempFiles\Tempypmw_2512_.tmp)

TASK(SQLTOTXT, [%COLUMNS%], [FB_ACCOUNT,FB_CATEGORY,FB_ENTITY,FB_TIME,SIGNEDDATA,FB_DATE,FB_DEP,FB_DATASRC])

INFO(%TEMPFILE%, C:\PC_MS\Data\WebFolders\FEDERAL_BANK\FB_PLANNING\PrivatePublications\Naidu G\TempFiles\Tempviuh_2512_.tmp)

TASK(CONVERT data, INPUTFILE, C:\PC_MS\Data\WebFolders\FEDERAL_BANK\FB_PLANNING\PrivatePublications\Naidu G\TempFiles\Tempypmw_2512_.tmp)

TASK(CONVERT data, OUTPUTFILE, C:\PC_MS\Data\WebFolders\FEDERAL_BANK\FB_PLANNING\PrivatePublications\Naidu G\TempFiles\Tempviuh_2512_.tmp)

TASK(CONVERT data, CONVERSIONFILE, C:\PC_MS\Data\WebFolders\FEDERAL_BANK\FB_PLANNING\DataManager\TransformationFiles\\JUN2.XLS)

TASK(CONVERT data, STRAPPSET, FEDERAL_BANK)

TASK(CONVERT data, STRAPP, FB_PLANNING)

TASK(LOAD AND PROCESS, APPSET, FEDERAL_BANK)

TASK(LOAD AND PROCESS, APP, FB_PLANNING)

TASK(LOAD AND PROCESS, USER, SAPDEMO\Naidu G)

TASK(LOAD AND PROCESS, DATATRANSFERMODE, 4)

TASK(LOAD AND PROCESS, CLEARDATA, 0)

TASK(LOAD AND PROCESS, FILE, C:\PC_MS\Data\WebFolders\FEDERAL_BANK\FB_PLANNING\PrivatePublications\Naidu G\TempFiles\Tempviuh_2512_.tmp)

TASK(LOAD AND PROCESS, RUNTHELOGIC, 1)

TASK(LOAD AND PROCESS, CHECKLCK, 1)

INFO(%DB%, FEDERAL_BANK)

INFO(%TABLE%, tblFactCOPY)

INFO(%COLUMNS%, FB_ACCOUNT,FB_CATEGORY,FB_ENTITY,FB_TIME,SIGNEDDATA,FB_DATE,FB_DEP,FB_DATASRC)

INFO(%TRANSFORMATION%, C:\PC_MS\Data\WebFolders\FEDERAL_BANK\FB_PLANNING\DataManager\TransformationFiles\\JUN2.XLS)

INFO(%CLEARDATA%, 0)

INFO(%RUNLOGIC%, 1)

INFO(%CHECKLCK%, 1)

INFO(%APPSET%, FEDERAL_BANK)

INFO(%APP%, FB_PLANNING)

INFO(%DEFAULT_MEASURE%, PERIODIC)

INFO(%MEASURES%, Periodic,QTD,YTD)

INFO(%OLAPSERVER%, SAPDEMO)

INFO(%SQLSERVER%, SAPDEMO)

INFO(%APPPATH%, C:\PC_MS\Data\WebFolders\FEDERAL_BANK\FB_PLANNING\)

INFO(%DATAPATH%, C:\PC_MS\Data\WebFolders\FEDERAL_BANK\FB_PLANNING\DataManager\DataFiles\)

INFO(%DATAROOTPATH%, C:\PC_MS\Data\WebFolders\)

INFO(%SELECTIONPATH%, C:\PC_MS\Data\WebFolders\FEDERAL_BANK\FB_PLANNING\DataManager\SelectionFiles\)

INFO(%CONVERSIONPATH%, C:\PC_MS\Data\WebFolders\FEDERAL_BANK\FB_PLANNING\DataManager\ConversionFiles\)

INFO(%TEMPPATH%, C:\PC_MS\Data\WebFolders\FEDERAL_BANK\FB_PLANNING\PrivatePublications\Naidu G\TempFiles\)

INFO(%LOGICPATH%, C:\PC_MS\Data\WebFolders\FEDERAL_BANK\Adminapp\FB_PLANNING\)

INFO(%TRANSFORMATIONPATH%, C:\PC_MS\Data\WebFolders\FEDERAL_BANK\FB_PLANNING\DataManager\TransformationFiles\)

INFO(%DIMS%, [FB_Account],[FB_Category],[FB_DataSrc],[FB_DATE],[FB_DEP],[FB_Entity],[FB_Time])

INFO(%FACTDIMS%, [FB_Account],[FB_Category],[FB_DataSrc],[FB_DATE],[FB_DEP],[FB_Entity],[TIMEID])

INFO(%CATEGORY_DIM%, [FB_Category])

INFO(%TIME_DIM%, [FB_Time])

INFO(%ENTITY_DIM%, [FB_Entity])

INFO(%ACCOUNT_DIM%, [FB_Account])

INFO(%CURRENCY_DIM%, )

INFO(%APP_LIST%, FB_PLANNING,Rate)

INFO(DATAMGRGLOBALBPU, )

INFO(DATAMGRGLOBALCLIENTMACHINEID, WIPRO-1)

INFO(DATAMGRGLOBALERROR, )

INFO(DATAMGRGLOBALPACKAGEINFOR, )

INFO(DATAMGRGLOBALPACKAGENAME, C:\PC_MS\Data\WebFolders\FEDERAL_BANK\FB_PLANNING\DataManager\PackageFiles\custom packages/may 31 2013/MAY312013.dtsx)

INFO(DATAMGRGLOBALSEQ, 2512)

INFO(DATAMGRGLOBALSITEID, )

INFO(MODIFYSCRIPT, 'TURN DEBUG MODE ON. THIS WILL SAVE THE EXECUTED VERSION OF THIS SCRIPT (ALL KEYWORDS REPLACED)<BR>'INTO A FILE NAMED EVMODIFYPKG.LOG SAVED IN THE TEMP DIRECTORY OF DATA MANAGER<BR>'-----------------------------------------------------------------------------------------------------------------------------------------<BR>DEBUG(ON)<BR>'-------------------------------------------------------------------------------------------------------------------------<BR><BR>PROMPT(TEXT,FEDERAL_BANK,"SELECT THE SQL DB TO IMPORT",,"")<BR>PROMPT(TEXT,tblFactCOPY,"SELECT THE SQL TABLE TO IMPORT.",,"")<BR>PROMPT(TEXT,FB_ACCOUNT,FB_CATEGORY,FB_ENTITY,FB_TIME,SIGNEDDATA,FB_DATE,FB_DEP,FB_DATASRC,"ENTER THE NAMES OF THE COLUMN HEADS THESE CONTAIN THE DATA TO IMPORT. (USE DELIMITER AS ',')",,"")<BR>PROMPT(TRANSFORMATION,C:\PC_MS\Data\WebFolders\FEDERAL_BANK\FB_PLANNING\DataManager\TransformationFiles\\JUN2.XLS,"TRANSFORMATION FILE:",,,IMPORT.XLS)<BR>PROMPT(RADIOBUTTON,0,"SELECT THE METHOD FOR IMPORTING THE DATA FROM THE SOURCE FILE TO THE DESTINATION DATABASE",0,{{"MERGE DATA VALUES (IMPORTS ALL RECORDS, LEAVING ALL REMAINING RECORDS IN THE DESTINATION INTACT)","REPLACE && CLEAR DATA VALUES (CLEARS THE DATA VALUES FOR ANY EXISTING RECORDS THAT MIRROR EACH ENTITY/CATEGORY/TIME COMBINATION DEFINED IN THE SOURCE, THEN IMPORTS THE SOURCE RECORDS)"}},{{"0","1"}})<BR>PROMPT(RADIOBUTTON,1,"SELECT WHETHER TO RUN DEFAULT LOGIC FOR STORED VALUES AFTER IMPORTING",1,{{"YES","NO"}},{{"1","0"}})<BR>PROMPT(RADIOBUTTON,1,"SELECT WHETHER TO CHECK WORK STATUS SETTINGS WHEN IMPORTING DATA.",1,{{"YES, CHECK FOR WORK STATUS SETTINGS BEFORE IMPORTING","NO, DO NOT CHECK WORK STATUS SETTINGS"}},{{"1","0"}})<BR><BR>'INFO(FEDERAL_BANK,FEDERAL_BANK)<BR>INFO(C:\PC_MS\Data\WebFolders\FEDERAL_BANK\FB_PLANNING\PrivatePublications\Naidu G\TempFiles\Tempypmw_2512_.tmp,C:\PC_MS\Data\WebFolders\FEDERAL_BANK\FB_PLANNING\PrivatePublications\Naidu G\TempFiles\Tempa84z_2512_.tmp)<BR><BR>CONNECTION(SQL,CONNECTIONSTRING,DATA SOURCE=SAPDEMO;PROVIDER=SQLNCLI10.1;INTEGRATED SECURITY=SSPI;AUTO TRANSLATE=FALSE;)<BR>CONNECTION(SQL,INITIALCATALOG,FEDERAL_BANK)<BR>CONNECTION(SQL,SERVERNAME,SAPDEMO)<BR><BR>CONNECTION(TEXTFILE,CONNECTIONSTRING,C:\PC_MS\Data\WebFolders\FEDERAL_BANK\FB_PLANNING\PrivatePublications\Naidu G\TempFiles\Tempypmw_2512_.tmp)<BR>OLEDBCONN(SQL,OPENROWSET,[DBO].[tblFactCOPY])<BR><BR>TASK(SQLTOTXT,[FB_ACCOUNT,FB_CATEGORY,FB_ENTITY,FB_TIME,SIGNEDDATA,FB_DATE,FB_DEP,FB_DATASRC],[FB_ACCOUNT,FB_CATEGORY,FB_ENTITY,FB_TIME,SIGNEDDATA,FB_DATE,FB_DEP,FB_DATASRC])<BR><BR>'USER DEFINED KEYWORDS. JUST FOR READABILITY PURPOSES<BR>'-------------------------------------------------------------------------------------------------------------------------<BR>INFO(C:\PC_MS\Data\WebFolders\FEDERAL_BANK\FB_PLANNING\PrivatePublications\Naidu G\TempFiles\Tempviuh_2512_.tmp,C:\PC_MS\Data\WebFolders\FEDERAL_BANK\FB_PLANNING\PrivatePublications\Naidu G\TempFiles\Tempa84z_2512_.tmp)<BR><BR>'-------------------------------------------------------------------------------------------------------------------------<BR>'MODIFY CONVERT DATA TASK<BR>'---------------------------------------------------------------------------------------------------------------------------------------<BR>TASK(CONVERT DATA,INPUTFILE,C:\PC_MS\Data\WebFolders\FEDERAL_BANK\FB_PLANNING\PrivatePublications\Naidu G\TempFiles\Tempypmw_2512_.tmp)<BR>TASK(CONVERT DATA,OUTPUTFILE,C:\PC_MS\Data\WebFolders\FEDERAL_BANK\FB_PLANNING\PrivatePublications\Naidu G\TempFiles\Tempviuh_2512_.tmp)<BR>TASK(CONVERT DATA,CONVERSIONFILE,C:\PC_MS\Data\WebFolders\FEDERAL_BANK\FB_PLANNING\DataManager\TransformationFiles\\JUN2.XLS)<BR>TASK(CONVERT DATA,STRAPPSET,FEDERAL_BANK)<BR>TASK(CONVERT DATA,STRAPP,FB_PLANNING)<BR>TASK(CONVERT DATA,STRUSERNAME,SAPDEMO\Naidu G)<BR><BR>'MODIFY LOAD AND PROCESS TASK<BR>'-------------------------------------------------------------------------------------------------------------------------<BR>TASK(LOAD AND PROCESS,APPSET,FEDERAL_BANK)<BR>TASK(LOAD AND PROCESS,APP,FB_PLANNING)<BR>TASK(LOAD AND PROCESS,USER,SAPDEMO\Naidu G)<BR>TASK(LOAD AND PROCESS,DATATRANSFERMODE,4)<BR>TASK(LOAD AND PROCESS,CLEARDATA,0)<BR>TASK(LOAD AND PROCESS,FILE,C:\PC_MS\Data\WebFolders\FEDERAL_BANK\FB_PLANNING\PrivatePublications\Naidu G\TempFiles\Tempviuh_2512_.tmp)<BR>TASK(LOAD AND PROCESS,RUNTHELOGIC,1)<BR>TASK(LOAD AND PROCESS,CHECKLCK,1))

Thanks and Regards,

Naidu