Integration Services :: Dynamic Creation On DataBase
Jul 31, 2015I have a reqirement where in i need to create a Database dynamically in SSIS, Database name is given in table.
View 5 Replies
ADVERTISEMENT
Integration Services :: Database Creation Dynamically In Server From SSIS
Jul 31, 2015I need to create a DB dynamically in the Server from the SSIS only....
How to implement this....
Integration Services :: Dynamic Database For OLEDB Connection Manager In SSIS
Nov 10, 2015I am trying to dynamically change my initial catalog in a SSIS project. Each day I get a snapshot of data from the production server. I am performing ETL on that database and loading it into my warehouse. I am trying to put something together so that each day I get the latest snapshot and if its newer then my most recent one I pass that new catalog into the connection manager. I can already run the sql script to compare the two catalogs but I can't populate the connection manager.
View 3 Replies View RelatedIntegration Services :: Cube Creation In SSAS
Aug 24, 2015I need to create 3 fact table in a cube with dimensions ?? is it possible . What are the steps to do.
View 3 Replies View RelatedIntegration Services :: SSIS Flat File Processing And Creation
Nov 30, 2015I am facing one process related issue.
I have one task in which i have to collect lots of .txt file having ## delimiter my requirement is to convert the delimiter from ## to comma and save the new file with .dat extension in different folder.
I have done all required process and run the application which should flow like collect source .txt file do Script component processing and create new .dat file with processed data in Data Flow task, but in my Task the Source and Destination start on same time and process start after words which cause empty file or some time a.txt file data stored in b.dat file where as a.dat file is completely empty.
The process should flow in sequence but behavior is totally against the process, i am using Foreach Loop Container for pick up each file.
Dynamic Database Creation
Apr 6, 2007Hi All,
I have a web site where the user can select from a list of databases (they're actually all the same structure, but have data unique to each client). Each database is a SQL 2005 database held in the App_Data folder. This all works a treat. Each database is simply named after the client it is holding data for, and the connection string for each is stored in a database that is accessed
However, what I need is a form where a database name can be entered, and I can then create a new database with the same structure as the others - including tables, indexes and stored-procedures. I know I can use the copy database wizard in the Management Studio, but that isn't an option to the users of this system (it's to be used on an intranet, and the page I'm trying to create will only be used by project managers - but these people won't have a clue when it comes to configuring databases themselves). I have scripted a database, but have no idea on how I can actually use this script from within Visual Studio and more importantly, don't know how to configue it to have the database name as a parameter.
I've also seen some examples that suggest using SQL-DMO, but haven't been able to work out how to use it and according to Microsoft it shouldn't be used for new developments (http://msdn2.microsoft.com/en-us/library/ms132924.aspx).
Anyway, I really hope someone is able to offer some assistance on this one as I've spent a whole day trawling forums and various web-sites trying to find a solution to this.
Thanks & regards,
Paul
Project Creation Error With Ivolva Component Wizards For Integration Services
Jun 5, 2006Hi,
I'm just getting started with SSIS and want to create a custom data flow component. I found the Ivolva Digital "Component Wizards for Integration Services" which says it make starting your own custom task or data flow component
a snap by providing a functional base project for your task or component.
Therefore I installed the Component Wizards for Integration Services and everything seemed to install ok - when I started Visual Studio I had the "Custom Data Flow Component" and "Custom Task" templates available in the New Projects dialog.
However, when I try to create a project of either of these types I get the message "Creating project 'CustomTask1'...project creation error" in the status bar, and can't get past the New Project dialog. (I can create all other project types ok, though).
Can anyone offer any advice that might help me out here?
Thanks in advance,
Lawrie.
Integration Services :: How To Import Filenames And File Creation Date Into Table
Apr 20, 2015I am importing Differennt Excels Files into table. my require ment is after importing completed I need to insert all these Filenames ,File creation date into table. (for Auditing).
View 9 Replies View RelatedAnalysis :: SSIS Dynamic Database Creation
Jul 31, 2015I have a requirement where in i need to create a Database dynamically in SSIS, Database name is given in table.
View 3 Replies View RelatedDynamic List Of Tables In Integration Services
Apr 27, 2006I need to report on data from several databases on several servers. They are all SQL Server 2005 databases. I am trying to created an Integration Services task to consolidate and transform this data for easy reporting. The problem I am having is one database in particular. It has tables like this:
tblLookupData_Customer1
tblLookupParseData_Customer1
tblLookupData_Customer2
tblLookupParseData_Customer2
tblLookupData_Customer3
tblLookupParseData_Customer3
I want to use only the tables of the form "tblLookupParseData*" for this list. I can do this in Stored Procedures by dynmacally building up the sql query. I cannot find out how to do this in Integration Services. When I make Datasource Views, they seem to expect me to pick from a list of known tables. This list of tables grows as Customers are added to the system.
NOTE: The way the tables are structured was NOT my idea. I hate storing "data" in the structure of the database. Many people also do this when they create "period" tables such as "CustomerData_05_2005". It speeds up writing the data, and querying a specific table, but it is a nightmare for reporting. I cannot change this as it is not in my responsibility.
Integration Services :: Flat File With Dynamic Columns
Aug 31, 2015I will be receiving a CSV daily where columns within the file will change. The column order and number of columns can change daily. I need a way to read in the header from the csv and create a flat file connection that reflects the columns listed in the header.
Is there an easy way to do this using a script task? I have already read the header into a table but I have been unable to create the dynamic file connection.
Integration Services :: Importing A File With Dynamic Columns
Jul 16, 2015I am new to SSIS and C#. In SQL Server 2008 I am importing data from a .csv file. Now I have the columns dynamic. They can be around 22 columns (some times more or less). I created a staging table with 25 columns and import data into it. In essence each flat file that I import has different number of columns. They are all properly formatted only. My task is to import all the rows from a .csv flat file including the headers. I want to put this in a job so I can import multiple files into the table daily.
So inside a for each loop I have a data flow task within which I have a script component. I came up(research online) with the C# code below but I get error:Index was outside the bounds of the array.I tried to find the cause using MessageBox and I found it is reading the first line and the index is going outside the bounds of the array after the first line.
My File1Conn is the flat file connection instead I want to read it directly from a variable User::FileName
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Windows.Forms;
using System.IO;
[code]....
Integration Services :: Dynamic Email Subject Line
Aug 6, 2015I have the variable @Monthname defined as shown below:
(MONTH(getdate()) == 1 ? "January" : MONTH(getdate()) == 2 ? "February" : MONTH(getdate()) == 3 ? "March" :
MONTH(getdate()) == 4 ? "April" : MONTH(getdate()) == 5 ? "May" : MONTH(getdate()) == 6 ? "June" :
MONTH(getdate()) == 7 ? "July" : MONTH(getdate()) == 8 ? "August" : MONTH(getdate()) == 9 ? "September" :
MONTH(getdate()) == 10 ? "October" : MONTH(getdate()) == 11 ? "November" : MONTH(getdate()) == 12? "December":"")
By itself, it is working fine. I am attempting to have the @SubjectLine variable display the below:
CA - TFL for the month of August 2015
I am using the expression below in the expression builder:
"CA - TFL for the month of " + @[User::MonthName] + (DT_WSTR,4)YEAR(GETDATE())
When evaluating the expression, I get the below (month is missing):
CA - TFL for the month of 2015
Integration Services :: Dynamic Password In FTP Task In SSIS?
Dec 31, 2013I can set almost all properties of FTP task and FTP connection manager using expressions option. In that option, I don't see that I can set FTP PASSWORD using a variable. How do I set the password dynamically?
View 2 Replies View RelatedIntegration Services :: Dynamic Query For Lookup Transformation
Nov 3, 2015In my package I am using lookup to get new and similar record. I want to filter the rows for Lookup Reference Data Set by using Variable Value.
I have created variable @[User::CustId] with Int32 datatype, having default value 2 when I am trying to evaluate below query I am getting error
"select CustId,PartNm,LocId,LocTyp from loc where CustId= "+ @[User::CustId]
Error. The Data types "DT_WSTR" and "DT_I4" are incompatible for binary operator "+".
The operand types could not be implicitly cast into compatible types for the operation. To perform this operation , one or both operands need to be explicitly cast with the operator.
Integration Services :: Export To Excel Dynamic Number Of Columns
Sep 11, 2015We have a requirement to produce adhoc Excel reports with a standardized header page with a disclaimer attached. We want to be able to feed in a SQL Statement, or a table with the resultset from a SQL Statement and have SSIS populate an existing blank Excel workbook, which the disclaimer attached. The use of xp_cmdshell is not an option.I've spent a lot of time looking for solutions on the web and it seems though its not possible - although many articles are 3-5 years old. Before I throw in the towel, I just wanted to get feedback from this group if it still is not possible in the latest versions of SQLServer and SSIS, or to ask if there are any other 3rd party solutions that can do this today.
View 5 Replies View RelatedIntegration Services :: Dynamic Column Mapping In SSIS Package
Nov 2, 2015I have some source files is there today it will have 4 columns..Tomorrow it will have 10 columns...my package is dynamically load the data to destination table..How we have do it in Using script task...
View 4 Replies View RelatedIntegration Services :: Dynamic Mapping From XML Source To Destination Table
Jun 1, 2015I have a requirement to take xml file, in case the number of column changes, it should not fail the package, rather it should load the data in destination table. Destination table could be altered separately depending on xml schema by the DB team in production.
View 3 Replies View RelatedIntegration Services :: How To Load Data From CSV File To Dynamic Table
Jun 11, 2015I have a requirement to load bulk of csv files to sql table. some times, some columns could not come in csv file(some times 100 columns and some times 80 cloumns). That time the package is getting failed. How to create a table dynamically based on csv file structure.
View 8 Replies View RelatedIntegration Services :: Dynamic SIS Package Changing Source Connection At Run Time
Jul 30, 2015I am in between of creating a dynamic SSSIS package which will run for multiple zones having different source connection.My source is in Oracle.I am having 3 DFT with the 3 different source tables.I want to create a package with above DFT dynamically so that my single package can run for the entire zone with dynamically source connection change.I have created a Master table which stores the zone source connection string and zone name. I have 2 different connection.so if in future any new zones come so only newly zone details need to be add in master table without opening the package.
View 3 Replies View RelatedIntegration Services :: Generate Dynamic Excel Worksheets With Data In SSIS?
Sep 4, 2015I want to export the data into multiple sheets with same template, all the worksheets have to split dynamically with specific Sheet Name and template also copied to all other sheets
For Example:
Sheet Name: Guru
Name Age
Guru 24
Sheet Name: Johnson
Name Age
Johnson 32
it goes on......
Integration Services :: Dynamic Column Mapping Between Excel And Fact Table
May 18, 2011Have to create SSIS package for the below requirement:
I have source data in 2 excel files. Data from both these excel files should be loaded to the same single Fact table.
The column names in excel files and table are not same. I have a Reference table which has the column mappings between excel and Fact Table.
I have to refer this Reference Tabel for column mappings, plus i have to add some derived columns (Created_Date) to load the Fact_Table.
I have given a sample data structure below:
Source Data
Excel1_Order.xls
OrderNumber OrderQuantity OrderDate
Order10001 100 01-01-2011
Excel2_Customer.xls
CustomerNumber CustomerName CustomerAddress
[Code] ....
Is there any way to handle this in SSIS?
Integration Services :: Creating Dynamic Server Tables Through SSIS As Per XML Data Files Metadata
Feb 15, 2011I have a scenario, need to create SQL server Tables dynamically.
I Have multiple xml data file on a particular location, and want to load those XML data into sql server tables, but he metadata of each xml data files are not same.
Hence the approach is that,
1. Pick first file from that location
2. Create a table according to that xml data file metada
3. load data on newly created table.
4. Pickup the next xml data files.
5. loop through, till the XML data files are exists on that location.
Analysis Services 2005 Database Processing Fails When Run From Integration Services
Oct 11, 2007Hello, I have a problem when trying to fully process an SSAS database using Integration Services "Analysis Services Processing Task" task. I have 2 of these tasks which are responsible for processing the Dimensions then the Cubes. When I run the package either via the BIDS environment or on the local server from the Integration Services engine, I will get an error after about 20 minutes stating:
"Error: Memory Error: Allocation failure. Not enough storage is available to process this command""Error: Errors in the metadata manager. An error occurred when loading the <cube name> cube from the file \?D:Program FilesMicrosoft SQL ServerMSSQL.2OLAPDataMyWarehouse<cube file>.xml"
The cube name is not specific, it will fail and any of my cubes could be in the error log
If I fully process the AS database using the AS engine (logon to local AS server, right-click AS database and click Process), I get no errors at all, it processes and completes fine. The processing options are identical when I run in AS or via the SSIS "Analysis Services Processing Task" task.
I've searched quite a lot online but no joy, the information I have gleaned from various sites does not directly link SSIS with SSAS processing problems.
When either the AS processing starts via SSAS or SSIS the memory usage of MSMDSRV.exe increases to around 1.4 / 1.5 GB but never goes to 2GB ever, even when the error appears.
I've done the following with no effect.
" Have run via AS and works fine
" No specific cube it fails on
" Have created a Dimension only package, same problem
" Changed the maxmemorylimit
" Changed the connections to localhost
" Memory DOES NOT max out on server
Server Specs:
Windows Server 2003 Standard + Service Pack 2
4GM ram, 2GB paging file
SQL Server 2005 + Service Pack 2
Can anyone help?
Andy
Integration Services :: Get FileName Fo Each File Created Via Dynamic Flat File Destination
Jul 24, 2015Need to know how I can get the dynamic filename created in the FlatFile destination for insert into a package audit table?
Scenario: Have created a package that successfully outputs Dynamiclly named flat files { Format: C:Test’Comms_File_’ + ‘User::FileNumber’+’_’+Date +’.txt’
E.g.: Comms_File_1_20150724.txt, Comms_File_2_20150724.txt etc} using Foreach Loop Container :
* Enumerator Set to: “Foreach ADO Enumerator” with the ADO object source variable selected to identify how many total loop iterations there are i.e. Let’s say 4 thus 4 files to be created
*Variable Mappings : added the User::FileNumber – indicates which file number current loop iteration is i.e. 1,2,3,4
For the DataFlow task have a OLDBSource and a FlatFile Destination where Flat File ConnectionString is set up as:
@[User::Output_Path] + "Comms_File"+ @[User:: FileNumber] +"_" + replace((DT_WSTR, 10) (DT_DBDATE) GETDATE(),"-","")+ ".txt"
All this successfully creates these 4 files:
Comms_File_1_20150724.txt, Comms_File_2_20150724.txt, Comms_File_3_20150724.txt, Comms_File_4_20150724.txt
Now the QUESTION is how do I get these filenames as I need to insert them into a DB Audittable. The audit table looks like this:
CREATE TABLE dbo.MMMAudit
(
AuditID INT IDENTITY(1, 1) NOT NULL,
PackageName VARCHAR(100) NULL,
FileName VARCHAR(100) NULL,
LoadTime DATETIME NULL,
NumberofRecords INT NULL
)
To save the Filename & how many records in each file in our Audit Table, am using an Execute SQL Task and configuring it as this:
Execute SQL Task
Parameter mapping - Mapped the User Variable (RecordsInserted) and System Variable( PackageName) to Insert statement as shown below
SQLStatement: INSERT INTO [dbo].[MMMAudit] (
PackageName,NumerofRecords,LoadTime)
(?,?.GETDATE)
Again this all works terrific & populates the dbo.MMMAudit table as shown below BUT I also need to insert the respsctive file name – How do I do that?
AuditID PackageName FileName NumberOfRecords
1 MMM NULL 12
2 MMM NULL 23
3 MMM NULL 14
4 MMM NULL 1
Dynamic Table Creation
Apr 19, 2007Hi,I'm trying to create some tables dynamically based on the content of another table in the same database. I found a post that does what I want to do, but I can't get my code (that is similar to the post) to work.Given below is my code: 1 DECLARE @deptCode varchar(50), @numberOfDept int, @tableName varchar(MAX), @columnName varchar(MAX)
2 DECLARE @lengthDeptCode int, @lengthTableName int, @lengthColumnName int
3
4 SELECT @numberOfDept = COUNT(DISTINCT DeptCode)
5 FROM tbl_Department;
6
7 WHILE (@numberOfDept >=0)
8 BEGIN
9 SELECT @deptCode = DeptCode, @lengthDeptCode = LEN(DeptCode)
10 FROM tbl_Department;
11
12 SET @tableName = 'tbl_ProjectNumber'+@deptCode
13 SET @lengthTableName = LEN(@tableName)
14 SET @columnName = 'ProjectNumber'+@deptCode
15 SET @lengthColumnName = LEN(@columnName)
16
17 CREATE TABLE CAST(@tableName as char(@lengthTableName))
18 (
19 CAST(@columnName as char(@lengthColumnName)) int IDENTITY(1,1) NOT NULL
20 )
21
22 SET @numberOfDept = @numberOfDept - 1
23 END
This is actually my first time using SQL programatically so I'm guessing there are alot of problems with it. I just don't know what exactly. The error I get is:Msg 102, Level 15, State 1, Line 18Incorrect syntax near '@tableName'. Thanks.
Dynamic Table Name Creation...
Jun 19, 2007Hi there,I am trying to generate tables names on the fly depending on another table. So i am creating a local variable containing the table names as required. I am storing the tables in a local variable called@TABLENAME VARCHAR(16)and when i say SELECT * FROM @TABLENAMEit is giving me an error and I think I cannot declare @TABLENAME as a table variable because I do not want to create a temp table of sorts.I hope I am clear.thanks,Murthy here
View 6 Replies View RelatedDynamic Table Creation
Mar 19, 2004Suppose I have a table named table1 which has a field question_Id.There are many values for this field say 100,101,102.
Now I want to make a table with the field name 100,101,102 but the problem is that it is not fixed how many values are there for question_id.Here in this example I mentioned three(100,101,102) but it may be anything.How can I make a table with the field names this way?
Subhasish
Integration Services :: Import Varying Number Of Tables Each Time From One Database To Different Database
Sep 9, 2015I am new to SSIS. I have been struggling with this for the past one week. I have a weird task. I need to import several tables from one database to a different server with a new database name. We need to do this at the end of every year. The main problem here is that the number of tables varies every year. You may not have all the tables as last year or may have more tables. So I need to create a dynamic task that takes care of this every year without changing the package.
I have performed the following tasks **
1. Create a new dynamic database. ( I have used Execute SQL Task to do this) 2. Copy all the table structures ( I have used Execute SQL Task to do this)
3. Import Data. This is the main problem. I was trying to create a dynamic connection string with variables as suggested in several forums but I finally came to know that this cannot be done if the table structures are different as the metadata cannot be refreshed at runtime.
4. The final step to create a process to validate the data (the count from each table for both source and destination. I think this can be done with Sql task.
What is the best method to do this? My DBA does not like “Transfer SQL Objects Task” or “transfer Database Task”. I would like to create this as a dynamic process.
Dynamic Table Creation/Modification
Oct 1, 2006I'm wondering if there is a control available for creating/modifying db tables through a web interface. I want for users to be able to add/remove, rename, and change the datatype of certain fields in a database table. I've been searching all day online if such a control exists in asp.net but haven't found anything.
View 1 Replies View RelatedDynamic Flat File Creation!
May 14, 2008I'm trying to read the folder which has flat files and transform it and put it another folder with the SAME original name.
I created a For Eachloop containter and assign it to variable infile. In the data flow I added some transform and flat file destination. currently its reading 3 files and output is only one file.
I would like to create dynamic flat file connection so that it'll create separate files (same name) in different folder.
Thanks,
Dynamic Flat File Creation
Mar 19, 2008
Hi All,
I'm new to SSIS. I have a small problem. In our system we are trying to create flat files based on data from returned from different SQL stored procedures. The configuration settings for creating the flat files are stored in a SQL table.
For example, the config table will look like below :
Feed Id Feed Name Feed File Name Feed Stored Procedure Name
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1 Biz C:Biz.txt dvx_GetBiz
2 PG C:PG.csv dvx_GetPG
3 NexT C:NexT.txt dvx_GetNexT
4 Amez C:Amez.csv dvx_GetAmez
The problem is dvx_GetBiz, dvx_GetPG,... all have different number and types of columns. So, I'm not able to create a dynamic Flat File destination depending on the columns returned from the corresponding stored procedure.
Is there a way to accomplish this ?
Pl. help.
Awaiting someones reply ASAP.
Thanks & Regards,
Vidya.
Creation Of Dynamic Cubes Using XMLA?
Mar 12, 2008I hope some of you can point me in the right direction. I am new to all this and
working on a project using Cubes.
The cubes need to be generated in a dynamic fashion as the contents
(that means field) in the fact tables change change every quarter and are
based off of a external application.
The base strucutre stays the same though. So we thought we would export the
XMLA representation of the cube and then munge (word for alter) the data in the
XMLA file and send it someplace to create the new cube on a SQL 2005 server.
This seemed easy enough but I have found no real information on how to
run some piece of code that would create the cube based on this XMLA code.
On another note is there a parsing object for the XMLA so i dont have to right one from
scratch?
Thanks,
Bob