Tagged Questions
0
votes
0answers
16 views
XML data source with XSD validation importing the same data despite different files
Please bear with me, I've never used XML as a data source, nor am I familiar with XML itself, so I'm not entirely familiar with the nomenclature.
I have a series of XML files with data to be imported ...
3
votes
1answer
44 views
Convert SQL Server 2012 SSIS package to 2008
I've created a few SSIS packages in SQL Server 2012 that I need to run on SQL Server 2008. I've been told that it is impossible in their current state.
Is there a way to convert the 2012 packages to ...
0
votes
3answers
74 views
SQL agent job step's connection managers
Is there any way with T-SQL to list the connection managers that are used in an SQL Agent Job step?
3
votes
3answers
103 views
Fixed length text file insert into SQL table
I am not a DBA on this one and I will not have access to BCP or anything like that. So I'm wondering if there is still a way to do it in SQL keeping it very basic.
I have 100's of text files that ...
0
votes
0answers
51 views
Transfer tables from multiple servers into one single server [duplicate]
I have to migrate similar tables present in multiple different servers (30+) into our new server using SSIS package. What would be the right approach towards this?
I was successfully able to transfer ...
3
votes
1answer
49 views
What is the safest way to run SSIS on Multiple Named SQL Server Instances?
I have an Active/Active SQL Server 2008 R2 cluster. There are two named instances on each node and no default instance.
I need to deploy SSIS packages to the different instances. To do this, I ...
0
votes
1answer
44 views
SSIS convert DT_WSTR to date
I'm new to SSIS trying to covert data types. I'm pulling data from Google Analytics and loading data into a SQL 2008 database. The date column from Google analytics appears as "DT_WSTR" and the values ...
0
votes
2answers
85 views
Sync Indexes between two tables
I need to automate and Sync only indexes between two tables ( Primary and Stage_Table) within the same database.
I tried using SSIS SQL Server Objects Task, but looks like it works only when we sync ...
1
vote
1answer
38 views
SSIS Suddenly Requires DCOM Permissions
I have recently deployed some SSIS packages. I made a job to run them one after another. However, I am now allowed to use MSDTC. So I chained the jobs together like I have done before, but now I am ...
0
votes
1answer
210 views
How to override SSIS 2008 package config file path?
I have an SSIS 2008 package with a single configuration named Config. I would like to use the dtexec /SET switch to change the path of the config file that it uses so our developers that build the ...
0
votes
0answers
49 views
SSIS Data Flow Task Slows Down
I am trying to transfer data from one table to another in the same database using SSIS. It seems to go fine (I can see the record count going up) until it gets to about 30 million records, then it ...
3
votes
2answers
58 views
Executing Child packages without using master package
I have many Fact and Dimension packages which are executed using vb script . Today one of my seniors proposed that these packages should be executed individually instead of creating a master package ...
1
vote
0answers
115 views
SSIS Merge Join Trouble: 3 tables into 1
So I am having trouble merging three tables into one. All sources are DB, and I am using the MERGE JOIN function. When I join my first two tables, using standard left inner join, it returns the exact ...
5
votes
2answers
207 views
SSIS keeps force changing excel source string to float
There is a column in Excel that is supposed to be text, but SSIS sees numeric text there and automatically makes it double-precision float [DT_R8].
I can change it manually in the Output branch's ...
2
votes
2answers
201 views
SSIS Data Flow Task Excel to SQL table NULL value will not work for small INT datatype
I have a package that imports excel data into a SQL table. One of the columns in the table is of "smallint" data type and it allows for nulls. The excel file column that has the data that is suppose ...
1
vote
2answers
154 views
SSIS Expressions Date Format get just YY for year
I found many articles on date formatting expressions in SSIS but I couldn't find one that specified how to get just the Year such as 13 for 2013 or 12 for 2012... I tried
DATAPART("yy",GETDATE())
...
-1
votes
1answer
115 views
SSIS SQL Result to tab delimited txt file [closed]
I have created a package that Runs a Sql Query and then puts the results into a flat tab delimited txt file.
When I opened the flat file in excel everything was in the right columns but in columns ...
1
vote
1answer
44 views
SQL Server 2012 catalog.executions to sysjobhistory - any way to join them?
I have exhausted my resources and can't find a foolproof way to join the ssisdb.catalog tables to the jobs that run them. Trying to write some custom sprocs to monitor my execution times and rows ...
1
vote
2answers
129 views
SSIS Visual Studio 2008 reference variable scope in expression builder
When creating an SSIS package in Visual Studio 2008, in the expression builder, I know I can reference a variable by using @[User::FilePath] for example but this only works if you are evaluating the ...
5
votes
1answer
304 views
SSIS File System Task Copy File Could not find part of the path error
I have created an SSIS package that uses a File Systen Task (copy) that copies a file from 1 folder to another. The package runs fine when I run it from my local computer but when I import the package ...
2
votes
1answer
191 views
SSIS Package Data Flow Task RollBack on Error
I tried running a package and it failed but I noticed in the output box (in visual studio) that it managed to write 2000+ rows to a table. I was wondering if an SSIS package would roll back the ...
3
votes
0answers
153 views
SSIS Recordset Destination error
I have an SSIS package targeted at SQL Server 2012.
I have it deployed into the Stored Packages in Integration Services and then have a SQL Server Agent job which executes it.
The first part of the ...
4
votes
1answer
75 views
In SSIS 2012, what is the purpose of flagging a package as an “Entry Point” package
In the Visual Studio designer you can right click on a SSIS package and designate it as an "Entry Point" package"
Doing a search I found this page on MSDN which states:
The value of 1 signifies ...
0
votes
2answers
249 views
Advice for ETL from Oracle to SQL Server?
I'm building a data mart in SQL Server which needs to extract each month from an Oracle database. Currently this is done by exporting to flat files, then running all the inserts/updates in SQL ...
2
votes
1answer
212 views
SSIS 2008 Package Configurations being ignored
With the change to package configurations in 2008 compared to 2005 when I specify /ConfigFile something.dtsConfig on the command line, variables defined in the package are keeping their design-time ...
1
vote
2answers
474 views
How to import table's data in MySql from Sql Server?
I am trying to export table from SQL Server 2008 R2 TO MySql 5.5. For this I am using SQL Server Import and Export Wizard of Sql Server 2008, but it is giving this error.
Here this error may be ...
1
vote
1answer
58 views
SQL Data Collector - SSIS required on source server?
For SQL Data Collector:
Do you need SSIS (SQL Server Integration Services) installed on the server that you are collecting data from. Or can the packages be run from another server -- like the one ...
6
votes
2answers
154 views
SSIS Validation slow. OLTP OLAP running on same server
I have four identical servers 2 are used in production and the other two are demo machines/emergency fail over boxes. The more powerful machine a 16 core 127 gigs of Ram hosts both our Datawarehouse ...
2
votes
1answer
118 views
How can I make sure the SQL Server query optimizer uses the exact tables in the query
I have a query running in SQL Server 2008:
select count(*)
from table1 join
table2 on table1.col1 = table2.col2
In the data warehouse where this script runs, a table called thin_table1 is ...
3
votes
1answer
58 views
Integration Services Catalogs - Version Description
In Integration Services Catalogs I can see what versions of a package/project are available and if necessary revert to an older version. On this dialog I see a field "description" however I found no ...
0
votes
1answer
179 views
Import batches of rows from Excel in parallel
We receive an Excel file weekly currently containing about 250k rows. The file grows by about 300 rows each week.
In SSIS I've hooked up an Excel source to an OLE DB destination to import the file ...
2
votes
0answers
311 views
Importing Excel files with SSIS
I'm trying to import an Excel spreadsheet into SQL Server 2012 using SSIS. There is only one sheet in the file. The file is a SalesForce export. I believe the Sheet name changes depending on when the ...
1
vote
1answer
156 views
How to avoid log space error while executing SSIS packages?
I'm executing ssis package with more than 70 million data's. But I'm facing issue as
log space full error message while executing the package.
Is there any option to resolve this issue?
0
votes
0answers
312 views
Using SSIS to download an attachment from Exchange/Outlook
I am recieving a weekly email that includes an attachment of an excel file. I'd rather not how to manually drop this email in a file location or manually update the data. I'd like to use SSIS to ...
6
votes
1answer
468 views
Timeout expired error while using copy database wizard
Microsoft made me believe that SQL Server 2012 Copy Database Wizard is the most optimal way of copying a SQL Server 2000 database to SQL Server 2012. After struggling a few hours, I was able to get ...
4
votes
1answer
1k views
ETL: extracting from 200 tables - SSIS data flow or custom T-SQL?
Based on my analysis, a complete dimensional model for our data warehouse will require extraction from over 200 source tables. Some of these tables will be extracted as part of an incremental load and ...
0
votes
0answers
201 views
SSIS package to create maintenance plan fails silently
Here's what I did:
Connect to SSIS on server A (from management studio on server B, click connect, choose integration services, choose server A)
Find required maintenance plan
Right click and export ...
3
votes
1answer
380 views
Can I connect to SQL Server 2008 R2 database from SSIS 2012?
I need to redevelop an ETL process and I'm evaluating options.
I'm running SQL Server 2008 R2 database, and SSIS 2008 will take too long to develop and test the required packages. I understand that ...
2
votes
2answers
512 views
Average Time to Load 1.2 million records from Oracle INTO SQL SERVER 2008R2
I have an ssis package which loads 1.2 million records of data from oracle into SQL SERVER 2008R2(with 32 GB RAM) dataware house table once every week. (truncating and loading)
This process is taking ...
2
votes
2answers
189 views
SQL Server imports data in wrong charset with SSIS
I have a flat file which I am importing into a SQL Server table using a SSIS package. The code page is set to 65001.
However, when I run a
SELECT * FROM tableName
the data appears mangled. For ...
4
votes
1answer
152 views
Calling a .NET method in a SQL Server script
In the data migration process, I need to convert some columns values form the source system before writing to the target table.
I've been provided with a Csharp code which reads a string and do the ...
0
votes
1answer
68 views
Logging of incorrect row in SQL Server Integration Services
I newbie to SQL Server Integration Services and I have a problem:
Data loaded from file looks like this:
1 'a' 'b'
2 'a' 'b'
1 'c' 'd'
And this rows would be loaded in table like this:
CREATE ...
2
votes
1answer
186 views
SQL column goes from first to last position in output
I have an SQL query inside a SQL Server Integration Services (SSIS) package where the preview shows the column order correctly, however the actual output uses a different column order.
This is ...
1
vote
3answers
408 views
Encrypt/Decrypt output & input for SSIS task
I use SSIS packages to output the data from one system as Comma Separated Values, and then import that same data into a second system. I have a new requirement to encrypt the CSV text files that are ...
5
votes
2answers
750 views
Running SSIS from a different server than the primary database instance
We currently have a single SQL Server Integration Services (SSIS) package that performs an extract-transfer-load (ETL) process to move some data from SQL Server to DB2. On the current production ...
1
vote
2answers
393 views
Moving data from Oracle to SQL Server : Table size optimization
I am loading data using SSIS from Oracle to an SQL Server database. One table I moved had a size of 4 MB under Oracle but when loaded to SQL Server, the size grows to 7.96 MB!
I analyzed the size ...
1
vote
1answer
509 views
SSIS: “The buffer manager cannot write 8 bytes to file”
We have about 60 or more SSAS cubes on one server. All the cubes have the same structure. Each cube has a different size because each cube has a different underlying data warehouse. However the schema ...
1
vote
2answers
103 views
How do I run a SQL Server 2005 job on Windows 7?
I have Windows 7 and SQL Server 2005 Version 9.00.1399.
I can successfully create an SSIS Package, however I am unable to execute it successfully as a scheduled job. When I run the job, SQL Agent ...
3
votes
1answer
311 views
What is a “Reporting Task for Subplan” task in SSIS, and how can I create one?
I'm trying to script a daily full backup to a job using an SSIS package. I've inherited several databases from a previous DBA, and I'm trying to emulate the ones he made in order to be consistent. ...
5
votes
2answers
421 views
Passing values to variables defined in SSIS package at run time
I need to pass values to the variables in a SSIS package at run time. Obviously it is not practical to open BIDS and set a variable every time the package needs to be run. We run it on SQL Server 2008 ...
