Integration Services :: Create A Table Based On Existence Dynamically

Aug 10, 2015

I am using the following script to check existence of table in the Database and create it dynamically...

This is working when table not existed, it error-ed when the table existed...

This script i am using in the Exec Sql Task.....

[Execute SQL Task] Error: Executing the query "declare @ODSDB varchar(50)
declare @SQLSTMT varcha..." failed with the following error: "There is already an object named 'addressTable' in the database.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not
set correctly, or connection not established correctly.

declare @ODSDB varchar(50)
declare @SQLSTMT varchar(max)
set @ODSDB = 'SampleDB'
begin
set @SQLSTMT = '
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(''' + @ODSDB + '.dbo.addressTable'') and Type=''U'')

[Code] ...........

View 8 Replies


ADVERTISEMENT

Integration Services :: Create SSIS Package Dynamically For Inserting Data From Flat File To Table?

Sep 30, 2015

I have requirement like  to develop dynamic package for inserting data from flat file to table.

Find below points for more clarification :--

1) if I changed the flat file values and name  in source variable AND  the table name should be also changed based on variable value .

2) it should dynamically mapped with column values with source file as we have to insert data in target table.

See below diagram for more clarification.

View 10 Replies View Related

Integration Services :: Create Excel With Sheet-name And Column Name Dynamically?

Jun 27, 2015

how to create excel with sheetname and column name dynamically

View 4 Replies View Related

Integration Services :: Create Excel Sheet Dynamically In For Each Loop Container

Jun 4, 2014

I have a for each loop(ADO Enumerator) container which executes for each Advertiserid which is coming from database. In for each loop I have to create a new excel file with the advertiser name. So if the loop executes 7 times there should be seven excel spreadsheets with seven advertiser names.

How can i create an excel dynamically in the foreach loop container.

View 10 Replies View Related

Create Target Table Dynamically Based On Source Table Schema?

Sep 13, 2005

I’ve got a situation where the columns in a table we’re grabbing from a source database keep changing as we need more information from that database. As new columns are added to the source table, I would like to dynamically look for those new columns and add them to our local database’s schema if new ones exist. We’re dropping and creating our target db table each time right now based on a pre-defined known schema, but what we really want is to drop and recreate it based on a dynamic schema, and then import all of the records from the source table to ours.It looks like a starting point might be EXEC sp_columns_rowset 'tablename' and then creating some kind of dynamic SQL statement based on that. However, I'm hoping someone might have a resource that already handles this that they might be able to steer me towards.Sincerely, 
Bryan Ax

View 9 Replies View Related

Integration Services :: Split CSV File Based On First Column Value Changes And Load Into Destination Table In SSIS?

Jun 10, 2015

Import.csv file looks like,

TABLE_NAME DESC CODE
tab1 table1 A
tab1 table1 B
tab1 table1 C
tab2 table2 D
tab2 table2 E
tab2 table2 G...

First column values are table names which are already exists in target database. Next two columns[Desc],[Code] data gets populate from CSV file to table.

In this scenario, how to load tab1 data into the same table in destination and so on.

Which way will be more standard to accomplish this task? If its a script task using C#, looking for clear script to identify a value changes in the first column.

View 4 Replies View Related

Integration Services :: SSIS Dynamically Export Tables To Raw Files

Nov 16, 2011

Every day an application creates new tables and dumps static info into them. 

I would like to create a package to dynamically export those database tables to raw files for long term archive, one file per table. Here is what I have so far and the issue I am having.

1)     Get a list of un-archived tables.
2)     Foreach table do the following.

a. Export the table into raw file.
b. Zip the raw file.
c. Update archive tracking table.

As long as the metadata for each table is the same this package seems to work fine. However, I have many tables with different metadata.  How can I dynamically get the package update the metadata column collection when it hits a new table? When it hits a table with different metadata I am getting warnings like this:

The column "some_column" needs to be added to the external metadata column collection.

The "external metadata column "someother_column" (103)" needs to be removed from the external metadata column collection.

Then I get this error:
Error: 0xC004706B at dump the table into a raw file, DTS.Pipeline: "component "OLE DB Source" (1)" failed validation and returned validation status "VS_NEEDSNEWMETADATA"

View 7 Replies View Related

Integration Services :: Database Creation Dynamically In Server From SSIS

Jul 31, 2015

I need to create a DB dynamically in the Server from the SSIS only....

How to implement this....

View 4 Replies View Related

Integration Services :: Execute Stored Procedures In Parallel And Dynamically?

Feb 19, 2015

I have a stored procedure. It can be executed like this

exec test @p = 1;
exec test @p = 2
exec test @p = n;
n can be hundred.

I want the sp being executed in parallel, not sequence. It means the 3 examples above can be run at the same time.
If I know the number in advance, say 3, I can create 3 different Execution SQL Tasks. They can be run in parallel.

However, the n is not static. It is coming from a table. 

How can I execute Stored Procedures in PARALLEL and DYNAMICALLY ?

I think about using script task. In the script, I get the value of n, and the list of p, from the table, then running a loop with. In the loop, I create a threat and in the threat, I execute the sp like : exec test @p = p. So the exec test may be run parallel. But I am not sure if it works.

View 4 Replies View Related

Integration Services :: Dynamically Setting Values Using XMLConfig Files

Sep 29, 2015

I have developed an SSIS package which extracts and creates 5 flat files and finally using Process Extraction task zip the folder. On my Dev environment everything is working fine but when I am moving to SIT and UAT, not able to set up jobs dynamically by importing XMLConfig file.I created variables and assigned values but still it doesnt take.Below are varaibles I created for flat file destination, Arguments and Working Directory (for zipping)On UAT when I go to SQLAgentJobs to set, import .dtsx file, XML config file....the new values doesnt appear. why ?DataSource is taking always dev location....why ? How can I set it up to take dynamic values what I mentioned in config file ?

View 14 Replies View Related

Integration Services :: How To Handle Dynamically Changing Source Columns

Jun 29, 2015

I have a scenario where we have to handle dynamically changing source columns.

For example , some times in the source files the number of columns will be increased or decreased, new columns can be added in the middle or in the end of the source file.

How to handle this kind of scenario in the SSIS ?

View 9 Replies View Related

Delete Record Based On Existence Of Another Record In Same Table?

Jul 20, 2005

Hi All,I have a table in SQL Server 2000 that contains several million memberids. Some of these member ids are duplicated in the table, and eachrecord is tagged with a 1 or a 2 in [recsrc] to indicate where theycame from.I want to remove all member ids records from the table that have arecsrc of 1 where the same member id also exists in the table with arecsrc of 2.So, if the member id has a recsrc of 1, and no other record exists inthe table with the same member id and a recsrc of 2, I want it leftuntouched.So, in a theortetical dataset of member id and recsrc:0001, 10002, 20001, 20003, 10004, 2I am looking to only delete the first record, because it has a recsrcof 1 and there is another record in the table with the same member idand a recsrc of 2.I'd very much appreciate it if someone could help me achieve this!Much warmth,Murray

View 3 Replies View Related

Reporting Services :: How To Arrange Dynamically Column Based On Parameter In SSRS 2008 R2

Apr 19, 2013

i have below table in DB

DB Table
ID
Column
Row data
1
Supplier CODE
1001
2
Supplier Name
ACB
3
Product
7K7
4
Price
1000

now I create  one report parameter order1 IF I will   give order1.value=1,2,3 then Report will come like this :--

Suppliercode

Supplier Name

Product

1001

ACB

7K7

IF I will   give order1.value=3,2,1 then Report will come like this :--

Product

Supplier Name

Suppliercode

7K7

ACB

1001

IF I will   give order1.value=1,3 then Report will come like this :--

Suppliercode

Product

1001

7K7

View 35 Replies View Related

Integration Services :: Load Multiple Flat Files Into Destination Dynamically?

May 29, 2015

how do you load the multiple flat files  to into destination dynamically?

View 9 Replies View Related

Integration Services :: SSIS Add / Remove Columns Dynamically Into Unpivot Object

Jul 9, 2015

I've created a SSIS package which takes a matrix from Excel file and insert into SQL table. It works perfectly! However, if I would add a new column into that matrix in Excel. Unpivot tool should take into process dynamically. Is there a way to provide this automatically? 

View 4 Replies View Related

Integration Services :: Generate CSV File Dynamically With A Fixed Amount Of Rows

Jun 5, 2015

I need to generate a csv file from another csv file, seems to be simple but let's go the trick thing:

Needs to have maximum 1000 lines, if I reach to this, I need to create another csv and fill that new one.

Exemplifying:

I have a csv file called fileA and this has 2000 lines and another csv called fileB with 1500 lines.

I need to loop a folder and get the fileA, create an output called FileAOutput and start to fill that, if I reach to 1000 lines, I need to create a FileAOutput_2 and fill the other 1000 lines...so I'll go to fileB and do the same thing, but in the second case, I'll have 500 lines in the second output.

View 5 Replies View Related

Integration Services :: Send HTML Email With Multiple Attachments Dynamically

Sep 30, 2015

I am using the below code to send HTML Email body to multiple recipients and CC, its working fine. Now i have to attach multiple files in that mail. Is there any possibilities to attach multiple files with the below code else provide any other code to achieve this task.

Code:
/*
   Microsoft SQL Server Integration Services Script Task
   Write scripts using Microsoft Visual C# 2008.
   The ScriptMain is the entry point class of the script.

[code]...

View 6 Replies View Related

Integration Services :: Passing Connection String Dynamically To SSIS Package

Jul 12, 2012

I have create packages which loads the data from flat file to sql server table, now I want to make my destination table connection dynamic what is format of connection string. I also need to pass user name and password for sql server dynamically in this case, what is the format for the connection string.

Also  in package i used ADO.net  as source for  *.mdb files how i can set the commection to .mdb files dynamically which is used as source in my package.

View 8 Replies View Related

Integration Services :: Load Excel File Dynamically With Different Columns And Worksheet Names

Apr 2, 2014

 I have a situation where I want to load the Excel file dynamically, and the excel file have different columns or even worksheet name. How I could approach this? I believe there's no way to modify the meta data (specifically the mapping) in the data flow.

View 6 Replies View Related

Integration Services :: Assign Values To Variables Dynamically In 2012 SSIS Package

Jul 16, 2015

Can I assign values to variables in 2012 using below command? I have used the same command in 2008 and it works fine.

DTEXEC
/SERVER"XXXXXXXXSQLSERVER2012"/SQL"Mypackage.dtsx"/SETPackage.Variables[FilePath].Value;"C:Test estvariable.csv"

Wondering is there a different way in 2012 to pass values to variables dynamically.

View 2 Replies View Related

Create Table Dynamically

Apr 14, 2008

Hi,

I have N1 table where columns name(id,Field). Base on the fields of this table I want to create N2 table from SP where data from N1 will be columns in N2.
id Field
-- ------
1 ID
2 First
3 Last

Create table N2(ID,First,Last)

regards,
Mark

View 6 Replies View Related

Integration Services :: Filter Data Based On Particular Format

Jul 27, 2015

I am loading data from Source to Target SQL table.

Source data format:
#19067;#90;#19068;#91;#19069;#92;#19070;#93;
Expected data in Target : 90;91;92;93

can you let me know how this can be achieved through transformations?

View 2 Replies View Related

Integration Services :: Move Files Based On Filename?

May 12, 2015

I have a requirement to move files from HOLD folder to input folder. In HOLD folder I receive multiple files starting with af, ai, ar i.e. af*.txt , ai*.txt, ar*.txt . I need to move one file at a time to input folder as each file is to be loaded into database before next file is processed. In all the files the SSIS has to look at ai*.txt files first followed by af*.txt and lastly ar*.txt. If there are multiple files of same group the file with oldest date has to be moved first. How do I achieve this?

View 5 Replies View Related

Dynamic Create Table, Create Index Based Upon A Given Database

Jul 20, 2005

Can I dynamically (from a stored procedure) generatea create table script of all tables in a given database (with defaults etc)a create view script of all viewsa create function script of all functionsa create index script of all indexes.(The result will be 4 scripts)Arno de Jong,The Netherlands.

View 1 Replies View Related

T-SQL (SS2K8) :: Create Table Dynamically?

Sep 5, 2014

I am having SP which gives, two result sets. The columns which are coming from result sets are also dynamic.
i.e. some time 5 columns and some time 10 columns.

Now I want to load this output into 2 different tables on daily basis. This would be truncate/delete table and load again.

Now my problem is that as I am not sure about columns, Is it possible to create table(Physical Table) depends on output of SP, and after load data into it.

During each load we can drop table, No issue and we can handle this through SSIS Package.

View 2 Replies View Related

Create Table And Column Dynamically

Jun 2, 2008

Hi,
There is a table exists in a database, I have to write a stored procedure to create the same table in different database, with the same column name and field. This should be done in runtime. Is it possible. The table will be passed as a parameter to the stored procedure.

View 3 Replies View Related

How To Dynamically Create Columns For A Table

Apr 29, 2008

Hi,
how to dynamically create columns for a table

View 2 Replies View Related

Integration Services :: Merge Inner Join Gives Different Output Based On Sort Key?

Sep 23, 2015

In the first image as can be seens i have 2 different data sources and then they are being joined using "Merge Inner Join". The "sort" is on BusinessEntityID column of Person table and "Sort1" is on "PersonID" of Customer table. The merge join of these 2 result in 19,119 rows.

On the other hand, if i use single data source and use a query with inner join on  tables  used in the first image (ie. 2 tables being used in 2 different data sources) as depicted in second image. Also,  since merge cannot operate without SortKey i have defined TerritoryID as sort key in the advanced editor. The number of rows i get after this is "10,274". My select query was :

SELECT
P.BusinessEntityID,
P.PersonType,
P.Title,
P.FirstName,
P.MiddleName,
P.LastName,
P.Suffix,
C.TerritoryID
FROM stg.Person AS P
INNER JOIN stg.Customer AS C ON C.CustomerID = P.BusinessEntityID
ORDER BY C.TerritoryID;

According to me, it should have been the same as in first case i am using merge inner join and in second case i am using SELECT query with inner join. Upon drilling down i found that in the first case , my sort keys are BusinessEntityID  and PersonID, if i modify this to CustomerID  and BusinessEntityID as this is my join condition (in ithe inner join query shown above), i get the desired output. What i was wondering was, how  the sort order change the Join Condition?

View 3 Replies View Related

Is It Possible To Dynamically Create Columns In A Table In SSRS

Nov 19, 2007



Hi,
I have a sproc that returns somevalues and everything is working fine... and in my reports i am assigning the header data (in a detail column) based on the some feilds in the sproc... and there around 20 feilds that i want to show... but at a given time i am pretty sure that there wont be more than 10 fields that will have data.

So is it possible that show only the columns that have data in it and sometimes if there is less that 5 - 6 fields.. i want to realign the widths in those tables..

any help is appreciated..
Regards
Karen


View 9 Replies View Related

Create Destination Table Dynamically In A Program

Feb 17, 2007

HI,

I'm programmatically able to import data between tables when the Destination table already exists but when Detination table has to be created on the fly (Name will be provided), I'm not successful in doing so.

Basically the requirement is to dump the resultset from the source in to a temp table so that the temp (Destination) table matches the Source's Schema exactly.

Has anybody done that?

Any help in this regard is greatly appreciated.

Pavan



View 3 Replies View Related

Triggering SSIS Package Based On A File Existence

Apr 29, 2008

I am trying to trigger SSIS package if a file is present in a folder, can I use any of the SSIS tasks in doing that??
Thanks!!

View 13 Replies View Related

Exec SSIS Package Based On A File Existence

May 13, 2008

I am not sure if we can execute SSIS package using t-sql, but I want to do the following:
Step1. Execute SSIS package1
Step2. Check for the file1.txt in c drive using the below code:
Declare @result int
exec xp_fileexist 'c:file1.txt', @result output
IF (@result = 1) --if file exists exec ssis package
begin
Execute SSIS package2
end

If we can't use t-sql, please let me know how I can do that as part of sql job. Thanks!!

View 1 Replies View Related

Triggering SSIS Package Based On A File Existence

Apr 29, 2008

I am trying to trigger SSIS package if a file is present in a folder, can I use any of the SSIS tasks in doing that??
Thanks!!

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved